SqlDapperEasyUtil：.NET CORE下的Dapper封装操作类
之前介绍了基于Dapper二次封装了一个易用的ORM工具类：SqlDapperUtil，这个在.NET FX下还是比较好用的，现在都流行.NET CORE，故我这边再次进行精简修改，以便适应.NET CORE并支持依赖注入。
-
提取定义了一个通用访问数据的接口：
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(); } -
精简版的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 { } } } -
在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变即可。 }如有疑问或好的建议，欢迎评论交流。
