Dapper的正确使用姿势

谁借莪１个温暖的怀抱￠ 2022-01-29 04:21 453阅读 0赞

# Dapper优势和缺点 #

### 优点 ###

高性能、易排查、易运维、灵活可控

### 缺点 ###

和EF相比，手写sql当修改表结构不易发现bug。  
习惯了EF后再来使用Dapper，会很难适应那种没有了强类型的安全感。不过可以用单元测和心细来避免。

# 数据库连接 #

问题：IDbConnection需不需要手动Open打开连接  
答案：有时候需要有时候不需要

Dapper连接可分两种：主动管理（自己管理连接的打开和关闭）和自动管理（自动管理连接的打开和关闭）

//短短三行代码即实现了dapper连接的主动管理和自动管理
    
    bool wasClosed = cnn.State == ConnectionState.Closed;//判断连接是否为关闭状态
    ...
    if (wasClosed) cnn.Open();
    ...
    if (wasClosed) cnn.Close();

源码位置 [https://github.com/StackExchange/Dapper/blob/master/Dapper/SqlMapper.cs\#L530][https_github.com_StackExchange_Dapper_blob_master_Dapper_SqlMapper.cs_L530]

Note：ADO.NET默认是启用连接池的 Pooling = true,连接池中最大连接数,默认为100

在使用Dapper的过程中，你有可能遇到过连接池超过最大限制。那问题是怎么来的呢？  
如果主动管理或者自动管理连接都不会有问题。就怕你管理一半，打开不关闭：

//循环执行两百次左右就可以重现连接池超过最大限制
    DBContext dBContext2 = new DBContext();
    dBContext2.DbConnection.Open();

解决办法相信不用我说了。

Note：在使用事务的时候需要手动打开连接，请不要忘记在catch里面Close。

# 增删改查的优化 #

### 批量新增 ###

//1、可通过匿名对象集合进行参数化数据新增。（性能优化参考3）
    DbConnection.Execute(sqlStr, ListEntity);
    //2、【sql拼接可大大优化执行效率】在values后面带上多有要插入的值。（如果数据太大可分批插入，如1000条一提交）
    insert into tt (a,b,c,d) values (50,1,'1','1'), (51,2,'1','2'); 
    //3、参数化防sql注入
    var sql = insert into tt (a,b,c,d) values (@a1,@b1,@c1,@d1), (@a2,@b2,@c2,@d2); 
    DynamicParameters dynamicParameters = new DynamicParameters();
    dynamicParameters.Add("a1","value");
    dynamicParameters.Add("b1","value");
    dynamicParameters.Add("c1","value");
    dynamicParameters.Add("a2","value");
    dynamicParameters.Add("b2","value");
    dynamicParameters.Add("c2","value");
    dynamicParameters.Add("d2","value");
    DbConnection.ExecuteScalar<int>(sql, dynamicParameters)

### 批量修改 ###

//1、可通过匿名对象集合进行参数化数据修改。（需要修改的值都不一样的情况下,性能优化参考4）
    DbConnection.Execute(sqlStr, ListEntity);
    //2、如果需要修改的值都是一样，只是条件不一样。(使用SQL语句中的IN语法)
    DbConnection.Execute("UPDATE tt SET aa = @aa where bb in @bb;", new { aa, bb }); 
    //3、快速批量修改（此方法非常适合`新增或修改`数据的场景，可通过建联合唯一索引来实现新增或修改的区分。【组合字段不能为空，否则为空 不做唯一，有重复空数据】）
    insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);
    //4、参数化防sql注入
    var sql = insert into test_tbl (id,dr) values (@id1,@dr1),(@id2,@dr2),...(@idn,@drn) on duplicate key update dr=values(dr);
    DynamicParameters dynamicParameters = new DynamicParameters();
    dynamicParameters.Add("id1","value");
    dynamicParameters.Add("dr1","value");
    dynamicParameters.Add("id2","value");
    dynamicParameters.Add("dr2","value");
    ...
    dynamicParameters.Add("idn","value");
    dynamicParameters.Add("drn","value");
    DbConnection.ExecuteScalar<int>(sql, dynamicParameters)

### 批量删除 ###

同理，也可以使用参数化和IN语法

### 查询第一条数据 ###

dBContext.DbConnection.QueryFirstOrDefault<ItemFCLPO>("SELECT * from itemfcl_temp limit 1;");    //正确
    dBContext.DbConnection.QueryFirstOrDefault<ItemFCLPO>("SELECT * from itemfcl_temp;");            //错误
    dBContext.DbConnection.Query<ItemFCLPO>("SELECT * from itemfcl_temp;").FirstOrDefault();         //错误
    dBContext.DbConnection.Query<ItemFCLPO>("SELECT * from itemfcl_temp;").ToList().FirstOrDefault();//错误

### If扩展方法 ###

使用过Mybatis的同学都知道，在xml里面写if、else还是蛮好用的。虽然我还是不喜欢在xml里面写sql。  
那么在Dapper里面是不是也能简便操作，答案是肯定的。这就得庆幸C\#牛逼的语法了。

public static class StringExtension
    {
        public static string If(this string str, bool condition)
        {
            return condition ? str : string.Empty;
        }
    }

然后我们的sql就可以这样拼接了

left join MaintenanceTemplates it on it.Id = m.MaintenanceTemplateId
    where m.IsDeleted = 0
    {" and m.Code = @KeyWord ".If(!string.IsNullOrWhiteSpace(input.KeyWord))}
    {" and m.ProjectId = @ProjectId ".If(input.ProjectId.HasValue)}
    {" and a.ProductId = @ProductId ".If(input.ProductId.HasValue)}

比起以前又臭又长的if判断，个人感觉好多了。

Note：Dapper不会因为传多了参数而报错，所以放心使用If。

# 工作单元 #

使用EF的时候很方便做事务处理，而在Dapper中貌似就没那么优雅了。  
我们每次在事务逻辑开始前都需要`BeginTransaction`开启，事务结束后都需要`CommitTransaction`提交。代码看起来也就稍显混乱。  
如果我们通过特性标记的方式，在标记了`UnitOfWork`特性的方法自动开启和提交事务那就完美了。如下：

[UnitOfWork]
    public virtual void Test()
    {
        //执行业务逻辑
    }

当然，这是可行的。通过AOP拦截，在方法执行前开启事务，在方法执行后提交事务就可以了。  
实现如下：  
需要Nuget包`Autofac.Extensions.DependencyInjection` `Autofac.Extras.DynamicProxy`

[UnitOfWork]
    public virtual void DelUser()
    {
        var sql = "select * from UserTemp";
        var userList = dBContext.DbConnection.Query<object>(sql);
    
        var sql2 = $@"INSERT into UserTemp VALUES(0,'{DateTime.Now.ToString()}','sql2执行成功')";
        dBContext.DbConnection.Execute(sql2);
        throw new Exception("主动报错");//验证事务 是否有效
    
        var sq3 = $@"INSERT into UserTemp VALUES(0,'{DateTime.Now.ToString()}','sq3执行成功')";
        dBContext.DbConnection.Execute(sq3);
    }
    public class UnitOfWorkIInterceptor : IInterceptor
    {
        private DBContext dBContext;
        public UnitOfWorkIInterceptor(DBContext dBContext)
        {
            this.dBContext = dBContext;
        }
        public void Intercept(IInvocation invocation)
        {
            MethodInfo methodInfo = invocation.MethodInvocationTarget;
            if (methodInfo == null)
                methodInfo = invocation.Method;
    
            UnitOfWorkAttribute transaction = methodInfo.GetCustomAttributes<UnitOfWorkAttribute>(true).FirstOrDefault();
            //如果标记了 [UnitOfWork]，并且不在事务嵌套中。
            if (transaction != null && dBContext.Committed)
            {
                //开启事务
                dBContext.BeginTransaction();
                try
                {
                    //事务包裹 查询语句 
                    //https://github.com/mysql-net/MySqlConnector/issues/405
                    invocation.Proceed();
                    //提交事务
                    dBContext.CommitTransaction();
                }
                catch (Exception ex)
                {
                    //回滚
                    dBContext.RollBackTransaction();
                    throw;
                }
            }
            else
            {
                //如果没有标记[UnitOfWork]，直接执行方法
                invocation.Proceed();
            }
        }
    }

完整的测试源码，会在文末提供。

# SQL监控 #

使用EF的同学应该很多人都知道`MiniProfiler`，我在前些年[分享EF的时候][EF]有做过简单介绍。  
那么我们在执行Dapper的时候是不是也可以对生成的sql做检测和性能监控。  
答案是肯定的。[Git地址][Git]

MiniProfiler监控套件还真不是一般的强。EF、MongoDB、MySql、Redis、SqlServer统统支持。  
接下来我们实现对Dapper监控，导入Nuget包`MiniProfiler.AspNetCore`

public class ActionFilter : IAsyncActionFilter
    {
        public async Task OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
        {
            var profiler = MiniProfiler.StartNew("StartNew");
            using (profiler.Step("Level1"))
            {
                //执行Action
                await next();
            }
            WriteLog(profiler);
        }
    
        /// <summary>
        /// sql跟踪
        /// 下载：MiniProfiler.AspNetCore
        /// </summary>
        /// <param name="profiler"></param>
        private void WriteLog(MiniProfiler profiler)
        {
            if (profiler?.Root != null)
            {
                var root = profiler.Root;
                if (root.HasChildren)
                {
                    root.Children.ForEach(chil =>
                    {
                        if (chil.CustomTimings?.Count > 0)
                        {
                            foreach (var customTiming in chil.CustomTimings)
                            {
                                var all_sql = new List<string>();
                                var err_sql = new List<string>();
                                var all_log = new List<string>();
                                int i = 1;
                                customTiming.Value?.ForEach(value =>
                                {
                                    if (value.ExecuteType != "OpenAsync")
                                        all_sql.Add(value.CommandString);
                                    if (value.Errored)
                                        err_sql.Add(value.CommandString);
                                    var log = $@"【{customTiming.Key}{i++}】{value.CommandString} Execute time :{value.DurationMilliseconds} ms,Start offset :{value.StartMilliseconds} ms,Errored :{value.Errored}";
                                    all_log.Add(log);
                                });
    
                                //TODO  日志记录
                                //if (err_sql.Any())
                                //    Logger.Error(new Exception("sql异常"), "异常sql:\r\n" + string.Join("\r\n", err_sql), sql: string.Join("\r\n\r\n", err_sql));
                                //Logger.Debug(string.Join("\r\n", all_log), sql: string.Join("\r\n\r\n", all_sql));
                            }
                        }
                    });
                }
            }
        }
    }

运行效果：

# Demo源码 #

完整的Demo源码：[https://github.com/zhaopeiym/BlogDemoCode/tree/master/Dapper\_Demo/DapperDemo][https_github.com_zhaopeiym_BlogDemoCode_tree_master_Dapper_Demo_DapperDemo]

# 结束 #

最后给大家推荐一个开源项目[quartzui][]：[https://github.com/zhaopeiym/quartzui][quartzui]  
基于Quartz.NET 3.0的web管理界面，开箱即用。也可以完美运行在树莓派上。

docker run -v /fileData/quartzuifile:/app/File  --restart=unless-stopped --privileged=true --name quartzui -dp 5088:80 bennyzhao/quartzui:RaspberryPi

运行在普通PC或云主机上

docker run -v /fileData/quartzuifile:/app/File  --restart=unless-stopped --privileged=true --name quartzui -dp 5088:80 bennyzhao/quartzui

[https_github.com_StackExchange_Dapper_blob_master_Dapper_SqlMapper.cs_L530]: https://links.jianshu.com/go?to=https%3A%2F%2Fgithub.com%2FStackExchange%2FDapper%2Fblob%2Fmaster%2FDapper%2FSqlMapper.cs%23L530
[EF]: https://links.jianshu.com/go?to=https%3A%2F%2Fwww.cnblogs.com%2Fzhaopei%2Fp%2F5721789.html
[Git]: https://links.jianshu.com/go?to=https%3A%2F%2Fgithub.com%2FMiniProfiler%2Fdotnet
[https_github.com_zhaopeiym_BlogDemoCode_tree_master_Dapper_Demo_DapperDemo]: https://links.jianshu.com/go?to=https%3A%2F%2Fgithub.com%2Fzhaopeiym%2FBlogDemoCode%2Ftree%2Fmaster%2FDapper_Demo%2FDapperDemo
[quartzui]: https://links.jianshu.com/go?to=https%3A%2F%2Fgithub.com%2Fzhaopeiym%2Fquartzui