NET Core 实战 Dapper 扩展数据访问

NET Core 实战：基于 Dapper 扩展你的数据访问方法

一、前言

　　在非静态页面的项目开发中，必定会涉及到对于数据库的访问，最开始呢，我们使用 Ado.Net，通过编写 SQL 帮助类帮我们实现对于数据库的快速访问，后来，ORM（Object Relational Mapping，对象关系映射）出现了，我们开始使用 EF、Dapper、NHibernate，亦或是国人的 SqlSugar 代替我们原来的 SqlHelper.cs。通过这些 ORM 工具，我们可以很快速的将数据库中的表与代码中的类进行映射，同时，通过编写 SQL 或是 Lambda 表达式的方式，更加便捷的实现对于数据层的访问。

　　就像文章标题中所说的这样，在这个项目中我是使用的 Dapper 来进行的数据访问，每个人都有自己的编程习惯，本篇文章只是介绍我在 Grapefruit.VuCore 这个项目中是如何基于 Dapper 创建自己使用的帮助方法的，不会涉及各种 ORM 工具的对比，请友善查看、讨论。

　　系列目录地址：ASP.NET Core 项目实战
　　仓储地址：https://github.com/Lanesra712/Grapefruit.VuCore

 二、Step by Step

　　1、整体思路

　　在 Grapefruit.VuCore 这个项目中，我选择将 SQL 语句存储在 XML 文件中（XML 以嵌入的资源的方式嵌入到程序集中），通过编写中间件的方式，在程序运行时将存储有 SQL 语句的 XML 程序集写入到 Redis 缓存中。当使用到 SQL 语句时，通过 Redis 中的 Key 值进行获取到 Value，从而将 SQL 语句与我们的代码进行拆分。

　　涉及到的类文件主要是在以下的类库中，基于 Dapper 的数据访问代码则位于基础构造层（02_Infrastructure）中，而使用到这些数据访问代码的，有且仅在位于领域层（03_Domain）中的代码。同时，领域层的文件分布结构和应用层（04_Applicatin）保持相同。

　　2、扩展数据访问方法

　　在使用 Dapper 之前，我们首先需要在 Grapefruit.Infrastructure 这个类库中添加对于 Dapper 的引用。同时，因为需要将 SQL 语句存储到 Redis 缓存中，与之前使用 Redis 存储 Token 时相同，这里，也是使用的微软的分布式缓存接口，因此，同样需要添加对于此 DLL 的引用。

Install-Package Dapper
Install-Package Microsoft.Extensions.Caching.Abstractions

　　在 Grapefruit.Infrastructure 类库中创建一个 Dapper 文件夹，我们基于 Dapper 的扩展代码全部置于此处，整个的代码结构如下图所示。

　　在整个 Dapper 文件夹下类/接口/枚举文件，主要可以按照功能分为三部分。

　　2.1、辅助功能文件

　　主要包含 DataBaseTypeEnum 这个枚举类以及 SqlCommand 这个用来将存储在 XML 中的 SQL 进行映射的帮助类。

　　DataBaseTypeEnum 这个数据库类型枚举类主要定义了可以使用的数据库类型。我们知道，Dapper 这个 ORM 主要是通过扩展 IDbConnection 接口，从而给我们提供附加的数据操作功能，而我们在创建数据库连接对象时，不管是 SqlConnection 还是 MySqlConnection 最终对于数据库最基础的操作，都是继承于 IDbConnection 这个接口。因此，我们可以在后面创建数据库连接对象时，通过不同的枚举值，创建针对不同数据库操作的数据库连接对象。

public enum DataBaseTypeEnum
{
    SqlServer = 1,
    MySql = 2,
    PostgreSql = 3,
    Oracle = 4
}

　　SqlCommand 这个类文件只是定义了一些属性，因为我是将 SQL 语句写到 XML 文件中，同时会将 XML 文件存储到 Redis 缓存中，因此，SqlCommand 这个类主要用来将我们获取到的 SQL 语句与类文件做一个映射关系。

public class SqlCommand
{
    /// <summary>
    /// SQL语句名称
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// SQL语句或存储过程内容
    /// </summary>
    public string Sql { get; set; }
}

　　2.2、SQL 存储读取

　　对于 SQL 语句的存储、读取，我定义了一个 IDataRepository 接口，DataRepository 继承于 IDataRepository 实现对于 SQL 语句的操作。

public interface IDataRepository
{
    /// <summary>
    /// 获取 SQL 语句
    /// </summary>
    /// <param name="commandName"></param>
    /// <returns></returns>
    string GetCommandSQL(string commandName);

    /// <summary>
    /// 批量写入 SQL 语句
    /// </summary>
    void LoadDataXmlStore();
}

　　存储 SQL 的 XML 我是以附加的资源存储到 dll 中，因此，这里我是通过加载 dll 的方式获取到所有的 SQL 语句，之后，根据 Name 属性判断 Redis 中是否存在，当不存在时就写入 Redis 缓存中。核心的代码如下所示，如果你需要查看完整的代码，可以去 Github 上查看。

/// <summary>
/// 载入dll中包含的SQL语句
/// </summary>
/// <param name="fullPath">命令名称</param>
private void LoadCommandXml(string fullPath)
{
    SqlCommand command = null;
    Assembly dll = Assembly.LoadFile(fullPath);
    string[] xmlFiles = dll.GetManifestResourceNames();
    for (int i = 0; i < xmlFiles.Length; i++)
    {
        Stream stream = dll.GetManifestResourceStream(xmlFiles[i]);
        XElement rootNode = XElement.Load(stream);
        var targetNodes = from n in rootNode.Descendants("Command")
                          select n;
        foreach (var item in targetNodes)
        {
            command = new SqlCommand
            {
                Name = item.Attribute("Name").Value.ToString(),
                Sql = item.Value.ToString().Replace("<![CDATA[", "").Replace("]]>", "")
            };
            command.Sql = command.Sql.Replace("
", "").Replace("
", "").Trim();
            LoadSQL(command.Name, command.Sql);
        }
    }
}

/// <summary>
/// 载入SQL语句
/// </summary>
/// <param name="commandName">SQL语句名称</param>
/// <param name="commandSQL">SQL语句内容</param>
private void LoadSQL(string commandName, string commandSQL)
{
    if (string.IsNullOrEmpty(commandName))
    {
        throw new ArgumentNullException("CommandName is null or empty!");
    }

    string result = GetCommandSQL(commandName);

    if (string.IsNullOrEmpty(result))
    {
        StoreToCache(commandName, commandSQL);
    }
}

　　2.3、数据操作

　　对于数据的操作，这里我定义了 IDataAccess 这个接口，提供了同步、异步的方式，实现对于数据的访问。在项目开发中，对于数据的操作，更多的还是根据字段值获取对象、获取对象集合、执行 SQL 获取受影响的行数，获取字段值，所以，这里主要就定义了这几类的方法。

public interface IDataAccess
{
    /// 关闭数据库连接
    bool CloseConnection(IDbConnection connection);

    /// 数据库连接
    IDbConnection DbConnection();

    /// 执行SQL语句或存储过程返回对象
    T Execute<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text);

    /// 执行SQL语句返回对象
    T Execute<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text);

    /// 执行SQL语句或存储过程返回对象
    Task<T> ExecuteAsync<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text);

    /// 执行SQL语句返回对象
    Task<T> ExecuteAsync<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text);

    /// 执行SQL语句或存储过程，返回IList<T>对象
    IList<T> ExecuteIList<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text);

    /// 执行SQL语句或存储过程，返回IList<T>对象
    IList<T> ExecuteIList<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text);

    /// 执行SQL语句或存储过程，返回IList<T>对象
    Task<IList<T>> ExecuteIListAsync<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text);

    /// 执行SQL语句或存储过程，返回IList<T>对象
    Task<IList<T>> ExecuteIListAsync<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text);

    /// 执行SQL语句或存储过程返回受影响行数
    int ExecuteNonQuery(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text);

    /// 执行SQL语句或存储过程返回受影响行数
    int ExecuteNonQuery(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text);

    /// 执行SQL语句或存储过程返回受影响行数
    Task<int> ExecuteNonQueryAsync(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text);

    /// 执行SQL语句或存储过程返回受影响行数
    Task<int> ExecuteNonQueryAsync(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text);

    /// 执行语句返回T对象
    T ExecuteScalar<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text);

    /// 执行语句返回T对象
    Task<T> ExecuteScalarAsync<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text);
}

　　在 IDataAccess 接口的功能实现与调用上，我采用了代理模式的方式，会涉及到 DataAccess、DataAccessProxy、DataAccessProxyFactory、DBManager 这四个类文件，之间的调用过程如下。

　　DataAccess 是接口的实现类，通过下面的几个类进行隐藏，不直接暴露给外界方法。一些接口的实现如下所示。

/// <summary>
/// 创建数据库连接
/// </summary>
/// <returns></returns>
public IDbConnection DbConnection()
{
    IDbConnection connection = null;
    switch (_dataBaseType)
    {
        case DataBaseTypeEnum.SqlServer:
            connection = new SqlConnection(_connectionString);
            break;
        case DataBaseTypeEnum.MySql:
            connection = new MySqlConnection(_connectionString);
            break;
    };
    return connection;
}

/// <summary>
/// 执行SQL语句或存储过程，返回IList<T>对象
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="sql">SQL语句 or 存储过程名</param>
/// <param name="param">参数</param>
/// <param name="transaction">外部事务</param>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">命令类型</param>
/// <returns></returns>
public IList<T> ExecuteIList<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text)
{
    IList<T> list = null;
    if (connection.State == ConnectionState.Closed)
    {
        connection.Open();
    }
    try
    {
        if (commandType == CommandType.Text)
        {
            list = connection.Query<T>(sql, param, transaction, true, null, CommandType.Text).ToList();
        }
        else
        {
            list = connection.Query<T>(sql, param, transaction, true, null, CommandType.StoredProcedure).ToList();
        }
    }
    catch (Exception ex)
    {
        _logger.LogError($"SQL语句：{sql}，使用外部事务执行 ExecuteIList<T> 方法出错，错误信息：{ex.Message}");
        throw ex;
    }
    return list;
}

　　DBManager 是外界方法访问的类，通过 CreateDataAccess 方法会创建一个 IDataAccess 对象，从而达到访问接口中方法的目的。

[ThreadStatic]
private static IDataAccess _sMsSqlFactory;

/// <summary>
/// 
/// </summary>
/// <param name="cp"></param>
/// <returns></returns>
private static IDataAccess CreateDataAccess(ConnectionParameter cp)
{
    return new DataAccessProxy(DataAccessProxyFactory.Create(cp));
}

/// <summary>
/// MsSQL 数据库连接字符串
/// </summary>
public static IDataAccess MsSQL
{
    get
    {
        ConnectionParameter cp;
        if (_sMsSqlFactory == null)
        {
            cp = new ConnectionParameter
            {
                ConnectionString = ConfigurationManager.GetConfig("ConnectionStrings:MsSQLConnection"),
                DataBaseType = DataBaseTypeEnum.SqlServer
            };
            _sMsSqlFactory = CreateDataAccess(cp);
        }
        return _sMsSqlFactory;
    }
}

　　DataAccessProxy 就是实际接口功能实现类的代理，通过有参构造函数的方式进行调用，同时，类中继承于 IDataAccess 的方法都是不实现的，都是通过 _dataAccess 调用接口中的方法。

/// <summary>
/// 
/// </summary>
private readonly IDataAccess _dataAccess;

/// <summary>
/// ctor
/// </summary>
/// <param name="dataAccess"></param>
public DataAccessProxy(IDataAccess dataAccess)
{
    _dataAccess = dataAccess ?? throw new ArgumentNullException("dataAccess is null");
}

/// <summary>
/// 执行SQL语句或存储过程，返回IList<T>对象
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="sql">SQL语句 or 存储过程名</param>
/// <param name="param">参数</param>
/// <param name="transaction">外部事务</param>
/// <param name="connection">数据库连接</param>
/// <param name="commandType">命令类型</param>
/// <returns></returns>
public IList<T> ExecuteIList<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text)
{
    return _dataAccess.ExecuteIList<T>(sql, param, transaction, connection, commandType);
}

　　DataAccessProxyFactory 这个类有一个 Create 静态方法，通过实例化 DataAccess 类的方式返回 IDataAccess 接口，从而达到真正调用到接口实现类。

/// <summary>
/// 创建数据库连接字符串
/// </summary>
/// <param name="cp"></param>
/// <returns></returns>
public static IDataAccess Create(ConnectionParameter cp)
{
    if (string.IsNullOrEmpty(cp.ConnectionString))
    {
        throw new ArgumentNullException("ConnectionString is null or empty!");
    }
    return new DataAccess(cp.ConnectionString, cp.DataBaseType);
}

　　3、使用方法

　　因为我们对于 SQL 语句的获取全部是从缓存中获取的，因此，我们需要在程序执行前将所有的 SQL 语句写入 Redis 中。在 ASP.NET MVC 中，我们可以在 Application_Start 方法中进行调用，但是在 ASP.NET Core 中，我一直没找到如何实现仅在程序开始运行时执行代码，所以，这里，我采用了中间件的形式将 SQL 语句存储到 Redis 中，当然，你的每一次请求，都会调用到这个中间件。如果大家有好的方法，欢迎在评论区里指出。

public class DapperMiddleware
{
    private readonly ILogger _logger;

    private readonly IDataRepository _repository;

    private readonly RequestDelegate _request;

    /// <summary>
    /// ctor
    /// </summary>
    /// <param name="repository"></param>
    /// <param name="logger"></param>
    /// <param name="request"></param>
    public DapperMiddleware(IDataRepository repository, ILogger<DapperMiddleware> logger, RequestDelegate request)
    {
        _repository = repository;
        _logger = logger;
        _request = request;
    }

    /// <summary>
    /// 注入中间件到HttpContext中
    /// </summary>
    /// <param name="context"></param>
    /// <returns></returns>
    public async Task InvokeAsync(HttpContext context)
    {
        Stopwatch sw = new Stopwatch();
        sw.Start();

        //加载存储xml的dll
        _repository.LoadDataXmlStore();

        sw.Stop();
        TimeSpan ts = sw.Elapsed;

        _logger.LogInformation($"加载存储 XML 文件DLL，总共用时：{ts.TotalMinutes} 秒");

        await _request(context);
    }
}

　　中间件的实现，只是调用了之前定义的 IDataRepository 接口中的 LoadDataXmlStore 方法，同时记录下了加载的时间。在 DapperMiddlewareExtensions 这个静态类中，定义了中间件的使用方法，之后我们在 Startup 的 Configure 方法里调用即可。

public static class DapperMiddlewareExtensions
{
    /// <summary>
    /// 调用中间件
    /// </summary>
    /// <param name="builder"></param>
    /// <returns></returns>
    public static IApplicationBuilder UseDapper(this IApplicationBuilder builder)
    {
        return builder.UseMiddleware<DapperMiddleware>();
    }
}

　　中间件的调用代码如下，同时，因为我们在中间件中通过依赖注入的方式使用到了 IDataRepository 接口，所以，我们也需要在 ConfigureServices 中注入该接口，这里，采用单例的方式即可。

public class Startup
{
    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        //DI Sql Data
        services.AddTransient<IDataRepository, DataRepository>();
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IHostingEnvironment env, IApiVersionDescriptionProvider provider)
    {
        //Load Sql Data
        app.UseDapper();
    }
}

　　 当所有的 SQL 语句写入到缓存中后，我们就可以使用了，这里的示例代码实现的是上一篇（ASP.NET Core 实战：基于 Jwt Token 的权限控制全揭露）中，进行 Jwt Token 授权，验证登录用户信息的功能。

　　整个的调用过程如下图所示。

　　在 SecretDomain 中，我定义了一个 GetUserForLoginAsync 方法，通过帐户名和密码获取用户的信息，调用了之前定义的数据访问方法。 

public class SecretDomain : ISecretDomain
{
    #region Initialize

    /// <summary>
    /// 仓储接口
    /// </summary>
    private readonly IDataRepository _repository;

    /// <summary>
    /// ctor
    /// </summary>
    /// <param name="repository"></param>
    public SecretDomain(IDataRepository repository)
    {
        _repository = repository;
    }

    #endregion

    #region API Implements

    /// <summary>
    /// 根据帐户名、密码获取用户实体信息
    /// </summary>
    /// <param name="account">账户名</param>
    /// <param name="password">密码</param>
    /// <returns></returns>
    public async Task<IdentityUser> GetUserForLoginAsync(string account, string password)
    {
        StringBuilder strSql = new StringBuilder();
        strSql.Append(_repository.GetCommandSQL("Secret_GetUserByLoginAsync"));
        string sql = strSql.ToString();

        return await DBManager.MsSQL.ExecuteAsync<IdentityUser>(sql, new
        {
            account,
            password
        });
    }

    #endregion
}

　　XML 的结构如下所示，注意，这里需要修改 XML 的属性，生成操作改为附加的资源。

<?xml version="1.0" encoding="utf-8" ?>
<Commands>
  <Command Name="Secret_GetUserByLoginAsync">
    <![CDATA[
        SELECT Id ,Name ,Account ,Password ,Salt
          FROM IdentityUser
          WHERE Account=@account AND Password=@password;
      ]]>
  </Command>
  <Command Name="Secret_NewId">
    <![CDATA[
        select NEWID();
      ]]>
  </Command>
</Commands>

　　因为篇幅原因，这里就不把所有的代码都列出来，整个调用的过程演示如下，如果有不明白的，或是有什么好的建议的，欢迎在评论区中提出。因为，数据库表并没有设计好，这里只是建了一个实验用的表，，这里我使用的是 SQL Server 2012，创建表的 SQL 语句如下。

USE [GrapefruitVuCore]
GO

ALTER TABLE [dbo].[IdentityUser] DROP CONSTRAINT [DF_User_Id]
GO

/****** Object:  Table [dbo].[IdentityUser]    Script Date: 2019/2/24 9:41:15 ******/
DROP TABLE [dbo].[IdentityUser]
GO

/****** Object:  Table [dbo].[IdentityUser]    Script Date: 2019/2/24 9:41:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[IdentityUser](
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Account] [nvarchar](50) NOT NULL,
    [Password] [nvarchar](100) NOT NULL,
    [Salt] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK__User__3214EC07D257C709] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IdentityUser] ADD  CONSTRAINT [DF_User_Id]  DEFAULT (newid()) FOR [Id]
GO

 三、总结

 　　 这一章主要是介绍下我是如何使用 Dapper 构建我的数据访问帮助方法的，每个人都会有自己的编程习惯，这里只是给大家提供一个思路，适不适合你就不一定啦。因为年后工作开始变得多起来了，现在主要都是周末才能写博客了，所以更新的速度会变慢些，同时，这一系列的文章，按我的设想，其实还有一两篇文章差不多就结束了（VUE 前后端交互、Docker 部署），嗯，因为 Vue 那块我还在学习中（其实就是很长时间没看了。。。），所以接下来的一段时间可能会侧重于 Vue 系列（Vue.js 牛刀小试），ASP.NET Core 系列可能会不定期更新，希望大家同样可以多多关注啊。最后，感谢之前赞赏的小伙伴。

作者：墨墨墨墨小宇

出处：https://www.cnblogs.com/danvic712/p/10425501.html