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

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.

## Before you begin

1. Install CockroachDB.
2. Start up a secure or insecure local cluster.
3. Choose the instructions that correspond to whether your cluster is secure or insecure:

## Step 1. Create a .NET project

    $ 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 2. 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 3. Create the `maxroach` user and `bank` database

Start the built-in SQL shell:

    $ cockroach sql --certs-dir=certs

In the SQL shell, issue the following statements to create the `maxroach` user and `bank` database:

    > CREATE USER IF NOT EXISTS maxroach;

    > CREATE DATABASE bank;

Give the `maxroach` user the necessary permissions:

    > GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

    > \q

## Step 4. Generate a certificate for the `maxroach` user

Create a certificate and key for the `maxroach` user by running the following command. The code samples will run as this user.

    $ cockroach cert create-client maxroach --certs-dir=certs --ca-key=my-safe-directory/ca.key

## Step 5. Convert the key file for use by C# programs

The private key generated for user `maxroach` by CockroachDB is PEM encoded. To read the key in a C# application, you will need to convert it into PKCS#12 format.

To convert the key to PKCS#12 format, run the following OpenSSL command on the `maxroach` user's key file in the directory where you stored your certificates:

    $ openssl pkcs12 -inkey client.maxroach.key -password pass: -in client.maxroach.crt -export -out client.maxroach.pfx

As of December 2018, you need to provide a password for this to work on macOS. See https://github.com/dotnet/corefx/issues/24225.

## Step 6. Run the C# code

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

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

### Basic example

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

    using System;
    using System.Data;
    using System.Security.Cryptography.X509Certificates;
    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 = "maxroach";
          connStringBuilder.Database = "bank";
          Simple(connStringBuilder.ConnectionString);
        }

        static void Simple(string connString)
        {
          using (var conn = new NpgsqlConnection(connString))
          {
            conn.ProvideClientCertificatesCallback += ProvideClientCertificatesCallback;
            conn.UserCertificateValidationCallback += UserCertificateValidationCallback;
            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));
          }
        }

        static void ProvideClientCertificatesCallback(X509CertificateCollection clientCerts)
        {
          // To be able to add a certificate with a private key included, we must convert it to
          // a PKCS #12 format. The following openssl command does this:
          // openssl pkcs12 -password pass: -inkey client.maxroach.key -in client.maxroach.crt -export -out client.maxroach.pfx
          // As of 2018-12-10, you need to provide a password for this to work on macOS.
          // See https://github.com/dotnet/corefx/issues/24225

          // Note that the password used during X509 cert creation below
          // must match the password used in the openssl command above.
          clientCerts.Add(new X509Certificate2("client.maxroach.pfx", "pass"));
        }

        // By default, .Net does all of its certificate verification using the system certificate store.
        // This callback is necessary to validate the server certificate against a CA certificate file.
        static bool UserCertificateValidationCallback(object sender, X509Certificate certificate, X509Chain defaultChain, SslPolicyErrors defaultErrors)
        {
          X509Certificate2 caCert = new X509Certificate2("ca.crt");
          X509Chain caCertChain = new X509Chain();
          caCertChain.ChainPolicy = new X509ChainPolicy()
          {
            RevocationMode = X509RevocationMode.NoCheck,
            RevocationFlag = X509RevocationFlag.EntireChain
          };
          caCertChain.ChainPolicy.ExtraStore.Add(caCert);

          X509Certificate2 serverCert = new X509Certificate2(certificate);

          caCertChain.Build(serverCert);
          if (caCertChain.ChainStatus.Length == 0)
          {
            // No errors
            return true;
          }

          foreach (X509ChainStatus status in caCertChain.ChainStatus)
          {
            // Check if we got any errors other than UntrustedRoot (which we will always get if we do not install the CA cert to the system store)
            if (status.Status != X509ChainStatusFlags.UntrustedRoot)
            {
              return false;
            }
          }
          return true;
        }

      }
    }

Then, run the code to connect as the `maxroach` user. 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:

    $ dotnet run

The output should be:

    Initial balances:
        account 1: 1000
        account 2: 250

### Transaction example (with retry logic)

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

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. The code sample below shows how it is used.

    using System;
    using System.Data;
    using System.Security.Cryptography.X509Certificates;
    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 = "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.ProvideClientCertificatesCallback += ProvideClientCertificatesCallback;
            conn.UserCertificateValidationCallback += UserCertificateValidationCallback;

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

        static void ProvideClientCertificatesCallback(X509CertificateCollection clientCerts)
        {
          // To be able to add a certificate with a private key included, we must convert it to
          // a PKCS #12 format. The following openssl command does this:
          // openssl pkcs12 -inkey client.maxroach.key -in client.maxroach.crt -export -out client.maxroach.pfx
          // As of 2018-12-10, you need to provide a password for this to work on macOS.
          // See https://github.com/dotnet/corefx/issues/24225
          clientCerts.Add(new X509Certificate2("client.maxroach.pfx", "pass"));
        }

        // By default, .Net does all of its certificate verification using the system certificate store.
        // This callback is necessary to validate the server certificate against a CA certificate file.
        static bool UserCertificateValidationCallback(object sender, X509Certificate certificate, X509Chain defaultChain, SslPolicyErrors defaultErrors)
        {
          X509Certificate2 caCert = new X509Certificate2("ca.crt");
          X509Chain caCertChain = new X509Chain();
          caCertChain.ChainPolicy = new X509ChainPolicy()
          {
            RevocationMode = X509RevocationMode.NoCheck,
            RevocationFlag = X509RevocationFlag.EntireChain
          };
          caCertChain.ChainPolicy.ExtraStore.Add(caCert);

          X509Certificate2 serverCert = new X509Certificate2(certificate);

          caCertChain.Build(serverCert);
          if (caCertChain.ChainStatus.Length == 0)
          {
            // No errors
            return true;
          }

          foreach (X509ChainStatus status in caCertChain.ChainStatus)
          {
            // Check if we got any errors other than UntrustedRoot (which we will always get if we do not install the CA cert to the system store)
            if (status.Status != X509ChainStatusFlags.UntrustedRoot)
            {
              return false;
            }
          }
          return true;
        }
      }
    }

Then, run the code to connect as the `maxroach` user. 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:

    $ 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 --certs-dir=certs --database=bank -e 'SELECT id, balance FROM accounts'

      id | balance
    +----+---------+
       1 |     900
       2 |     350
    (2 rows)

## Step 3. Create the `maxroach` user and `bank` database

Start the built-in SQL shell:

    $ cockroach sql --insecure

In the SQL shell, issue the following statements to create the `maxroach` user and `bank` database:

    > CREATE USER IF NOT EXISTS maxroach;

    > CREATE DATABASE bank;

Give the `maxroach` user the necessary permissions:

    > GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

    > \q

## Step 4. Run the C# code

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

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

### Basic example

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

    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.SslMode = SslMode.Disable;
          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:

    $ dotnet run

The output should be:

    Initial balances:
        account 1: 1000
        account 2: 250

### Transaction example (with retry logic)

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

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. The code sample below shows how it is used.

    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.SslMode = SslMode.Disable;
          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 connect as the `maxroach` user. 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:

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

- Client Connection Parameters
- Data Replication
- Fault Tolerance & Recovery
- Replication & Rebalancing
- Cross-Cloud Migration
- Automated Operations