.net core 中简单封装Dapper.Extensions 并使用sqlsuger自动生成实体类

引言

由公司需要使用dapper 同时支持多数据库又需要支持实体类又需要支持sql 还需要支持事务所以采用了 dapper + dapperExtensions 并配套生成实体类小工具的方式

环境准备

dapper环境

nuget中下载安装 Dapper， DapperExtensions.NetCore

数据驱动准备

orcale: Oracle.ManagedDataAccess.Core

SQLite: System.Data.SQLite.Core

日志组件

log4net

Dapper 实体操作简单使用

 var orcalConn = new OracleConnection(strConn);
                    var orcaleconfig = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new OracleDialect());
                    var orcaleGenerator = new SqlGeneratorImpl(orcaleconfig);
                    connection = new Database(orcalConn, orcaleGenerator);

注：数据库不同时改变这段内容即可

插入数据使用方式举例：

 Connection.Insert(list, tran, commandTimeout);

针对DapperExtensions简单封装

新建core 类库项目并引用好所需nuget包

增加以下类

using DapperExtensions.Mapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;
using Dapper;
using DapperExtensions;
using System.Reflection;
using DapperExtensions.Sql;
using Oracle.ManagedDataAccess.Client;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace CommonHelper
{
    /// <summary>
    /// 数据库连接辅助类
    /// </summary>
    public class ConnectionFactory
    {
        /// <summary>
        /// 转换数据库类型
        /// </summary>
        /// <param name="databaseType">数据库类型</param>
        /// <returns></returns>
        public static DatabaseType GetDataBaseType(string databaseType)
        {
            DatabaseType returnValue = DatabaseType.SqlServer;
            foreach (DatabaseType dbType in Enum.GetValues(typeof(DatabaseType)))
            {
                if (dbType.ToString().Equals(databaseType, StringComparison.OrdinalIgnoreCase))
                {
                    returnValue = dbType;
                    break;
                }
            }
            return returnValue;
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        public static Database CreateConnection(string strConn, DatabaseType databaseType = DatabaseType.Oracle)
        {
            Database connection = null;
            //获取配置进行转换
            switch (databaseType)
            {
                case DatabaseType.SqlServer:
                    var sqlConn = new SqlConnection(strConn);
                    var sqlconfig = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new SqlServerDialect());
                    var sqlGenerator = new SqlGeneratorImpl(sqlconfig);
                    connection = new Database(sqlConn, sqlGenerator);
                    break;
                case DatabaseType.MySql:
                    var mysqlConn = new MySqlConnection(strConn);
                    var mysqlconfig = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new MySqlDialect());
                    var mysqlGenerator = new SqlGeneratorImpl(mysqlconfig);
                    connection = new Database(mysqlConn, mysqlGenerator);
                    break;
                case DatabaseType.Sqlite:
                    var sqlliteConn = new SQLiteConnection(strConn);
                    var sqlliteconfig = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new SqliteDialect());
                    var sqlliteGenerator = new SqlGeneratorImpl(sqlliteconfig);
                    connection = new Database(sqlliteConn, sqlliteGenerator);
                    break;
                case DatabaseType.Oracle:
                    var orcalConn = new OracleConnection(strConn);
                    var orcaleconfig = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new OracleDialect());
                    var orcaleGenerator = new SqlGeneratorImpl(orcaleconfig);
                    connection = new Database(orcalConn, orcaleGenerator);
                    break;
            }
            return connection;
        }
    }
}

ConnectionFactory.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;
using Microsoft.Extensions.Configuration;

namespace CommonHelper
{
    /// <summary>
    /// dapper 帮助类
    /// </summary>
    public class DapperHelper : IDapperHelper, IDisposable
    {
        private string ConnectionString = string.Empty;
        private Database Connection = null;
        /// <summary>
        /// 初始化 若不传则默认从appsettings.json读取Connections:DefaultConnect节点
        /// 传入setting:xxx:xxx形式 则会从指定的配置文件中读取内容
        /// 直接传入连接串则
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="jsonConfigFileName"> 配置文件名称</param>
        public DapperHelper(string conn = "", string jsonConfigFileName = "appsettings.json", DatabaseType databaseType = DatabaseType.Oracle)
        {
            var config = new ConfigurationBuilder()
  .SetBasePath(Directory.GetCurrentDirectory())
  .AddJsonFile(jsonConfigFileName, optional: true)
  .Build();
            if (string.IsNullOrEmpty(conn))
            {
                conn = config.GetSection("Connections:DefaultConnect").Value;
            }
            else if (conn.StartsWith("setting:"))
            {
                conn = config.GetSection(conn.Substring(8)).Value;
            }
            ConnectionString = conn;
            Connection = ConnectionFactory.CreateConnection(ConnectionString, databaseType);
        }
        public Database GetConnection()
        {
            return Connection;
        }
        public IDbTransaction TranStart()
        {
            if (Connection.Connection.State == ConnectionState.Closed)
                Connection.Connection.Open();
            return Connection.Connection.BeginTransaction();
        }
        public void TranRollBack(IDbTransaction tran)
        {
            tran.Rollback();
            if (Connection.Connection.State == ConnectionState.Open)
                tran.Connection.Close();
        }
        public void TranCommit(IDbTransaction tran)
        {
            tran.Commit();
            if (Connection.Connection.State == ConnectionState.Open)
                tran.Connection.Close();
        }

        public bool Delete<T>(T obj, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {

            return Connection.Delete(obj, tran, commandTimeout);
        }

        public bool Delete<T>(IEnumerable<T> list, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {

            return Connection.Delete(list, tran, commandTimeout);
        }
        public void Dispose()
        {
            if (Connection != null)
            {
                Connection.Dispose();
            }
        }
        public T Get<T>(string id, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {
            return Connection.Get<T>(id, tran, commandTimeout);
        }

        public IEnumerable<T> GetAll<T>(object predicate = null, IList<ISort> sort = null, IDbTransaction tran = null, int? commandTimeout = null, bool buffered = true) where T : class
        {
            return Connection.GetList<T>(predicate, sort, tran, commandTimeout, buffered);
        }
        public IEnumerable<T> GetPage<T>(object predicate, IList<ISort> sort, int page, int pagesize, IDbTransaction tran = null, int? commandTimeout = null, bool buffered = true) where T : class
        {
            return Connection.GetPage<T>(predicate, sort, page, pagesize, tran, commandTimeout, buffered);
        }
        public dynamic Insert<T>(T obj, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {
            return Connection.Insert(obj, tran, commandTimeout);
        }

        public void Insert<T>(IEnumerable<T> list, IDbTransaction tran = null, int? commandTimeout = null) where T : class
        {
            Connection.Insert(list, tran, commandTimeout);
        }
        public bool Update<T>(T obj, IDbTransaction tran = null, int? commandTimeout = null, bool ignoreAllKeyProperties = true) where T : class
        {
            return Connection.Update(obj, tran, commandTimeout, ignoreAllKeyProperties);
        }

        public bool Update<T>(IEnumerable<T> list, IDbTransaction tran = null, int? commandTimeout = null, bool ignoreAllKeyProperties = true) where T : class
        {
            return Connection.Update(list, tran, commandTimeout, ignoreAllKeyProperties);
        }
        public List<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Connection.Connection.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).AsList();
        }
        public int Execute<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Connection.Connection.Execute(sql, param, transaction, commandTimeout, commandType);
        }
    }
}

DapperHelper.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace CommonHelper
{
    /// <summary>
    /// 数据库类型定义
    /// </summary>
    public enum DatabaseType
    {
        SqlServer,  //SQLServer数据库
        MySql,      //Mysql数据库
        Oracle,     //Oracle数据库
        Sqlite,     //SQLite数据库
    }
}

DatabaseType.cs

using DapperExtensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace CommonHelper
{
    public interface IDapperHelper
    {
        Database GetConnection();
        T Get<T>(string id, IDbTransaction tran = null, int? commandTimeout = null) where T : class;
        IEnumerable<T> GetAll<T>(object predicate = null, IList<ISort> sort = null, IDbTransaction tran = null, int? commandTimeout = null, bool buffered = true) where T : class;
        IEnumerable<T> GetPage<T>(object predicate, IList<ISort> sort, int page, int pagesize, IDbTransaction tran = null, int? commandTimeout = null, bool buffered = true) where T : class;
        dynamic Insert<T>(T obj,IDbTransaction tran = null, int? commandTimeout = null) where T : class;
        void Insert<T>(IEnumerable<T> list, IDbTransaction tran = null, int? commandTimeout = null) where T : class;
        bool Update<T>(T obj, IDbTransaction tran = null, int? commandTimeout = null, bool ignoreAllKeyProperties = false) where T : class;
        bool Update<T>(IEnumerable<T> list, IDbTransaction tran = null, int? commandTimeout = null, bool ignoreAllKeyProperties = false) where T : class;
        bool Delete<T>(T obj, IDbTransaction tran = null, int? commandTimeout = null) where T : class;
        bool Delete<T>(IEnumerable<T> list, IDbTransaction tran = null, int? commandTimeout = null) where T : class;
        IDbTransaction TranStart();
        void TranRollBack(IDbTransaction tran);
        void TranCommit(IDbTransaction tran);
        List<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);
        int Execute<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);
    }
}

IDapperHelper.cs

using log4net;
using log4net.Config;
using log4net.Repository;
using System;
using System.IO;
//[assembly: log4net.Config.XmlConfigurator(ConfigFile = "log4net.config", ConfigFileExtension = "config", Watch = true)]
namespace CommonHelper
{
    public static class LogHelper
    {
        private static ILoggerRepository repository = LogManager.CreateRepository("ApiLogs");
        static LogHelper()
        {
            XmlConfigurator.Configure(repository, new FileInfo("log4net.config"));
        }
        private static ILog log = LogManager.GetLogger(repository.Name, "LogHelper");
        private static ILog log_Normal = LogManager.GetLogger(repository.Name, "LogHelperNormal");
        public static void Write(string msg, LogLev lev)
        {
            switch (lev)
            {
                case LogLev.Debug:
                    log_Normal.Debug(msg);
                    break;
                case LogLev.Error:
                    log.Error(msg);
                    break;
                case LogLev.Fatal:
                    log.Fatal(msg);
                    break;
                case LogLev.Info:
                    log_Normal.Info(msg);
                    break;
                case LogLev.Warn:
                    log_Normal.Warn(msg);
                    break;
                default:
                    break;
            }
        }
        public static void Write(string msg, LogLev lev, params object[] parm)
        {
            switch (lev)
            {
                case LogLev.Debug:
                    log_Normal.DebugFormat(msg, parm);
                    break;
                case LogLev.Error:
                    log.ErrorFormat(msg, parm);
                    break;
                case LogLev.Fatal:
                    log.FatalFormat(msg, parm);
                    break;
                case LogLev.Info:
                    log_Normal.InfoFormat(msg, parm);
                    break;
                case LogLev.Warn:
                    log_Normal.WarnFormat(msg, parm);
                    break;
                default:
                    break;
            }
        }
        public static void Write(Exception ex, LogLev lev)
        {
            switch (lev)
            {
                case LogLev.Debug:
                    log_Normal.Debug(ex);
                    break;
                case LogLev.Error:
                    log.Error(ex);
                    break;
                case LogLev.Fatal:
                    log.Fatal(ex);
                    break;
                case LogLev.Info:
                    log_Normal.Info(ex);
                    break;
                case LogLev.Warn:
                    log_Normal.Warn(ex);
                    break;
                default:
                    break;
            }
        }
        public static void Log(Exception ex)
        {
            Write("方法:{0} 消息:{1} 类:{2} 堆:{3} ", LogLev.Fatal, ex.TargetSite, ex.Message,ex.Source, ex.StackTrace);
        }
        public static void Log(Exception ex,int fmodelid)
        {
            Write("方法:{0} 消息:{1} 类:{2} 堆:{3} fmodelid:{4}", LogLev.Fatal, ex.TargetSite, ex.Message, ex.Source, ex.StackTrace,fmodelid);
        }
    }
}

LogHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using log4net;

namespace CommonHelper
{
    public enum LogLev
    {
        Debug,
        Error,
        Fatal,
        Info,
        Warn
    }
}

LogLev.cs

引用类库项目后调用方式如下：

在配置文件appsettings.json 中增加

 "Connections": {
    "DefaultConnect": "Data Source=192.168.1.xxx/orcl;User ID=xxx;Password=xxxx;"
  }

using CommonHelper;
using DapperExtensions;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Linq;
using System.Threading.Tasks;

namespace DapperHelperTest
{
    [TestClass]
    public class UnitTest1
    {
        /// <summary>
        /// 基本测试  详细参考https://github.com/tmsmith/Dapper-Extensions/blob/master/DapperExtensions.Test/IntegrationTests/Oracle/CrudFixture.cs
        /// </summary>
        [TestMethod]
        public void TestMethod1()
        {
            using (var dp = new DapperHelper())
            {
                var obj = new USER() { FID = "test222", FNAME = "test", FCREATETIME = DateTime.Now, FREALNAME = "t" };
                //增
                var insert = dp.Insert(obj);
                Assert.IsTrue(insert == "test222");
                obj.FNAME = "test2";
                //改
                var update = dp.Update(obj);
                Assert.IsTrue(update);
                //取所有带条件
                var predicate = Predicates.Field<USER>(f => f.FNAME, Operator.Eq, "test2");
                var allrecords = dp.GetAll<USER>(predicate);
                Assert.IsTrue(allrecords.Count() > 0);
                //取
                var user2 = dp.Get<USER>("test222");
                Assert.IsTrue(user2 != null);
                //删
                bool isdel = dp.Delete(obj);
                Assert.IsTrue(isdel);
            }
        }
        /// <summary>
        /// 测试事务
        /// </summary>
        [TestMethod]
        public void TestMethod2()
        {
            using (var dp = new DapperHelper())
            {
                var tran = dp.TranStart();
                var obj = new USER() { FID = "test222", FNAME = "test", FCREATETIME = DateTime.Now, FREALNAME = "t" };
                //事务回滚
                var insert = dp.Insert(obj, tran);
                var user2 = dp.Get<USER>("test222", tran);
                Assert.IsTrue(user2 != null);
                tran.Rollback();
                var user3 = dp.Get<USER>("test222");
                Assert.IsTrue(user3 == null);
                //事务提交
                tran = dp.TranStart();
                insert = dp.Insert(obj, tran);
                Assert.IsTrue(user2 != null);
                tran.Commit();
                user3 = dp.Get<USER>("test222");
                Assert.IsTrue(user3 != null);
                //删除测试数据
                bool isdel = dp.Delete(obj);
                Assert.IsTrue(isdel);
            }
        }
        /// <summary>
        /// 测试sql
        /// </summary>
        [TestMethod]
        public void TestMethod3()
        {
            using (var dp = new DapperHelper())
            {
                var tt = dp.Query<USER>("select * from USER");
            }
        }

    }
}

单元测试示例

注：需要在引用的项目中增加log4net.config文件并设置始终复制或者较新则复制

文件参考如下：

<log4net>
  <logger name="LogHelper">
    <level value="ALL" />
    <appender-ref ref="Appender" />
  </logger>
  <logger name="LogHelperNormal">
    <level value="ALL" />
    <appender-ref ref="NormalAppender" />
  </logger>
  <appender name="Appender" type="log4net.Appender.RollingFileAppender">
    <!--日志文件名开头-->
    <param name="File" value="Log\" />
    <!--是否追加到文件,默认为true，通常无需设置-->
    <param name="AppendToFile" value="true" />
    <param name="MaxSizeRollBackups" value="100" />
    <param name="MaxFileSize" value="10240" />
    <param name="StaticLogFileName" value="false" />
    <!--日期的格式，每天换一个文件记录，如不设置则永远只记录一天的日志，需设置-->
    <param name="DatePattern" value="yyyyMMdd&quot;_Exception.log&quot;" />
    <param name="RollingStyle" value="Date" />
    <layout type="log4net.Layout.PatternLayout">
      <param name="ConversionPattern" value="&lt;HR COLOR=red&gt;%n异常时间：%d [%t] &lt;BR&gt;%n异常级别：%-5p &lt;BR&gt;%n异 常 类：%c [%x] &lt;BR&gt;%n%m &lt;BR&gt;%n &lt;HR Size=1&gt;"  />
    </layout>
  </appender>
  <appender name="NormalAppender" type="log4net.Appender.RollingFileAppender">
    <param name="File" value="Log\" />
    <param name="AppendToFile" value="true" />
    <param name="MaxFileSize" value="10240" />
    <param name="MaxSizeRollBackups" value="100" />
    <param name="StaticLogFileName" value="false" />
    <param name="DatePattern" value="yyyyMMdd&quot;_Normal.log&quot;" />
    <param name="RollingStyle" value="Date" />
    <layout type="log4net.Layout.PatternLayout">
      <param name="ConversionPattern" value="&lt;HR COLOR=blue&gt;%n日志时间：%d [%t] &lt;BR&gt;%n日志级别：%-5p &lt;BR&gt;%n日 志 类：%c [%x] &lt;BR&gt;%n%m &lt;BR&gt;%n &lt;HR Size=1&gt;"  />
    </layout>
  </appender>
</log4net>

log4net.config

使用sqlsuger 打造简易实体类生成工具

新建core 控制台程序

添加appsettings.json

{
  "Connections": {
    "DefaultConnect": "Data Source=192.168.1.xxx/orcl;User ID=xxx;Password=xxx;"
  },
  "Settings": {
    "NameSpace": "DBModels",//命名空间
    "RelativePath": "AuthService/Models",//相对路径地址
    "FullPath": "",//全路径地址 填写以后 相对路径地址失效
    "GenerateTables": "USER" //需要生成的表名 不填默认生成全部的表
  }
}

改变Program.cs 如下（简易版本注释部分内容为继承基类的配置如需要继承基类去除注释部分代码即可）

using Microsoft.Extensions.Configuration;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace ModelGeneration
{
    /// <summary>
    /// 使用sqlsuger 自动生成实体类
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            var config = new ConfigurationBuilder()
 .SetBasePath(Directory.GetCurrentDirectory())
 .AddJsonFile("appsettings.json", optional: true)
 .Build();
            var conn = config.GetSection("Connections:DefaultConnect").Value;
            string path = string.Empty;
            var relativePath = config.GetSection("Settings:RelativePath").Value;
            //自动找最外层并 找到更外层 方便附加到其他项目中
            if (!string.IsNullOrEmpty(relativePath))
            {
                var basePath = new DirectoryInfo(Directory.GetCurrentDirectory());
                while ((basePath.FullName.Contains(@"Debug") || basePath.FullName.Contains(@"in"))&&!string.IsNullOrEmpty(basePath.FullName))
                {
                    basePath=basePath.Parent;
                }
                path = Path.Combine(basePath.Parent.FullName, relativePath);
            }
            var fullPath= config.GetSection("Settings:FullPath").Value;
            if (!string.IsNullOrEmpty(fullPath))
                path = fullPath;
            InitModel(conn,config.GetSection("Settings:NameSpace").Value, path, config.GetSection("Settings:GenerateTables").Value);
        }
        public static void InitModel(string conn,string namespaceStr, string path,string genaratetables)
        {
            try
            {
                Console.WriteLine("开始创建");
                var tableNames = genaratetables.Split(',').ToList();
                for (int i = 0; i < tableNames.Count; i++)
                {
                    tableNames[i] = tableNames[i].ToLower();
                }
                var suger = GetInstance(conn).DbFirst.SettingClassTemplate(old =>
                {
                    return old.Replace("{Namespace}", namespaceStr);//.Replace("class {ClassName}", "class {ClassName} :BaseEntity");//改变命名空间
                });
                if (tableNames.Count >= 0)
                {
                    suger.Where(it => tableNames.Contains(it.ToLower())).IsCreateDefaultValue();
                }
                else
                {
                    suger.IsCreateDefaultValue();
                }
                //过滤BaseEntity中存在的字段
                //var pros = typeof(BaseEntity).GetProperties();
                //var list = new List<SqlSugar.IgnoreColumn>();
                var tables = suger.ToClassStringList().Keys;
                //foreach (var item in pros)
                //{
                //    foreach (var table in tables)
                //    {
                //        list.Add(new SqlSugar.IgnoreColumn() { EntityName = table, PropertyName = item.Name });
                //    }
                //}
                //suger.Context.IgnoreColumns.AddRange(list);
                suger.CreateClassFile(path);
                Console.WriteLine("创建完成");
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        public static SqlSugarClient GetInstance(string conn)
        {
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = conn,
                DbType = DbType.Oracle,
                IsAutoCloseConnection = true,
                IsShardSameThread = true //设为true相同线程是同一个SqlSugarClient
            });
            db.Ado.IsEnableLogEvent = true;
            db.Ado.LogEventStarting = (sql, pars) =>
            {

            };
            return db;
        }
    }
}

Program.cs

相关阅读:
[BZOJ1584] [Usaco2009 Mar]Cleaning Up 打扫卫生（DP）
[BZOJ1583] [Usaco2009 Mar]Moon Mooing 哞哞叫（队列）
[BZOJ1582] [Usaco2009 Hol]Holiday Painting 节日画画（线段树）
[BZOJ1579] [Usaco2009 Feb]Revamping Trails 道路升级（分层图最短路 + 堆优化dijk）
[ZPG TEST 115] 字符串【归类思想】
[ZPG TEST 114] 阿狸的英文名【水题】
[USACO 2012 Open Gold] Bookshelf【优化dp】
[USACO 2012 Mar Silver] Landscaping【Edit Distance】
[USACO 2012 Mar Gold] Large Banner
[USACO 2012 Feb Gold] Cow Coupons【贪心堆】
原文地址：https://www.cnblogs.com/nontracey/p/9988759.html