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

On this page

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

Note:

Organizations without billing information on file can only create one CockroachDB Basic cluster.

If you haven't already, sign up for a CockroachDB Cloud account.
Log in to your CockroachDB Cloud account.
On the Clusters page, click Create cluster.
On the Select a plan page, select Basic.
On the Cloud & Regions page, select a cloud provider (GCP or AWS) in the Cloud provider section.
In the Regions section, select a region for the cluster. Refer to CockroachDB Cloud Regions for the regions where CockroachDB Basic clusters can be deployed. To create a multi-region cluster, click Add region and select additional regions.
Click Next: Capacity.
On the Capacity page, select Start for free. Click Next: Finalize.
On the Finalize page, 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

Once your cluster is created, the Connect to cluster-name dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

In your terminal, run the second command from the dialog to create a new certs directory on your local machine and download the CA certificate to that directory:
```
curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/{cluster-id}/cert
```
Your cert file will be downloaded to ~/.postgresql/root.crt.
```
curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/{cluster-id}/cert
```
Your cert file will be downloaded to ~/.postgresql/root.crt.
```
mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/{cluster-id}/cert -OutFile $env:appdata\.postgresql\root.crt
```
Your cert file will be downloaded to %APPDATA%/.postgresql/root.crt.
Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

Warning:

This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the SQL Users page for the cluster, found at https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users.
```
cockroach sql --url 'postgresql://<username>:<password>@<cluster-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
```
```
cockroach sql --url 'postgresql://<username>:<password>@<cluster-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
```
```
cockroach sql --url "postgresql://<username>:<password>@<cluster-host>:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt"
```
Where:
- <username> is the SQL user. By default, this is your CockroachDB Cloud account username.
- <password> is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.
- <cluster-hostname> is the hostname of your CockroachDB Cloud cluster.
- <cluster-id> is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.
You can find these settings in the Connection parameters tab of the Connection info dialog.

If you haven't already, download the CockroachDB binary.
Run the cockroach demo command:
```
$ 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.

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:

$ dotnet new console -o cockroachdb-test-app

$ 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:

$ dotnet add package Npgsql

Step 4. Create a database

In the SQL shell, create the bank database that your application will use:
```
> CREATE DATABASE bank;
```
Create a SQL user for your app:
```
> CREATE USER <username> WITH PASSWORD <password>;
```
Take note of the username and password. You will use it in your application code later.
Give the user the necessary permissions:
```
> GRANT ALL ON DATABASE bank TO <username>;
```

If you haven't already, download the CockroachDB binary.
Start the built-in SQL shell using the connection string you got from the CockroachDB Cloud Console earlier:
```
$ cockroach sql \
--url='postgres://<username>:<password>@<global host>:26257/<cluster_name>.defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/cc-ca.crt'
```
In the connection string copied from the CockroachDB Cloud Console, your username, password and cluster name are pre-populated. Replace the <certs_dir> placeholder with the path to the certs directory that you created earlier.
In the SQL shell, create the bank database that your application will use:
```
> CREATE DATABASE bank;
```
Exit the SQL shell:
```
> \q
```

Step 5. Run the C# code

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

Create a table and insert some rows
Execute a batch of statements as a transaction

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.

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.SslMode = SslMode.VerifyFull;
      string? databaseUrlEnv = Environment.GetEnvironmentVariable("DATABASE_URL");
      if (databaseUrlEnv == null) {
        connStringBuilder.Host = "localhost";
        connStringBuilder.Port = 26257;
        connStringBuilder.Username = "{username}";
        connStringBuilder.Password = "{password}";
      } else {
        Uri databaseUrl = new Uri(databaseUrlEnv);
        connStringBuilder.Host = databaseUrl.Host;
        connStringBuilder.Port = databaseUrl.Port;
        var items = databaseUrl.UserInfo.Split(new[] { ':' });
        if (items.Length > 0) connStringBuilder.Username = items[0];
        if (items.Length > 1) connStringBuilder.Password = items[1];
      }
      connStringBuilder.Database = "bank";
      Simple(connStringBuilder.ConnectionString);
    }

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

        // Create the "accounts" table.
        using (var cmd = new NpgsqlCommand("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)", conn))
        {
          cmd.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));
      }
    }
  }
}

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.VerifyFull;
      connStringBuilder.Username = "{username}";
      connStringBuilder.Password = "{password}";
      connStringBuilder.Database = "bank";
      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:

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.

In the CockroachDB Cloud Console, select the Connection Parameters tab of the Connection Info dialog.
In a text editor, modify the connection parameters in Program.cs with the settings to connect to your cluster:

connStringBuilder.Host = "{host-name}";
connStringBuilder.Port = 26257;
connStringBuilder.SslMode = SslMode.Require;
connStringBuilder.Username = "{username}";
connStringBuilder.Password = "{password}";
connStringBuilder.Database = "{cluster-name}.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 Serverless host (e.g., free-tier.gcp-us-central1.cockroachlabs.cloud).
{cluster_name} is the name of your cluster.

Run the code

Compile and run the code:

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.

using System;
using System.Data;
using System.Net.Security;
using Npgsql;

namespace Cockroach
{
  class TransactionsClass
  {
    static void Main(string[] args)
    {
      var connStringBuilder = new NpgsqlConnectionStringBuilder();
      connStringBuilder.SslMode = SslMode.VerifyFull;
      // use the DATABASE_URL environment variable if it is set
      string? databaseUrlEnv = Environment.GetEnvironmentVariable("DATABASE_URL");
      if (databaseUrlEnv == null) {
        connStringBuilder.Host = "localhost";
        connStringBuilder.Port = 26257;
        connStringBuilder.Username = "{username}";
        connStringBuilder.Password = "{password}";
      } else {
        Uri databaseUrl = new Uri(databaseUrlEnv);
        connStringBuilder.Host = databaseUrl.Host;
        connStringBuilder.Port = databaseUrl.Port;
        var items = databaseUrl.UserInfo.Split(new[] { ':' });
        if (items.Length > 0) connStringBuilder.Username = items[0];
        if (items.Length > 1) connStringBuilder.Password = items[1];
      }
      connStringBuilder.Database = "bank";
      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));
      }
    }
  }
}

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.VerifyFull;
      connStringBuilder.Username = "{username}";
      connStringBuilder.Password = "{password}";
      connStringBuilder.Database = "bank";
      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:

$ 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:

$ 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:

Was this helpful?

Pricing

Contact us

Sign In

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

Step 1. Start CockroachDB

Create a free cluster

Set up your cluster connection

Step 2. Create a .NET project

Step 3. Install the Npgsql driver

Step 4. Create a database

Step 5. Run the C# code

Basic example

Get the code

Update the connection parameters

Run the code

Transaction example (with retry logic)

Get the code

Run the code

What's next?