TiDB与C#的简单CRUD应用程序示例

本文演示如何使用 C# 语言实现对 TiDB 的基础增删改查操作，包含了 C# 中常用的几种数据库访问方式。

相关环境

Ubuntu 18.04

.NET 6.0

C# 10

Visual Studio Code 1.63.2

TiDB 6.0-DMR

创建 TiDB 测试集群

你可以使用以下方式快速搭建一个 TiDB 测试集群：

使用 TiDB Cloud 免费创建在线集群

使用 TiUP 部署本地测试集群

使用 TiUP 部署标准 TiDB 集群

使用 TiDB Operator 在 Kubernetes 中部署 TiDB 集群

本文仅用于代码演示，在单机环境使用 TiUP Playground 搭建了一套最基础的测试集群：

[root@dbserver1 ~]# tiup playground v6.0.0 --db 1 --pd 1 --kv 3 --tag tidb tiup is checking updates for component playground ...timeout! Starting component `playground`: /root/.tiup/components/playground/v1.9.5/tiup-playground /root/.tiup/components/playground/v1.9.5/tiup-playground v6.0.0 --db 1 --pd 1 --kv 3 --tag tidb Playground Bootstrapping... Start pd instance:v6.0.0 Start tikv instance:v6.0.0 Start tikv instance:v6.0.0 Start tikv instance:v6.0.0 Start tidb instance:v6.0.0 Waiting for tidb instances ready 127.0.0.1:4000 ... Done Start tiflash instance:v6.0.0 Waiting for tiflash instances ready 127.0.0.1:3930 ... Done CLUSTER START SUCCESSFULLY, Enjoy it ^-^ To connect TiDB: mysql --comments --host 127.0.0.1 --port 4000 -u root -p (no password) To view the dashboard: http://127.0.0.1:2379/dashboard PD client endpoints: [127.0.0.1:2379] To view the Prometheus: http://127.0.0.1:9090 To view the Grafana: http://127.0.0.1:3000

数据库启动成功之后，我们用官方提供的Bookshop 示例应用作为测试数据，使用如下命令生成数据：

tiup demo bookshop prepare --users=1000 --books=5000 --authors=1000 --ratings=10000 --orders=2000 --drop-tables

最后看一下测试数据的生成情况：

+-----------------------+----------------+-----------+------------+--------+ | Table Name            | Number of Rows | Data Size | Index Size | Total  | +-----------------------+----------------+-----------+------------+--------+ | bookshop.orders       |           2000 | 0.08MB    | 0.02MB     | 0.09MB | | bookshop.ratings      |          10000 | 0.31MB    | 0.31MB     | 0.61MB | | bookshop.book_authors |           5000 | 0.08MB    | 0.08MB     | 0.15MB | | bookshop.authors      |           1000 | 0.04MB    | 0.00MB     | 0.04MB | | bookshop.users        |            999 | 0.03MB    | 0.01MB     | 0.04MB | | bookshop.books        |           5000 | 0.28MB    | 0.00MB     | 0.28MB | +-----------------------+----------------+-----------+------------+--------+ 6 rows in set (0.01 sec)

创建 C# 应用程序

为了简化演示代码，这里构建一个最简单的控制台应用程序用于数据库访问。

dotnet new console --name tidb-example-csharp --framework net6.0

看一下这个控制台程序的项目结构：

dc@dc-virtual-machine:~/dotnet$ ll tidb-example-csharp/ total 20 drwxrwxr-x 3 dc dc 4096 May 17 16:43 ./ drwxrwxr-x 3 dc dc 4096 May 17 16:43 ../ drwxrwxr-x 2 dc dc 4096 May 17 16:43 obj/ -rw-rw-r-- 1 dc dc  105 May 17 16:43 Program.cs -rw-rw-r-- 1 dc dc  305 May 17 16:43 tidb-example-csharp.csproj

tidb-example-csharp.csproj是项目工程文件，Program.cs是程序入口文件。

验证一下程序是否能正常运行：

dc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run Hello, World!

驱动程序和ORM

ADO.NET 提供了开发者在 .NET 平台上对各种数据源的一致性访问标准，类似于 Java 的 jdbc，或者 Golang 的 sql/database 。在此基础上，我们通过实现了 ADO.NET 接口的驱动程序就能访问和操作不同的数据库，包括 ***、***、MySQL、***、Sqlite、Access 等等。

ADO.NET 体系结构（图片来自微软官网）：

TiDB 高度兼容 MySQL 5.7 协议，还实现了一部分 MySQL 8.0 特性，所以市面上 MySQL 能使用的驱动程序和 ORM 框架基本都能用在 TiDB 上面。

梳理一下可以发现，在 C# 中实现对 TiDB 的操作分为以下两种方式：

基于 ADO.NET 的驱动程序，代表有*** Connector/NET、MySqlConnector。

ORM 数据访问框架，代表有Entity Framework、Dapper。

下面分别演示如何对 TiDB 实现增删改查操作。

使用 *** Connector/NET

Connector/NET 是 MySQL 官方提供的符合标准 ADO.NET 体系的数据库访问驱动，由于 TiDB 高度兼容 MySQL 协议，所以市面上 MySQL 能使用的驱动基本都能用在 TiDB 上面。

如果要以 ADO.NET 接口方式访问 TiDB ，首先安装驱动程序包：

dotnet add package MySql.Data --version 8.0.29

测试是否能连接上 TiDB ：

using MySql.Data.MySqlCli     MySqlConnection conn = new MySqlConnection(conectionStr);     conn.Open();     var cmd = conn.CreateCommand();     cmd.CommandText = "select tidb_version()";     var result = cmd.ExecuteScalar();     Console.WriteLine(result);     conn.Close(); }dc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run Release Version: v6.0.0 Edition: Community Git Commit Hash: 36a9810441ca0e496cbd22064af274b3be771081 Git Branch: heads/refs/tags/v6.0.0 UTC Build Time: 2022-03-31 10:33:28 GoVersion: go1.18 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false

一个简单的查询示例：

public void TestRead() {    MySqlConnection conn = new MySqlConnection(conectionStr);    conn.Open();    var cmd = conn.CreateCommand();    cmd.CommandText = "select * from books limit 5";     MySqlDataReader reader = cmd.ExecuteReader();    while (reader.Read())   {        Console.WriteLine($"id: {reader["id"]} title: {reader["title"]} type: {reader["type"]} published_at: {reader["published_at"]}");   }   conn.Close(); }dc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run id: 648872 title: Sam Mayert type: Life published_at: 2/26/1953 12:53:33 PM id: 6583703 title: Aron Kilback type: Kids published_at: 11/23/1923 9:19:43 AM id: 6810515 title: Chelsey Dickens type: Education & Reference published_at: 4/8/1985 9:23:37 PM id: 7884508 title: Annetta Rodriguez type: Education & Reference published_at: 5/11/1962 9:54:58 PM id: 8683541 title: The Documentary of hamster type: Magazine published_at: 10/3/1945 1:44:52 AM

一个简单的新增修改删除示例：

public void TestWrite() {    MySqlConnection conn = new MySqlConnection(conectionStr);    conn.Open();.AddWithValue("@id", bookId);    cmd1.Parameters.AddWithValue("@title", "TiDB in action");    cmd1.Parameters.AddWithValue("@type", "Science & Technology");    cmd1.Parameters.AddWithValue("@published_at", DateTime.Now);    cmd1.Parameters.AddWithValue("@stock", 1000);    cmd1.Parameters.AddWithValue("@price", 66.66);    int insertCnt = cmd1.ExecuteNonQuery();    Console.WriteLine($"insert successed {qlCommand("delete from books where id=@id", conn);    cmd3.Parameters.AddWithValue("@id", bookId);    int deleteCnt = cmd3.ExecuteNonQuery();    Console.WriteLine($"delete successed {updateCnt} books."); dc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run insert successed 1 books. id: 888888 title: TiDB in action type: Science & Technology published_at: 5/18/2022 3:22:02 PM stock: 1000 price: 66.66 update successed 1 books. id: 888888 title: TiDB in action type: Science & Technology published_at: 5/18/2022 3:22:02 PM stock: 999 price: 66.66 delete successed 1 books. book id 888888 not found.

更多 API 用法可以参考官方文档：

https://dev.mysql.com/doc/connector-net/en/connector-net-programming.html

https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials.html`

注意：对于这种数据库 IO 类型请求，建议使用 API 的异步同名方法提高程序处理效率，例如ExecuteNonQueryAsync。

这里梳理一下连接字符串中的核心参数。

参数名描述默认值Server ,Host ,Data Source ,DataSource数据库连接主机localhostPort数据库连接端口3306UserID ,User Id ,Username ,Uid ,User name ,User数据库登录用户名nullPassword , pwd数据库登录用户密码ConnectionTimeout ,Connect Timeout ,Connection Timeout数据量连接超时时间15sDefaultCommandTimeout ,Default Command TimeoutSQL 执行的超时时间30sPooling是否启用连接池trueConnectionLifeTime ,Connection Lifetime连接过期时间，超过这个时间的连接会被销毁重新创建0(不限制)MaxPoolSize, Max Pool Size连接池最大连接数100MinPoolSize, Min Pool Size连接池最小连接数0TableCaching ,Table Cache ,TableCache是否开启客户端表数据缓存falseDefaultTableCacheAge ,Default Table Cache Age客户端表数据缓存时间60sAllowBatch ,Allow Batch是否允许批量 SQL 执行true

关于连接池参数的最佳实践可以参考TiDB官网文档。

使用 MySqlConnector

MySqlConnector 也是广泛使用的一种实现了 ADO.NET 接口的 MySQL 驱动，它提供了比MySql.Data更好的异步性能，很多 ORM 框架底层都是依赖于MySqlConnector 实现对 MySQL 的访问。

首先在项目中安装依赖包：

dotnet add package MySqlConnector --version 2.1.9

看一个数据库连接示例：

using MySqlConnec    using (var conn = new MySqlConnection(conectionStr))   {        await  conn.OpenAsync();        using (var cmd = new MySqlCommand("select tidb_version()", conn))       {            var result = await cmd.ExecuteScalarAsync();            Console.WriteLine(result);       }   } }dc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run Release Version: v6.0.0 Edition: Community Git Commit Hash: 36a9810441ca0e496cbd22064af274b3be771081 Git Branch: heads/refs/tags/v6.0.0 UTC Build Time: 2022-03-31 10:33:28 GoVersion: go1.18 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false

可以看到使用方式和Connector/NET非常相似都是标准的 ADO.NET 风格，MySqlConnector的连接字符串参数绝大部分都兼容

Connector/NET，并且在此基础上提供了一些新的特性，比如负载均衡功能：

参数名说明默认值Server, Host, Data Source, DataSource, Address, Addr, Network Address数据库请求地址，可以用逗号分隔填写多个地址实现负载均衡localhostLoad Balance, LoadBalance负载均衡策略，支持 RoundRobin、LeastConnections、Failover 三种模式，需要开启连接池RoundRobin

【注意】

有些参数在MySqlConnector已经禁用了，更多差异和新增功能参考官网文档：https://mysqlconnector.net/connection-options/

除了可以使用连接字符串，MySqlConnector还支持 Builder 对象模式，连接串里的参数都能在MySqlConnectionStringBuilder找到对应的字段，例如：

var builder = new MySqlConnectionStringBuilder { Server = "your-server", using var connection = new MySqlConnection(builder.ConnectionString); await connection.OpenAsync();

一个简单的查询示例：

public async Task TestRead() {    using (var conn = new MySqlConnection(conectionStr)) ataReader reader =await cmd.ExecuteReaderAsync();        while (await reader.ReadAsync())       {            Console.WriteLine($"id: {reader.GetInt32("id")} balance: {reader.GetDecimal("balance")} nicknmame: {reader.GetString("nickname")} ");       }   } }dc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run id: 525196 balance: 9490.89 nicknmame: Goodwin4601 id: 822804 balance: 1197.81 nicknmame: Treutel4269 id: 4147652 balance: 349.36 nicknmame: Pacocha6285 id: 9704562 balance: 2292.28 nicknmame: Grady8130 id: 17101775 balance: 5054.69 nicknmame: Macejkovic7559

一个简单的增删改示例：

publ.AddWithValue("@balance", 0.01);        cmd1.Parameters.AddWithValue("@nickname", "hey-hoho");        int insertCnt = await cmd1.ExecuteNonQueryAsync();        Console.WriteLine($"insert successed {insertCnt} users."       var cmd3 = new MySqlCommand("delete from users where id=@id", conn);        cmd3.Parameters.AddWithValue("@id", userId);        int deleteCnt = await cmd3.ExecuteNonQueryAsync();        Console.WriteLine($"deldc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run insert successed 1 users. id: 888888 balance: 0.01 nicknmame: hey-hoho update successed 1 users. id: 888888 balance: 99.01 nicknmame: hey-hoho delete successed 1 users. user id 888888 not found.

一个简单的事务使用示例：

public async Task TestTransaction() {    // 用一个事务演示购书流程    using (var conn = new MySqlConnection(conectionStr))   {        await con            cmd1.Parameters.AddWithValue("@id", 999999);            cmd1.Parameters.AddWithValue("@book_id", bookId);            cmd1.Parameters.AddWithValue("@user_id", userId);            cmd1.            var cmd2 = new MySqlCommand("update users set balance=balance-@price where id =@id", conn, tnx);            cmd2.Parameters.AddWithValue("@id", userId);            cmd2.Parameters.AddWithValue("@price", price);       }        catch (Exception ex)       {            Console.WriteLine(ex.ToString());            // 异常回滚事务            await tnx.RollbackAsync();       }   } }

需要注意的是，如果有大量重复的 SQL 需要执行，建议使用 TiDB 的 Prepare Statement 特性，它能有效减少 SQL 编译解析的时间提升执行效率，使用示例：

cmd.CommandText = "insert into books values (@id, @title, @type, @published_at, @stock, @price)"; {    cmd.Parameters["@id"].Value = i;    cmd.Parameters["@title"].Value = $"TiDB in action - {i}";   ...    await cmd.ExecuteNonQueryAsync(); }

更多用法可以参考官网文档：https://mysqlconnector.net/

使用 Entity Framework

Entity Framework (EF) 是 .NET 领域最知名的