Build a C# App with CockroachDB and the .NET Npgsql Driver

On this page Carat arrow pointing down
Warning:
Cockroach Labs will stop providing Assistance Support for v21.2 on May 16, 2023. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

This tutorial shows you how build a simple C# application with CockroachDB and the .NET Npgsql driver.

We have tested the .NET Npgsql driver enough to claim beta-level support. If you encounter problems, please open an issue with details to help us make progress toward full support.

Step 1. Start CockroachDB

Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachDB serverless. The instructions below will adjust accordingly.

Create a free cluster

  1. If you haven't already, sign up for a CockroachDB Cloud account.
  2. Log in to your CockroachDB Cloud account.
  3. On the Clusters page, click Create Cluster.
  4. On the Create your cluster page, select Serverless.

    Unless you change your monthly budget, this cluster will be free forever.

  5. Click Create cluster.

    Your cluster will be created in a few seconds and the Create SQL user dialog will display.

Set up your cluster connection

The Connection info dialog shows information about how to connect to your cluster.

  1. Click the Choose your OS dropdown, and select the operating system of your local machine.

  2. Click the Connection string tab in the Connection info dialog.

  3. Open a new terminal on your local machine, and run the command provided in step 1 to download the CA certificate. This certificate is required by some clients connecting to CockroachDB Cloud.

  4. Copy the connection string provided in step 2 to a secure location.

    Note:

    The connection string is pre-populated with your username, password, cluster name, and other details. Your password, in particular, will be provided only once. Save it in a secure place (Cockroach Labs recommends a password manager) to connect to your cluster in the future. If you forget your password, you can reset it by going to the SQL Users page.

  1. If you haven't already, download the CockroachDB binary.
  2. Run the cockroach demo command:

    icon/buttons/copy
    $ cockroach demo \
    --no-example-database
    

    This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster. Any changes to the database will not persist after the cluster is stopped.

    Note:

    If cockroach demo fails due to SSL authentication, make sure you have cleared any previously downloaded CA certificates from the directory ~/.postgresql.

  3. Take note of the (sql) connection string in the SQL shell welcome text:

    # Connection parameters:
    #   (webui)    http://127.0.0.1:8080/demologin?password=demo76950&username=demo
    #   (sql)      postgres://demo:demo76950@127.0.0.1:26257?sslmode=require
    #   (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257
    

Step 2. Create a .NET project

In your terminal, run the following commands:

icon/buttons/copy
$ dotnet new console -o cockroachdb-test-app
icon/buttons/copy
$ cd cockroachdb-test-app

The dotnet command creates a new app of type console. The -o parameter creates a directory named cockroachdb-test-app where your app will be stored and populates it with the required files. The cd cockroachdb-test-app command puts you into the newly created app directory.

Step 3. Install the Npgsql driver

Install the latest version of the Npgsql driver into the .NET project using the built-in nuget package manager:

icon/buttons/copy
$ dotnet add package Npgsql

Step 4. Create a database

  1. In the SQL shell, create the bank database that your application will use:

    icon/buttons/copy
    > CREATE DATABASE bank;
    
  2. Create a SQL user for your app:

    icon/buttons/copy
    > CREATE USER <username> WITH PASSWORD <password>;
    

    Take note of the username and password. You will use it in your application code later.

  3. Give the user the necessary permissions:

    icon/buttons/copy
    > GRANT ALL ON DATABASE bank TO <username>;
    
  1. If you haven't already, download the CockroachDB binary.
  2. Start the built-in SQL shell using the connection string you got from the CockroachDB Cloud Console:

    icon/buttons/copy
    $ cockroach sql \
    --url='<connection-string>'
    
  3. In the SQL shell, create the bank database that your application will use:

    icon/buttons/copy
    > CREATE DATABASE bank;
    
  4. Exit the SQL shell:

    icon/buttons/copy
    > \q
    

Step 5. Run the C# code

Now that you have set up your project and created a database, in this section you will:

Basic example

Get the code

Replace the contents of the Program.cs file that was automatically generated in your cockroachdb-test-app directory with the code below:

Note:

The following examples use the SSL mode require because the .NET Npgsql driver validates certificates differently from other PostgreSQL drivers. For other drivers, we recommend using verify-full as a security best practice.

icon/buttons/copy
using System;
using System.Data;
using System.Net.Security;
using Npgsql;

namespace Cockroach
{
  class MainClass
  {
    static void Main(string[] args)
    {
      var connStringBuilder = new NpgsqlConnectionStringBuilder();
      connStringBuilder.Host = "localhost";
      connStringBuilder.Port = 26257;
      connStringBuilder.SslMode = SslMode.Require;
      connStringBuilder.Username = "{username}";
      connStringBuilder.Password = "{password}";
      connStringBuilder.Database = "bank";
      connStringBuilder.TrustServerCertificate = true;
      Simple(connStringBuilder.ConnectionString);
    }

    static void Simple(string connString)
    {
      using (var conn = new NpgsqlConnection(connString))
      {
        conn.Open();

        // Create the "accounts" table.
        new NpgsqlCommand("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)", conn).ExecuteNonQuery();

        // Insert two rows into the "accounts" table.
        using (var cmd = new NpgsqlCommand())
        {
          cmd.Connection = conn;
          cmd.CommandText = "UPSERT INTO accounts(id, balance) VALUES(@id1, @val1), (@id2, @val2)";
          cmd.Parameters.AddWithValue("id1", 1);
          cmd.Parameters.AddWithValue("val1", 1000);
          cmd.Parameters.AddWithValue("id2", 2);
          cmd.Parameters.AddWithValue("val2", 250);
          cmd.ExecuteNonQuery();
        }

        // Print out the balances.
        System.Console.WriteLine("Initial balances:");
        using (var cmd = new NpgsqlCommand("SELECT id, balance FROM accounts", conn))
        using (var reader = cmd.ExecuteReader())
          while (reader.Read())
            Console.Write("\taccount {0}: {1}\n", reader.GetValue(0), reader.GetValue(1));
      }
    }
  }
}

icon/buttons/copy
using System;
using System.Data;
using System.Net.Security;
using Npgsql;

namespace Cockroach
{
  class MainClass
  {
    static void Main(string[] args)
    {
      var connStringBuilder = new NpgsqlConnectionStringBuilder();
      connStringBuilder.Host = "{host-name}";
      connStringBuilder.Port = 26257;
      connStringBuilder.SslMode = SslMode.Require;
      connStringBuilder.Username = "{username}";
      connStringBuilder.Password = "{password}";
      connStringBuilder.Database = "{cluster-name}.bank";
      connStringBuilder.RootCertificate = "~/.postgres/root.crt";
      connStringBuilder.TrustServerCertificate = true;
      connStringBuilder.ApplicationName = "docs_simplecrud_csharp";
      Simple(connStringBuilder.ConnectionString);
    }

    static void Simple(string connString)
    {
      using (var conn = new NpgsqlConnection(connString))
      {
        conn.Open();

        // Create the "accounts" table.
        new NpgsqlCommand("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)", conn).ExecuteNonQuery();

        // Insert two rows into the "accounts" table.
        using (var cmd = new NpgsqlCommand())
        {
          cmd.Connection = conn;
          cmd.CommandText = "UPSERT INTO accounts(id, balance) VALUES(@id1, @val1), (@id2, @val2)";
          cmd.Parameters.AddWithValue("id1", 1);
          cmd.Parameters.AddWithValue("val1", 1000);
          cmd.Parameters.AddWithValue("id2", 2);
          cmd.Parameters.AddWithValue("val2", 250);
          cmd.ExecuteNonQuery();
        }

        // Print out the balances.
        System.Console.WriteLine("Initial balances:");
        using (var cmd = new NpgsqlCommand("SELECT id, balance FROM accounts", conn))
        using (var reader = cmd.ExecuteReader())
          while (reader.Read())
            Console.Write("\taccount {0}: {1}\n", reader.GetValue(0), reader.GetValue(1));
      }
    }
  }
}

Update the connection parameters

In a text editor, modify Program.cs with the settings to connect to the demo cluster:

icon/buttons/copy
connStringBuilder.Host = "{localhost}";
connStringBuilder.Port = 26257;
connStringBuilder.SslMode = SslMode.Require;
connStringBuilder.Username = "{username}";
connStringBuilder.Password = "{password}";
connStringBuilder.Database = "bank";
connStringBuilder.TrustServerCertificate = true;

Where {username} and {password} are the database username and password you created earlier.

  1. In the CockroachDB Cloud Console, select the Connection Parameters tab of the Connection Info dialog.

  2. In a text editor, modify the connection parameters in Program.cs with the settings to connect to your cluster:

icon/buttons/copy
connStringBuilder.Host = "{host-name}";
connStringBuilder.Port = 26257;
connStringBuilder.SslMode = SslMode.Require;
connStringBuilder.Username = "{username}";
connStringBuilder.Password = "{password}";
connStringBuilder.Database = "{routing-id}.bank";
connStringBuilder.RootCertificate = "~/.postgresql/root.crt";
connStringBuilder.TrustServerCertificate = true;

Where:

  • {username} and {password} specify the SQL username and password that you created earlier.
  • {host-name} is the name of the CockroachDB Cloud free tier host (e.g., free-tier.gcp-us-central1.cockroachlabs.cloud).
  • {routing-id} is your cluster's routing ID (e.g., funky-skunk-123). The routing ID identifies your tenant cluster on a multi-tenant host.

Run the code

Compile and run the code:

icon/buttons/copy
dotnet run

The output should be:

Initial balances:
    account 1: 1000
    account 2: 250

Transaction example (with retry logic)

Get the code

Open cockroachdb-test-app/Program.cs again and replace the contents with the code shown below. Make sure to keep the connection parameters the same as in the previous example.

icon/buttons/copy
using System;
using System.Data;
using System.Net.Security;
using Npgsql;

namespace Cockroach
{
  class MainClass
  {
    static void Main(string[] args)
    {
      var connStringBuilder = new NpgsqlConnectionStringBuilder();
      connStringBuilder.Host = "localhost";
      connStringBuilder.Port = 26257;
      connStringBuilder.SslMode = SslMode.Require;
      connStringBuilder.Username = "{username}";
      connStringBuilder.Password = "{password}";
      connStringBuilder.Database = "bank";
      connStringBuilder.TrustServerCertificate = true;
      Simple(connStringBuilder.ConnectionString);
    }

    static void TransferFunds(NpgsqlConnection conn, NpgsqlTransaction tran, int from, int to, int amount)
    {
      int balance = 0;
      using (var cmd = new NpgsqlCommand(String.Format("SELECT balance FROM accounts WHERE id = {0}", from), conn, tran))
      using (var reader = cmd.ExecuteReader())
      {
        if (reader.Read())
        {
          balance = reader.GetInt32(0);
        }
        else
        {
          throw new DataException(String.Format("Account id={0} not found", from));
        }
      }
      if (balance < amount)
      {
        throw new DataException(String.Format("Insufficient balance in account id={0}", from));
      }
      using (var cmd = new NpgsqlCommand(String.Format("UPDATE accounts SET balance = balance - {0} where id = {1}", amount, from), conn, tran))
      {
        cmd.ExecuteNonQuery();
      }
      using (var cmd = new NpgsqlCommand(String.Format("UPDATE accounts SET balance = balance + {0} where id = {1}", amount, to), conn, tran))
      {
        cmd.ExecuteNonQuery();
      }
    }

    static void TxnSample(string connString)
    {
      using (var conn = new NpgsqlConnection(connString))
      {
        conn.Open();

        // Create the "accounts" table.
        new NpgsqlCommand("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)", conn).ExecuteNonQuery();

        // Insert two rows into the "accounts" table.
        using (var cmd = new NpgsqlCommand())
        {
          cmd.Connection = conn;
          cmd.CommandText = "UPSERT INTO accounts(id, balance) VALUES(@id1, @val1), (@id2, @val2)";
          cmd.Parameters.AddWithValue("id1", 1);
          cmd.Parameters.AddWithValue("val1", 1000);
          cmd.Parameters.AddWithValue("id2", 2);
          cmd.Parameters.AddWithValue("val2", 250);
          cmd.ExecuteNonQuery();
        }

        // Print out the balances.
        System.Console.WriteLine("Initial balances:");
        using (var cmd = new NpgsqlCommand("SELECT id, balance FROM accounts", conn))
        using (var reader = cmd.ExecuteReader())
        while (reader.Read())
          Console.Write("\taccount {0}: {1}\n", reader.GetValue(0), reader.GetValue(1));

        try
        {
          using (var tran = conn.BeginTransaction())
          {
            tran.Save("cockroach_restart");
            while (true)
            {
              try
              {
                TransferFunds(conn, tran, 1, 2, 100);
                tran.Commit();
                break;
              }
              catch (NpgsqlException e)
              {
                // Check if the error code indicates a SERIALIZATION_FAILURE.
                if (e.ErrorCode == 40001)
                {
                  // Signal the database that we will attempt a retry.
                  tran.Rollback("cockroach_restart");
                }
                else
                {
                  throw;
                }
              }
            }
          }
        }
        catch (DataException e)
        {
          Console.WriteLine(e.Message);
        }

        // Now printout the results.
        Console.WriteLine("Final balances:");
        using (var cmd = new NpgsqlCommand("SELECT id, balance FROM accounts", conn))
        using (var reader = cmd.ExecuteReader())
        while (reader.Read())
          Console.Write("\taccount {0}: {1}\n", reader.GetValue(0), reader.GetValue(1));
      }
    }
  }
}

icon/buttons/copy
using System;
using System.Data;
using System.Net.Security;
using Npgsql;

namespace Cockroach
{
  class MainClass
  {
    static void Main(string[] args)
    {
      var connStringBuilder = new NpgsqlConnectionStringBuilder();
      connStringBuilder.Host = "{host-name}";
      connStringBuilder.Port = 26257;
      connStringBuilder.SslMode = SslMode.Require;
      connStringBuilder.Username = "{username}";
      connStringBuilder.Password = "{password}";
      connStringBuilder.Database = "{cluster-name}.bank";
      connStringBuilder.RootCertificate = "~/.postgres/root.crt";
      connStringBuilder.TrustServerCertificate = true;
      TxnSample(connStringBuilder.ConnectionString);
    }

    static void TransferFunds(NpgsqlConnection conn, NpgsqlTransaction tran, int from, int to, int amount)
    {
      int balance = 0;
      using (var cmd = new NpgsqlCommand(String.Format("SELECT balance FROM accounts WHERE id = {0}", from), conn, tran))
      using (var reader = cmd.ExecuteReader())
      {
        if (reader.Read())
        {
          balance = reader.GetInt32(0);
        }
        else
        {
          throw new DataException(String.Format("Account id={0} not found", from));
        }
      }
      if (balance < amount)
      {
        throw new DataException(String.Format("Insufficient balance in account id={0}", from));
      }
      using (var cmd = new NpgsqlCommand(String.Format("UPDATE accounts SET balance = balance - {0} where id = {1}", amount, from), conn, tran))
      {
        cmd.ExecuteNonQuery();
      }
      using (var cmd = new NpgsqlCommand(String.Format("UPDATE accounts SET balance = balance + {0} where id = {1}", amount, to), conn, tran))
      {
        cmd.ExecuteNonQuery();
      }
    }

    static void TxnSample(string connString)
    {
      using (var conn = new NpgsqlConnection(connString))
      {
        conn.Open();

        // Create the "accounts" table.
        new NpgsqlCommand("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)", conn).ExecuteNonQuery();

        // Insert two rows into the "accounts" table.
        using (var cmd = new NpgsqlCommand())
        {
          cmd.Connection = conn;
          cmd.CommandText = "UPSERT INTO accounts(id, balance) VALUES(@id1, @val1), (@id2, @val2)";
          cmd.Parameters.AddWithValue("id1", 1);
          cmd.Parameters.AddWithValue("val1", 1000);
          cmd.Parameters.AddWithValue("id2", 2);
          cmd.Parameters.AddWithValue("val2", 250);
          cmd.ExecuteNonQuery();
        }

        // Print out the balances.
        System.Console.WriteLine("Initial balances:");
        using (var cmd = new NpgsqlCommand("SELECT id, balance FROM accounts", conn))
        using (var reader = cmd.ExecuteReader())
        while (reader.Read())
          Console.Write("\taccount {0}: {1}\n", reader.GetValue(0), reader.GetValue(1));

        try
        {
          using (var tran = conn.BeginTransaction())
          {
            tran.Save("cockroach_restart");
            while (true)
            {
              try
              {
                TransferFunds(conn, tran, 1, 2, 100);
                tran.Commit();
                break;
              }
              catch (NpgsqlException e)
              {
                // Check if the error code indicates a SERIALIZATION_FAILURE.
                if (e.ErrorCode == 40001)
                {
                  // Signal the database that we will attempt a retry.
                  tran.Rollback("cockroach_restart");
                }
                else
                {
                  throw;
                }
              }
            }
          }
        }
        catch (DataException e)
        {
          Console.WriteLine(e.Message);
        }

        // Now printout the results.
        Console.WriteLine("Final balances:");
        using (var cmd = new NpgsqlCommand("SELECT id, balance FROM accounts", conn))
        using (var reader = cmd.ExecuteReader())
        while (reader.Read())
          Console.Write("\taccount {0}: {1}\n", reader.GetValue(0), reader.GetValue(1));
      }
    }
  }
}

Run the code

This time, running the code will execute a batch of statements as an atomic transaction to transfer funds from one account to another, where all included statements are either committed or aborted:

icon/buttons/copy
$ dotnet run

The output should be:

Initial balances:
    account 1: 1000
    account 2: 250
Final balances:
    account 1: 900
    account 2: 350

However, if you want to verify that funds were transferred from one account to another, use the built-in SQL client:

icon/buttons/copy
$ cockroach sql --insecure  --database=bank -e 'SELECT id, balance FROM accounts'
  id | balance
+----+---------+
   1 |     900
   2 |     350
(2 rows)

What's next?

Read more about using the .NET Npgsql driver.

You might also be interested in the following pages:


Yes No
On this page

Yes No