Dapper

什么是Dapper？
Dapper是Stack Overflow团队开发的Micro-ORM框架，Dapper是一个简单对象mapper对于Net并且在速度方面有King of Micro ORM称号，几乎与原生ADO.NET data reader一样。ORM是一个对象关系映射,负责映射database和应用程序对象。

Dapper比Entity Framework快的原因是Dapper简洁，不像EF有复杂的逻辑，例如数据追踪，不高效的数据查询，支持懒加载等。
当然EF Core也是相当快的，问题取决于你是倾向于写更多的sql 还是linq。

Dapper工作原理
Dapper扩展IDbConnection接口提供扩展方法来查询数据库，它使用动态方式生成，使其能够直接从查询结果映射POCO，也允许直接从database columns映射到POCO 属性。
Dapper是大小写不敏感的。

Dapper支持多种数据类型
Anonymous：Useful for simple queries where you don't need to create a separate class to represent your data.
Dynamic： Useful for when you need to create a dynamic list of parameters, or when you need to dynamically change the value of a parameter.
List: This allows you to specify multiple parameters on an IN clause by using a list.
String: Useful when working with SQL Server stored procedures that accept varchar input parameters.

Dapper Plus是一个高性能的micro-ORM框架，提供了简单的方式映射class到数据库table and vice versa，Dapper plus也提供了bulk 操作，提高应用程序的性能，Dapper plus扩展了IDbConnection 接口支持bulk：
Bulk Insert
Bulk Update
Bulk Delete
Bulk Merge
不幸的是Dapper Plus是收费的。

Dapper -Transaction
Dapper支持管理Transaction，Transaction允许你将多个操作分组到一个工作单元中，之后可以作为一个单元执行committed或rolled back。

下面通过Code详细解析在项目中的应用场景：

首先创建Dapper Library应用

1、创建2张表，Company&User，Company与User为1:N的关系

CREATE TABLE [dbo].[Company](
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Location] [nvarchar](50) NULL,
 CONSTRAINT [PK_Company] 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]


CREATE TABLE [dbo].[User](
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [DisplayName] [nvarchar](50) NULL,
    [CompanyId] [uniqueidentifier] NULL,
 CONSTRAINT [PK_User] 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].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([Id])

FK_<tablename>_<tablename>, where the first tablename is the name of the foreign key table, and the second tablename is the name of the primary key table. This is simply a default and common naming convention for the (Name) field of the foreign key object.

2、创建DB table映射的实体类

public abstract class Entity<TKey>
{
    [ExplicitKey]
    public virtual TKey Id { get; set; }
}

[TableAttribute("Company")]
public class Company : Entity<Guid>
{
    public string Name { get; set; }
    public string Location { get; set; }
}

[TableAttribute("User")]
public class User : Entity<Guid>
{
    public string Name { get; set; }
    public string DisplayName { get; set; }
    public Guid CompanyId { get; set; }
}

3、Nuget Package Manager添加package引用
Dapper.Contrib(2.0.78)

System.Data.SqlClient(4.8.5)

4、自定义BaseRepository及Service Repository

using Dapper;
using Dapper.Contrib.Extensions;
using System.Data;

namespace API.Dapper.Repository;

public abstract class BaseRepository<T> where T : class, new()
{
    protected IDbTransaction _transaction;
    protected IDbConnection _connection 
    {
        get { return _transaction?.Connection; }
    }
    protected Int32 _commandTimeout { get; set; } = 30;

    public BaseRepository(IDbTransaction transaction)
    {
        this._transaction = transaction;
    }

    public async Task<IEnumerable<T>> GetAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return await _connection.QueryAsync<T>(sql, param, _transaction, _commandTimeout, CommandType.Text);
    }

    public async Task AddAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout, CommandType.Text);
    }

    public Task<bool> DeleteAsync(T t)
    {
        return _connection.DeleteAsync(t, _transaction, commandTimeout: _commandTimeout);
    }

    public async Task DeleteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout, CommandType.Text);
    }

    public async Task<Int32> UpdateAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout,CommandType.Text);
    }
}

using API.Dapper.Entity;
using System.Data;

namespace API.Dapper.Repository;

public class CompanyRepository : BaseRepository<Company>
{
    public CompanyRepository(IDbTransaction transaction)
        : base(transaction)
    {

    }
}


public class UserRepository: BaseRepository<User>
{
    public UserRepository(IDbTransaction transaction)
        : base(transaction)
    {
        
    }
}

5、创建Dapper 工作单元，工作单元的作用是为了保证数据的一致性，当一个request需要操作多个table，可以将多个操作放在一个unitOfWork中，只有多个操作全部执行成功才执行Committed，否则执行Rollback,保证操作的原子性从而保证数据的一致性及完整性。

public interface IUnitOfWork : IDisposable
{
    IDbConnection DbConnection { get; set; }

    IDbTransaction DbTransaction { get; set; }

    void Commit();

    void Rollback();
}

using System.Data;
using System.Data.SqlClient;

namespace API.Dapper.UnitOfWork;

public class UnitOfWork : IUnitOfWork
{
    public IDbConnection DbConnection { get; set; }
    public IDbTransaction DbTransaction { get; set; }

    public UnitOfWork(string connectionString)
        : this(connectionString, IsolationLevel.Unspecified)
    {
        
    }

    public UnitOfWork(string connectionString, IsolationLevel isolationLevel)
    {
        DbConnection = new SqlConnection(connectionString);
        DbConnection.Open();
        DbTransaction = DbConnection.BeginTransaction(isolationLevel);
    }

    public void Commit()
    {
        try
        {
            DbTransaction.Commit();
        }
        catch
        {
            DbTransaction.Rollback();
        }
    }

    public void Dispose()
    {
        if (DbTransaction != null)
        {
            DbTransaction.Dispose();
            DbTransaction = null;
        }
        if (DbConnection != null)
        {
            DbConnection.Dispose();
            DbConnection = null;
        }
        GC.SuppressFinalize(this);
    }

    public void Rollback()
    {
        DbTransaction.Rollback();
    }

    public async Task<IEnumerable<T>> BatchQuery<T>(IEnumerable<Guid> objs, Func<List<Guid>, Task<IEnumerable<T>>> func, Int32 batchSize = 2100)
    {
        var result = new List<T>();
        var skip = 0;
        List<Guid> pageObjs;
        do
        {
            pageObjs = objs.Skip(skip * batchSize).Take(batchSize).ToList();
            var pageResult = await func(pageObjs);
            result.AddRange(pageResult);
            skip++;
        } while (pageObjs.Count == batchSize);
        return result;
    }

    public async Task<Int32> RunBatch<T>(IEnumerable<T> objs, Func<List<T>, Task<Int32>> func, Int32 batchSize = 2100)
    {
        var affectedRows = 0;
        var skip = 0;
        List<T> pageObjs;
        do
        {
            pageObjs = objs.Skip(skip * batchSize).Take(batchSize).ToList();
            var pageAffectedRows = await func(pageObjs);
            affectedRows += pageAffectedRows;
            skip++;
        } while (pageObjs.Count == batchSize);
        return affectedRows;
    }
}

using API.Dapper.Repository;

namespace API.Dapper.UnitOfWork;

public class UserCompanyUnitOfWork : UnitOfWork
{
    public UserRepository UserRepository { get;  }
    public CompanyRepository CompanyRepository { get; }

    public UserCompanyUnitOfWork(string connectionString)
        : base(connectionString)
    {
        UserRepository = new UserRepository(DbTransaction);
        CompanyRepository = new CompanyRepository(DbTransaction);
    }
}

 

然后创建NetCore web project，添加Dapper Library的引用
1、配置文件指定数据库连接字符串

  "ConnectionString": "Server=localhost;Database=API.Demo;User Id=sa;Password=1qaz2wsxE;Trusted_Connection=False;"

2、创建UserService操作User table

using API.Dapper.Entity;
using API.Dapper.UnitOfWork;
using Dapper;

namespace API.ServiceA.Service;

public class UserService
{
    private readonly IConfiguration _configuration;
    public UserService(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public async Task<IEnumerable<User>> GetAllAsync()
    {
        using UserCompanyUnitOfWork unitOfWork = new UserCompanyUnitOfWork(_configuration["ConnectionString"]);
        var sql = $"SELECT * FROM [{typeof(User).Name}]";
        return await unitOfWork.UserRepository.GetAsync(sql);
    }

    public async Task<IEnumerable<User>> GetAsync(IEnumerable<Guid> ids, IEnumerable<string> selectedProperties)
    {
        var properties = typeof(User).GetProperties().Select(p => p.Name);
        var selectClause = string.Join(",", properties.Intersect(selectedProperties));
        using UserCompanyUnitOfWork unitOfWork = new UserCompanyUnitOfWork(_configuration["ConnectionString"]);
        return await unitOfWork.BatchQuery<User>(ids, async pageIds => 
        {
            var sql = $"SELECT {selectClause} FROM [{typeof(User).Name}] WHERE Id IN @Ids";
            var sqlParams = new DynamicParameters();
            sqlParams.Add("Ids", ids);
            return await unitOfWork.UserRepository.GetAsync(sql, sqlParams);
        }, 2100);
    }

    public async Task AddAsync(User user)
    {
        try
        {
            using UserCompanyUnitOfWork unitOfWork = new UserCompanyUnitOfWork(_configuration["ConnectionString"]);
            var properties = typeof(User).GetProperties().Select(p => p.Name);
            var sql = $"INSERT INTO [{typeof(User).Name}]({string.Join(",", properties)}) VALUES({string.Join(",", properties.Select(p => $"@{p}"))})";
            await unitOfWork.UserRepository.AddAsync(sql, user);
            unitOfWork.Commit();
        }
        catch (Exception)
        {
            throw;
        }
    }

    public async Task UpdateAsync(IEnumerable<User> users, IEnumerable<string> updateProperties, IEnumerable<string> whereProperties)
    {
        try
        {
            var properties = typeof(User).GetProperties().Select(p => p.Name);
            var updateClause = string.Join(",", properties.Intersect(updateProperties).Select(p => $"{p}=@{p}"));
            var whereClause = string.Join(" AND ", properties.Intersect(whereProperties).Select(p => $"{p}=@{p}"));
            var sql = $"UPDATE [dbo].[{typeof(User).Name}] SET {updateClause} WHERE {whereClause}";
            using UserCompanyUnitOfWork unitOfWork = new UserCompanyUnitOfWork(_configuration["ConnectionString"]);
            await unitOfWork.UserRepository.UpdateAsync(sql, users);
            unitOfWork.Commit();
        }
        catch (Exception)
        {
            throw;
        }
    }
}

3、添加测试controller

private readonly UserService _userService;
        public ServiceAController(UserService userService)
        { 
            _userService = userService;
        }

        [HttpPost("user")]
        public async Task<IActionResult> AddUser([FromBody] User user)
        {
            await _userService.AddAsync(user);
            return new JsonResult("OK");
        }

        [HttpPatch("updateuser")]
        public async Task<IActionResult> UpadateUser()
        {
            var users = await _userService.GetAllAsync();         
            users = await _userService.GetAsync(users.Select(u => u.Id), typeof(User).GetProperties().Select(p => p.Name));
            Array.ForEach(users.ToArray(),
               user =>
               {
                   user.DisplayName = $"{user.DisplayName}_updated";
               });
            await _userService.UpdateAsync(users, new List<string> { "DisplayName" }, new List<string> { "Id" });
            return new JsonResult("OK");
        }

4、添加default company record，原因是添加user record有外键依赖company