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
$ 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. Start a single-node cluster
For the purpose of this tutorial, you need only one CockroachDB node running in insecure mode:
$ 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.
$ 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.
$ cockroach sql --insecure -e 'CREATE DATABASE bank'
Then grant privileges to the maxroach user.
$ 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:
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:
$ 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:
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:
$ dotnet run
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:
$ 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:
- Client Connection Parameters
- Data Replication
- Fault Tolerance & Recovery
- Automatic Rebalancing
- Cross-Cloud Migration
- Follow-the-Workload
- Automated Operations
Was this helpful?
