基于 Dapper. Net 数据访问层的封装
2019年做项目使用的数据访问层都是基于dapper.net和DapperExtensions基础上加以封装，个人感觉非常的好用。学习起来没有什么成本、简单、清理、灵活，但也有很多缺点 就是DapperExtensions（orm） 扩展 很久没有更新了，多表查询个人感觉不是很好用。比如更新要更新指定的属性，需要到源码里面去改动（有可能是我还没有找到用法，如果哪位知道请告诉我下（非常的感谢））。下面我我将把自己封装的数据库访问层代码贴上来，不足的、错误的希望给予指正，相互学习相互进步。
首先是接口封装：
using DapperExtensions; using DapperExtensions.Mapper; using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Com.Marecente.DbHelper { /// <summary> /// 数据访问层接口 /// </summary> public interface ISqlHelper { /// <summary> /// 是否使用事务 /// </summary> bool HasActiveTransaction { get; } /// <summary> /// 数据库连接 Connection 对象 /// </summary> IDbConnection Connection { get; } /// <summary> /// 指定事务的隔离级别，默认 /// </summary> /// <param name="isolationLevel">不可以在事务期间读取可变数据，但是可以修改</param> void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted); /// <summary> /// 提交事务 /// </summary> void Commit(); /// <summary> /// 事务回滚 /// </summary> void Rollback(); /// <summary> /// 执行一个事物 Action 的写法 /// </summary> /// <param name="action">委托（Action）</param> void RunInTransaction(Action action); /// <summary> /// 执行一个事物 Func<T> 的写法 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="func"></param> /// <returns></returns> T RunInTransaction<T>(Func<T> func); /// <summary> /// 获取一个对象 /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="id">dynamic</param> /// <param name="commandTimeout">sql超时时间</param> /// <returns></returns> T Get<T>(dynamic id, int? commandTimeout = null) where T : class; /// <summary> /// 新增对象（无返回值） /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="entities">泛型集合</param> /// <param name="commandTimeout">sql超时时间</param> void Insert<T>(IEnumerable<T> entities, int? commandTimeout = null) where T : class; /// <summary> /// 新增对象 /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="entity">泛型参数·</param> /// <param name="commandTimeout">sql超时时间</param> /// <returns></returns> dynamic Insert<T>(T entity, int? commandTimeout = null) where T : class; /// <summary> /// 更新对象 /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="entity">更新对象</param> /// <param name="updateProperties">更新的属性</param> /// <param name="commandTimeout">sql超时时间</param> /// <param name="ignoreAllKeyProperties"></param> /// <returns>bool</returns> bool Update<T>(T entity, List<string> updateProperties = null, int? commandTimeout = null, bool ignoreAllKeyProperties = false) where T : class; /// <summary> /// /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="entity">删除对象</param> /// <param name="commandTimeout">sql超时时间</param> /// <returns></returns> bool Delete<T>(T entity, int? commandTimeout = null) where T : class; /// <summary> /// 根据条件删除对象 /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="predicate">条件</param> /// <param name="commandTimeout">sql超时时间</param> /// <returns></returns> bool Delete<T>(object predicate, int? commandTimeout = null) where T : class; /// <summary> /// /// </summary> /// <typeparam name="T">指定获取的类型</typeparam> /// <param name="predicate">查询条件</param> /// <param name="sort">排序</param> /// <param name="commandTimeout">sql超时时间</param> /// <param name="buffered"></param> /// <returns></returns> IEnumerable<T> GetList<T>(object predicate = null, IList<ISort> sort = null, int? commandTimeout = null, bool buffered = true) where T : class; /// <summary> /// /// </summary> /// <typeparam name="T">指定获取的类型</typeparam> /// <param name="predicate">查询条件</param> /// <param name="sort">排序</param> /// <param name="page"></param> /// <param name="resultsPerPage"></param> /// <param name="commandTimeout"></param> /// <param name="buffered"></param> /// <returns></returns> IEnumerable<T> GetPage<T>(object predicate, IList<ISort> sort, int page, int resultsPerPage, int? commandTimeout = null, bool buffered = true) where T : class; /// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <param name="sort"></param> /// <param name="firstResult"></param> /// <param name="maxResults"></param> /// <param name="commandTimeout"></param> /// <param name="buffered"></param> /// <returns></returns> IEnumerable<T> GetSet<T>(object predicate, IList<ISort> sort, int firstResult, int maxResults, int? commandTimeout, bool buffered) where T : class; /// <summary> /// 聚合函数 Count /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <param name="commandTimeout"></param> /// <returns></returns> int Count<T>(object predicate, int? commandTimeout = null) where T : class; /// <summary> /// 返回多个结果集 /// </summary> /// <param name="predicate"></param> /// <param name="commandTimeout"></param> /// <returns></returns> IMultipleResultReader GetMultiple(GetMultiplePredicate predicate, int? commandTimeout = null); /// <summary> /// 清除缓存 /// </summary> void ClearCache(); /// <summary> /// 生成GUID对象 /// </summary> /// <returns></returns> Guid GetNextGuid(); /// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> IClassMapper GetMap<T>() where T : class; /// <summary> /// 释放资源 /// </summary> void Dispose(); #region sql 语句 /// <summary> /// 获取第一个字段第一个值（sql） /// </summary> /// <typeparam name="T">泛型对象</typeparam> /// <param name="sql">sql</param> /// <param name="param">查询参数</param> /// <param name="commandTimeout">超时时间</param> /// <param name="commandType">commandType</param> /// <returns>T</returns> T GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null); /// <summary> /// 获取list泛型集合 /// </summary> /// <typeparam name="T">泛型对象</typeparam> /// <param name="sql">sql语句</param> /// <param name="param">查询参数</param> /// <param name="commandTimeout">超时时间</param> /// <param name="commandType">commandType</param> /// <returns>List<T></returns> List<T> Query<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null); /// <summary> /// 返回动态的 Dictionary 泛型 /// </summary> /// <typeparam name="T1">泛型对象1</typeparam> /// <typeparam name="T2">泛型对象2</typeparam> /// <param name="sql">sql语句</param> /// <param name="param">查询参数</param> /// <param name="commandTimeout">超时时间</param> /// <param name="commandType">commandType</param> /// <returns>Dictionary<T1, T2></returns> Dictionary<dynamic, dynamic> GetDic<T1, T2>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null); /// <summary> /// Dictionary<string, dynamic> sql /// </summary> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>Dictionary<string, dynamic></returns> Dictionary<string, dynamic> GetFirstValues(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null); /// <summary> /// GetModel<T> sql /// </summary> /// <typeparam name="T">T</typeparam> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>T</returns> T GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) where T : class; /// <summary> /// GetModelList<T> /// </summary> /// <typeparam name="T">泛型参数T</typeparam> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="buffered">buffered</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>List<T></returns> List<T> GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class; /// <summary> /// GetDynamicModel<T> sql /// </summary> /// <typeparam name="T">泛型T</typeparam> /// <param name="buildModelFunc">buildModelFunc</param> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="buffered">buffered</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>GetDynamicModel</returns> T GetDynamicModel<T>(Func<IEnumerable<dynamic>, T> buildModelFunc, string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null); /// <summary> /// GetMultModelList<T> sql /// </summary> /// <typeparam name="T">泛型参数T</typeparam> /// <param name="sql">sql</param> /// <param name="types">types</param> /// <param name="map">map</param> /// <param name="param">param</param> /// <param name="buffered">buffered</param> /// <param name="splitOn">splitOn</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>List<T></returns> 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); /// <summary> /// ExecuteCommand sql /// </summary> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>bool</returns> bool ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null); /// <summary> /// ExecuteScalar<T> sql /// </summary> /// <typeparam name="T">泛型参数T</typeparam> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>T</returns> T ExecuteScalar<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null); #endregion } }
实现接口：
using DapperExtensions; using DapperExtensions.Mapper; using DapperExtensions.Sql; using System; using System.Collections.Generic; using System.Data; using System.Data.OracleClient; using System.Data.SqlClient; using System.Data.SQLite; using System.Linq; using System.Reflection; namespace Com.Marecente.DbHelper { public class SqlHelper : ISqlHelper { private readonly IDapperImplementor _dapper; private IDbTransaction _transaction; /// <summary> /// 访问数据库实例 /// </summary> public static SqlHelper System { get { return new SqlHelper(); } } /// <summary> /// 构造函数 SqlHelper /// </summary> public SqlHelper() { var connectionString = Com.Marecente.Util.Config.GetValue("System_ConnectionString"); var dbtype = Com.Marecente.Util.ObjHelper.GetDBType(); if (dbtype == Util.DBType.SQLite) { Connection = new SQLiteConnection(connectionString); var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new SqliteDialect()); _dapper = new DapperImplementor(new SqlGeneratorImpl(config)); } else if (dbtype == Util.DBType.SQLServer) { var connection = new SqlConnection(connectionString); var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new SqlServerDialect()); var sqlGenerator = new SqlGeneratorImpl(config); _dapper = new DapperImplementor(sqlGenerator); Connection = connection; } else if (dbtype == Util.DBType.Oracle) { var connection = new OracleConnection(connectionString); var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new OracleDialect()); var sqlGenerator = new SqlGeneratorImpl(config); _dapper = new DapperImplementor(sqlGenerator); Connection = connection; } else if (dbtype == Util.DBType.MySql) { //var connection = new MySqlConnection("Server=localhost;Port=3306;Database=dapperTest;uid=root;password=password!"); //var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new MySqlDialect()); //var sqlGenerator = new SqlGeneratorImpl(config); //_dapper = new DapperImplementor(sqlGenerator); //Connection = connection; } if (Connection.State != ConnectionState.Open) { Connection.Open(); } } /// <summary> /// 是否执行了事物 /// </summary> public bool HasActiveTransaction { get { return _transaction != null; } } public IDbConnection Connection { get; private set; } /// <summary> /// 资源释放 /// </summary> public virtual void Dispose() { if (Connection.State != ConnectionState.Closed) { if (_transaction != null) { _transaction.Rollback(); } Connection.Close(); } } #region orm /// <summary> /// 指定事务的隔离级别，默认 /// </summary> /// <param name="isolationLevel">不可以在事务期间读取可变数据，但是可以修改</param> public void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted) { _transaction = Connection.BeginTransaction(isolationLevel); } /// <summary> /// 提交事务 /// </summary> public void Commit() { _transaction.Commit(); _transaction = null; } /// <summary> /// 事务回滚 /// </summary> public void Rollback() { _transaction.Rollback(); _transaction = null; } /// <summary> /// 执行一个事物 Action 的写法 /// </summary> /// <param name="action">委托（Action）</param> public void RunInTransaction(Action action) { BeginTransaction(); try { action(); Commit(); } catch (Exception ex) { if (HasActiveTransaction) { Rollback(); } throw ex; } } /// <summary> /// 执行一个事物 Func<T> 的写法 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="func"></param> /// <returns></returns> public T RunInTransaction<T>(Func<T> func) { BeginTransaction(); try { T result = func(); Commit(); return result; } catch (Exception ex) { if (HasActiveTransaction) { Rollback(); } throw ex; } } /// <summary> /// 获取一个对象 /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="id">dynamic</param> /// <param name="commandTimeout">sql超时时间</param> /// <returns>T</returns> public T Get<T>(dynamic id, int? commandTimeout) where T : class { return (T)_dapper.Get<T>(Connection, id, _transaction, commandTimeout); } /// <summary> /// 新增对象（无返回值） /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="entities">泛型集合</param> /// <param name="commandTimeout">sql超时时间</param> public void Insert<T>(IEnumerable<T> entities, int? commandTimeout) where T : class { _dapper.Insert<T>(Connection, entities, _transaction, commandTimeout); } /// <summary> /// 新增对象 /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="entity">泛型参数·</param> /// <param name="commandTimeout">sql超时时间</param> /// <returns>dynamic</returns> public dynamic Insert<T>(T entity, int? commandTimeout) where T : class { return _dapper.Insert<T>(Connection, entity, _transaction, commandTimeout); } /// <summary> /// 更新对象 /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="entity">更新对象</param> /// <param name="updateProperties">更新的属性</param> /// <param name="commandTimeout">sql超时时间</param> /// <param name="ignoreAllKeyProperties"></param> /// <returns>bool</returns> public bool Update<T>(T entity, List<string> updateProperties, int? commandTimeout, bool ignoreAllKeyProperties = false) where T : class { return _dapper.Update<T>(Connection, entity, updateProperties, _transaction, commandTimeout, ignoreAllKeyProperties); } /// <summary> /// 删除对象 /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="entity">删除对象</param> /// <param name="commandTimeout">sql超时时间</param> /// <returns>bool</returns> public bool Delete<T>(T entity, int? commandTimeout) where T : class { return _dapper.Delete(Connection, entity, _transaction, commandTimeout); } /// <summary> /// 根据条件删除对象 /// </summary> /// <typeparam name="T">参数类型</typeparam> /// <param name="predicate">条件</param> /// <param name="commandTimeout">sql超时时间</param> /// <returns>bool</returns> public bool Delete<T>(object predicate, int? commandTimeout) where T : class { return _dapper.Delete<T>(Connection, predicate, _transaction, commandTimeout); } /// <summary> /// 获取列表 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <param name="sort"></param> /// <param name="commandTimeout"></param> /// <param name="buffered"></param> /// <returns>IEnumerable<T></returns> public IEnumerable<T> GetList<T>(object predicate, IList<ISort> sort, int? commandTimeout, bool buffered) where T : class { return _dapper.GetList<T>(Connection, predicate, sort, _transaction, commandTimeout, false); } /// <summary> /// 获取列表 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <param name="sort"></param> /// <param name="page"></param> /// <param name="resultsPerPage"></param> /// <param name="commandTimeout"></param> /// <param name="buffered"></param> /// <returns>IEnumerable<T></returns> public IEnumerable<T> GetPage<T>(object predicate, IList<ISort> sort, int page, int resultsPerPage, int? commandTimeout, bool buffered) where T : class { return _dapper.GetPage<T>(Connection, predicate, sort, page, resultsPerPage, _transaction, commandTimeout, buffered); } /// <summary> /// 获取列表 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <param name="sort"></param> /// <param name="firstResult"></param> /// <param name="maxResults"></param> /// <param name="commandTimeout"></param> /// <param name="buffered"></param> /// <returns>IEnumerable<T></returns> public IEnumerable<T> GetSet<T>(object predicate, IList<ISort> sort, int firstResult, int maxResults, int? commandTimeout, bool buffered) where T : class { return _dapper.GetSet<T>(Connection, predicate, sort, firstResult, maxResults, _transaction, commandTimeout, buffered); } /// <summary> /// 聚合函数 Count /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <param name="commandTimeout"></param> /// <returns></returns> public int Count<T>(object predicate, int? commandTimeout) where T : class { return _dapper.Count<T>(Connection, predicate, _transaction, commandTimeout); } /// <summary> /// 返回多个结果集 /// </summary> /// <param name="predicate"></param> /// <param name="commandTimeout"></param> /// <returns></returns> public IMultipleResultReader GetMultiple(GetMultiplePredicate predicate, int? commandTimeout) { return _dapper.GetMultiple(Connection, predicate, _transaction, commandTimeout); } /// <summary> /// 清除缓存 /// </summary> public void ClearCache() { _dapper.SqlGenerator.Configuration.ClearCache(); } /// <summary> /// 生成GUID对象 /// </summary> /// <returns></returns> public Guid GetNextGuid() { return _dapper.SqlGenerator.Configuration.GetNextGuid(); } /// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public IClassMapper GetMap<T>() where T : class { return _dapper.SqlGenerator.Configuration.GetMap<T>(); } #endregion #region sql语句 /// <summary> /// 获取第一个字段第一个值（sql） /// </summary> /// <typeparam name="T">泛型对象</typeparam> /// <param name="sql">sql</param> /// <param name="param">查询参数</param> /// <param name="commandTimeout">超时时间</param> /// <param name="commandType">commandType</param> /// <returns>T</returns> T ISqlHelper.GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) { return Dapper.SqlMapper.ExecuteScalar<T>(Connection, sql, param, _transaction, commandTimeout, commandType); } /// <summary> /// 获取list泛型集合 /// </summary> /// <typeparam name="T">泛型对象</typeparam> /// <param name="sql">sql语句</param> /// <param name="param">查询参数</param> /// <param name="commandTimeout">超时时间</param> /// <param name="commandType">commandType</param> /// <returns>List<T></returns> List<T> ISqlHelper.Query<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) { return Dapper.SqlMapper.Query<T>(Connection, sql, param, _transaction, true, commandTimeout, commandType).ToList(); } /// <summary> /// 返回动态的 Dictionary 泛型 /// </summary> /// <typeparam name="T1">泛型对象1</typeparam> /// <typeparam name="T2">泛型对象2</typeparam> /// <param name="sql">sql语句</param> /// <param name="param">查询参数</param> /// <param name="commandTimeout">超时时间</param> /// <param name="commandType">commandType</param> /// <returns>Dictionary<T1, T2></returns> Dictionary<dynamic, dynamic> ISqlHelper.GetDic<T1, T2>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) { var dict = Dapper.SqlMapper.Query(Connection, sql, param, _transaction, true, commandTimeout, commandType).ToDictionary( row => (dynamic)row.ID, row => (dynamic)row.Name); return dict; } /// <summary> /// Dictionary<string, dynamic> sql /// </summary> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>Dictionary<string, dynamic></returns> Dictionary<string, dynamic> ISqlHelper.GetFirstValues(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) { Dictionary<string, dynamic> firstValues = new Dictionary<string, dynamic>(); List<string> indexColNameMappings = new List<string>(); int rowIndex = 0; using (var reader = Dapper.SqlMapper.ExecuteReader(Connection, sql, param, _transaction, 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> /// GetModel<T> sql /// </summary> /// <typeparam name="T">T</typeparam> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>T</returns> T ISqlHelper.GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) { return Dapper.SqlMapper.QueryFirstOrDefault<T>(Connection, sql, param, _transaction, commandTimeout, commandType); } /// <summary> /// GetModelList<T> /// </summary> /// <typeparam name="T">泛型参数T</typeparam> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="buffered">buffered</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>List<T></returns> List<T> ISqlHelper.GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) { var list = Dapper.SqlMapper.Query<T>(Connection, sql, param, _transaction, buffered, commandTimeout, commandType).ToList(); if (list == null) { list = new List<T>(); } return list; } /// <summary> /// GetDynamicModel<T> sql /// </summary> /// <typeparam name="T">泛型T</typeparam> /// <param name="buildModelFunc">buildModelFunc</param> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="buffered">buffered</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>GetDynamicModel</returns> T ISqlHelper.GetDynamicModel<T>(Func<IEnumerable<dynamic>, T> buildModelFunc, string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) { return default(T); } /// <summary> /// GetMultModelList<T> sql /// </summary> /// <typeparam name="T">泛型参数T</typeparam> /// <param name="sql">sql</param> /// <param name="types">types</param> /// <param name="map">map</param> /// <param name="param">param</param> /// <param name="buffered">buffered</param> /// <param name="splitOn">splitOn</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>List<T></returns> List<T> ISqlHelper.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 Dapper.SqlMapper.Query<T>(Connection, sql, types, map, param, _transaction, buffered, splitOn, commandTimeout, commandType).ToList(); } /// <summary> /// ExecuteCommand sql /// </summary> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>bool</returns> bool ISqlHelper.ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) { int result = Dapper.SqlMapper.Execute(Connection, sql, param, _transaction, commandTimeout, commandType); return (result > 0); } /// <summary> /// ExecuteScalar<T> sql /// </summary> /// <typeparam name="T">泛型参数T</typeparam> /// <param name="sql">sql</param> /// <param name="param">param</param> /// <param name="commandTimeout">commandTimeout</param> /// <param name="commandType">commandType</param> /// <returns>T</returns> T ISqlHelper.ExecuteScalar<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null) { T result = Dapper.SqlMapper.ExecuteScalar<T>(Connection, sql, param, _transaction, commandTimeout, commandType); return result; } #endregion } }
其中更新的地方有一个参数：List<string> updateProperties 这里我改动了 DapperExtensions插件的源码，在该类中：DapperImplementor 中的 Update 方法中204行增加如下代码：
if (updateProperties != null) { var l = new List<IPropertyMap>(); foreach (var item in classMap.Properties) { if (item.Name == "ID") continue; var res = updateProperties.Contains(item.ColumnName); if (!res) { l.Add(item); } } if (l.Count > 0) { foreach (var item in l) { // 移除不需要修改的属性 classMap.Properties.Remove(item); } } }
如图：
使用只测试了几个：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp1 { class Program { static void Main(string[] args) { Com.Marecente.DbHelper.ISqlHelper sqlHelper = Com.Marecente.DbHelper.SqlHelper.System; #region 新增 // int id = sqlHelper.Insert<Test>(new Test { Name = "小王", Age = 18 }); #endregion // var test = sqlHelper.Get<Test>(1); #region 新增使用事务测试(测试回滚) //sqlHelper.BeginTransaction(); //try //{ // int id = sqlHelper.Insert<Test>(new Test { Name = "事务测试", Age = 18 }); // sqlHelper.Commit(); //} //catch (Exception ex) //{ // sqlHelper.Rollback(); //} #endregion } } public class Test { public int ID { get; set; } public string Name { get; set; } public int Age { get; set; } } }
该访问层我只测试了sql server、sqlite，其他还支持MySql （未测试）等，不足的地方欢迎指正。
