TIPS: 如遇兼容性问题,可提交github issue以获取帮助:https://github.com/cockroachdb/cockroach/issues/new

本小节将展示如何使用Npgsql驱动进行数据库编程交互。用户需要预先安装、配置CockroachDB.NET SDK环境。

使用Npgsql驱动

Step 1: 创建.net项目

dotnet new console -o cockroachdb-test-app
cd cockroachdb-test-app

上述dotnet命令创建了console类型的应用,并使用-o参数指定了项目文件夹。

Step 2: 安装Npgsql驱动

dotnet add package Npgsql

Step 3: 启动单节点

cockroach start --insecure --store=hello-1 --host=localhost

Step 4: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 5: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 6: 编写、执行C#代码

基本操作

代码:

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.GetString(0), reader.GetString(1));
      }
    }
  }
}

执行:

dotnet run

输出:

Initial balances:
    account 1: 1000
    account 2: 250

创建事务(带重试逻辑)

代码:

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.GetString(0), reader.GetString(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.GetString(0), reader.GetString(1));
      }
    }
  }
}

执行:

dotnet run

输出:

Initial balances:
    account 1: 1000
    account 2: 250
Final balances:
    account 1: 900
    account 2: 350

执行查询:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

NOTE: 在默认的隔离级别SERIALIZABLE下,因读写冲突导致事务执行失败,需要用户主动地重新提交事务。用户也实现通用的retry函数在事务内部重新执行事务。