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="sql"></param>
        /// <param name="act"></param>
        /// <param name="parameters"></param>
        void GetListForDataReader(string sql, Action<IDataReader> 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);

        /// <summary>
        /// 
        /// </summary>
        /// <param name="act"></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>
        void PaginationForDataReader(Action<IDataReader> 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 void GetListForDataReader(string sql, Action<IDataReader> act, object parameters = null)
        {
            using (var conn = new SqlConnection(_DB.CRM))
            {
                using (var reader = conn.ExecuteReader(sql, parameters))
                {
                    act(reader);
                }
            }
        }

        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 void PaginationForDataReader(Action<IDataReader> 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 reader = conn.ExecuteReader(sql + countSql, parameters))
                {
                    act(reader);
                }
            }
        }

        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内读取：

            var marketList = new List<Market>();
            _marketService.Pagination((x) =>
            {
                marketList = x.Read<Market>().ToList();
            }, 1, 10);

 或者是

            var tb = new System.Data.DataTable("test");
            _marketService.PaginationForDataReader((x) =>
            {
                tb.Load(x);// 无需x.NextResult();Load自动执行NextResult
                int count = 0;
                if (x.Read())
                {
                    count = Convert.ToInt32(x[0]);
                }
            }, 1, 10);

 需要映射模型的时候映射模型，不想映射则自己操作DataReader读取数据，都很方便。

 比如我有时候不需要映射，读取到DataTable就可以了，之后直接丢NPOI导出Excel，速度就很快。需要有业务逻辑处理的时候，就映射一下。这样就在写的时候平衡了很多。