.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包
增加以下类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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; } } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); } } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using System; using System.Collections.Generic; using System.Text; namespace CommonHelper { /// <summary> /// 数据库类型定义 /// </summary> public enum DatabaseType { SqlServer, //SQLServer数据库 MySql, //Mysql数据库 Oracle, //Oracle数据库 Sqlite, //SQLite数据库 } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); } } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
引用类库项目后调用方式如下:
在配置文件appsettings.json 中增加
"Connections": { "DefaultConnect": "Data Source=192.168.1.xxx/orcl;User ID=xxx;Password=xxxx;" }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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文件 并设置始终复制 或者较新则复制
文件参考如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<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"_Exception.log"" /> <param name="RollingStyle" value="Date" /> <layout type="log4net.Layout.PatternLayout"> <param name="ConversionPattern" value="<HR COLOR=red>%n异常时间:%d [%t] <BR>%n异常级别:%-5p <BR>%n异 常 类:%c [%x] <BR>%n%m <BR>%n <HR Size=1>" /> </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"_Normal.log"" /> <param name="RollingStyle" value="Date" /> <layout type="log4net.Layout.PatternLayout"> <param name="ConversionPattern" value="<HR COLOR=blue>%n日志时间:%d [%t] <BR>%n日志级别:%-5p <BR>%n日 志 类:%c [%x] <BR>%n%m <BR>%n <HR Size=1>" /> </layout> </appender> </log4net>
使用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 如下(简易版本 注释部分内容为 继承基类的配置 如需要继承基类 去除注释部分代码即可)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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(@"\bin"))&&!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; } } }