Build a C# (.NET) App with CockroachDB

On this page Carat arrow pointing down
Warning:
As of April 12, 2019, CockroachDB v1.1 is no longer supported. For more details, refer to the Release Support Policy.

This tutorial shows you how build a simple C# (.NET) application with CockroachDB using a PostgreSQL-compatible driver.

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

Before You Begin

Make sure you have already installed CockroachDB and the .NET SDK for your OS.

Step 1. Create a .NET project

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 2. 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 3. Start a single-node cluster

For the purpose of this tutorial, you need only one CockroachDB node running in insecure mode:

icon/buttons/copy
$ cockroach start \
--insecure \
--store=hello-1 \
--host=localhost

Step 4. Create a user

In a new terminal, as the root user, use the cockroach user command to create a new user, maxroach.

icon/buttons/copy
$ cockroach user set maxroach --insecure

Step 5. Create a database and grant privileges

As the root user, use the built-in SQL client to create a bank database.

icon/buttons/copy
$ cockroach sql --insecure -e 'CREATE DATABASE bank'

Then grant privileges to the maxroach user.

icon/buttons/copy
$ cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 6. Run the C# code

Now that you have a database and a user, you'll run code to create a table and insert some rows, and then you'll run code to read and update values as an atomic transaction.

Basic Statements

Replace the contents of cockraochdb-test-app/Program.cs with the following code:

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

namespace Cockroach
{
  class MainClass
  {
    static void Main(string[] args)
    {
      var connStringBuilder = new NpgsqlConnectionStringBuilder();
      connStringBuilder.Host = "localhost";
      connStringBuilder.Port = 26257;
      connStringBuilder.Username = "maxroach";
      connStringBuilder.Database = "bank";
      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));
      }
    }
  }
}

Then run the code to connect as the maxroach user and execute some basic SQL statements, creating a table, inserting rows, and reading and printing the rows:

icon/buttons/copy
$ dotnet run

The output should be:

Initial balances:
    account 1: 1000
    account 2: 250

Transaction (with retry logic)

Open cockraochdb-test-app/Program.cs again and replace the contents with the following code:

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

namespace Cockroach
{
  class MainClass
  {
    static void Main(string[] args)
    {
      var connStringBuilder = new NpgsqlConnectionStringBuilder();
      connStringBuilder.Host = "localhost";
      connStringBuilder.Port = 26257;
      connStringBuilder.Username = "maxroach";
      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));
      }
    }
  }
}

Then run the code to again connect as the maxroach user but this time 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
Note:
With the default SERIALIZABLE isolation level, CockroachDB may require the client to retry a transaction in case of read/write contention. CockroachDB provides a generic retry function that runs inside a transaction and retries it as needed. You can copy and paste the retry function from here into your code.

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 -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| 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 using a local cluster to explore the following CockroachDB benefits:


Yes No
On this page

Yes No