TiDB和C#的簡單CRUD應用程式

語言: CN / TW / HK

時間 2022-06-22 23:01:47 TiDB社群乾貨傳送門

主題:

\n> 原文來源：http://tidb.net/blog/845e490e\n\n本文演示如何使用C#語言實現對TiDB的基礎增刪改查操作，包含了C#中常用的幾種資料庫訪問方式。

相關環境

Ubuntu 18.04

.NET 6.0

C# 10

Visual Studio Code 1.63.2

TiDB 6.0-DMR

建立TiDB測試叢集

你可以使用以下方式快速搭建一個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!

使用Oracle Connector/NET

Connector/NET是MySQL官方提供的符合標準ADO.NET體系的資料庫訪問驅動，由於TiDB高度相容MySQL協議，所以市面上MySQL能使用的驅動基本都能用在TiDB上面。

ADO.NET體系結構（圖片來自微軟官網）：

如果要以ADO.NET介面方式訪問TiDB，首先安裝驅動程式包：

dotnet add package MySql.Data --version 8.0.29

測試是否能連線上TiDB：

using MySql.Data.MySqlClient;
        
const string conectionStr = "Server=127.0.0.1;UserId=root;Password=;Port=4000;Database=bookshop";

public void TestConnection()
{
     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();

    int bookId = 888888;

    var cmd1 = new MySqlCommand("insert into books values (@id,@title,@type,@published_at,@stock,@price)", conn);
    cmd1.Parameters.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 {insertCnt} books.");
    TestQueryBook(conn, bookId);

    var cmd2 = new MySqlCommand("update books set stock=stock-1 where id=@id", conn);
    cmd2.Parameters.AddWithValue("@id", bookId);
    int updateCnt = cmd2.ExecuteNonQuery();
    Console.WriteLine($"update successed {updateCnt} books.");
    TestQueryBook(conn, bookId);

    var cmd3 = new MySqlCommand("delete from books where id=@id", conn);
    cmd3.Parameters.AddWithValue("@id", bookId);
    int deleteCnt = cmd3.ExecuteNonQuery();
    Console.WriteLine($"delete successed {updateCnt} books.");
    TestQueryBook(conn, bookId);

    conn.Close();
}

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用法可以參考官方文件：

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

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

注意：對於這種資料庫IO型別請求，建議使用API的非同步同名方法提高程式處理效率，例如ExecuteNonQueryAsync。

這裡梳理一下連線字串中的核心引數。

引數名	描述	預設值
`Server` , `Host` , `Data Source` , `DataSource`	資料庫連線主機	localhost
`Port`	資料庫連線埠	3306
`UserID` , `User Id` , `Username` , `Uid` , `User name` , `User`	資料庫登入使用者名稱	null
`Password` , `pwd`	資料庫登入使用者密碼
`ConnectionTimeout` , `Connect Timeout` , `Connection Timeout`	資料量連線超時時間	15s
`DefaultCommandTimeout` , `Default Command Timeout`	SQL執行的超時時間	30s
`Pooling`	是否啟用連線池	true
`ConnectionLifeTime` , `Connection Lifetime`	連線過期時間超過這個時間的連線會被銷燬重新建立	0(不限制)
`MaxPoolSize`, `Max Pool Size`	連線池最大連線數	100
`MinPoolSize`, `Min Pool Size`	連線池最小連線數	0
`TableCaching` , `Table Cache` , `TableCache`	是否開啟客戶端表資料快取	false
`DefaultTableCacheAge` , `Default Table Cache Age`	客戶端表資料快取時間	60s
`AllowBatch` , `Allow Batch`	是否允許批量SQL執行	true

關於連線池引數的最佳實踐可以參考TiDB官網文件。

使用MySqlConnector

MySqlConnector也是廣泛使用的一種實現了ADO.NET介面的MySQL驅動，它提供了比MySql.Data更好的非同步效能，很多ORM框架底層都是依賴於MySqlConnector實現對MySQL的訪問。

首先在專案中安裝依賴包：

dotnet add package MySqlConnector --version 2.1.9

看一個數據庫連線示例：

using MySqlConnector;

const string conectionStr = "Server=127.0.0.1;UserId=root;Password=;Port=4000;Database=bookshop";

public async Task TestConnection()
{
    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`	資料庫請求地址可以用逗號分隔填寫多個地址實現負載均衡	localhost
`Load Balance`, `LoadBalance`	負載均衡策略，支援RoundRobin、LeastConnections、Failover三種模式需要開啟連線池	RoundRobin

【注意】

有些引數在MySqlConnector已經禁用了，更多差異和新增功能參考官網文件：http://mysqlconnector.net/connection-options/

除了可以使用連線字串，MySqlConnector還支援Builder物件模式，連線串裡的引數都能在MySqlConnectionStringBuilder找到對應的欄位，例如：

var builder = new MySqlConnectionStringBuilder
{
    Server = "your-server",
    UserID = "database-user",
    Password = "P@ssw0rd!",
    Database = "database-name",
};

// open a connection asynchronously
using var connection = new MySqlConnection(builder.ConnectionString);
await connection.OpenAsync();

一個簡單的查詢示例：

public async Task TestRead()
{
    using (var conn = new MySqlConnection(conectionStr))
    {
        await conn.OpenAsync();

        var cmd = conn.CreateCommand();
        cmd.CommandText = "select * from users limit 5;";
        MySqlDataReader 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

一個簡單的增刪改示例：

public async Task TestWrite()
{
    using (var conn = new MySqlConnection(conectionStr))
    {
        await conn.OpenAsync();

        int userId = 888888;

        var cmd1 = new MySqlCommand("insert into users values (@id,@balance,@nickname)", conn);
        cmd1.Parameters.AddWithValue("@id", userId);
        cmd1.Parameters.AddWithValue("@balance", 0.01);
        cmd1.Parameters.AddWithValue("@nickname", "hey-hoho");
        int insertCnt = await cmd1.ExecuteNonQueryAsync();
        Console.WriteLine($"insert successed {insertCnt} users.");
        TestQueryUser(conn, userId);

        var cmd2 = new MySqlCommand("update users set balance=balance+99 where id=@id", conn);
        cmd2.Parameters.AddWithValue("@id", userId);
        int updateCnt =await cmd2.ExecuteNonQueryAsync();
        Console.WriteLine($"update successed {updateCnt} users.");
        TestQueryUser(conn, userId);

        var cmd3 = new MySqlCommand("delete from users where id=@id", conn);
        cmd3.Parameters.AddWithValue("@id", userId);
        int deleteCnt = await cmd3.ExecuteNonQueryAsync();
        Console.WriteLine($"delete successed {updateCnt} users.");
        TestQueryUser(conn, userId);

    }
}

dc@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 conn.OpenAsync();

        int userId = 525196, bookId = 648872;
        decimal price = 15;

        var tnx = await conn.BeginTransactionAsync();

        try
        {
            // 新增一個訂單
            var cmd1 = new MySqlCommand("insert into orders values(@id, @book_id, @user_id, @quality, @ordered_at)", conn, tnx);
            cmd1.Parameters.AddWithValue("@id", 999999);
            cmd1.Parameters.AddWithValue("@book_id", bookId);
            cmd1.Parameters.AddWithValue("@user_id", userId);
            cmd1.Parameters.AddWithValue("@quality", 1);
            cmd1.Parameters.AddWithValue("@ordered_at", DateTime.Now);
            await cmd1.ExecuteNonQueryAsync();

            // 扣減賬戶餘額
            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);
            await cmd2.ExecuteNonQueryAsync();

            // 更新商品庫存
            var cmd3 = new MySqlCommand("update books set stock=stock-1 where id =@id", conn, tnx);
            cmd3.Parameters.AddWithValue("@id", bookId);
            await cmd3.ExecuteNonQueryAsync();

            // 提交事務
            await tnx.CommitAsync();
        }
        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.Add("@id", MySqlDbType.Int32);
cmd.Parameters.Add("@title", MySqlDbType.String);
...
cmd.Prepare();

for (int i = 1; i <= 1000; i++)
{
    cmd.Parameters["@id"].Value = i;
    cmd.Parameters["@title"].Value = $"TiDB in action - {i}";
    ...
    await cmd.ExecuteNonQueryAsync();
}

企業微信截圖_20220519171121.png

更多用法可以參考官網文件：http://mysqlconnector.net/

使用Entity Framework

Entity Framework (EF)是.NET領域最知名的跨平臺資料庫訪問ORM框架，它最早在2008年作為.NET Framework的一部分發布，現在的最新版本是EF Core 6.0，也已經開源。

它支援豐富的資料訪問驅動，基於這個特性我們可以使用一套統一的API介面訪問各種型別的資料庫，比如Sqlite、SQL Server、MySQL、PostgreSQL、Spanner等等。在MySQL協議上，廣泛使用的驅動有兩個：

在使用之前先安裝Entity Framework的基礎包：

dotnet add package Microsoft.EntityFrameworkCore --version 6.0.5
dotnet add package Microsoft.Extensions.Logging.Console //記錄日誌用，非必須

Pomelo.EntityFrameworkCore.MySql

Pomelo.EntityFrameworkCore.MySql是最流行的相容MySQL協議的EF Core驅動，也是微軟官方推薦使用的方式，它底層依賴於前面提到的MySqlConnector，所以在連線串配置上並無差別。

dotnet add package Pomelo.EntityFrameworkCore.MySql --version 6.0.1

先構造一個DbContext型別和對應的實體類：

using Microsoft.EntityFrameworkCore;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;
using Pomelo.EntityFrameworkCore.MySql;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.Extensions.Logging;

public class BookShopContext : DbContext
    {
        const string conectionStr = "Server=127.0.0.1;UserId=root;Password=;Port=4000;Database=bookshop";

        public DbSet<User> Users { get; set; }
        public DbSet<Book> Books { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseMySql(conectionStr, ServerVersion.Create(new Version("5.7.25"), ServerType.MySql));

            options.UseLoggerFactory(LoggerFactory.Create(builder =>
            {
                builder.AddConsole();
            }));
        }
    }

    public class User
    {
        [Column("id")]
        public long Id { get; set; }
        [Column("balance")]
        public decimal Balance { get; set; }
        [Column("nickname")]
        public string Nickname { get; set; }
    }

    public class Book
    {
        [Column("id")]
        public long Id { get; set; }
        [Column("title")]
        public string Title { get; set; }
        [Column("type")]
        public string Type { get; set; }
        [Column("published_at")]
        public DateTime PublishedAt { get; set; }
        [Column("stock")]
        public int Stock { get; set; }
        [Column("price")]
        public decimal Price { get; set; }
    }

驗證是否能連線上TiDB：

public async Task TestConnection()
{
    using var context = new BookShopContext();
    Console.WriteLine($"TiDB CanConnect: {await context.Database.CanConnectAsync()}");
}

dc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run
TiDB CanConnect: True

一個單表分頁查詢結果集的示例：

public async Task TestRead()
{
    using var context = new BookShopContext();

    var books = await context.Books.Where(b => b.Title.Contains("db")).OrderBy(b => b.Id).Skip(2).Take(5).ToListAsync();
    foreach (var book in books)
    {
        Console.WriteLine($"id: {book.Id}  title: {book.Title}  type: {book.Type} published_at: {book.PublishedAt}");
    }
}

dc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 6.0.5 initialized 'BookShopContext' using provider 'Pomelo.EntityFrameworkCore.MySql:6.0.1' with options: ServerVersion 5.7.25-mysql
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (47ms) [Parameters=[@__p_1='?' (DbType = Int32), @__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT `b`.`id`, `b`.`price`, `b`.`published_at`, `b`.`stock`, `b`.`title`, `b`.`type`
      FROM `Books` AS `b`
      WHERE `b`.`title` LIKE '%db%'
      ORDER BY `b`.`id`
      LIMIT @__p_1 OFFSET @__p_0
id: 474329852  title: Geovany Padberg  type: Science & Technology published_at: 2010/1/28 6:45:33
id: 1890134379  title: The Adventures of Hilda Padberg  type: Comics published_at: 1943/7/17 16:28:02
id: 2181887016  title: Catalina Padberg  type: Education & Reference published_at: 1961/3/28 18:18:37
id: 2193223665  title: Caroline Padberg  type: Kids published_at: 1994/11/12 20:56:53
id: 2359817065  title: Darryl Padberg  type: Science & Technology published_at: 1940/11/13 9:53:21

一個簡單的增刪改示例：

public async Task TestWrite()
{
    using var context = new BookShopContext();

    long userId = 888888;

    var user = new User
    {
        Id = userId,
        Balance = 0.01M,
        Nickname = "hey-hoho"
    };
    context.Add(user);
    int insertCnt = await context.SaveChangesAsync();
    Console.WriteLine($"insert successed {insertCnt} users.");
    TestQueryUser(context, userId);

    user.Balance += 99;
    int updateCnt = await context.SaveChangesAsync();
    Console.WriteLine($"update successed {updateCnt} users.");
    TestQueryUser(context, userId);

    context.Remove(user);
    int deleteCnt = await context.SaveChangesAsync();
    Console.WriteLine($"delete successed {deleteCnt} users.");
    TestQueryUser(context, userId);
}

dc@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.

【注意】

當只需要查詢結果而不用對結果進行修改時，建議關閉EF Core的tracking功能，它能提高執行速度。比如：
// 單個查詢關閉跟蹤
var books = await context.Books.OrderBy(b => b.Id).Take(3).AsNoTracking().ToListAsync();
// 整個上下文關閉跟蹤
options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

Pomelo.EntityFrameworkCore.MySql的文件地址：

http://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/wiki

MySql.EntityFrameworkCore

MySql.EntityFrameworkCore是Oracle官方釋出的Entity Framework支援MySQL的資料庫驅動，所以它的底層是依賴於Connector/NET。

如果你想使用Connector/NET 8.0.28及以後的版本，那麼安裝這個包：

dotnet add package MySql.EntityFrameworkCore --version 6.0.1 //版本對應.NET的版本

如果你使用的是Connector/NET早期版本，那麼安裝這個包：

dotnet add package MySql.Data.EntityFrameworkCore --version 8.0.22

在使用方式上只有配置連線串上的一點點差別：

protected override void OnConfiguring(DbContextOptionsBuilder options)
{
    options.UseMySQL("server=localhost;database=library;user=user;password=password");
}

其他資料庫操作都是用標準的EF API。

MySql.EntityFrameworkCore的文件地址：

http://dev.mysql.com/doc/connector-net/en/connector-net-entity-framework.html

總體來說，使用Entity Framework操作TiDB並沒有什麼特殊的地方，有使用經驗的開發者幾乎不用任何學習成本就能快速上手。

Entity Framework更多用法可以參考官網文件：http://docs.microsoft.com/en-us/ef/core/

使用Dapper

Dapper是StackExchange開源的一款輕量級ORM框架，它以高效能著稱，在.NET領域使用非常廣泛。它擴充套件了ADO.NET的IDbConnection介面，底層同樣依賴於MySqlConnector或者Connector/NET，使用方式介於原生ADO.NET和Entity Framework之間。

dotnet add package Dapper.StrongName --version 2.0.123

測試資料庫連線：

using Dapper;
using MySqlConnector;

const string conectionStr = "Server=127.0.0.1;UserId=root;Password=;Port=4000;Database=bookshop";

public async Task TestConnection()
{
    MySqlConnection conn = new MySqlConnection(conectionStr);
    var version =await  conn.ExecuteScalarAsync<string>("select tidb_version()");
    Console.WriteLine(version);
}

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 class Book
{
    public long Id { get; set; }
    public string Title { get; set; }
    public string Type { get; set; }
    public DateTime Published_At { get; set; }
    public int Stock { get; set; }
    public decimal Price { get; set; }
}

public async Task TestQuery()
{
    MySqlConnection conn = new MySqlConnection(conectionStr);

    var book = await conn.QueryFirstAsync<Book>("select * from books where id = @id", new { id = 8683541 });
    Console.WriteLine($"id: {book.Id}  title: {book.Title}  type: {book.Type} published_at: {book.Published_At} stock: {book.Stock} price: {book.Price}");

    var books = await conn.QueryAsync<Book>("select * from books where type = @type and stock < @stock order by published_at limit 3", new { type = "Sports" stock = 100 });
    books.ToList().ForEach(b =>
       Console.WriteLine($"id: {b.Id}  title: {b.Title}  type: {b.Type} published_at: {b.Published_At} stock: {b.Stock} price: {b.Price}")
    );
}

dc@dc-virtual-machine:~/dotnet/tidb-example-csharp$ dotnet run
id: 8683541  title: The Documentary of hamster  type: Magazine published_at: 1945/10/3 1:44:52 stock: 190 price: 74.38

id: 1201887882  title: Nayeli Luettgen  type: Sports published_at: 1901/11/20 19:37:37 stock: 53 price: 141.50
id: 3705800883  title: Arianna Considine  type: Sports published_at: 1905/8/31 5:25:53 stock: 72 price: 312.93
id: 1811359739  title: Dawson Hackett  type: Sports published_at: 1910/12/1 21:22:47 stock: 72 price: 415.72

帶事務的增刪改示例：

public class Order
{
    public long Id { get; set; }
    public long Book_Id { get; set; }
    public long User_Id { get; set; }
    public int Quality { get; set; }
    public DateTime Ordered_At { get; set; }
}
// 用一個事務演示購書流程
public async Task TestTransaction()
{
    MySqlConnection conn = new MySqlConnection(conectionStr);

    int userId = 525196, bookId = 648872;
    decimal price = 15;

    await conn.OpenAsync();
    var tnx = await conn.BeginTransactionAsync();

    try
    {
        // 新增一個訂單
        var order = new Order
        {
            Id = 666666,
            Book_Id = bookId,
            User_Id = userId,
            Quality = 1,
            Ordered_At = DateTime.Now
        };
        await conn.ExecuteAsync("insert into orders values(@id, @book_id, @user_id, @quality, @ordered_at)", order, tnx);

        // 更新賬戶餘額
        await conn.ExecuteAsync("update users set balance=balance-@price where id =@id", new { id = userId, price = price }, tnx);

        // 更新商品庫存
        await conn.ExecuteAsync("update books set stock=stock-1 where id =@id", new { id = bookId }, tnx);

        // 提交事務
        await tnx.CommitAsync();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        // 異常回滾事務
        await tnx.RollbackAsync();
    }
}

Dapper一般情況下會自動管理連線的開啟關閉狀態，使用起來比原生ADO.NET更加方便。但是要注意的是，在使用顯式事務的時候要提前手動開啟連線，否則會報異常System.InvalidOperationException: Connection is not open.。

Dapper會快取每一次查詢語句，因此推薦的做法是使用引數化方式進行傳參，一方面能提高SQL執行效率，另一方面可以減少記憶體佔用。

更多用法可以參考官方文件：http://github.com/DapperLib/Dapper

最佳實踐

原生ADO.NET能夠帶來非常優秀的效能，但是缺點就是需要大量的手寫SQL和型別轉換，對於應用開發不是很友好。而ORM框架雖然解決了程式碼複雜度的問題，但也帶來了新的問題，就是無法精準控制SQL的行為不夠靈活，以及大幅效能損耗。

所以在實際專案中，推薦使用Entity Framework+Dapper的組合，底層資料驅動選擇MySqlConnector，簡單讀寫場景交給Entity Framework去處理，想高度控制SQL的場景交給Dapper去處理，兼顧了效能和開發效率兩方面。Entity Framework的上下文物件提供了一個訪問原始ADO.NET Connection的入口，這就使得把兩者結合起來非常方便。

需要匯入的程式集：

Microsoft.EntityFrameworkCore
Pomelo.EntityFrameworkCore.MySql
Dapper

使用示例：

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;
using Dapper;

public async Task TestWithDapper()
{
    using var context = new BookShopContext();
    var conn = context.Database.GetDbConnection();

    // 使用dapper查詢
    string version = conn.ExecuteScalar<string>("select tidb_version()");
    Console.WriteLine(version);

    // 使用EF查詢
    var book = context.Books.Where(u => u.Id == 1).FirstOrDefault();

    // 共享事務，類似的還可以使用TransactionScope或EF的UseTransaction
    using IDbContextTransaction tnx = context.Database.BeginTransaction();
    conn.ExecuteScalar<dynamic>("select /*+ read_from_storage(tiflash[ratings]) */ book_id,count(*),avg(score) from ratings group by book_id", transaction: tnx.GetDbTransaction());
    context.Books.Add(new Book { });
    await tnx.CommitAsync();
}

【注意】

using會自動處理IDbContextTransaction物件的rollback和dispose，如果沒有使用using語法，需要手動處理事務異常情況。

還有其他一些ORM框架例如NHibernate、FreeSql等使用方式大同小異，底層都是依賴前面提到的資料庫驅動，參考各自API文件即可。