SqlDapperEasyUtil:.NET CORE下的Dapper封装操作类

之前介绍了基于Dapper二次封装了一个易用的ORM工具类:SqlDapperUtil,这个在.NET FX下还是比较好用的,现在都流行.NET CORE,故我这边再次进行精简修改,以便适应.NET CORE并支持依赖注入。

  1. 提取定义了一个通用访问数据的接口:

    public interface IDbAccesser
        {
            void Commit();
            bool ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null);
            T GetDynamicModel<T>(Func<IEnumerable<dynamic>, T> buildModelFunc, string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);
            Dictionary<string, dynamic> GetFirstValues(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null);
            T GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) where T : class;
            List<T> GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class;
            List<T> GetMultModelList<T>(string sql, Type[] types, Func<object[], T> map, object param = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);
            T GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null);
            void Rollback();
            void UseDbTransaction();
        }
  2. 精简版的Dapper封装操作类:SqlDapperEasyUtil:

        /// <summary>
        /// 基于Dapper的数据操作类封装的工具类(简易版)
        /// Author:左文俊
        /// Date:2019/6/28
        /// </summary>
        public class SqlDapperEasyUtil : IDbAccesser
        {
            private readonly string dbConnectionString = null;
            private const string dbProviderName = "System.Data.SqlClient";
            private IDbConnection dbConnection = null;
            private bool useDbTransaction = false;
            private IDbTransaction dbTransaction = null;
    
            static SqlDapperEasyUtil()
            {
                DbProviderFactories.RegisterFactory(dbProviderName, SqlClientFactory.Instance);//.NET CORE需先提前注册
            }
    
            #region 私有方法
    
            private IDbConnection GetDbConnection()
            {
                bool needCreateNew = false;
                if (dbConnection == null || string.IsNullOrWhiteSpace(dbConnection.ConnectionString))
                {
                    needCreateNew = true;
                }
    
                if (needCreateNew)
                {
                    var dbProviderFactory = DbProviderFactories.GetFactory(dbProviderName);
                    dbConnection = dbProviderFactory.CreateConnection();
                    dbConnection.ConnectionString = dbConnectionString;
                }
    
                if (dbConnection.State == ConnectionState.Closed)
                {
                    dbConnection.Open();
                }
    
                return dbConnection;
            }
    
    
    
            private T UseDbConnection<T>(Func<IDbConnection, T> queryOrExecSqlFunc)
            {
                IDbConnection dbConn = null;
    
                try
                {
                    dbConn = GetDbConnection();
                    if (useDbTransaction && dbTransaction == null)
                    {
                        dbTransaction = GetDbTransaction();
                    }
    
                    return queryOrExecSqlFunc(dbConn);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (dbTransaction == null && dbConn != null)
                    {
                        CloseDbConnection(dbConn);
                    }
                }
            }
    
            private void CloseDbConnection(IDbConnection dbConn, bool disposed = false)
            {
                if (dbConn != null)
                {
                    if (disposed && dbTransaction != null)
                    {
                        dbTransaction.Rollback();
                        dbTransaction.Dispose();
                        dbTransaction = null;
                    }
    
                    if (dbConn.State != ConnectionState.Closed)
                    {
                        dbConn.Close();
                    }
                    dbConn.Dispose();
                    dbConn = null;
                }
            }
    
            /// <summary>
            /// 获取一个事务对象(如果需要确保多条执行语句的一致性,必需使用事务)
            /// </summary>
            /// <param name="il"></param>
            /// <returns></returns>
            private IDbTransaction GetDbTransaction(IsolationLevel il = IsolationLevel.Unspecified)
            {
                return GetDbConnection().BeginTransaction(il);
            }
    
    
            #endregion
    
            public SqlDapperEasyUtil(string connStr)
            {
                dbConnectionString = connStr;
            }
    
    
            /// <summary>
            /// 使用事务
            /// </summary>
            public void UseDbTransaction()
            {
                useDbTransaction = true;
            }
    
    
            /// <summary>
            /// 获取一个值,param可以是SQL参数也可以是匿名对象
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="transaction"></param>
            /// <param name="commandTimeout"></param>
            /// <param name="commandType"></param>
            /// <returns></returns>
            public T GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
            {
                return UseDbConnection((dbConn) =>
                 {
                     return dbConn.ExecuteScalar<T>(sql, param, dbTransaction, commandTimeout, commandType);
                 });
            }
    
            /// <summary>
            /// 获取第一行的所有值,param可以是SQL参数也可以是匿名对象
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="transaction"></param>
            /// <param name="commandTimeout"></param>
            /// <param name="commandType"></param>
            /// <returns></returns>
            public Dictionary<string, dynamic> GetFirstValues(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
            {
                return UseDbConnection((dbConn) =>
                {
                    Dictionary<string, dynamic> firstValues = new Dictionary<string, dynamic>();
                    List<string> indexColNameMappings = new List<string>();
                    int rowIndex = 0;
                    using (var reader = dbConn.ExecuteReader(sql, param, dbTransaction, commandTimeout, commandType))
                    {
                        while (reader.Read())
                        {
                            if ((++rowIndex) > 1) break;
                            if (indexColNameMappings.Count == 0)
                            {
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    indexColNameMappings.Add(reader.GetName(i));
                                }
                            }
    
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                firstValues[indexColNameMappings[i]] = reader.GetValue(i);
                            }
                        }
                        reader.Close();
                    }
    
                    return firstValues;
    
                });
            }
    
            /// <summary>
            /// 获取一个数据模型实体类,param可以是SQL参数也可以是匿名对象
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="transaction"></param>
            /// <param name="commandTimeout"></param>
            /// <param name="commandType"></param>
            /// <returns></returns>
            public T GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) where T : class
            {
                return UseDbConnection((dbConn) =>
                {
                    return dbConn.QueryFirstOrDefault<T>(sql, param, dbTransaction, commandTimeout, commandType);
                });
            }
    
            /// <summary>
            /// 获取符合条件的所有数据模型实体类列表,param可以是SQL参数也可以是匿名对象
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="transaction"></param>
            /// <param name="buffered"></param>
            /// <param name="commandTimeout"></param>
            /// <param name="commandType"></param>
            /// <returns></returns>
            public List<T> GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class
            {
                return UseDbConnection((dbConn) =>
                {
                    return dbConn.Query<T>(sql, param, dbTransaction, buffered, commandTimeout, commandType).ToList();
                });
            }
    
            /// <summary>
            /// 获取符合条件的所有数据并根据动态构建Model类委托来创建合适的返回结果(适用于临时性结果且无对应的模型实体类的情况)
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="buildModelFunc"></param>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="buffered"></param>
            /// <param name="commandTimeout"></param>
            /// <param name="commandType"></param>
            /// <returns></returns>
            public T GetDynamicModel<T>(Func<IEnumerable<dynamic>, T> buildModelFunc, string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
            {
                var dynamicResult = UseDbConnection((dbConn) =>
               {
                   return dbConn.Query(sql, param, dbTransaction, buffered, commandTimeout, commandType);
               });
    
                return buildModelFunc(dynamicResult);
            }
    
            /// <summary>
            /// 获取符合条件的所有指定返回结果对象的列表(复合对象【如:1对多,1对1】),param可以是SQL参数也可以是匿名对象
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="sql"></param>
            /// <param name="types"></param>
            /// <param name="map"></param>
            /// <param name="param"></param>
            /// <param name="transaction"></param>
            /// <param name="buffered"></param>
            /// <param name="splitOn"></param>
            /// <param name="commandTimeout"></param>
            /// <param name="commandType"></param>
            /// <returns></returns>
    
            public List<T> GetMultModelList<T>(string sql, Type[] types, Func<object[], T> map, object param = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
            {
                return UseDbConnection((dbConn) =>
                {
                    return dbConn.Query<T>(sql, types, map, param, dbTransaction, buffered, splitOn, commandTimeout, commandType).ToList();
                });
            }
    
    
    
    
            /// <summary>
            /// 执行SQL命令(CRUD),param可以是SQL参数也可以是要添加的实体类
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <param name="transaction"></param>
            /// <param name="commandTimeout"></param>
            /// <param name="commandType"></param>
            /// <returns></returns>
            public bool ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
            {
                return UseDbConnection((dbConn) =>
                {
                    int result = dbConn.Execute(sql, param, dbTransaction, commandTimeout, commandType);
                    return (result > 0);
                });
            }
    
    
    
            /// <summary>
            /// 当使用了事务,则最后需要调用该方法以提交所有操作
            /// </summary>
            /// <param name="dbTransaction"></param>
            public void Commit()
            {
                try
                {
                    if (dbTransaction.Connection != null && dbTransaction.Connection.State != ConnectionState.Closed)
                    {
                        dbTransaction.Commit();
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (dbTransaction.Connection != null)
                    {
                        CloseDbConnection(dbTransaction.Connection);
                    }
                    dbTransaction.Dispose();
                    dbTransaction = null;
                    useDbTransaction = false;
    
                    if (dbConnection != null)
                    {
                        CloseDbConnection(dbConnection);
                    }
                }
            }
    
            /// <summary>
            /// 当使用了事务,如果报错或需要中断执行,则需要调用该方法执行回滚操作
            /// </summary>
            /// <param name="dbTransaction"></param>
            public void Rollback()
            {
                try
                {
                    if (dbTransaction.Connection != null && dbTransaction.Connection.State != ConnectionState.Closed)
                    {
                        dbTransaction.Rollback();
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (dbTransaction.Connection != null)
                    {
                        CloseDbConnection(dbTransaction.Connection);
                    }
    
                    dbTransaction.Dispose();
                    dbTransaction = null;
                    useDbTransaction = false;
                }
            }
    
            ~SqlDapperEasyUtil()
            {
                try
                {
                    CloseDbConnection(dbConnection, true);
                }
                catch
                { }
            }
    
        }
  3. 在ASP.NET CORE中应用:

    //1.在Startup.ConfigureServices方法注入依赖
    //如果在多个并发场景中使用,建议使用:AddTransient
     services.AddScoped<IDbAccesser>(provider =>
                {
                    string connStr = provider.GetService<IConfiguration>().GetConnectionString("配置连接的name");
                    return new SqlDapperEasyUtil(connStr);
                });
    
    //2.在具体的controller、service中通过构造函数注入或其它方式注入获取实例,如:
            [Route("PushRealNameCheck")]
            [HttpPost]
            public ApiResult PushRealNameCheck([FromServices] RealNameCheckService realNameCheckService, [FromBody]RealNameCheckReqeust realNameCheckReqeust)
            {
                return realNameCheckService.PushRealNameCheck(realNameCheckReqeust);
            }
    
    
    
        public class RealNameCheckService
        {
            private ILogger<RealNameCheckService> logger;
            private IDbAccesser dbAccesser;
            public RealNameCheckService(ILogger<RealNameCheckService> logger, IDbAccesser dbAccesser)
            {
                this.logger = logger;
                this.dbAccesser = dbAccesser;
            }
    
            //其它方法代码(如:PushRealNameCheck方法),在此省略...
            //若需操作DB,则可使用dbAccesser变即可。
        }

    如有疑问或好的建议,欢迎评论交流。

SqlDapperEasyUtil:.NET CORE下的Dapper封装操作类

上一篇:App自动化测试(一)之环境搭建


下一篇:030.CI4框架CodeIgniter, 文件的上传与移动