之前介绍了基于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变即可。
}如有疑问或好的建议，欢迎评论交流。
SqlDapperEasyUtil：.NET CORE下的Dapper封装操作类
原文：https://www.cnblogs.com/zuowj/p/12394120.html