[Day21] 在 .NET 使用 Dapper 操作 MySQL

现在我们的资料库已经就绪了，我们赶快来透过 .NET Web API 操作资料库吧！

现在写 .NET 操作资料库通常都会用 ORM（Object Relation Map）套件帮我们对应我们的 C# 物件 与资料库之间的关系。目前 .NET 生态中最流行大概是以下两个：

• Entity Framework
  微软力推的强大 ORM，非常的完整功能强大，搭配 LINQ 语法操作资料库，只要安装不同的提供器（provider），就能用同一份 C# 程序操作不同的资料库。
• Dapper
  轻量级的 ORM，必须要自己定义 C# class、管理连线、自己写 SQL，但是套件非常迷你而且效能非常好，很适合已经学会目标资料库语法的开发者。

ORM 的选择其实与团队还有「信仰」比较有关，笔者自己使用的感觉是：EF 真的很方便，Dapper 真的很快。本系列因为介绍过 MySQL 的语法，所以会使用 Dapper 来示范，偷渡一下推广这个轻巧、高效能的 ORM。

参数化查询

在实际使用 Dapper 之前，我们先来聊聊参数化查询。参数化查询简单来说就是不要直接用字串写上 SQL 指令所需要的值，而是在指令中给一个代号，接着再让我们程序中的物件，根据代号把查询所需要的值带入指令的代号中。例如

var queryParams = new { UserId = 1 };
var targetUser = dbConnection.Query(
    "SELECT * FROM `user` WHERE user_id = @UserId",
    queryParams
);

上面的程序码中，第一行宣告了一个物件，里面只有一个值为 1 的 UserId。第二行执行 SQL 指令，尝试从 user 资料表取出 user_id = 1 的资料，这个查询的字串中我们并没有直接给值，而是给了一个代号 @UserId，待会真正要查询的时候，我们才会从 Query() 方法的第二个参数 queryParams 里面，找出名字为 UserId 的属性，并把值取出来，最後送到资料库，由资料库帮我们把值带入 SQL 指令并执行。

参数化查询最主要的好处是可以有效杜绝 SQL 注入（injection）攻击，SQL 注入简单来说就是在给值的时候，偷偷藏了可以执行的 SQL 指令在里面，这样资料库在值型我们的查询的时候，就有可能执行被偷藏在里面的有害指令，造成我们的资料外泄或损毁。

安装 Dapper

拖了这麽久，终於要开始使用 Dapper 的章节了！首先，我们需要安装相关的套件

1. Dapper
2. MySQL Connector

首先对我们的专案点右键 -> 管理 NuGet 套件，然後在浏览那边搜寻这两个套件并安装。.NET 5 现在很新，基本上至街庄最新版就好，不必刻意选版本，有旧专案要用再注意一下相依版本就好。

Dapper 基础用法

装完之後就可以开始来用 Dapper 了，Dapper 的基本用法包含四个步骤

1. 定义一个用来接资料的 Model 类别
   Dapper 支援泛型，只要把指定的型别告诉 Dapper，而且类别的属性与 MySQL 的栏位有对上，Dapper 就会自动帮我们把取回来的资料放进 Model 类别。开发时期资料还不固定可以暂时用 dynamic，但是固定下来之後建议还是宣告一个 Model Class 来储存，因为 dynamic 型别比较容易出错、不容易维护。

MySQL 与 .NET 的命名惯例不同，MySQL 大多使用小写英文 + 下底线，.NET 大多使用驼峰式命名，大多数的状况无法直接对应上，这时候只要使用 AS 语法改变选取结果的名称，就能轻松地对上。例如
SELECT user_naem AS UserName FROM `user` WHERE user_id = 1;

2. 开启一个 MySQL 连线
   用 var _db = new MySqlConnection("连线资讯"); 就能开启连线。比较需要注意的是，最好用注入组态设定的方法取得连线资讯，以及用 using 语法自动帮我们关闭连线，以免忘记关闭造成连线被占用。

3. 执行 SQL 指令
   Dapper 有多种执行 SQL 指令的语法，以下对常用的几个语法做说明

   • 查询多笔资料
     用 Query<> 泛型方法，执行完 SQL 查询完成後，Dapper 会自己帮我们把资料对应到 Model 类别

   var results = _db.Query<Model类别>(“查询多笔资料的 SQL 指令”).ToList();
   

   • 查询一个纯量
     使用 ExecuteScalar<> 泛型方法，查询完成後 Dapper 会自动把纯量转型。

   var result = _db. ExecuteScalar<资料型态>(“查询纯量的 SQL 指令”);
   

   • 执行 SP 并取得回传值（SP 最後 SELECT 的资料）
     同样使用 Query<> 泛型方法，但需要指定 commandType 为 StoredProcedure

   var spParams = new { 参数名称1: 参数值1, 参数名称2: 参数值2, …};
   var results = _db.Query<Model类别>("SP名称", spParams, commandType: CommandType.StoredProcedure);
   

   • 执行 SP 但不接资料
     使用 Execute() 方法，一样要指定 commandType 为 StoredProcedure

   _db. Execute("SP名称", spParams, commandType: CommandType.StoredProcedure);
   

   • 执行 SP 并取得 OUT 参数的值
     使用 Execute() 方法搭配 DynamicParameters 类别储存参数，OUT 参数需要额外再指定 direction 为 Output，一样要指定 commandType 为 SP。执行完毕後，可以从 DynamicParameters 透过 Get 泛型方法，把结果的值取出。

   var spParams = new DynamicParameters();
   spParams.Add("IN参数名称1", IN参数值1);
   spParams.Add("IN参数名称2", IN参数值2);
   ...
   spParams.Add("OUT参数名称1", OUT参数值2, direction: ParameterDirection.Output);
   spParams.Add("OUT参数名称2", OUT参数值2, direction: ParameterDirection.Output);
   ...
   
   _db.Execute("SP名称", spPrams, commandType: CommandType.StoredProcedure);
   
   var outParam1 = spParams.Get<资料型态1>("OUT参数名称1");
   var outParam2 = spParams.Get<资料型态2>("OUT参数名称2");
   ...
   

   4 关闭连线
   如果前面开启连线没有使用 using 关键字，务必记得手动关闭连线
   db?.Close()

用 Dapper 做一个 User Service

请各位回味一下本系列关於依赖注入的文章，我们现在要来建立另一个实作 IUserCRUD 介面的 Service，用 MySQL 管理使用者资料。

首先，因应资料库的变更修改我们的 User 类别

public class User
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public bool Verified { get; set; } // 加入这个属性
}

再来，新增 MySQL 需要的连线资讯到 appsettings.json 与 appsettings.Development.json

// appsettings.json 
// 其他不变
"MySqlOptions": {
    "ConnectionString": "Server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;" // 要上版控前记得挖掉密码
}

// appsettings.Development.json 
// 其他不变
"MySqlOptions": {
    "ConnectionString": "Server=localhost;Database=world;Uid=root;Pwd=root;" // 本机用的不会上版控，可以放心打密码
}

接着，宣告一个 MySqlOption 类别，用来储存 appsettings 里的设定值，并在 Startup.cs 注册 MySQL 需要用到的 Option

public class MySqlOptions
{
    public string ConnectionString { get; set; }
}

services.Configure<MySqlOptions>(
    Configuration.GetSection("MySqlOptions"));

主角终於登场了！宣告一个 UserServiceWithMySQL 并实作 IUserCRUD 介面，透过 Dapper 操作 MySQL

public class UserServiceWithMySQL : IUserCRUD
{
    private readonly IOptions<MySqlOptions> _options;

    public UserServiceWithMySQL(IOptions<MySqlOptions> options)
    {
        _options = options;
    }

    public void CreateUser(User model)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            bool emailUsed = db.ExecuteScalar<bool>(
                @"SELECT EXISTS 
                    (SELECT user_id FROM `user` WHERE email = @Email)",
                model);

            if (!emailUsed)
            {
                db.Execute(
                    @"INSERT INTO `user` (user_name, email, verified)
                      VALUES (@UserName, @Email, 0)",
                    model);
            }
        }
    }

    public void DeleteUser(int id)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            db.Execute(
                @"DELETE FROM `user`
                  WHERE user_id = @UserId",
                new { UserId = id});
        }
    }

    public List<User> GetAllUsers()
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            var result = db.Query<User>(
                @"SELECT
                    user_id AS UserID,
                    user_name AS UserName,
                    email AS Email,
                    verified AS Verified
                  FROM `user`",
                new {});
            return result.ToList();
        }
    }

    public User GetUserById(int id)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            var result = db.QuerySingleOrDefault<User>(
                @"SELECT
                    user_id AS UserID,
                    user_name AS UserName,
                    email AS Email,
                    verified AS Verified
                  FROM `user`
                  WHERE user_id = @UserId",
                new { UserId = id});
            return result;
        }
    }

    public void UpdateUser(int id, User model)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            bool emailUsed = db.ExecuteScalar<bool>(
                @"SELECT EXISTS 
                    (SELECT user_id FROM `user` WHERE email = @UserId)",
                model);

            if (!emailUsed)
            {
                db.Execute(
                    @"UPDATE `user` 
                      SET
                        user_name = @UserName,
                        email = @Email,
                        verified = @Verified
                      WHERE
                        user_id = @UserId",
                    model);
            }
        }
    }
}

写完 Service 之後，到 Startup.cs 换掉注册的 UserService

// 把原本的 UserService 替换成 UserServiceWithMySQL
services.AddScoped<IUserCRUD, UserServiceWithMySQL>();

因为我们的 Controller 依赖 IUserCRUD，而我们新的 service 有实作 IUserCRUD，所以 Controller 完全不用改，F5 执行就直接把使用者的管理换到 MySQL，舒服！

明天，我们将短暂的再次回到云端，在我们的 VM 上面装 MySQL，然後调整环境，让布署在上面的 API 程序存取同一个 VM 上的 MySQL。