Dapper的新实践，Dapper.Contrib的使用与扩展

案头见蠹鱼，犹胜凡俦侣。这篇文章主要讲述Dapper的新实践，Dapper.Contrib的使用与扩展相关的知识，希望能为你提供帮助。
之前使用Dapper都是搭配github上一些其他网友开发的扩展库进行的，时间久了，大多都不更新了，也在使用的时候遇到些BUG。最后回到了最开始的地方，使用起了Dapper.Contrib，并对它进行了一些简单扩展。
现如今的.NET Core的库都很成熟，也很方便，简单的ORM不想用EF，可以直接用Dapper满足需求。
第一步是建立一个通用化的接口

public interface IBaseService< T> where T : class { /// < summary> /// /// < /summary> /// < param name="t"> < /param> /// < returns> < /returns> long Insert(T t); /// < summary> /// /// < /summary> /// < param name="t"> < /param> long Insert(List< T> t); /// < summary> /// /// < /summary> /// < param name="t"> < /param> /// < returns> < /returns> bool Update(T t); /// < summary> /// /// < /summary> /// < param name="t"> < /param> /// < returns> < /returns> bool Update(List< T> t); /// < summary> /// 单行结果 /// < /summary> /// < param name="id"> < /param> /// < returns> < /returns> T Get(object id); /// < summary> /// 所有结果 /// < /summary> /// < param name="id"> < /param> /// < returns> < /returns> List< T> GetAll(); /// < summary> /// 带条件查询 /// < /summary> /// < param name="sql"> < /param> /// < param name="parameters"> < /param> /// < returns> < /returns> List< T> GetList(string sql, object parameters = null); /// < summary> /// 多结果集返回 /// < /summary> /// < param name="sql"> < /param> /// < param name="func"> 外部自行定义读取方式< /param> /// < param name="parameters"> < /param> void GetList(string sql, Action< SqlMapper.GridReader> act, object parameters = null); /// < summary> /// /// < /summary> /// < param name="id"> < /param> /// < returns> < /returns> bool Delete(T id); /// < summary> /// /// < /summary> /// < param name="list"> < /param> /// < returns> < /returns> bool Delete(List< T> list); /// < summary> /// /// < /summary> /// < returns> < /returns> bool DeleteAll(); /// < summary> /// 非多表关联的翻页 /// < /summary> /// < param name="func"> 结果集处理< /param> /// < param name="page"> 页码< /param> /// < param name="limit"> 偏移< /param> /// < param name="count"> 总数< /param> /// < param name="parameters"> 查询条件< /param> void Pagination(Action< SqlMapper.GridReader> act, int page, int limit, string tableName = "", string selectColumns = "*", string sort = "", Func< string, string, string> conditionsFunc = null, object parameters = null); }

第二步是对应的一些扩展，很多代码都是直接抄Dapper.Contrib的，需要读取表名、键名，主要是用来灵活的分页（狗头保命）。

public class SQLLib { private static readonly ConcurrentDictionary< RuntimeTypeHandle, string> TypeTableName = new ConcurrentDictionary< RuntimeTypeHandle, string> (); private static readonly ConcurrentDictionary< RuntimeTypeHandle, IEnumerable< PropertyInfo> > KeyProperties = new ConcurrentDictionary< RuntimeTypeHandle, IEnumerable< PropertyInfo> > (); private static readonly ConcurrentDictionary< RuntimeTypeHandle, IEnumerable< PropertyInfo> > TypeProperties = new ConcurrentDictionary< RuntimeTypeHandle, IEnumerable< PropertyInfo> > (); private static readonly ConcurrentDictionary< RuntimeTypeHandle, IEnumerable< PropertyInfo> > ExplicitKeyProperties = new ConcurrentDictionary< RuntimeTypeHandle, IEnumerable< PropertyInfo> > (); /// < summary> /// sql体系 /// < /summary> public enum SQLDialect { SQLServerOld = 0, PostgreSQL = 1, SQLite = 2, mysql = 3, SQLServerNew = 4, } /// < summary> /// 返回分页模板 /// < /summary> /// < param name="dialect"> < /param> /// < returns> < /returns> public static string GetPaginationTemplate(SQLDialect dialect) { switch (dialect) { case SQLDialect.PostgreSQL: //_encapsulation = ""{0}""; //_getIdentitySql = string.Format("SELECT LASTVAL() AS id"); return "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage}); "; //break; case SQLDialect.SQLite: //_encapsulation = ""{0}""; //_getIdentitySql = string.Format("SELECT LAST_INSERT_ROWID() AS id"); return "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage}); "; //break; case SQLDialect.MySQL: //_encapsulation = "`{0}`"; //_getIdentitySql = string.Format("SELECT LAST_INSERT_ID() AS id"); return "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {Offset},{RowsPerPage}; "; //break; case SQLDialect.SQLServerNew: return @"SELECT {SelectColumns} FROM {TableName} {WhereClause} ORDER BY {OrderBy} OFFSET {Offset} ROWS FETCH NEXT {RowsPerPage} ROWS ONLY; "; default: //_encapsulation = "[{0}]"; //_getIdentitySql = string.Format("SELECT CAST(SCOPE_IDENTITY()AS BIGINT) AS [id]"); return "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {OrderBy}) AS PagedNumber, {SelectColumns} FROM {TableName} {WhereClause}) AS u WHERE PagedNumber BETWEEN (({PageNumber}-1) * {RowsPerPage} + 1) AND ({PageNumber} * {RowsPerPage}); "; //break; } } /// < summary> /// 返回分页语句 /// < /summary> /// < param name="dialect"> < /param> /// < param name="TableName"> 表名< /param> /// < param name="SelectColumns"> 查询列< /param> /// < param name="pageNumber"> 页码< /param> /// < param name="rowsPerPage"> 每页行数< /param> /// < param name="conditions"> 查询条件< /param> /// < param name="orderby"> 排序条件< /param> /// < returns> < /returns> public static string PaginationSQL(SQLDialect dialect, string tableName, string selectColumns, int pageNumber, int rowsPerPage, string conditions, string orderby) { string query = GetPaginationTemplate(dialect); query = query.Replace("{SelectColumns}", selectColumns); //查询列 query = query.Replace("{TableName}", tableName); //表名 query = query.Replace("{PageNumber}", pageNumber.ToString()); //页码 query = query.Replace("{RowsPerPage}", rowsPerPage.ToString()); //每页行数 query = query.Replace("{OrderBy}", orderby); //排序条件 query = query.Replace("{WhereClause}", conditions); //查询条件 query = query.Replace("{Offset}", ((pageNumber - 1) * rowsPerPage).ToString()); //偏移量 return query; }/// < summary> /// 生成取总数的SQL /// < /summary> /// < param name="tableName"> < /param> /// < param name="conditions"> < /param> /// < returns> < /returns> public static string CountSQL(string tableName, string conditions = "", string countColumn = "*") { string query = "SELECT count({CountColumn}) FROM {TableName} {WhereClause}; "; query = query.Replace("{CountColumn}", countColumn); //列名 query = query.Replace("{TableName}", tableName); //表名 query = query.Replace("{WhereClause}", conditions); //查询条件 return query; }/// < summary> /// 去类对应的表名 /// < /summary> /// < param name="type"> < /param> /// < returns> < /returns> public static string GetTableName(Type type) { if (TypeTableName.TryGetValue(type.TypeHandle, out string name)) return name; //来自Dapper.Contrib源码 //NOTE: This as dynamic trick falls back to handle both our own Table-attribute as well as the one in EntityFramework var tableAttrName = type.GetCustomAttribute< Dapper.Contrib.Extensions.TableAttribute> (false)?.Name ?? (type.GetCustomAttributes(false).FirstOrDefault(attr => attr.GetType().Name == "TableAttribute") as dynamic)?.Name; if (tableAttrName != null) { name = tableAttrName; } else { name = type.Name + "s"; if (type.IsInterface & & name.StartsWith("I")) name = name.Substring(1); }TypeTableName[type.TypeHandle] = name; return name; }public static PropertyInfo GetSingleKey< T> (string method) { var type = typeof(T); var keys = KeyPropertiesCache(type); var explicitKeys = ExplicitKeyPropertiesCache(type); var keyCount = keys.Count + explicitKeys.Count; if (keyCount > 1) throw new DataException($"{method}< T> only supports an entity with a single [Key] or [ExplicitKey] property. [Key] Count: {keys.Count}, [ExplicitKey] Count: {explicitKeys.Count}"); if (keyCount == 0) throw new DataException($"{method}< T> only supports an entity with a [Key] or an [ExplicitKey] property"); return keys.Count > 0 ? keys[0] : explicitKeys[0]; }private static List< PropertyInfo> KeyPropertiesCache(Type type) { if (KeyProperties.TryGetValue(type.TypeHandle, out IEnumerable< PropertyInfo> pi)) { return pi.ToList(); }var allProperties = TypePropertiesCache(type); var keyProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute)).ToList(); if (keyProperties.Count == 0) { var idProp = allProperties.Find(p => string.Equals(p.Name, "id", StringComparison.CurrentCultureIgnoreCase)); if (idProp != null & & !idProp.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute)) { keyProperties.Add(idProp); } }KeyProperties[type.TypeHandle] = keyProperties; return keyProperties; } private static List< PropertyInfo> TypePropertiesCache(Type type) { if (TypeProperties.TryGetValue(type.TypeHandle, out IEnumerable< PropertyInfo> pis)) { return pis.ToList(); }var properties = type.GetProperties().Where(IsWriteable).ToArray(); TypeProperties[type.TypeHandle] = properties; return properties.ToList(); } private static bool IsWriteable(PropertyInfo pi) { var attributes = pi.GetCustomAttributes(typeof(WriteAttribute), false).AsList(); if (attributes.Count != 1) return true; var writeAttribute = (WriteAttribute)attributes[0]; return writeAttribute.Write; } private static List< PropertyInfo> ExplicitKeyPropertiesCache(Type type) { if (ExplicitKeyProperties.TryGetValue(type.TypeHandle, out IEnumerable< PropertyInfo> pi)) { return pi.ToList(); }var explicitKeyProperties = TypePropertiesCache(type).Where(p => p.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute)).ToList(); ExplicitKeyProperties[type.TypeHandle] = explicitKeyProperties; return explicitKeyProperties; } }public static class Extensions { /// < summary> /// 生成取总数的SQL /// < /summary> /// < typeparam name="T"> < /typeparam> /// < param name="connection"> < /param> /// < param name="entity"> < /param> /// < param name="tableName"> < /param> /// < param name="conditions"> < /param> /// < param name="countColumn"> < /param> /// < returns> < /returns> public static int Count< T> (this IDbConnection connection, T entity, string tableName = "", string conditions = "", string countColumn = "*") { if (string.IsNullOrEmpty(tableName)) { var type = typeof(T); tableName = SQLLib.GetTableName(type); } return connection.Query< int> (SQLLib.CountSQL(tableName, conditions, countColumn)).FirstOrDefault(); } }

第三步，建一个服务测试，DB是在Startup.ConfigureServices进行注入的配置

public interface IMarketService:IBaseService< Market> { //有扩展写这里} public class MarketService : IMarketService { private readonly DB _DB; //注入配置public MarketService(IOptions< DB> db) { _DB = db.Value; } public bool Delete(Market id) { using (var conn = new SqlConnection(_DB.CRM)) { return conn.Delete(id); } }public bool Delete(List< Market> list) { using (var conn = new SqlConnection(_DB.CRM)) { return conn.Delete(list); } }public bool DeleteAll() { using (var conn = new SqlConnection(_DB.CRM)) { return conn.DeleteAll< Market> (); } }public Market Get(object id) { using (var conn = new SqlConnection(_DB.CRM)) { return conn.Get< Market> (id); } }public List< Market> GetAll() { using (var conn = new SqlConnection(_DB.CRM)) { return conn.GetAll< Market> ().ToList(); } }public List< Market> GetList(string sql, object parameters = null) { using (var conn = new SqlConnection(_DB.CRM)) { return conn.Query< Market> (sql).ToList(); } }public void GetList(string sql, Action< SqlMapper.GridReader> act, object parameters = null) { using (var conn = new SqlConnection(_DB.CRM)) { using (var multi = conn.QueryMultiple(sql, parameters)) { act(multi); } } }public long Insert(Market t) { using (var conn = new SqlConnection(_DB.CRM)) { return conn.Insert(t); } }public long Insert(List< Market> t) { using (var conn = new SqlConnection(_DB.CRM)) { return conn.Insert(t); } }/// < summary> /// /// < /summary> /// < param name="func"> < /param> /// < param name="page"> < /param> /// < param name="limit"> < /param> /// < param name="tableName"> 默认不填去类的表名< /param> /// < param name="selectColumns"> 默认全部*< /param> /// < param name="sort"> < /param> /// < param name="conditionsFunc"> < /param> /// < param name="parameters"> < /param> public void Pagination(Action< SqlMapper.GridReader> act, int page, int limit, string tableName = "",string selectColumns="*",string sort= "",Func< string,string,string> conditionsFunc=null, object parameters = null) { using (var conn = new SqlConnection(_DB.CRM)) { if (string.IsNullOrEmpty(tableName)) { tableName = SQLLib.GetTableName(typeof(Market)); } if (string.IsNullOrEmpty(sort)) { var key = SQLLib.GetSingleKey< Market> (nameof(Pagination)); sort = $"{key.Name} DESC"; } string prefix = "where 1=1 "; string conditions = ""; if (conditionsFunc!=null) { conditions = conditionsFunc(prefix, tableName); } string sql = SQLLib.PaginationSQL(SQLLib.SQLDialect.SQLServerOld, tableName, selectColumns, page, limit, conditions, sort); string countSql = SQLLib.CountSQL(SQLLib.GetTableName(typeof(Market)), conditions); Debug.WriteLine(sql + countSql); using (var multi = conn.QueryMultiple(sql + countSql, parameters)) { act(multi); } } }public bool Update(Market t) { using (var conn = new SqlConnection(_DB.CRM)) { return conn.Update(t); } }public bool Update(List< Market> t) { using (var conn = new SqlConnection(_DB.CRM)) { return conn.Update(t); } } }

第四步，在Controller内读取：