储存层的考虑及达成,搭建一套自身实用的

基于Dapper3次封装了贰个易用的O汉兰达M工具类:SqlDapperUtil,把常备能用到的各类CRUD都进展了简化封装,让通常程序员只需关怀业务即可,因为非常简单,故直接贴源代码,大家若需使用能够一向复制到项目中,该SqlDapperUtil已大面积用于集团项目中。

   
近来趁着不忙,在揣摩3个搭建贰个开源的完好项目,至于原因以及全部项目框架后面小说笔者再作证。既然要起贰个完完全全的品类,那么数量存款和储蓄访问就少不了,那篇小说作者首要介绍那个新项目(OSS.Core)中笔者对仓库储存层的简便思维和达成进度(当前项目还地处搭建阶段),首要集中在以下多少个地点:

   
近日趁着不忙,在思索二个搭建二个开源的总体项目,至于原因以及全部项目框架前边小说小编再作证。既然要起2个完好无缺的档次,那么数量存款和储蓄访问就少不了,那篇小说作者重要介绍那一个新项目(OSS.Core)中作者对仓库储存层的简单思维和完毕进度(当前项目还处于搭建阶段),首要集中在以下几个方面:

近年来成熟的O景逸SUVM不胜枚举,那里只介绍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
            { }
        }

    }
}
  1. 数量存款和储蓄层的须要

  2. ORAV4M框架选取

  3. OSS.Core储存层设计完成

  4. 调用示例

  1. 数据存款和储蓄层的急需

  2. OLacrosseM框架选拔

  3. OSS.Core仓库储存层设计达成

  4. 调用示例

优点:

ColumnAttributeTypeMapper协助类相关代码如下:(假若不考虑实体类的属性与表字段不等同的景色,如下映射类能够不须求添加,同时SqlDapperUtil中移除相关倚重ColumnAttributeTypeMapper逻辑即可)

   上面的落实部分中可能须要你对.NET的
泛型,委托,扩张,表达式等有二个基础驾驭。正是因为那个语言特征,方便大家对操作共性的抽取统一。

   下面的兑现部分中也许供给你对.NET的
泛型,委托,扩充,表达式等有三个基础掌握。便是因为那么些语言特征,方便大家对操作共性的抽取统一。

一 、开源、轻量、小巧、上手简单。

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);
        }
    }

}

一. 数码存款和储蓄层供给

一. 数量存储层须要

二 、补助的数据库还蛮多的,
Mysql,SqlLite,Sqlserver,Oracle等一多级的数据库。

选取示例方法如下:

  既然是2个完完全全的品种,数据访问是其最宗旨的一对,同时,数据访问也是总体项目最容易并发瓶颈的地点。在自家的分割中,其负责的剧中人物是负责整个数据的输入输出,不仅仅是本着单数据库(有时甚至多库),有时还索要形成一流缓存的实现,给逻辑层提供最基础的多寡支撑。

  既然是2个完好的类型,数据访问是其最大旨的一对,同时,数据访问也是成套项目最不难并发瓶颈的地点。在自家的分割中,其负责的角色是负担整个数据的输入输出,不仅仅是指向单数据库(有时甚至多库),有时还索要做到一级缓存的兑现,给逻辑层提供最基础的多寡援助。

叁 、Dapper原理通过Emit反射IDataReader的队列队列来十分的快的拿走和发生对象。质量一般很牛逼的金科玉律

 1.先来效仿各类查询数据(由于是直接写模拟SQL输出,故并未原则,也惠及我们COPY后直接可以测试结果)

 
 业务永远是在转变的,那么项目也要具有快捷多变的能力,所以自个儿期待数据层能够保持相对的简易,在结构上尽量收缩复杂的耦合查询,在性质上尽量收缩不供给的损耗,例如反射的豁达用到。同时针对各种工作对象完毕数据库层面为主的CRUD统一封装完毕。假使有亟待的时候还可以够在至少的改变下进入缓存的翻新。(对于哪些促成差异模块区别缓存存款和储蓄策略,像Redis,Memcached会在前边小说介绍)

 
 业务永远是在转变的,那么项目也要负有飞速多变的能力,所以本身期待数据层能够保持相对的简约,在结构上尽量收缩复杂的耦合查询,在性质上尽量减弱不须要的损耗,例如反射的汪洋用到。同时针对各样工作对象达成数据库层面为主的CRUD统一封装完结。假设有亟待的时候仍是能够在至少的更动下进入缓存的换代。(对于什么贯彻不相同模块不一致缓存存款和储蓄策略,像Redis,Memcached会在背后小说介绍)

缺点:

            //实例化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");

  同时,对于一个有点有点规模的类型来说,消除数据库访问的最飞快做法正是贯彻读写分离,所以,作者期望那些框架能够在一从头在底层就落实了读写分离的支撑,以幸免中期再重头对工作代码的大度改动。  

  同时,对于二个略带有点范围的门类以来,消除数据库访问的最便捷做法正是落到实处读写分离,所以,小编希望以此框架能够在一从头在底层就兑现了读写分离的支持,以制止前期再重头对事情代码的大气改动。  

用作一款O奥迪Q3M太过分轻量级了,依据指标自动生成sql的效力依旧空荡荡,须求协调来扩充,

2.底下是出现说法怎么着进展增、删、改以及动态查询的景色:

二. O卡宴M 框架选用

二. O奥迪Q5M 框架选取

当然那也是可取, 好声音的元帅们时不时说某人正是张白纸……

            //使用事务创建多张表,多条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 = "张三" });

  当然,要是为了简单和总体性,直接ADO.NET连接理论上的话是比较火速的做法,不过如此会导致多量的重新操作逻辑代码,同时也会促成代码的一无可取,增添有限协助复杂度。作为技术人员,不仅需求解决业务难点进步效用,同时也要增加协调的频率,所以小编会选取2个O奥德赛M框架来成功部分基础工作。

  当然,如若为了简单和质量,直接ADO.NET连接理论上的话是相比较火速的做法,也就那样会导致大气的重新操作逻辑代码,同时也会促成代码的眼花缭乱,扩张入保险障复杂度。作为技术职员,不仅须要缓解业务难点升高效能,同时也要升高自个儿的功用,所以笔者会选择三个O中华VM框架来形成都部队分基础工作。

于是针对Dapper已经有好多早熟的扩张项目了,Dapper.Rainbow、Dapper.Contrib,DapperExtensions。

3.还有三个点子:BatchCopyData、BatchMoveData,那是新鲜封装的,不是依照Dapper而是基于原生的Ado.net及BCP,目标是快速大批量跨DB跨表COPY数据或更换数据,使用也不复杂,提出想询问的网上朋友能够查看本身过去的稿子

  当前在.NET种类下,开源的ORAV4M框架很多,如:Entityframework,NHibernate,iBATIS.NET,Dapper等等,各有特色,基于后边作者说的,保险功能的还要,兼顾不难仍是能够最大程度减弱品质的花费,并且提供.net
standard标准库下的扶助。那里比较之后小编采纳Dapper那么些半自动化的O奥迪Q7M作为仓库储存层的底蕴框架,选拔原因如下:

  当前在.NET种类下,开源的O景逸SUVM框架很多,如:Entityframework,NHibernate,iBATIS.NET,Dapper等等,各有特色,基于前边小编说的,保障作用的还要,兼顾简单还能够最大程度收缩质量的消耗,并且提供.net
standard标准库下的扶助。这里比较之后笔者选用Dapper那么些半自动化的OPRADOM作为仓库储存层的底蕴框架,接纳原因如下:

我们那边介绍的是DapperExtensions

上述示例方法用到了五个类,如下:

  1. 其布局不难,整个封装重要集聚Dapper.cs文件中,体量相当小

  1. 其结构简单,整个封装首要集聚Dapper.cs文件中,体积一点都不大

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; }
        }

      2. 封装功效简单强大,对原生SQL的帮助上很利索

      2. 装进成效不难强大,对原生SQL的帮衬上很灵敏

Dapper-Extensions源码:

SqlDapperUtil类中凭借了事先笔者封装的类:如:MemoryCacheUtil(本地内部存款和储蓄器注重缓存实用工具类)、ConfigUtil(配置文件管理工具类)、EncryptUtil(加密工具类),若是项目中不想引用这么些类,能够移除或改成任何方式即可。

储存层的考虑及达成,搭建一套自身实用的。    那点差不离完胜其他框架,无需任何多余的装置,同时基本上你可调用全部原生ADO.NET的成效,sql语句完全自身掌握控制,却又无需关心command的参数赋值,以及结果实体转换等。

    这一点差不多完胜其他框架,无需任何多余的设置,同时基本上你可调用全数原生ADO.NET的效率,sql语句完全自身掌握控制,却又无需关切command的参数赋值,以及结果实体转换等。

Dapper-Extensions的优点

 其它说澳优(Ausnutria Hyproca)下,为了避防万一和削减因DB连接未即刻放出导致的连接池不足等原因,故暗中同意执行全部的CRUD方法都以用完即自由,但有一种状况不会自由正是选择了事情,若选择工作,则少不了配套使用:UseDbTransaction、Commit、或破产执行Rollback,不然可能引致不可能马上放出对象,当然最终当SqlDapperUtil实例被回收后事务若没有付诸或回滚,会强制执行回滚操作并释放工作及连接对象,幸免大概的财富浪费情形。

  3. 质量上的全速

  3. 个性上的便捷

1、开源

自然早就想计算一下那篇小说,但直接由于工作太忙没有时间,明日采纳加班商量.NET
CORE的空当时间成功,请大家支持,有好东西自身必然会享用的,即便不自然高大上,但一定实用且项目中有实战过的。

    很多O奥迪Q3M的实体映射通过反射来达成,这一点上Dapper再一次展现其魔力,在Commond参数赋值,以及实体转换等重庆大学模块,使用了Reflection.Emit成效,直接完毕了MSIL编写翻译层面包车型地铁赋值完结,之所以说直接,是因为其自己代码还亟需编写翻译器生成IL代码。在运作时依据项目属性动态创设赋值委托方法。

    很多O路虎极光M的实业映射通过反射来形成,那一点上Dapper再度展现其魔力,在Commond参数赋值,以及实体转换等重庆大学模块,使用了Reflection.Emit功用,间接实现了MSIL编写翻译层面包车型客车赋值完结,之所以说直接,是因为其本身代码还要求编写翻译器生成IL代码。在运行时依照项目属性动态创立赋值委托方法。

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

 

 

三 、需求映射的实业类自个儿0配置,无需加个性什么的。是通过单独的映射类来拍卖,可以设置类映射到DB的小名,字段的小名等等。

三. OSS.Core仓库储存层设计完成

三. OSS.Core仓库储存层设计达成

Dapper-Extensions的缺点:

 
 通过Dapper能够兑未来数据库访问片段一层简易的包裹,可是小编依然须求手动编写不少的sql语句,同时还要实行参数化的拍卖,包蕴数据的读写分离等。那么这几个职能的贯彻作者将在OSS.Core.RepDapper中形成,为了便利精通,先贴出四个简单易行的卷入后的艺术调用传输流程:

 
 通过Dapper能够兑未来数据库访问一些一层简易的卷入,然则小编依然亟待手动编写不少的sql语句,同时还要开始展览参数化的拍卖,包蕴数据的读写分离等。那么这个成效的完成自个儿将在OSS.Core.RepDapper中成功,为了便于领悟,先贴出1个简易的包装后的不二法门调用传输流程:

壹 、好几年没更新了

澳门葡京备用网址 1

澳门葡京备用网址 2

二 、不匡助oracle(木有oracle的方言,已经消除)

  在这几个图里显示3个简短的章程调用流程,围绕那张图的多少个主导部分,笔者分别介绍下:

  在那几个图里呈现三个简易的法门调用流程,围绕那张图的多少个为主部分,笔者分别介绍下:

3、不能而且协助两种数据库

  1. 接口设计

  1. 接口设计

四 、部分代码有些bug

  因为本身期待以此是总体的演示项目,所在此从前边希望能够同盟不相同数据库,因而对外的蕴藏访问都基于接口调用。当然假诺你的连串素来未曾切换数据库的急需,作者更提议去掉这一环节,直接在基类中落到实处单例形式,业务逻辑层直接调用。

  因为作者期待以此是完好的演示项目,所从前边希望可以同盟差别数据库,因而对外的仓储访问都基于接口调用。当然就算你的类型一贯没有切换数据库的急需,小编更建议去掉这一环节,直接在基类中落到实处单例方式,业务逻辑层直接调用。

下面先简单介绍一下Dapper的主导语法。

  图中得以看出接口层独立于贯彻部分,作者将现实事务实人体模型型和接口
单独放在了OSS.Core.DomainMos
类库中,一方面是为了实人体模型型在各模块中的共用,另一方面解耦业务逻辑层(Services)和储存层(Reps)之间的重视关系。

  图中得以看看接口层独立于贯彻部分,笔者将切实事务实人体模型型和接口
单独放在了OSS.Core.DomainMos
类库中,一方面是为了实人体模型型在各模块中的共用,另一方面解耦业务逻辑层(Services)和储存层(Reps)之间的正视关系。

Dapper就二个.cs文件,能够松手项目代码中央直机关接编写翻译,也可以直接引用DLL文件。

  同时一个类别中数据库访问代码多数都会以CRUD为主,所以那边作者定义了一个基础接口(IBaseRep),其涵盖的法子首要有(表达式部分在背后介绍):

  同时叁个门类中数据库访问代码多数都会以CRUD为主,所以这里自个儿定义了贰个基础接口(IBaseRep),其包罗的主意首要有(说明式部分在背后介绍):

Dapper对DB的操作重视于Connection,为了扶助多库,咱们用IDbConnection conn

澳门葡京备用网址 3

澳门葡京备用网址 4

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

  具体的作业数据接口继承至基础接口就好,当中表明式部分是本身本身做了1个卷入,前边会不难介绍。

  具体的政工数据接口继承至基础接口就好,个中表明式部分是作者本人做了四个卷入,前面会不难介绍。

上边是带参数的语法

 

 

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

  2.
囤积基类完毕(BaseRep)

  2.
囤积基类达成(BaseRep)

各个情势都重载了业务的操作,一般的数据库操作都帮忙。可是每一趟执行都急需传递sql,而且每一趟都要运用Using,望着不爽啊,
那……

  首先,如图所示,大家落到实处了读写分离的多少个增加,其实说到底都会经过Excute方法,那么那里突显下方法的现实性贯彻:

  首先,如图所示,大家落到实处了读写分离的七个扩充,其实最后都会经过Excute方法,那么那里呈现下方法的现实性实现:

行吗上边简单介绍下行使Dapper-Extensions的主导语法(在Dapper-Extensions
的基础上用了Repository格局,代码效果如下)。

澳门葡京备用网址 5

澳门葡京备用网址 6

            //实体类            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);

  能够看看在那些方法提供了2个针对性IDbConnection的嘱托,提供调用层自由使用Dapper方法的同时,统一了数额访问方法入口,便于日志记录,和排查。

  能够见见在这几个法子提供了3个对准IDbConnection的嘱托,提供调用层自由使用Dapper方法的还要,统一了数码访问方法入口,便于日志记录,和排查。

在说OSportageM之前,依旧要说一下HY.DataAccess那个模块

 

 

澳门葡京备用网址 7

  其次,在很多品类中会出现用户和订单在区别库中的那类情形,因为关乎到分库的景色,所以必要子类中能有涂改连接串能力,那么那里作者通过构造函数的样式,提供了三个可空参数:

  其次,在很多门类中会出现用户和订单在不相同库中的那类景况,因为涉嫌到分库的意况,所以须要子类中能有修改连接串能力,那么那里作者经过构造函数的款型,提供了多个可空参数:

这么些模块是对数据访问提供的3个Helper的作用,里面含有了
各类DB的SqlHelper,分页。

澳门葡京备用网址 8

澳门葡京备用网址 9

DBHelper 都一而再自IDBHelper.cs

  能够看到,假设子类中定义了协调的连年串,则以子类自定义为主,不然走暗中同意的连接新闻。

  能够见到,假如子类中定义了投机的连续串,则以子类自定义为主,不然走暗中同意的连日音信。

澳门葡京备用网址 10澳门葡京备用网址 11

  

  

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

澳门葡京备用网址 12

澳门葡京备用网址 13

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

  同时,为了保证子类中可见插足缓存处理,所以选用了虚方法(virtual)的花样,保险子类能够重写。

  同时,为了确认保证子类中可见参加缓存处理,所以采取了虚方法(virtual)的花样,保证子类能够重写。

澳门葡京备用网址 14澳门葡京备用网址 15

 

 

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();    }}

  3. 基于Connection的扩展

  3. 基于Connection的扩展

View Code

  这几个地点重点分为五个部分,a. 表明式的分析,以及参数化的处理   b.
扩展Connection的Insert,Update…等Dapper没有扩展的艺术:

  这几个地点根本分为多少个部分,a. 表明式的分析,以及参数化的处理   b.
扩充Connection的Insert,Update…等Dapper没有扩张的法门:

SqlConnectionFactory.cs 这一个类是运用工厂方式开创DB连接的卷入,代码如下:

  a.
纯熟Expression表明式的仇敌应该比较明白,表明式自个儿是贰个树形接口,遵照区别的类型,可以不断的解析其子表达式,直到不持有持续分析的可能。所以那么些就相当粗略就是递归的无休止迭代,依据其差别的NodeType能够组建不相同的sql成分,因为代码较长,能够参见github下的SqlExpressionVisitor.cs类,其中参数的赋值部分,没有应用反射,而是利用的反光发射,代码详见SqlParameterEmit.cs

  a.
熟知Expression表明式的爱人应该比较精通,表达式本身是1个树形接口,遵照不相同的档次,可以持续的解析其子表达式,直到不负有继续分析的或者。所以那个就很简短就是递归的不断迭代,依照其不相同的NodeType能够组建分化的sql成分,因为代码较长,能够瞻仰github下的SqlExpressionVisitor.cs类,其中参数的赋值部分,没有运用反射,而是使用的反射发射,代码详见SqlParameterEmit.cs

澳门葡京备用网址 16澳门葡京备用网址 17

  b.
有了表明式的扩充之后,就足以获得相应的sql和参数,通过this扩张Connection方法即可,代码见ConnoctionExtention.cs

  b.
有了表达式的恢弘之后,就足以获得相应的sql和参数,通过this扩大Connection方法即可,代码见ConnoctionExtention.cs

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

四. 调用示例

四. 调用示例

O中华VM也不是文韬武韬的,比如做大数额的批量插入什么的,照旧必要SqlHelper,加上部分人就喜爱DataTable恐怕DataSet。

  1. 大家定义三个简单UserInfoMo实体(包涵mobile等品质)

  1. 我们定义2个简单UserInfoMo实体(包涵mobile等品质)

于是SqlHelper作为基础,O奥迪Q7M作为帮忙,万无一失啊。

  2. 定义接口  IUserInfoRep: IBaseRep

  2. 定义接口  IUserInfoRep: IBaseRep

下边说说O索罗德M这块的兑现格局。见下截图

  3. 定义实现类  UserInfoRep : BaseRep, IUserInfoRep

  3. 概念完毕类  UserInfoRep : BaseRep, IUserInfoRep

澳门葡京备用网址 18

  在不添加任何代码的基本功上,我们就能够完结上面包车型地铁调用:

  在不添加别的代码的底蕴上,大家就足以实现下边包车型客车调用:

IDataServiceRepository.cs(提供业务层使用,里面包车型大巴形式不帮助传递sql,包蕴sql的口舌最好依旧放在数据层操作的好)

澳门葡京备用网址 19

澳门葡京备用网址 20

澳门葡京备用网址 21澳门葡京备用网址 22

 

 

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;    }}

方今项目还在搭建中,假如有趣味的校友也欢迎参与进去,代码详见Github下OSS.Core

此时此刻项目还在搭建中,假诺有趣味的同学也欢迎参加进去,代码详见Github下OSS.Core

View Code

 

 

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

=============================

=============================

澳门葡京备用网址 23澳门葡京备用网址 24

一旦您还有别的标题,欢迎关切群众号(OSSCoder)

只要您还有别的标题,欢迎关心群众号(OSSCoder)

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);    }}

澳门葡京备用网址 25

澳门葡京备用网址 26

View Code

RepositoryServiceBase.cs(IDataServiceRepository的完结类)

澳门葡京备用网址 27澳门葡京备用网址 28

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的贯彻类)

澳门葡京备用网址 29澳门葡京备用网址 30

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修改的小地点还蛮多的,下图是3个代码相比较的截图。所以一会把代码打包贴上来吧。

澳门葡京备用网址 31

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

下边就能够在 自个儿业务层继承HY.O奥迪Q5M中的RepositoryServiceBase类
,数据层继承HY.O安德拉M中的 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<德姆oEntity>(“select * from 德姆o
where ID=@ID”, new { ID = 1 }); 那样的语法了。

现实的选取方发

下图是自家要介绍完结的花色截图:

澳门葡京备用网址 32

实际上也是三层,只是名字分裂等而已。

HY.Web

HY.Web.Iservice

HY.Web.Service(服务层,HY.Web.Iservice的落到实处类,
你也得以通晓为作业逻辑层BLL)

HY.Web.DAO(数据访问层, 你也得以清楚为DAL)

HY.Web.Entity(实体层, 近来只定义了数额实体,
倘诺你的种类须要给app提供数据,
那么传输的数码要切中要害,就要求单独定义DTO了。 )

就那用户表来做个实例吧,表结构如下:(下图是用代码生成器截图效果,能够直接改动数据库的描述新闻,开发利器。供给的敌人点那里【CodeBuilder-RazorEngine】)

澳门葡京备用网址 33

HY.Web.Entity

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

澳门葡京备用网址 34View
Code

HY.Web.DAO

概念基类 BaseRepository.cs (能够设置默许的DBsession,方便增加别的东东)

澳门葡京备用网址 35View
Code

定义数据访问层 Sys_UsersRepository.cs (代码里能够打包任何索要写sql
的代码)

澳门葡京备用网址 36View
Code

HY.Web.IService

概念接口 ISys_UsersService.cs ,提供给UI访问。

澳门葡京备用网址 37View
Code

HY.Web.Service

定义BaseService.cs,(能够安装默许的DBsession,方便扩充其它东东)

澳门葡京备用网址 38View
Code

定义Sys_UsersService.cs, 去实现ISys_UsersService。

澳门葡京备用网址 39View
Code

HY.Web

壹 、定义相关的Controller

2、ISys_UsersService iSys_UsersService = new Sys_Users瑟维斯();
(那块其实能够动用 IoC, 相关内容且听后续分解)

三 、调用接口

澳门葡京备用网址 40View
Code

**下载:**

HY.DataAccess

澳门葡京备用网址 ,修改后的DapperExtensions:Dapperextensions.RAR

ps:已经更新版本了, 出席了对lambda的壮大,点击那里进入

连锁小说:

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

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

搭建一套本身实用的.net架构【OWranglerM-Dapper+DapperExtensions】

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

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

初稿链接:

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website