您的位置:澳门新葡8455最新网站 > 编程教学 > 据他们说Dapper一次封装了二个易用的ORM工具类,

据他们说Dapper一次封装了二个易用的ORM工具类,

发布时间:2019-12-09 15:19编辑:编程教学浏览(176)

    基于Dapper二次封装了一个易用的ORM工具类:SqlDapperUtil,把日常能用到的各种CRUD都进行了简化封装,让普通程序员只需关注业务即可,因为非常简单,故直接贴源代码,大家若需使用可以直接复制到项目中,该SqlDapperUtil已广泛用于公司项目中。

    现在成熟的ORM比比皆是,这里只介绍Dapper的使用(最起码我在使用它,已经运用到项目中,小伙伴们反馈还可以)。

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Dapper;
    using System.Data;
    using System.Data.Common;
    using System.Reflection;
    using System.IO;
    using System.Collections.Concurrent;
    using System.Data.SqlClient;
    
    namespace Zuowj.Common
    {
        /// <summary>
        /// 基于Dapper的数据操作类封装的工具类
        /// Author:左文俊
        /// Date:2017/12/11
        /// </summary>
        public class SqlDapperUtil
        {
            private static string dbConnectionStringConfigPath = null;
            private readonly static ConcurrentDictionary<string, bool> dbConnNamesCacheDic = new ConcurrentDictionary<string, bool>();
    
            private string dbConnectionName = null;
            private string dbConnectionString = null;
            private string dbProviderName = null;
            private IDbConnection dbConnection = null;
            private bool useDbTransaction = false;
            private IDbTransaction dbTransaction = null;
    
    
            #region 私有方法
    
            private IDbConnection GetDbConnection()
            {
                bool needCreateNew = false;
                if (dbConnection == null || string.IsNullOrWhiteSpace(dbConnection.ConnectionString))
                {
                    needCreateNew = true;
                }
                else if (!MemoryCacheUtil.Contains(dbConnectionName))
                {
                    needCreateNew = true;
                }
    
                if (needCreateNew)
                {
                    dbConnectionString = GetDbConnectionString(dbConnectionName, out dbProviderName);
                    var dbProviderFactory = DbProviderFactories.GetFactory(dbProviderName);
                    dbConnection = dbProviderFactory.CreateConnection();
                    dbConnection.ConnectionString = dbConnectionString;
                }
    
                if (dbConnection.State == ConnectionState.Closed)
                {
                    dbConnection.Open();
                }
    
                return dbConnection;
            }
    
            private string GetDbConnectionString(string dbConnName, out string dbProviderName)
            {
                //如果指定的连接字符串配置文件路径,则创建缓存依赖,一旦配置文件更改就失效,再重新读取
                string[] connInfos = MemoryCacheUtil.GetOrAddCacheItem(dbConnName, () =>
                {
                    var connStrSettings = ConfigUtil.GetConnectionStringForConfigPath(dbConnName, SqlDapperUtil.DbConnectionStringConfigPath);
                    string dbProdName = connStrSettings.ProviderName;
                    string dbConnStr = connStrSettings.ConnectionString;
                    //LogUtil.Info(string.Format("SqlDapperUtil.GetDbConnectionString>读取连接字符串配置节点[{0}]:{1},ProviderName:{2}", dbConnName, dbConnStr, dbProdName), "SqlDapperUtil.GetDbConnectionString");
                    return new[] { EncryptUtil.Decrypt(dbConnStr), dbProdName };
                }, SqlDapperUtil.DbConnectionStringConfigPath);
    
                dbProviderName = connInfos[1];
                return connInfos[0];
            }
    
    
            private T UseDbConnection<T>(Func<IDbConnection, T> queryOrExecSqlFunc)
            {
                IDbConnection dbConn = null;
    
                try
                {
                    Type modelType = typeof(T);
                    var typeMap = Dapper.SqlMapper.GetTypeMap(modelType);
                    if (typeMap == null || !(typeMap is ColumnAttributeTypeMapper<T>))
                    {
                        Dapper.SqlMapper.SetTypeMap(modelType, new ColumnAttributeTypeMapper<T>());
                    }
    
                    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);
            }
    
            private DynamicParameters ToDynamicParameters(Dictionary<string, object> paramDic)
            {
                return new DynamicParameters(paramDic);
            }
    
            #endregion
    
            public static string DbConnectionStringConfigPath
            {
                get
                {
                    if (string.IsNullOrEmpty(dbConnectionStringConfigPath))//如果没有指定配置文件,则取默认的配置文件路径作为缓存依赖路径
                    {
                        dbConnectionStringConfigPath = BaseUtil.GetConfigPath();
                    }
    
                    return dbConnectionStringConfigPath;
                }
                set
                {
                    if (!string.IsNullOrWhiteSpace(value) && !File.Exists(value))
                    {
                        throw new FileNotFoundException("指定的DB连接字符串配置文件不存在:" + value);
                    }
    
                    //如果配置文件改变,则可能导致连接字符串改变,故必需清除所有连接字符串的缓存以便后续重新加载字符串
                    if (!string.Equals(dbConnectionStringConfigPath, value, StringComparison.OrdinalIgnoreCase))
                    {
                        foreach (var item in dbConnNamesCacheDic)
                        {
                            MemoryCacheUtil.RemoveCacheItem(item.Key);
                        }
                    }
    
                    dbConnectionStringConfigPath = value;
                }
            }
    
            public SqlDapperUtil(string connName)
            {
                dbConnectionName = connName;
                if (!dbConnNamesCacheDic.ContainsKey(connName)) //如果静态缓存中没有,则加入到静态缓存中
                {
                    dbConnNamesCacheDic[connName] = true;
                }
    
            }
    
    
            /// <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>
            /// 批量转移数据(利用SqlBulkCopy实现快速大批量插入到指定的目的表及SqlDataAdapter的批量删除)
            /// </summary>
            public bool BatchMoveData(string srcSelectSql, string srcTableName, List<SqlParameter> srcPrimarykeyParams, string destConnName, string destTableName)
            {
    
                using (SqlDataAdapter srcSqlDataAdapter = new SqlDataAdapter(srcSelectSql, GetDbConnectionString(dbConnectionName, out dbProviderName)))
                {
                    DataTable srcTable = new DataTable();
                    SqlCommand deleteCommand = null;
                    try
                    {
                        srcSqlDataAdapter.AcceptChangesDuringFill = true;
                        srcSqlDataAdapter.AcceptChangesDuringUpdate = false;
                        srcSqlDataAdapter.Fill(srcTable);
    
                        if (srcTable == null || srcTable.Rows.Count <= 0) return true;
    
                        string notExistsDestSqlWhere = null;
                        string deleteSrcSqlWhere = null;
    
                        for (int i = 0; i < srcPrimarykeyParams.Count; i++)
                        {
                            string keyColName = srcPrimarykeyParams[i].ParameterName.Replace("@", "");
                            notExistsDestSqlWhere += string.Format(" AND told.{0}=tnew.{0}", keyColName);
                            deleteSrcSqlWhere += string.Format(" AND {0}=@{0}", keyColName);
                        }
    
                        string dbProviderName2 = null;
                        using (var destConn = new SqlConnection(GetDbConnectionString(destConnName, out dbProviderName2)))
                        {
                            destConn.Open();
    
                            string tempDestTableName = "#temp_" + destTableName;
                            destConn.Execute(string.Format("select top 0 * into {0} from {1}", tempDestTableName, destTableName));
                            string destInsertCols = null;
                            using (var destSqlBulkCopy = new SqlBulkCopy(destConn))
                            {
                                try
                                {
                                    destSqlBulkCopy.BulkCopyTimeout = 120;
                                    destSqlBulkCopy.DestinationTableName = tempDestTableName;
                                    foreach (DataColumn col in srcTable.Columns)
                                    {
                                        destSqlBulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                                        destInsertCols += "," + col.ColumnName;
                                    }
    
                                    destSqlBulkCopy.BatchSize = 1000;
                                    destSqlBulkCopy.WriteToServer(srcTable);
                                }
                                catch (Exception ex)
                                {
                                    //LogUtil.Error("SqlDapperUtil.BatchMoveData.SqlBulkCopy:" + ex.ToString(), "SqlDapperUtil.BatchMoveData");
                                }
    
                                destInsertCols = destInsertCols.Substring(1);
    
                                destConn.Execute(string.Format("insert into {1}({0}) select {0} from {2} tnew where not exists(select 1 from {1} told where {3})",
                                                 destInsertCols, destTableName, tempDestTableName, notExistsDestSqlWhere.Trim().Substring(3)), null, null, 100);
                            }
                            destConn.Close();
                        }
    
                        deleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1}", srcTableName, deleteSrcSqlWhere.Trim().Substring(3)), srcSqlDataAdapter.SelectCommand.Connection);
                        deleteCommand.Parameters.AddRange(srcPrimarykeyParams.ToArray());
                        deleteCommand.UpdatedRowSource = UpdateRowSource.None;
                        deleteCommand.CommandTimeout = 200;
    
                        srcSqlDataAdapter.DeleteCommand = deleteCommand;
                        foreach (DataRow row in srcTable.Rows)
                        {
                            row.Delete();
                        }
    
                        srcSqlDataAdapter.UpdateBatchSize = 1000;
                        srcSqlDataAdapter.Update(srcTable);
                        srcTable.AcceptChanges();
    
                        return true;
                    }
                    catch (Exception ex)
                    {
                        //LogUtil.Error("SqlDapperUtil.BatchMoveData:" + ex.ToString(), "SqlDapperUtil.BatchMoveData");
                        return false;
                    }
                    finally
                    {
                        if (deleteCommand != null)
                        {
                            deleteCommand.Parameters.Clear();
                        }
                    }
                }
    
            }
    
            /// <summary>
            /// 批量复制数据(把源DB中根据SQL语句查出的结果批量COPY插入到目的DB的目的表中)
            /// </summary>
            public TResult BatchCopyData<TResult>(string srcSelectSql, string destConnName, string destTableName, IDictionary<string, string> colMappings, Func<IDbConnection, TResult> afterCoppyFunc)
            {
    
                using (SqlDataAdapter srcSqlDataAdapter = new SqlDataAdapter(srcSelectSql, GetDbConnectionString(dbConnectionName, out dbProviderName)))
                {
                    DataTable srcTable = new DataTable();
                    TResult copyResult = default(TResult);
                    try
                    {
                        srcSqlDataAdapter.AcceptChangesDuringFill = true;
                        srcSqlDataAdapter.AcceptChangesDuringUpdate = false;
                        srcSqlDataAdapter.Fill(srcTable);
    
                        if (srcTable == null || srcTable.Rows.Count <= 0) return copyResult;
    
    
                        string dbProviderName2 = null;
                        using (var destConn = new SqlConnection(GetDbConnectionString(destConnName, out dbProviderName2)))
                        {
                            destConn.Open();
                            string tempDestTableName = "#temp_" + destTableName;
                            destConn.Execute(string.Format("select top 0 * into {0} from {1}", tempDestTableName, destTableName));
                            bool bcpResult = false;
                            using (var destSqlBulkCopy = new SqlBulkCopy(destConn))
                            {
                                try
                                {
                                    destSqlBulkCopy.BulkCopyTimeout = 120;
                                    destSqlBulkCopy.DestinationTableName = tempDestTableName;
                                    foreach (var col in colMappings)
                                    {
                                        destSqlBulkCopy.ColumnMappings.Add(col.Key, col.Value);
                                    }
    
                                    destSqlBulkCopy.BatchSize = 1000;
                                    destSqlBulkCopy.WriteToServer(srcTable);
                                    bcpResult = true;
                                }
                                catch (Exception ex)
                                {
                                    //LogUtil.Error("SqlDapperUtil.BatchMoveData.SqlBulkCopy:" + ex.ToString(), "SqlDapperUtil.BatchMoveData");
                                }
                            }
    
                            if (bcpResult)
                            {
                                copyResult = afterCoppyFunc(destConn);
                            }
    
                            destConn.Close();
                        }
    
                        return copyResult;
                    }
                    catch (Exception ex)
                    {
                        //LogUtil.Error("SqlDapperUtil.BatchCopyData:" + ex.ToString(), "SqlDapperUtil.BatchCopyData");
                        return copyResult;
                    }
                }
    
            }
    
    
            /// <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;
                }
            }
    
            ~SqlDapperUtil()
            {
                try
                {
                    CloseDbConnection(dbConnection, true);
                }
                catch
                { }
            }
    
        }
    }
    

    优点:

    ColumnAttributeTypeMapper辅助类相关代码如下:(如果不考虑实体类的属性与表字段不一致的情况,如下映射类可以不需要添加,同时SqlDapperUtil中移除相关依赖ColumnAttributeTypeMapper逻辑即可)

    1、开源、轻量、小巧、上手容易。

    using Dapper;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Reflection;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace KYExpress.Common
    {
        public class ColumnAttributeTypeMapper<T> : FallbackTypeMapper
        {
            public ColumnAttributeTypeMapper()
                : base(new SqlMapper.ITypeMap[]
                    {
                        new CustomPropertyTypeMap(
                           typeof(T),
                           (type, columnName) =>
                               type.GetProperties().FirstOrDefault(prop =>
                                   prop.GetCustomAttributes(false)
                                       .OfType<ColumnAttribute>()
                                       .Any(attr => attr.Name == columnName)
                                   )
                           ),
                        new DefaultTypeMap(typeof(T))
                    })
            {
            }
        }
    
        [AttributeUsage(AttributeTargets.Property, AllowMultiple = true)]
        public class ColumnAttribute : Attribute
        {
            public string Name { get; set; }
        }
    
        public class FallbackTypeMapper : SqlMapper.ITypeMap
        {
            private readonly IEnumerable<SqlMapper.ITypeMap> _mappers;
    
            public FallbackTypeMapper(IEnumerable<SqlMapper.ITypeMap> mappers)
            {
                _mappers = mappers;
            }
    
    
            public ConstructorInfo FindConstructor(string[] names, Type[] types)
            {
                foreach (var mapper in _mappers)
                {
                    try
                    {
                        ConstructorInfo result = mapper.FindConstructor(names, types);
                        if (result != null)
                        {
                            return result;
                        }
                    }
                    catch (NotImplementedException)
                    {
                    }
                }
                return null;
            }
    
            public SqlMapper.IMemberMap GetConstructorParameter(ConstructorInfo constructor, string columnName)
            {
                foreach (var mapper in _mappers)
                {
                    try
                    {
                        var result = mapper.GetConstructorParameter(constructor, columnName);
                        if (result != null)
                        {
                            return result;
                        }
                    }
                    catch (NotImplementedException)
                    {
                    }
                }
                return null;
            }
    
            public SqlMapper.IMemberMap GetMember(string columnName)
            {
                foreach (var mapper in _mappers)
                {
                    try
                    {
                        var result = mapper.GetMember(columnName);
                        if (result != null)
                        {
                            return result;
                        }
                    }
                    catch (NotImplementedException)
                    {
                    }
                }
                return null;
            }
    
    
            public ConstructorInfo FindExplicitConstructor()
            {
                return _mappers
                    .Select(mapper => mapper.FindExplicitConstructor())
                    .FirstOrDefault(result => result != null);
            }
        }
    
    }
    

    2、支持的数据库还蛮多的, Mysql,SqlLite,Sqlserver,Oracle等一系列的数据库。

    使用示例方法如下:

    3、Dapper原理通过Emit反射IDataReader的序列队列来快速的得到和产生对象。性能貌似很牛逼的样子

     1.先来模拟各种查询数据(由于是直接写模拟SQL输出,故没有条件,也便于大家COPY后直接可以测试结果)

    缺点:

                //实例化SqlDapperUtil对象,构造函数是config文件中的connectionStrings的Name名
                var dapper = new SqlDapperUtil("LmsConnectionString");
    
                //查询1个值
                DateTime nowTime = dapper.GetValue<DateTime>("select getdate() as nowtime");
    
    
                //查询1行值,并转换成字典(这对于临时查询多个字段而无需定义实体类有用)
                Dictionary<string, dynamic> rowValues = dapper.GetFirstValues("select 0 as col0,1 as col1,2 as col2");
    
    
                //查询1行并返回实体类
                Person person = dapper.GetModel<Person>("select '张三' as Name,22 as Age,'2018-1-1' as BirthDay,'中国广东深圳' as HomeAddr");
    
    
                //查询1行表字段与实体类属性不一致映射
                Person person2 = dapper.GetModel<Person>("select '张三' as Name,22 as Age,'2018-1-1' as BirthDay,'中国广东深圳' as HomeAddress");
    
    
                //查询多行返回实体集合
                var persons = dapper.GetModelList<Person>(@"select '张三' as Name,22 as Age,'2018-1-1' as BirthDay,'中国广东深圳' as HomeAddr union all
                                                                                select '李四' as Name,25 as Age,'2018-10-1' as BirthDay,'中国广东深圳' as HomeAddress union all
                                                                                select '王五' as Name,35 as Age,'1982-10-1' as BirthDay,'中国广东广州' as HomeAddress
                                                                            ");
    
    
                //查询多行返回1对1关联实体结果集
                var personWithCarResult = dapper.GetMultModelList<Person>(@"select t1.*,t2.* from
                                                                                                        (select '张三' as Name,22 as Age,'2018-1-1' as BirthDay,'中国广东深圳' as HomeAddr union all
                                                                                                        select '李四' as Name,25 as Age,'2018-10-1' as BirthDay,'中国广东深圳' as HomeAddress union all
                                                                                                        select '王五' as Name,35 as Age,'1982-10-1' as BirthDay,'中国广东广州' as HomeAddress)as t1 inner join
                                                                                                        (
                                                                                                        select '张三' as DriverName,'大众' as Brand,'2018-8-8' as ManufactureDate union all
                                                                                                        select '李四' as DriverName,'奔驰' as Brand,'2018-1-8' as ManufactureDate union all
                                                                                                        select '王五' as DriverName,'奥迪' as Brand,'2017-8-8' as ManufactureDate
                                                                                                        )as t2
                                                                                                        on t1.Name=t2.DriverName
                                                                            ", new[] { typeof(Person), typeof(CarInfo) }, (objs) =>
                                                                             {
                                                                                 Person personItem = objs[0] as Person;
                                                                                 CarInfo carItem = objs[1] as CarInfo;
                                                                                 personItem.Car = carItem;
                                                                                 return personItem;
                                                                             }, splitOn: "DriverName");
    
    
    
    
                //查询多行返回1对多关联实体结果=personWithManyCars
                List<Person> personWithManyCars = new List<Person>();
                dapper.GetMultModelList<Person>(@"select t1.*,t2.* from
                                                                                                        (select '张三' as Name,22 as Age,'2018-1-1' as BirthDay,'中国广东深圳' as HomeAddr union all
                                                                                                        select '李四' as Name,25 as Age,'2018-10-1' as BirthDay,'中国广东深圳' as HomeAddress union all
                                                                                                        select '王五' as Name,35 as Age,'1982-10-1' as BirthDay,'中国广东广州' as HomeAddress)as t1 inner join
                                                                                                        (
                                                                                                        select '张三' as DriverName,'大众' as Brand,'2018-8-8' as ManufactureDate union all
                                                                                                        select '张三' as DriverName,'奔驰' as Brand,'2018-1-8' as ManufactureDate union all
                                                                                                        select '张三' as DriverName,'奥迪' as Brand,'2017-8-8' as ManufactureDate
                                                                                                        )as t2
                                                                                                        on t1.Name=t2.DriverName
                                                                            ", new[] { typeof(Person), typeof(CarInfo) }, (objs) =>
                                                                            {
                                                                                Person personItem = objs[0] as Person;
                                                                                CarInfo carItem = objs[1] as CarInfo;
    
                                                                                Person personItemMain = personWithManyCars.FirstOrDefault(p => p.Name == personItem.Name);
                                                                                if (personItemMain == null)
                                                                                {
                                                                                    personItem.Cars = new List<CarInfo>();
                                                                                    personItemMain = personItem;
                                                                                    personWithManyCars.Add(personItemMain);
                                                                                }
    
                                                                                personItemMain.Cars.Add(carItem);
                                                                                return personItemMain;
    
                                                                            }, splitOn: "DriverName");
    

    作为一款ORM太过于轻量级了,根据对象自动生成sql的功能还是空白,需要自己来扩展,

    2.下面是演示如何进行增、删、改以及动态查询的情况:

    当然这也是优点, 好声音的导师们经常说某人就是张白纸……

                //使用事务创建多张表,多条SQL语句写在一起
                try
                {
                    dapper.UseDbTransaction();
                    dapper.ExecuteCommand(@"create table T_Person(Name nvarchar(20) primary key,Age int,BirthDay datetime,HomeAddress nvarchar(200));
                                                        create table T_CarInfo(DriverName nvarchar(20) primary key,Brand nvarchar(50),ManufactureDate datetime)");
                    dapper.Commit();
                }
                catch (Exception ex)
                {
                    dapper.Rollback();
                    //记日志
                }
    
                //使用事务批量插入多张表的多个记录,多条SQL分多次执行(参数支持批量集合对象传入,无需循环)
                try
                {
                    dapper.UseDbTransaction();
                    dapper.ExecuteCommand(@"insert into T_Person
    select N'张三' as Name,22 as Age,'2018-1-1' as BirthDay,N'中国广东深圳' as HomeAddress union all
    select N'李四' as Name,25 as Age,'2018-10-1' as BirthDay,N'中国广东深圳' as HomeAddress union all
    select N'王五' as Name,35 as Age,'1982-10-1' as BirthDay,N'中国广东广州' as HomeAddress");
    
    
                    var carInfos = dapper.GetModelList<CarInfo>(@"
    select N'张三' as DriverName,N'大众' as Brand,'2018-8-8' as ManufactureDate union all
    select N'李四' as DriverName,N'奔驰' as Brand,'2018-1-8' as ManufactureDate union all
    select N'王五' as DriverName,N'奥迪' as Brand,'2017-8-8' as ManufactureDate");
    
                    dapper.ExecuteCommand(@"insert into T_CarInfo(DriverName,Brand,ManufactureDate) Values(@DriverName,@Brand,@ManufactureDate)", carInfos);
    
                    dapper.Commit();
                }
                catch (Exception ex)
                {
                    dapper.Rollback();
                    //记日志
                }
    
                //执行删除,有参数,参数可以是实体类、匿名对象、字典(如有需要,可以是集合,以支持批量操作)
                bool deleteResult = dapper.ExecuteCommand("delete from T_CarInfo where DriverName=@DriverName", new { DriverName = "李四" });
    
                //构建动态执行SQL语句(以下是更新,查询类似)
                StringBuilder updateSqlBuilder = new StringBuilder();
                var updateParams = new Dictionary<string, object>();
    
                if (1 == 1)
                {
                    updateSqlBuilder.Append(",Age=@Age");
                    updateParams["Age"] = 20;
                }
    
                if (2 == 2)
                {
                    updateSqlBuilder.Append(",BirthDay=@BirthDay");
                    updateParams["BirthDay"] = Convert.ToDateTime("2010-1-1");
                }
    
                if (3 == 3)
                {
                    updateSqlBuilder.Append(",HomeAddress=@HomeAddress");
                    updateParams["HomeAddress"] = "中国北京天安门";
                }
    
                string updateSql = string.Concat("update T_Person set ", updateSqlBuilder.ToString().TrimStart(','), "  where  Name=@Name");
                updateParams["Name"] = "张三";
    
                bool updateResult = dapper.ExecuteCommand(updateSql, updateParams);
    
                //查询返回动态自定义结果,之所以不直接返回Dynamic就好,是因为可读性差,故尽可能的在执行后就转成指定的类型
                Tuple<string, int> hasCarInfo = dapper.GetDynamicModel<Tuple<string, int>>((rs) =>
                {
                    var result = rs.First();
                    return Tuple.Create<string, int>(result.Name, result.CarCount);
                }, @"select a.Name,count(b.DriverName) as CarCount from T_Person a left join T_CarInfo b on a.Name=b.DriverName where a.Name=@Name group by a.Name", new { Name = "张三" });
    

    因此针对Dapper已经有很多成熟的扩展项目了,Dapper.Rainbow、Dapper.Contrib,DapperExtensions。

    3.还有两个方法:BatchCopyData、BatchMoveData,这是特殊封装的,不是基于Dapper而是基于原生的Ado.net及BCP,目的是快速大量跨DB跨表COPY数据或转移数据,使用也不复杂,建议想了解的网友可以查看我以往的文章

    我们这里介绍的是DapperExtensions

    以上示例方法用到了两个类,如下:

    dapper-dot-net源码: (更新频率快,项目包含了各种除了Dapper-Extensions的扩展项目)

            class Person
            {
                public string Name { get; set; }
    
    
                public int Age { get; set; }
    
                public DateTime BirthDay { get; set; }
    
                [Column(Name = "HomeAddress")]
                public string HomeAddr { get; set; }
    
                public CarInfo Car { get; set; }
    
                public List<CarInfo> Cars { get; set; }
            }
    
            class CarInfo
            {
                public string Brand { get; set; }
    
                public DateTime ManufactureDate { get; set; }
    
                public string DriverName { get; set; }
            }
    

    Dapper-Extensions源码:

    SqlDapperUtil类中依赖了之前我封装的类:如:MemoryCacheUtil(本地内存依赖缓存实用工具类)、ConfigUtil(配置文件管理工具类)、EncryptUtil(加密工具类),如果项目中不想引用这些类,可以移除或改成其它方法即可。

    Dapper-Extensions的优点

     另外说明一下,为了防止和减少因DB连接未及时释放导致的连接池不足等原因,故默认执行所有的CRUD方法都是用完即释放,但有一种情况不会释放就是使用了事务,若使用事务,则必需配套使用:UseDbTransaction、Commit、或失败执行Rollback,否则可能导致未能及时释放对象,当然最终当SqlDapperUtil实例被回收后事务若没有提交或回滚,会强制执行回滚操作并释放事务及连接对象,防止可能的资源浪费情况。

    1、开源

    本来早就想总结一下这篇文章,但一直由于工作太忙没有时间,今天利用加班研究.NET CORE的空隙时间完成,请大家支持,有好东西我一定会分享的,虽然不一定高大上,但一定实用且项目中有实战过的。

    2、针对Dapper封装了常用的CRUD方法,有独立的查询语法。

    3、需要映射的实体类本身0配置,无需加特性什么的。是通过独立的映射类来处理,可以设置类映射到DB的别名,字段的别名等等。

    Dapper-Extensions的缺点:

    1、好几年没更新了

    2、不支持oracle(木有oracle的方言,已经搞定)

    3、不能同时支持多种数据库

    4、部分代码有些bug

    下面先简单介绍一下Dapper的基本语法。

    Dapper就一个.cs文件,可以放到项目代码中直接编译,也可以直接引用DLL文件。

    Dapper对DB的操作依赖于Connection,为了支持多库,咱们用IDbConnection conn

    using (IDbConnection conn = GetConnection    {        const string query = "select * from XO order by id desc";        return conn.Query<XOEntity>(query,null);    }
    

    下面是带参数的语法

    int xoID=666; //变量主键
     using (IDbConnection conn = GetConnection    {        const string query = "select * from XO where Id=@MyID";        return conn.Query<XOEntity>(query, new { MyID = xoID});                            }
    

    各种方法都重载了事务的操作,一般的数据库操作都支持。但是每次执行都需要传递sql,而且每次都要使用Using,看着不爽啊, 这……

    好吧下面简单介绍下使用Dapper-Extensions的基本语法(在Dapper-Extensions 的基础上用了Repository模式,代码效果如下)。

                //实体类            DemoEntity entity = new DemoEntity();            //根据实体主键删除            this.Delete<DemoEntity>;            //根据主键ID删除            this.Delete<DemoEntity>(1);            //增加            this.Insert<DemoEntity>;            //更新            bool result = this.Update<DemoEntity>;            //根据主键返回实体            entity = this.GetById<DemoEntity>(1);            //返回 行数            this.Count<DemoEntity>(new { ID = 1 });            //查询所有            IEnumerable<DemoEntity> list = this.GetAll<DemoEntity>();            IList<ISort> sort = new List<ISort>();            sort.Add(new Sort { PropertyName = "ID", Ascending = false });            //条件查询            list = this.GetList<DemoEntity>(new { ID = 1, Name = "123" }, sort);            //orm 拼接条件 查询            IList<IPredicate> predList = new List<IPredicate>();            predList.Add(Predicates.Field<DemoEntity>(p => p.Name, Operator.Like, "不知道%"));            predList.Add(Predicates.Field<DemoEntity>(p => p.ID, Operator.Eq, 1));            IPredicateGroup predGroup = Predicates.Group(GroupOperator.And, predList.ToArray;                        list = this.GetList<DemoEntity>(predGroup);            //分页查询            long allRowsCount = 0;            this.GetPageList<DemoEntity>(1, 10, out allRowsCount, new { ID = 1 }, sort);
    

    在说ORM之前,还是要说一下HY.DataAccess这个模块

    图片 1

    这个模块是对数据访问提供的一个Helper的功能,里面包含了 各种DB的SqlHelper,分页。

    DBHelper 都继承自IDBHelper.cs

    图片 2图片 3

    using System.Data.Common;using System.Data;namespace HY.DataAccess{    /// <summary>    /// 提供对数据库的基本操作,连接字符串需要在数据库配置。    /// </summary>    public interface IDBHelper    {        /// <summary>        /// 生成分页SQL语句        /// </summary>        /// <param name="pageIndex"></param>        /// <param name="pageSize"></param>        /// <param name="selectSql"></param>        /// <param name="sqlCount"></param>        /// <param name="orderBy"></param>        /// <returns></returns>        string GetPagingSql(int pageIndex, int pageSize, string selectSql, string sqlCount, string orderBy);        /// <summary>        /// 开始一个事务        /// </summary>        /// <returns></returns>        DbTransaction BeginTractionand();        /// <summary>        /// 开始一个事务        /// </summary>        /// <param name="connKey">数据库连接字符key</param>        DbTransaction BeginTractionand(string connKey);        /// <summary>        /// 回滚事务        /// </summary>        /// <param name="dbTransaction">要回滚的事务</param>        void RollbackTractionand(DbTransaction dbTransaction);        /// <summary>        /// 结束并确认事务        /// </summary>        /// <param name="dbTransaction">要结束的事务</param>        void CommitTractionand(DbTransaction dbTransaction);        #region DataSet        /// <summary>        /// 执行sql语句,ExecuteDataSet 返回DataSet        /// </summary>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        DataSet ExecuteDataSet(string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,ExecuteDataSet 返回DataSet        /// </summary>        /// <param name="connKey">数据库连接字符key</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        DataSet ExecuteDataSet(string connKey, string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,ExecuteDataSet 返回DataSet        /// </summary>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        DataSet ExecuteDataSet(string commandText, CommandType commandType, params DbParameter[] parameterValues);        /// <summary>        /// 执行sql语句,ExecuteDataSet 返回DataSet        /// </summary>        /// <param name="connKey">数据库连接字符key</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        DataSet ExecuteDataSet(string connKey, string commandText, CommandType commandType, params DbParameter[] parameterValues);        #endregion        #region ExecuteNonQuery        /// <summary>        /// 执行sql语句,返回影响的行数        /// </summary>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        int ExecuteNonQuery(string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,返回影响的行数        /// </summary>        /// <param name="connKey">数据库连接字符key</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        int ExecuteNonQuery(string connKey, string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,返回影响的行数        /// </summary>        /// <param name="trans">事务对象</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        int ExecuteNonQuery(DbTransaction trans, string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,返回影响的行数        /// </summary>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        int ExecuteNonQuery(string commandText, CommandType commandType, params DbParameter[] parameterValues);        /// <summary>        /// 执行sql语句,返回影响的行数        /// </summary>        /// <param name="connKey">数据库连接字符key</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        int ExecuteNonQuery(string connKey, string commandText, CommandType commandType, params DbParameter[] parameterValues);        /// <summary>        /// 执行sql语句,返回影响的行数        /// </summary>        /// <param name="trans">事务对象</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        int ExecuteNonQuery(DbTransaction trans, string commandText, CommandType commandType, params DbParameter[] parameterValues);        #endregion        #region IDataReader        /// <summary>        /// 执行sql语句,ExecuteReader 返回IDataReader        /// </summary>           /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        IDataReader ExecuteReader(string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,ExecuteReader 返回IDataReader        /// </summary>         /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        IDataReader ExecuteReader(string commandText, CommandType commandType, params DbParameter[] parameterValues);        /// <summary>        /// 执行sql语句,ExecuteReader 返回IDataReader        /// </summary>        /// <param name="connKey">数据库连接字符key</param>                /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        IDataReader ExecuteReader(string connKey, string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,ExecuteReader 返回IDataReader        /// </summary>        /// <param name="connKey">数据库连接字符key</param>                /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        IDataReader ExecuteReader(string connKey, string commandText, CommandType commandType, params DbParameter[] parameterValues);        #endregion        #region ExecuteScalar        /// <summary>        /// 执行sql语句,ExecuteScalar 返回第一行第一列的值        /// </summary>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        object ExecuteScalar(string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,ExecuteScalar 返回第一行第一列的值        /// </summary>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        object ExecuteScalar(string commandText, CommandType commandType, params DbParameter[] parameterValues);        /// <summary>        /// 执行sql语句,ExecuteScalar 返回第一行第一列的值        /// </summary>        /// <param name="trans">事务</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        object ExecuteScalar(DbTransaction trans, string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,ExecuteScalar 返回第一行第一列的值        /// </summary>        /// <param name="connKey">数据库连接字符key</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        object ExecuteScalar(string connKey, string commandText, CommandType commandType);        /// <summary>        /// 执行sql语句,ExecuteScalar 返回第一行第一列的值        /// </summary>        /// <param name="connKey">数据库连接字符key</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        object ExecuteScalar(string connKey, string commandText, CommandType commandType, params DbParameter[] parameterValues);        /// <summary>        /// 执行sql语句,ExecuteScalar 返回第一行第一列的值        /// </summary>        /// <param name="trans">事务</param>        /// <param name="commandText">sql语句</param>        /// <param name="commandType"></param>        /// <param name="parameterValues">参数</param>        /// <returns></returns>        object ExecuteScalar(DbTransaction trans, string commandText, CommandType commandType, params DbParameter[] parameterValues);        #endregion    }}
    

    View Code

    IDBSession.cs 对数据访问对象的定义

    图片 4图片 5

    using System;using System.Data;namespace HY.DataAccess{    /// <summary>    /// 数据库接口    /// </summary>    public interface IDatabase    {        IDbConnection Connection { get; }        DatabaseType DatabaseType { get; }        string ConnKey { get; }    }    /// <summary>    /// 数据库类对象    /// </summary>    public class Database : IDatabase    {        public IDbConnection Connection { get; private set; }        public DatabaseType DatabaseType { get; private set; }        public string ConnKey { get; set; }        public Database(IDbConnection connection)        {            Connection = connection;        }        public Database(DatabaseType dbType, string connKey)        {            DatabaseType = dbType;            ConnKey = connKey;            Connection = SqlConnectionFactory.CreateSqlConnection(dbType, connKey);        }    }    /// <summary>    /// 数据连接事务的Session接口    /// </summary>    public interface IDBSession : IDisposable    {        string ConnKey { get; }        DatabaseType DatabaseType { get; }        IDbConnection Connection { get; }        IDbTransaction Transaction { get; }        IDbTransaction Begin(IsolationLevel isolation = IsolationLevel.ReadCommitted);        void Commit();        void Rollback();    }}
    

    View Code

    SqlConnectionFactory.cs 这个类是采用工厂模式创建DB连接的封装,代码如下:

    图片 6图片 7

    using System;using System.Collections.Generic;using System.Configuration;using System.Data;namespace HY.DataAccess{    public enum DatabaseType    {            SqlServer,        MySql,        Oracle,        DB2    }    public class SqlConnectionFactory    {        public static IDbConnection CreateSqlConnection(DatabaseType dbType, string strKey)        {            IDbConnection connection = null;            string strConn = ConfigurationManager.ConnectionStrings[strKey].ConnectionString;            switch             {                case DatabaseType.SqlServer:                    connection = new System.Data.SqlClient.SqlConnection;                    break;                case DatabaseType.MySql:                    //connection = new MySql.Data.MySqlClient.MySqlConnection;                    //break;                case DatabaseType.Oracle:                    //connection = new Oracle.DataAccess.Client.OracleConnection;                    connection = new System.Data.OracleClient.OracleConnection;                    break;                case DatabaseType.DB2:                    connection = new System.Data.OleDb.OleDbConnection;                    break;            }            return connection;        }    }}
    

    View Code

    ORM也不是万能的,比如做大数据的批量插入什么的,还是需要SqlHelper,加上有的人就喜欢DataTable或者DataSet。

    所以SqlHelper作为根基,ORM作为辅助,万无一失啊。

    下面说说ORM这块的实现方式。见下截图

    图片 8

    IDataServiceRepository.cs(提供业务层使用,里面的方法不支持传递sql,包含sql的语句最好还是放在数据层操作的好)

    图片 9图片 10

    using System.Collections.Generic;using System.Data;using DapperExtensions;using HY.DataAccess;namespace HY.ORM{    public interface IDataServiceRepository    {        IDBSession DBSession { get; }        T GetById<T>(dynamic primaryId) where T : class;        IEnumerable<T> GetByIds<T>(IList<dynamic> ids) where T : class;        IEnumerable<T> GetAll<T>() where T : class;        int Count<T>(object predicate, bool buffered = false) where T : class;        //lsit        IEnumerable<T> GetList<T>(object predicate = null, IList<ISort> sort = null, bool buffered = false) where T : class;        IEnumerable<T> GetPageList<T>(int pageIndex, int pageSize, out long allRowsCount, object predicate = null, IList<ISort> sort = null, bool buffered = true) where T : class;        dynamic Insert<T>(T entity, IDbTransaction transaction = null) where T : class;        bool InsertBatch<T>(IEnumerable<T> entityList, IDbTransaction transaction = null) where T : class;        bool Update<T>(T entity, IDbTransaction transaction = null) where T : class;        bool UpdateBatch<T>(IEnumerable<T> entityList, IDbTransaction transaction = null) where T : class;        int Delete<T>(dynamic primaryId, IDbTransaction transaction = null) where T : class;        int DeleteList<T>(object predicate, IDbTransaction transaction = null) where T : class;        bool DeleteBatch<T>(IEnumerable<dynamic> ids, IDbTransaction transaction = null) where T : class;    }}
    

    View Code

    IDataRepository.cs(提供数据层使用,继承了上面的IDataServiceRepository,支持传入sql)

    图片 11图片 12

    using System;using System.Collections.Generic;using System.Data;using Dapper;using HY.DataAccess;namespace HY.ORM{    public interface IDataRepository : IDataServiceRepository    {        IDBSession DBSession { get; }                    IEnumerable<T> Get<T>(string sql, dynamic param = null, bool buffered = true) where T : class;        IEnumerable<dynamic> Get(string sql, dynamic param = null, bool buffered = true);        IEnumerable<TReturn> Get<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map,            dynamic param = null, IDbTransaction transaction = null, bool buffered = true,            string splitOn = "Id", int? commandTimeout = null);         IEnumerable<TReturn> Get<TFirst, TSecond,TThird, TReturn>(string sql, Func<TFirst, TSecond,TThird, TReturn> map,            dynamic param = null, IDbTransaction transaction = null, bool buffered = true,            string splitOn = "Id", int? commandTimeout = null);        SqlMapper.GridReader GetMultiple(string sql, dynamic param = null, IDbTransaction transaction = null,            int? commandTimeout = null, CommandType? commandType = null);         IEnumerable<T> GetPage<T>(int pageIndex, int pageSize, out long allRowsCount, string sql, dynamic param = null, string allRowsCountSql=null,  dynamic allRowsCountParam = null, bool buffered = true) where T : class;              Int32 Execute(string sql, dynamic param = null, IDbTransaction transaction = null);    }}
    

    View Code

    RepositoryServiceBase.cs(IDataServiceRepository的实现类)

    图片 13图片 14

    using System.Collections.Generic;using System.Data;using System.Linq;using Dapper;using DapperExtensions;using HY.DataAccess;namespace HY.ORM{    public class RepositoryServiceBase : IDataServiceRepository    {        public RepositoryServiceBase()        {        }        public RepositoryServiceBase(IDBSession dbSession)        {            DBSession = dbSession;        }        public IDBSession DBSession { get; private set; }        public void SetDBSession(IDBSession dbSession)        {            DBSession = dbSession;        }        /// <summary>        /// 根据Id获取实体        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="primaryId"></param>        /// <returns></returns>        public T GetById<T>(dynamic primaryId) where T : class        {            return DBSession.Connection.Get<T>(primaryId as object, databaseType: DBSession.DatabaseType);        }        /// <summary>        /// 根据多个Id获取多个实体        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="ids"></param>        /// <returns></returns>        public IEnumerable<T> GetByIds<T>(IList<dynamic> ids) where T : class        {            var tblName = string.Format("dbo.{0}", typeof;            var idsin = string.Join(",", ids.ToArray<dynamic>;            var sql = "SELECT * FROM @table WHERE Id in ";            IEnumerable<T> dataList = DBSession.Connection.Query<T>(sql, new { table = tblName, ids = idsin });            return dataList;        }        /// <summary>        /// 获取全部数据集合        /// </summary>        /// <typeparam name="T"></typeparam>        /// <returns></returns>        public IEnumerable<T> GetAll<T>() where T : class        {            return DBSession.Connection.GetList<T>(databaseType: DBSession.DatabaseType);        }        /// <summary>        /// 统计记录总数        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="predicate"></param>        /// <param name="buffered"></param>        /// <returns></returns>        public int Count<T>(object predicate, bool buffered = false) where T : class        {            return DBSession.Connection.Count<T>(predicate, databaseType: DBSession.DatabaseType);        }        /// <summary>        /// 查询列表数据        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="predicate"></param>        /// <param name="sort"></param>        /// <param name="buffered"></param>        /// <returns></returns>        public IEnumerable<T> GetList<T>(object predicate = null, IList<ISort> sort = null,            bool buffered = false) where T : class        {            return DBSession.Connection.GetList<T>(predicate, sort, null, null, buffered, databaseType: DBSession.DatabaseType);        }        /// <summary>        /// 分页        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="pageIndex"></param>        /// <param name="pageSize"></param>        /// <param name="allRowsCount"></param>        /// <param name="predicate"></param>        /// <param name="sort"></param>        /// <param name="buffered"></param>        /// <returns></returns>        public IEnumerable<T> GetPageList<T>(int pageIndex, int pageSize, out long allRowsCount,            object predicate = null, IList<ISort> sort = null, bool buffered = true) where T : class        {            if (sort == null)            {                sort = new List<ISort>();            }            IEnumerable<T> entityList = DBSession.Connection.GetPage<T>(predicate, sort, pageIndex, pageSize, null, null, buffered, databaseType: DBSession.DatabaseType);            allRowsCount = DBSession.Connection.Count<T>(predicate, databaseType: DBSession.DatabaseType);            return entityList;        }        /// <summary>        /// 插入单条记录        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="entity"></param>        /// <param name="transaction"></param>        /// <returns></returns>        public dynamic Insert<T>(T entity, IDbTransaction transaction = null) where T : class        {            dynamic result = DBSession.Connection.Insert<T>(entity, transaction, databaseType: DBSession.DatabaseType);            return result;        }        /// <summary>        /// 更新单条记录        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="entity"></param>        /// <param name="transaction"></param>        /// <returns></returns>        public bool Update<T>(T entity, IDbTransaction transaction = null) where T : class        {            bool isOk = DBSession.Connection.Update<T>(entity, transaction, databaseType: DBSession.DatabaseType);            return isOk;        }        /// <summary>        /// 删除单条记录        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="primaryId"></param>        /// <param name="transaction"></param>        /// <returns></returns>        public int Delete<T>(dynamic primaryId, IDbTransaction transaction = null) where T : class        {            var entity = GetById<T>(primaryId);            var obj = entity as T;            int isOk = DBSession.Connection.Delete<T>(obj, databaseType: DBSession.DatabaseType);            return isOk;        }        /// <summary>        /// 删除单条记录        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="predicate"></param>        /// <param name="transaction"></param>        /// <returns></returns>         public int DeleteList<T>(object predicate = null, IDbTransaction transaction = null) where T : class        {            return DBSession.Connection.Delete<T>(predicate, transaction, databaseType: DBSession.DatabaseType);        }        /// <summary>        /// 批量插入功能        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="entityList"></param>        /// <param name="transaction"></param>        public bool InsertBatch<T>(IEnumerable<T> entityList, IDbTransaction transaction = null) where T : class        {            bool isOk = false;            foreach (var item in entityList)            {                Insert<T>(item, transaction);            }            isOk = true;            return isOk;        }        /// <summary>        /// 批量更新()        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="entityList"></param>        /// <param name="transaction"></param>        /// <returns></returns>        public bool UpdateBatch<T>(IEnumerable<T> entityList, IDbTransaction transaction = null) where T : class        {            bool isOk = false;            foreach (var item in entityList)            {                Update<T>(item, transaction);            }            isOk = true;            return isOk;        }        /// <summary>        /// 批量删除        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="ids"></param>        /// <param name="transaction"></param>        /// <returns></returns>        public bool DeleteBatch<T>(IEnumerable<dynamic> ids, IDbTransaction transaction = null) where T : class        {            bool isOk = false;            foreach (var id in ids)            {                Delete<T>(id, transaction);            }            isOk = true;            return isOk;        }    }}
    

    View Code

    RepositoryBase.cs(IDataRepository的实现类)

    图片 15图片 16

    using System;using System.Collections.Generic;using System.Data;using Dapper;using DapperExtensions;using HY.DataAccess;namespace HY.ORM{    /// <summary>    /// Repository基类    /// </summary>    public class RepositoryBase : RepositoryServiceBase, IDataRepository    {        public RepositoryBase()        {        }        public new void SetDBSession(IDBSession dbSession)        {            base.SetDBSession(dbSession);        }        public RepositoryBase(IDBSession dbSession)            : base(dbSession)        {        }        /// <summary>        /// 根据条件筛选出数据集合        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="sql"></param>        /// <param name="param"></param>        /// <param name="buffered"></param>        /// <returns></returns>        public IEnumerable<T> Get<T>(string sql, dynamic param = null, bool buffered = true) where T : class        {            return DBSession.Connection.Query<T>(sql, param as object, DBSession.Transaction, buffered);        }        /// <summary>        /// 根据条件筛选数据集合        /// </summary>        /// <param name="sql"></param>        /// <param name="param"></param>        /// <param name="buffered"></param>        /// <returns></returns>        public IEnumerable<dynamic> Get(string sql, dynamic param = null, bool buffered = true)        {            return DBSession.Connection.Query(sql, param as object, DBSession.Transaction, buffered);        }        /// <summary>        /// 分页查询        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="pageIndex"></param>        /// <param name="pageSize"></param>        /// <param name="allRowsCount"></param>        /// <param name="sql"></param>        /// <param name="param"></param>        /// <param name="allRowsCountSql"></param>        /// <param name="allRowsCountParam"></param>        /// <param name="buffered"></param>        /// <returns></returns>        public IEnumerable<T> GetPage<T>(int pageIndex, int pageSize, out long allRowsCount, string sql, dynamic param = null, string allRowsCountSql = null, dynamic allRowsCountParam = null, bool buffered = true) where T : class        {            IEnumerable<T> entityList = DBSession.Connection.GetPage<T>(pageIndex, pageSize, out allRowsCount, sql, param as object, allRowsCountSql, null, null, buffered, databaseType: DBSession.DatabaseType);            return entityList;        }        /// <summary>        /// 根据表达式筛选        /// </summary>        /// <typeparam name="TFirst"></typeparam>        /// <typeparam name="TSecond"></typeparam>        /// <typeparam name="TReturn"></typeparam>        /// <param name="sql"></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>        /// <returns></returns>        public IEnumerable<TReturn> Get<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map,            dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id",            int? commandTimeout = null)        {            return DBSession.Connection.Query(sql, map, param as object, transaction, buffered, splitOn);        }        /// <summary>        /// 根据表达式筛选        /// </summary>        /// <typeparam name="TFirst"></typeparam>        /// <typeparam name="TSecond"></typeparam>        /// <typeparam name="TReturn"></typeparam>        /// <param name="sql"></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>        /// <returns></returns>        public IEnumerable<TReturn> Get<TFirst, TSecond, TThird, TReturn>(string sql, Func<TFirst, TSecond, TThird, TReturn> map,            dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id",            int? commandTimeout = null)        {            return DBSession.Connection.Query(sql, map, param as object, transaction, buffered, splitOn);        }        /// <summary>        /// 获取多实体集合        /// </summary>        /// <param name="sql"></param>        /// <param name="param"></param>        /// <param name="transaction"></param>        /// <param name="commandTimeout"></param>        /// <param name="commandType"></param>        /// <returns></returns>        public SqlMapper.GridReader GetMultiple(string sql, dynamic param = null, IDbTransaction transaction = null,            int? commandTimeout = null, CommandType? commandType = null)        {            return DBSession.Connection.QueryMultiple(sql, param as object, transaction, commandTimeout, commandType);        }        /// <summary>        /// 执行sql操作        /// </summary>        /// <param name="sql"></param>        /// <param name="param"></param>        /// <returns></returns>        public int Execute(string sql, dynamic param = null, IDbTransaction transaction = null)        {            return DBSession.Connection.Execute(sql, param as object, transaction);        }    }}
    

    View Code

    说起DapperExtensions修改的小地方还蛮多的,下图是一个代码比较的截图。所以一会把代码打包贴上来吧。

    图片 17

    上述代码就可以编译成 HY.ORM.DLL文件了。

    下面就可以在 自己业务层继承HY.ORM中的RepositoryServiceBase类 ,数据层继承HY.ORM中的 RepositoryBase类。

    通过各自的构造函数或者,SetDBSession(Helper.CreateDBSession; 进行数据连接初始化。

    接下来配置实体类和DB的映射:

        public class DemoEntity    {                public int ID { get; set; }        public string Name { get; set; }    }    [Serializable]    public class DomoEntityORMMapper : ClassMapper<DemoEntity>    {        public DomoEntityORMMapper()        {            base.Table("Demo");            //Map(f => f.UserID).Ignore();//设置忽略            //Map(f => f.Name).Key(KeyType.Identity);//设置主键  (如果主键名称不包含字母“ID”,请设置)                       AutoMap();        }    }
    

    这样就可以在类中 实现 this.Get<DemoEntity>("select * from Demo where ID=@ID", new { ID = 1 }); 这样的语法了。

    具体的使用方发

    下图是我要介绍实现的项目截图:

    图片 18

    其实也是三层,只是名字不一样而已。

    HY.Web

    HY.Web.Iservice

    HY.Web.Service(服务层,HY.Web.Iservice的实现类, 你也可以理解为业务逻辑层BLL)

    HY.Web.DAO(数据访问层, 你也可以理解为DAL)

    HY.Web.Entity(实体层, 目前只定义了数据实体, 如果你的系统需要给app提供数据, 那么传输的数据要精简,就需要单独定义DTO了。 )

    就那用户表来做个实例吧,表结构如下:(下图是用代码生成器截图效果,可以直接修改数据库的描述信息,开发利器。需要的朋友点这里【CodeBuilder-RazorEngine】)

    图片 19

    HY.Web.Entity

    在HY.Web.Entity的项目中新建Sys_UsersEntity.cs 定义实体类

    图片 20View Code

    HY.Web.DAO

    定义基类 BaseRepository.cs (可以设置默认的DBsession,方便扩展其它东东)

    图片 21View Code

    定义数据访问层 Sys_UsersRepository.cs (代码里可以封装任何需要写sql 的代码)

    图片 22View Code

    HY.Web.IService

    定义接口 ISys_UsersService.cs ,提供给UI访问。

    图片 23View Code

    HY.Web.Service

    定义BaseService.cs,(可以设置默认的DBsession,方便扩展其它东东)

    图片 24View Code

    定义Sys_UsersService.cs, 去实现ISys_UsersService。

    图片 25View Code

    HY.Web

    1、定义相关的Controller

    2、ISys_UsersService iSys_UsersService = new Sys_UsersService(); (这块其实可以使用 IoC, 相关内容且听后续分解)

    3、调用接口

    图片 26View Code

    **下载:**

    HY.DataAccess

    修改后的DapperExtensions:Dapperextensions.RAR

    ps:已经更新版本了, 加入了对lambda的扩展,点击这里进入

    相关文章:

    搭建一套自己实用的.net架构

    搭建一套自己实用的.net架构【日志模块-log4net】

    搭建一套自己实用的.net架构【ORM-Dapper+DapperExtensions】

    搭建一套自己实用的.net架构续 【ORM Dapper+DapperExtensions+Lambda】

    搭建一套自己实用的.net架构【CodeBuilder-RazorEngine】

    原文链接:

    本文由澳门新葡8455最新网站发布于编程教学,转载请注明出处:据他们说Dapper一次封装了二个易用的ORM工具类,

    关键词: