让EFCore更疯狂些的扩展类库(一):通过json文件配置sql语句
前言
EF通过linq和各种扩展方法,再加上实体模型,编写数据库的访问代码确实是优美、舒服,但是生成的sql不尽如意、性能低下,尤其是复杂些的逻辑关系,最终大家还是会回归自然,选择能够友好执行sql语句的ORM,认认真真的编写sql;问题是:EF是否也能够很友好的执行sql语句?EF提供直接执行sql语句的方法并不多,而且也是极其简单的;那是否容易进行扩展?答案是肯定的,在DbContext下提供了Database属性就是为了执行sql用的,然后自己就通过Database下的方法属性进行了扩展(不过最后为了各种数据库的兼容性,使用了DbContext的扩展方法GetService获取相应的服务进行sql语句的执行),以完成这个扩展类库的编写。
扩展类库大体功能简介:
1) sql语句执行器:用于直接执行sql语句
2) EF的查询缓存器:IQueryable(linq) 或 sql语句 的查询缓存,分为本地存储 或 非本地存储(Redis)
a) 缓存存储:永久缓存(不过期) 或者 过期缓存
b) 缓存清理
3) sql配置管理器(让EFCore像MyBatis配置sql,但是通过json配置):加载与管理配置文件中的sql语句
a) sql配置执行器:用于执行配置的sql语句
b) 策略管理器:用于管理策略 与 策略执行器(目前分为三种策略执行器)
i. 策略管理:管理各种策略类型,用于初始化配置文件中的策略配置转换成对象
ii. 策略执行器(一般通过策略对象进行相应的处理)
1. 初始化型的策略执行器
a) 配置策略对象的初始化、替换表名、合并分部sql等的策略执行器
2. sql执行前的策略执行器
a) foreach策略执行器:对SqlParameter或者某些数据类型(list/dictionary/model)进行遍历生成字串替换到sql中
3. sql执行时的策略执行器
a) sql与参数的日志记录策略执行器
b) 查询缓存与清理策略执行器
4) 类库的扩展与优化(因为类库中的各种类是通过DI进行管理的,因此易于扩展与优化)
a) 将查询缓存存储到Redis中
b) 策略与策略执行器的扩展
c) 其他:例如反射帮助类的优化(如果有更好的实现,因为类库内部有不少实现需要通过反射)
源码:
github:https://github.com/skigs/EFCoreExtend
引用类库:
nuget:https://www.nuget.org/packages/EFCoreExtend/
PM> Install-Package EFCoreExtend
查询缓存引用Redis:
PM> Install-Package EFCoreExtend.Redis
类库的使用说明会分好几篇文章进行详细描述,也可参考源码(源码中也有使用测试),类库目前仅支持EFCore 1.1.0,兼容性:MSSqlServer、sqlite、mysql、PostgreSql基本都兼容(EFCore兼容的应该都可以兼容),因为刚完成不久,可能还存在一些bug或不合理的地方,望大家谅解,也请告知。
通过json文件配置sql
Person.json配置文件内容:
{ //"name" : "Person", //设置表名,如果不指定name,那么默认文件名为表名 //配置sql:key为Sql的名称(SqlName,获取配置sql执行器的时候需要根据key获取) "sqls": { "GetList": { //"sql": "select name,birthday,addrid from [Person] where name=@name or id=@id", "sql": "select name,birthday,addrid from ##tname where name=@name or id=@id", //##tname => 表名 "type": "query" } } }
表的实体模型:
[Table(nameof(Person))] public class Person { public int id { get; set; } public string name { get; set; } [Column(TypeName = "datetime")] public DateTime? birthday { get; set; } public int? addrid { get; set; } }
DbContext(MSSqlServer、sqlite、mysql、PostgreSql):
public class MSSqlDBContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (optionsBuilder.IsConfigured == false) { optionsBuilder.UseSqlServer(@"data source=localhost;initial catalog=TestDB;uid=sa;pwd=123;"); } base.OnConfiguring(optionsBuilder); } public DbSet<Person> Person { get; set; } } public class SqlieteDBContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (optionsBuilder.IsConfigured == false) { optionsBuilder.UseSqlite(@"data source=./Datas/db.sqlite"); //把/Datas/db.sqlite放到bin下 } base.OnConfiguring(optionsBuilder); } public DbSet<Person> Person { get; set; } } public class MysqlDBContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (optionsBuilder.IsConfigured == false) { //SapientGuardian.EntityFrameworkCore.MySql optionsBuilder.UseMySQL(@"Data Source=localhost;port=3306;Initial Catalog=testdb;user id=root;password=123456;"); } base.OnConfiguring(optionsBuilder); } public DbSet<Person> Person { get; set; } } public class PostgreSqlDBContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (optionsBuilder.IsConfigured == false) { optionsBuilder.UseNpgsql(@"User ID=admin;Password=123456;Host=localhost;Port=5432;Database=TestDB;Pooling=true;"); } base.OnConfiguring(optionsBuilder); } public DbSet<Person> Person { get; set; } }
加载配置文件(在程序初始化的时候调用):
////加载指定的配置文件 //EFHelper.Services.SqlConfigMgr.Config.LoadFile(Directory.GetCurrentDirectory() + "/Person.json"); //加载指定目录下的所有json配置文件 EFHelper.Services.SqlConfigMgr.Config.LoadDirectory(Directory.GetCurrentDirectory() + "/Datas");
获取与调用配置sql的代码:
DbContext db = new MSSqlDBContext(); //获取指定表(配置文件名)的配置信息 var tinfo = db.GetConfigTable<Person>(); //获取指定sql的执行器 var exc = tinfo.GetExecutor(); //使用了CallerMemberNameAttribute,因此会自动获取 方法/属性名 作为参数 var exc1 = tinfo.GetExecutor("GetList"); //这行和上面的一样,"GetList"为在配置文件配置的key //执行sql: //方式一:使用SqlParameter传递sql参数 var rtn1 = exc.Query<Person>( //泛型为返回值数据类型 //SqlParams ) }, //返回值类型中需要忽略的属性 new[] { "id" }); //select name,birthday,addrid,并没有加载获取id,因此需要忽略,否则抛异常 //方式二:使用Dictionary传递sql参数 var rtn2 = exc.QueryUseDict<Person>( //泛型为返回值数据类型 //Dictionary => SqlParams new Dictionary<string, object> { { "name", "tom" }, { }, }, //返回值类型中需要忽略的属性 new[] { "id" }); //select name,birthday,addrid,并没有加载获取id,因此需要忽略,否则抛异常 //方式三:使用Model传递sql参数 var rtn3 = exc.QueryUseModel<Person>( //Model => SqlParams , addrid = }, //参数Model需要忽略的属性 new[] { "addrid" }, //where name=@name or id=@id,并不需要设置addrid //返回值类型中需要忽略的属性 new[] { "id" }); //select name,birthday,addrid,并没有加载获取id,因此需要忽略,否则抛异常
增删改查sql语句配置内容:
{ //"name" : "Person", //设置表名,如果不指定name,那么默认文件名为表名 "policies": { ////表名策略 //"tname": { // //"tag": "##tname" //默认值为 ##tname // "prefix": "[", //前缀 // "suffix": "]" //后缀 //} }, //配置sql:key为Sql的名称(SqlName,获取配置sql执行器的时候需要根据key获取) "sqls": { "GetList": { //"sql": "select * from [Person] where name=@name", "sql": "select * from ##tname where name=@name", //##tname => Table Name "type": "query" //可以不设置,如果设置了会在执行前进行类型检测, // notsure(默认,不确定),query(查询), nonquery(非查询),scalar,nonexecute(不用于执行的sql,例如分部sql) }, "GetPerson": { "sql": "select * from ##tname where name=@name", "type": "query" }, "Count": { "sql": "select count(*) from ##tname", "type": "scalar" }, "UpdatePerson": { "sql": "update ##tname set birthday=@birthday, addrid=@addrid where name=@name", "type": "nonquery" }, "AddPerson": { "sql": "insert into ##tname(name, birthday, addrid) values(@name, @birthday, @addrid) ", "type": "nonquery" }, "DeletePerson": { "sql": "delete from ##tname where name=@name", "type": "nonquery" }, //执行存储过程 "ProcQuery": { "sql": "exec TestQuery @name", "type": "query" }, "ProcUpdate": { "sql": "exec TestUpdate @addrid,@name", "type": "nonquery" } } }
调用sql配置的代码(包括事物处理):
public class PersonBLL { string _name = "tom"; DBConfigTable tinfo; public PersonBLL(DbContext db) { //获取指定表(配置文件名)的配置信息 tinfo = db.GetConfigTable<Person>(); } public IReadOnlyList<Person> GetList() { return tinfo.GetExecutor().QueryUseModel<Person>( //Model => SqlParams }, //不需要的SqlParams new[] { "id" }, //返回值类型需要忽略的属性 new[] { "name" }); } public int AddPerson() { return tinfo.GetExecutor() //获取sql执行器 .NonQueryUseModel(new Person { addrid = , birthday = DateTime.Now, name = _name, }, null); } public int UpdatePerson(int? addrid = null) { var exc = tinfo.GetExecutor(); return exc.NonQueryUseModel(new { name = _name, birthday = DateTime.Now, addrid = addrid }, null); } public int DeletePerson() { return tinfo.GetExecutor().NonQueryUseModel(new { name = _name }, null); } public int Count() { var exc = tinfo.GetExecutor(); var rtn = exc.ScalarUseModel(new { name = _name }, null); //MSSqlServer返回值会为int,而Sqlite会为long,转换就会出错,因此需要ChangeValueType return (int)typeof(int).ChangeValueType(rtn); } public Person GetPerson() { return tinfo.GetExecutor().QueryUseModel<Person>(new { name = _name }, null)?.FirstOrDefault(); } //执行存储过程 public IReadOnlyList<Person> ProcQuery() { ////Stored procedure sql: //create proc TestQuery //@name varchar(256) = null //as //begin // select * from person where [name] = @name //end return tinfo.GetExecutor().QueryUseModel<Person>(new { name = "tom" }, null); } //执行存储过程 public int ProcUpdate() { ////Stored procedure sql: //create proc TestUpdate //@addrid int = 0, //@name varchar(256) //as //begin // update person set addrid = @addrid where[name] = @name //end , name = "tom" }, null); } //事物 public void DoTran() { try { //开启事物 tinfo.DB.Database.BeginTransaction(); ; bRtn &= AddPerson() > ; if (bRtn) { tinfo.DB.Database.CommitTransaction(); //提交 } else { tinfo.DB.Database.RollbackTransaction(); //回滚 } } catch (Exception ex) { tinfo.DB.Database.RollbackTransaction(); //回滚 } } }
通过代码设置sql配置:
配置sql除了通过配置文件之外 还可以通过代码进行配置的:
public void AddSqls() { EFHelper.Services.SqlConfigMgr.Config.AddSqls<Person>(new Dictionary<string, IConfigSqlInfo> { { "UpdatePerson", //SqlName new ConfigSqlInfo { Sql = $"update {nameof(Person)} set name=@name where id=@id", Type = ConfigSqlExecuteType.nonquery, } }, { "GetPersonList", //SqlName new ConfigSqlInfo { Sql = $"select * from {nameof(Person)} id=@id", Type = ConfigSqlExecuteType.query, } } }); } public void AddTables() { EFHelper.Services.SqlConfigMgr.Config.AddOrCombine(new[] { new ConfigTableInfo { Name = nameof(Person), //表名 Sqls = new Dictionary<string, IConfigSqlInfo> { { "UpdatePerson", new ConfigSqlInfo { Sql = $"update {nameof(Person)} set name=@name where id=@id", Type = ConfigSqlExecuteType.nonquery, } }, { "GetPersonList", new ConfigSqlInfo { Sql = $"select * from {nameof(Person)} id=@id", Type = ConfigSqlExecuteType.query, } } } }, new ConfigTableInfo { Name = nameof(Address), //表名 Sqls = new Dictionary<string, IConfigSqlInfo> { { "UpdateAddress", new ConfigSqlInfo { Sql = $"update {nameof(Address)} set fullAddress=@fullAddress where id=@id", Type = ConfigSqlExecuteType.nonquery, } }, { "GetAddressList", new ConfigSqlInfo { Sql = $"select * from {nameof(Address)} id=@id", Type = ConfigSqlExecuteType.query, } } } }, }); }
直接执行sql语句的方法:
如果不想通过配置文件配置sql,而是直接执行sql语句,那么:
DbContext db = new MSSqlDBContext(); var nRtn = db .NonQueryUseModel($"insert into {nameof(Person)}(name, birthday, addrid) values(@name, @birthday, @addrid)", //可以使用SqlParameter / Dictionary作为sql的参数(使用Model对象时通过反射转换成SqlParameter的,因此性能会慢些) new Person { name = "tom1", birthday = DateTime.Now, addrid = , }, //参数Model需要忽略的属性 new[] { "id" }); Assert.True(nRtn > ); var qRtn = db .QueryUseModel<Person>($"select name, birthday, addrid from {nameof(Person)} where name=@name", new { name = "tom1" }, //参数Model需要忽略的属性(这里设置为null) null, //返回值类型中需要忽略的属性 new[] { "id" }); Assert.True(qRtn?.Count > ); var sRtn = db.ScalarUseModel($"select count(id) from {nameof(Person)} where name=@name", new { name = "tom1" }, null); Assert.True((); var nRtn1 = db.NonQueryUseDict($"delete from {nameof(Person)} where name=@name", new Dictionary<string, object> { {"name", "tom1"} }); Assert.True(nRtn1 > );
执行sql语句的源码:
类库中是如何通过DbContext执行sql语句的,部分源码如下(更详细的可参考github中的源码):
public IReadOnlyList<T> Query<T>(DbContext db, string sql, IDataParameter[] parameters = null, IReadOnlyCollection<string> ignoreProptNames = null) where T : new() { var concurrencyDetector = db.GetService<IConcurrencyDetector>(); using (concurrencyDetector.EnterCriticalSection()) { var reader = GetReader(db, sql, parameters); var rtnList = new List<T>(); T model; object val; using (reader.DbDataReader) { var propts = _objReflec.GetPublicInstancePropts(typeof(T), ignoreProptNames); while (reader.DbDataReader.Read()) { model = new T(); foreach (var l in propts) { val = reader.DbDataReader[l.Name]; val = ChangeType(l.PropertyType, val); l.SetValue(model, val); } rtnList.Add(model); } } return rtnList; } } /// <summary> /// 值的类型转换 /// </summary> protected abstract object ChangeType(Type proptType, object val); protected RelationalDataReader GetReader(DbContext db, string sql, IDataParameter[] parameters) { ) { //带参数的 var cmd = db.GetService<IRawSqlCommandBuilder>() .Build(sql, parameters); return cmd .RelationalCommand .ExecuteReader( db.GetService<IRelationalConnection>(), parameterValues: cmd.ParameterValues); } else { //不带参数的 var cmd = db.GetService<IRawSqlCommandBuilder>() .Build(sql); return cmd .ExecuteReader(db.GetService<IRelationalConnection>()); } }
未完待续...
最新文章
- myeclipse+tomcat 工程名改名的问题 ——————完美解决方案
- bzoj1741 [Usaco2005 nov]Asteroids 穿越小行星群
- Linux第三次学习笔记
- 10 steps to becoming the developer everyone wants
- [BZOJ2423][HAOI2010]最长公共子序列
- 2016年12月13日 星期二 --出埃及记 Exodus 21:8
- 在其他页面调用 Discuz 7.2 BBS 论坛会员登录信息
- 真假云主机,VPS资料集合
- 获取js提交数据
- 获取其它进程窗口中的状态栏信息(FindWindowEx GetWindowThreadProcessId OpenProcess SendMessage轮番轰炸)
- css考核点整理(六)-水平居中定位的几种方式
- Mobile Matrices
- SQL SERVER存储过程生成字母+数字的编码
- Lucene全文检索技术
- 201521123098 《Java程序设计》第8周学习总结
- php多个文件上传
- 二维码开源库ZBar-windows下编译和使用
- 120. Triangle(中等)
- MVC详解:mvc是什么?为什么要用MVC?MVC工作原理以及MVC优缺点
- Go的sort接口实现