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.

Start CockroachDB

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

Create a free trial cluster

Create a CockroachDB Cloud account. If this is your first CockroachDB Cloud organization, it will be credited with $400 in free trial credits to get you started.
On the Get Started page, click Create cluster.
On the Select a plan page, select Standard.
On the Cloud & Regions page, select a cloud provider (GCP or AWS).
In the Regions section, select a region for the cluster. Refer to CockroachDB Cloud Regions for the regions where CockroachDB Standard clusters can be deployed. To create a multi-region cluster, click Add region and select additional regions.
Click Next: Capacity.
On the Capacity page, keep the Provisioned capacity at the default value of 2 vCPUs.

Click Next: Finalize.
On the Finalize page, name your cluster. If an active free trial is listed in the right pane, you will not need to add a payment method, though you will need to do this by the end of the trial to maintain your organization's clusters.

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.

Click the Choose your OS dropdown, and select the operating system of your local machine.
Click the Connection string tab in the Connection info dialog.
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.
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 for the cluster, found at https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users.

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

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.

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

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:
```
$ cockroach sql \
--url='<connection-string>'
```
In the SQL shell, create the bank database that your application will use:
```
> CREATE DATABASE bank;
```
Exit the SQL shell:
```
> \q
```

Set the connection string

Choose your OS:

Set a DATABASE_URL environment variable to your connection string.

export DATABASE_URL="{connection string}"

Set a DATABASE_URL environment variable to your connection string.

$env:DATABASE_URL = "{connection string}"

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:

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));
      }
    }
  }
}

Run the basic example

Compile and run the code:

dotnet run

The output should be:

Initial balances:
 account 1: 1000
 account 2: 250

Transaction example (with retry logic)

Modify the code

Open cockroachdb-test-app/Program.cs again and replace the contents with the code shown below.

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 transactions example

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:

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