代码改变世界....
Fast Framework 基于NET6.0 封装的轻量级 ORM 框架 支持多种数据库 SqlServer Oracle MySql PostgreSql Sqlite
优点： 体积小、可动态切换不同实现类库、原生支持微软特性、流畅API、使用简单、性能高、模型数据绑定采用Emit、强大的表达式解析、支持多种子查询可实现较为复杂查询、源代码可读性强。
-
Ado
IAdo ado = new AdoProvider(new DbOptions() { DbId = "1", DbType = DbType.MySQL, ProviderName = "MySqlConnector", FactoryName = "MySqlConnector.MySqlConnectorFactory,MySqlConnector", ConnectionStrings = "server=localhost;database=Test;user=root;pwd=123456789;port=3306;min pool size=3;max pool size=100;connect timeout=30;" });
-
DbContext 支持多租户 支持切换不同Ado实现类库 设置 ProviderName和FactoryName 即可
IDbContext db = new DbContext(new List<DbOptions>() { new DbOptions() { DbId = "1", DbType = DbType.MySQL, ProviderName = "MySqlConnector", FactoryName = "MySqlConnector.MySqlConnectorFactory,MySqlConnector", ConnectionStrings = "server=localhost;database=Test;user=root;pwd=123456789;port=3306;min pool size=3;max pool size=100;connect timeout=30;" }});
-
DbOptions Json文件配置格式
"DbOptions": [ { "DbId": 1, "DbType": "SQLServer", "ProviderName": "System.Data.SqlClient", "FactoryName": "System.Data.SqlClient.SqlClientFactory,System.Data", "ConnectionStrings": "server=localhost;database=Test;user=sa;pwd=123456789;min pool size=3;max pool size=100;connect timeout=120;" }]
-
主从分离（读写分离）配置
"DbOptions": [ { "DbId": 2, "DbType": "MySQL", "IsDefault": true, "ProviderName": "MySqlConnector", "FactoryName": "MySqlConnector.MySqlConnectorFactory,MySqlConnector", "ConnectionStrings": "server=localhost;database=Test;user=root;pwd=123456789;port=3306;min pool size=0;max pool size=100;connect timeout=120;AllowLoadLocalInfile=true;", "UseMasterSlaveSeparation": true,//使用主从分离 注意所有事务将强制走主库 "SlaveItems": [ { "Weight": 1,//注意多个从库 必须配置权重且总权重>从库数 "ConnectionStrings": "server=localhost;database=Test1;user=root;pwd=123456789;port=3306;min pool size=0;max pool size=100;connect timeout=120;AllowLoadLocalInfile=true;", "Description": "从库连接配置" }, { "Weight": 2, "ConnectionStrings": "server=localhost;database=Test2;user=root;pwd=123456789;port=3306;min pool size=0;max pool size=100;connect timeout=120;AllowLoadLocalInfile=true;", "Description": "从库连接配置" } ], "Description": "主库连接配置" } ]
-
Asp.net Core 依赖注入
// 注册服务 var builder = WebApplication.CreateBuilder(args);// 添加数据库上下文builder.Services.AddFastDbContext();// 从Json配置文件加载数据库选项builder.Services.Configure<List<DbOptions>>(builder.Configuration.GetSection("DbOptions"));// 产品服务类 通过构造方法注入public class ProductService{ /// <summary> /// 数据库 /// </summary> private readonly IDbContext db; /// <summary> /// 构造方法 /// </summary> /// <param name="db">数据库</param> public ProductService(IDbContext db) { this.db = db; }}
-
实体对象插入
var product = new Product() { ProductCode = "1001", ProductName = "测试商品1" }; var result = db.Insert(product).Exceute();
-
实体对象插入并返回自增ID 仅支持 SQLServer MySQL SQLite
var product = new Product() { ProductCode = "1001", ProductName = "测试产品1" }; var result = db.Insert(product).ExceuteReturnIdentity();
-
实体对象列表插入
var list = new List<Product>(); for (int i = 0; i < 2100; i++) { list.Add(new Product() { ProductCode = $"编号{i + 1}", ProductName = $"名称{i + 1}" }); } var result = db.Insert(list).Exceute();
-
匿名对象插入
var obj = new { ProductCode = "1001", ProductName = "测试商品1" }; //注意:需要使用As方法显示指定表名称 var result = db.Insert(obj).As("Product").Exceute();
-
匿名对象列表插入
var list = new List<object>(); for (int i = 0; i < 2100; i++) { list.Add(new { ProductCode = $"编号{i + 1}", ProductName = $"名称{i + 1}" }); } //注意:需要使用As方法显示指定表名称 var result = db.Insert(list).As("Product").Exceute();
-
字典插入
var product = new Dictionary<string, object>() { {"ProductCode","1001"}, { "ProductName","测试商品1"} }; var result = db.Insert(product).As("Product").Exceute();
-
字典列表插入
var list = new List<Dictionary<string, object>>(); for (int i = 0; i < 2100; i++) { list.Add(new Dictionary<string, object>() { {"ProductCode","1001"}, { "ProductName","测试商品1"} }); } var result = db.Insert(list).As("Product").Exceute();
-
实体对象更新
var product = new Product() { ProductId = 1, ProductCode = "1001", ProductName = "测试商品1" }; //注意:标记KeyAuttribute特性属性或使用Where条件，为了安全起见全表更新将必须使用Where方法 var result = db.Update(product).Exceute();
-
指定列更新
var result = db.Update<Product>(new Product() { ProductCode = "1001", ProductName = "1002" }).Columns(c=> new { c.ProductCode , c.ProductName }).Exceute(); // 推荐使用表达式 c=>new {} 好处更改属性名称可以同步修改
-
忽略列更新
var result = db.Update<Product>(new Product() { ProductCode = "1001", ProductName = "1002" }).IgnoreColumns(c=> new { c.Custom1 }).Exceute(); // 同上使用方法一样
-
实体对象列表更新
var list = new List<Product>(); for (int i = 0; i < 2022; i++) { list.Add(new Product() { ProductCode = $"编号{i + 1}", ProductName = $"名称{i + 1}" }); } //注意:标记KeyAuttribute特性属性或使用WhereColumns方法指定更新条件列 var result = db.Update(list).Exceute();
-
匿名对象更新
var obj = new { ProductId = 1, ProductCode = "1001", ProductName = "测试商品1" }; //注意:需要显示指定表名称 以及更新条件 使用 Where或者WhereColumns方法均可 var result = db.Update(obj).As("product").WhereColumns("ProductId").Exceute();
-
匿名对象列表更新
var list = new List<object>(); for (int i = 0; i < 2022; i++) { list.Add(new { ProductId = i + 1, ProductCode = $"编号{i + 1}", ProductName = $"名称{i + 1}" }); } //由于是匿名对象需要显示指定表名称,使用WhereColumns方法指定更新条件列 var result = db.Update(list).As("Product").WhereColumns("ProductId").Exceute();
-
字典更新
var product = new Dictionary<string, object>() { { "ProductId",1}, {"ProductCode","1001"}, { "ProductName","测试商品1"} }; var result = db.Update(product).As("Product").WhereColumns("ProductId").Exceute();
-
字典列表更新
var list = new List<Dictionary<string, object>>(); for (int i = 0; i < 2022; i++) { list.Add(new Dictionary<string, object>() { { "ProductId",i+1}, {"ProductCode",$"更新编号:{i+1}"}, { "ProductName",$"更新商品:{i + 1}"} }); } var result = db.Update(list).As("Product").WhereColumns("ProductId").Exceute();
-
设置列更新
// 设置列更新 db.Update<Product>().SetColumns(c => new Product() { ProductCode = "1001", ProductName = "测试产品1" }).Where(w => w.ProductId == 1).Exceute();
-
指定条件更新
var product = new Product() { ProductId = 1, ProductCode = "1001", ProductName = "测试商品1" }; var result = db.Update(product).Where(p => p.ProductId == 100).Exceute(); Console.WriteLine($"表达式更新 受影响行数 {result}");
-
并发更新 乐观锁-版本控制
//注意:仅支持非列表更新 版本列数据类型仅支持 object、string、Guid 时间类型存在精度丢失所以不做支持 var obj = db.Query<Product>().Where(w => w.ProductId == 1).Frist(); obj.Custom1 = "测试版本控制修改"; //参数为 true 更新失败将抛出异常 var result = db.Update(obj).ExceuteWithOptLock(true);
-
单一查询
var data = db.Query<Product>().First();
-
列表查询
var data = db.Query<Product>().ToList();
-
返回单个字典
var data = db.Query<Product>().ToDictionary();
-
返回字典列表
var data = db.Query<Product>().ToDictionaryList();
-
分页查询
//分页查询不返回总数 var data = db.Query<Product>().ToPageList(1,100); //分页查询返回总数 var total = 0;//定义总数变量 var data = db.Query<Product>().ToPageList(1, 1, ref total); Console.WriteLine($"总数：{total}");
-
计数查询
var data = db.Query<Product>().Count();
-
任何查询
var data = db.Query<Product>().Any();
-
条件查询
var data = db.Query<Product>().Where(w => w.ProductId == 1).ToList;
-
Like 查询
var data = db.Query<Product>().Where(w => w.ProductName.StartsWith("左模糊") || w.ProductName.EndsWith("右模糊") || w.ProductName.Contains("全模糊")).ToList();
-
Not Like查询
var data = db.Query<Product>().Where(w => !w.ProductName.StartsWith("左模糊") || !w.ProductName.EndsWith("右模糊") || !w.ProductName.Contains("全模糊")).ToList();
-
Select查询 (选择字段)
var data = db.Query<Product>().Select(s => new { s.ProductId, s.ProductName }).ToList();
-
Select查询 (Case When)
var data = db.Query<Product>().Select(s => new { CaseTest1 = SqlFunc.Case(s.Custom1).When("1").Then("xx1").When("2").Then("xx2").Else("xx3").End(), CaseTest2 = SqlFunc.CaseWhen<string>(s.Custom1 == "1").Then("xx1").When(s.Custom1 == "2").Then("xx2").Else("xx3").End() }).ToList();
-
分组查询
var data = db.Query<Product>().GroupBy(s => new { s.ProductId, s.ProductName }).ToList();
-
分组聚合查询
var sql = db.Query<Order>().InnerJoin<OrderDetail>((a, b) => a.OrderId == b.OrderId).GroupBy((a, b) => new { a.OrderCode }).Select((a, b) => new { a.OrderCode, Sum_Qty = SqlFunc.Sum(b.Qty)//支持嵌套 }).ToList();
-
排序查询
var data = db.Query<Product>().OrderBy(s => new { s.CreateTime }).ToList(); //这是多个字段排序使用方法 还有其它重载方法
-
Having查询
var data = db.Query<Product>().GroupBy(s => new { s.ProductId, s.ProductName }).Having(s => SqlFunc.Count(s.ProductId) > 1).ToList(); //必须先使用GroupBy方法 懂得都懂
-
联表查询
var data = db.Query<Product>().LeftJoin<Class1>((a, b) => a.ProductId == b.ProductId).ToList(); // 右连接 RightJoin 内连接 InnerJoin 全连接 FullJoin
-
联合查询
var query1 = db.Query<Product>(); var query2 = db.Query<Product>(); db.Union(query1, query2);//联合 db.UnionAll(query1, query2);//全联合 //执行查询调用Toxx方法
-
导航查询 (支持无限层级)
/// <summary> /// 类别 /// </summary> public class Category { /// <summary> /// 类别ID /// </summary> [Key] public int CategoryId { get; set; } /// <summary> /// 类别名称 /// </summary> public string CategoryName { get; set; } /// <summary> /// 产品 Navigate MainName和ChildName 可不显示指定，会自动查找主键匹配或ID为结尾的属性 /// </summary> [Navigate(MainName = nameof(CategoryId), ChildName = nameof(Product.CategoryId))] public IEnumerable<Product> Products { get; set; } } var data = db.Query<Category>() .Include(i => i.Products) .ToList();
-
查询并插入 仅支持同实例的数据库 跨库 个人还是建议 用事务分开写查询和插入
//方式1 var result1 = db.Query<Product>().Where(w => w.ProductId == 1489087).Select(s => new { s.ProductCode, s.ProductName }).Insert<Product>(p => new { p.ProductCode, p.ProductName }); //方式2 var result2 = db.Query<Product>().Where(w => w.ProductId == 1489087).Select(s => new { s.ProductCode, s.ProductName }).Insert("表名称 同实例不同库 可以使用 db.数据库名称.表名称 ", "列名称1", "列名称2"); //方式3 需要注意同方式2 一样 var result3 = db.Query<Product>().Where(w => w.ProductId == 1489087).Select(s => new { s.ProductCode, s.ProductName }).Insert("表名称 同实例不同库 可以使用 db.数据库名称.表名称 ", new List<string>() { "列名称1" });
-
In查询
var data1 = db.Query<Product>().Where(w => new List<string>(){"1001", "1002"}.Contains(w.ProductCode)).ToList();
-
Select嵌套查询和子查询
var data1 = db.Query<Product>().Select(s => new { XX = db.Query<Product>().Select(s => 1).First()//需调用返回结果的方法 否则无法解析 }).First(); //进价用法，下面示例方法的重载均支持 var count = 0; var refAsync = new RefAsync<int>(); var query = db.Query<Product>().Select(s => new { WithAttr_First = db.QueryWithAttr<Product>().First(), WithAttr_FirstAsync = db.QueryWithAttr<Product>().FirstAsync(), WithAttr_ToList = db.QueryWithAttr<Product>().ToList(), WithAttr_ToListAsync = db.QueryWithAttr<Product>().ToListAsync(), First_1 = db.Query<Category>().Select(s => 1).First(),//解析成Sql First = db.Query<Category>().First(), FirstAsync = db.Query<Category>().FirstAsync(), ToArray = db.Query<Category>().ToArray(), ToArrayAsync = db.Query<Category>().ToArrayAsync(), ToList = db.Query<Category>().ToList(), ToListAsync = db.Query<Category>().ToListAsync(), ToPageList = db.Query<Category>().ToPageList(1, 10), ToPageListAsync = db.Query<Category>().ToPageListAsync(1, 10), ToPageList_Count = db.Query<Category>().ToPageList(1, 10, ref count), ToPageListAsync_Count = db.Query<Category>().ToPageListAsync(1, 10, refAsync), ToDictionary = db.Query<Category>().ToDictionary(), ToDictionaryAsync = db.Query<Category>().ToDictionaryAsync(), ToDictionaryList = db.Query<Category>().ToDictionaryList(), ToDictionaryListAsync = db.Query<Category>().ToDictionaryListAsync(), ToDictionaryPageList = db.Query<Category>().ToDictionaryPageList(1, 10), ToDictionaryPageListAsync = db.Query<Category>().ToDictionaryPageListAsync(1, 10), ToDictionaryPageList_Count = db.Query<Category>().ToDictionaryPageList(1, 10, ref count), ToDictionaryPageListAsync_Count = db.Query<Category>().ToDictionaryPageListAsync(1, 10, refAsync), ToDataTable = db.Query<Category>().ToDataTable(), ToDataTableAsync = db.Query<Category>().ToDataTableAsync(), ObjToJson = db.Query<Category>().ObjToJson(), ObjToJsonAsync = db.Query<Category>().ObjToJsonAsync(), ObjListToJson = db.Query<Category>().ObjListToJson(), ObjListToJsonAsync = db.Query<Category>().ObjListToJsonAsync(), Max = db.Query<Category>().Max(a => a.CategoryId),//解析成Sql MaxAsync = db.Query<Category>().MaxAsync(a => a.CategoryId), Min = db.Query<Category>().Min(a => a.CategoryId),//解析成Sql MinAsync = db.Query<Category>().MinAsync(a => a.CategoryId), Count = db.Query<Category>().Count(),//解析成Sql CountAsync = db.Query<Category>().CountAsync(), Sum = db.Query<Category>().Sum(s => s.CategoryId),//解析成Sql SumAsync = db.Query<Category>().SumAsync(s => s.CategoryId), Avg = db.Query<Category>().Avg(s => s.CategoryId),//解析成Sql AvgAsync = db.Query<Category>().AvgAsync(s => s.CategoryId) }); var data2= query.First();
-
From子查询
var subQuery2 = db.Query<Product>().Select(s=>new { s.ProductId, s.CategoryId, s.ProductCode, s.ProductName, s.DeleteMark }); var data = db.Query(subQuery2).ToList();
-
Join子查询
var subQuery1 = db.Query<Product>().Select(s => new { s.ProductId, s.CategoryId, s.ProductCode, s.ProductName, s.DeleteMark }); var data = db.Query<Category>().InnerJoin(subQuery1, (a, b) => a.CategoryId == b.CategoryId).ToList();
-
Include查询
// 联表条件 默认优先匹配主键 其次带有ID结尾的名称 var data = db.Query<Category>().Include(i => i.Products).ToList();
-
Exists查询
var data = db.Query<Product>() .Where(w => db.Query<Product>().WhereIF(!string.IsNullOrWhiteSpace("测试"), a => a.ProductId == 1).Select(s => 1).Any()) .Select(s => new { s.ProductId, s.ProductCode }).ToList();
-
查询绑定字段（注意 字段必须是公开的，否则绑定外部无法访问，没有意义）
//当某些字段需要参与计算并且不返回前端时推荐用字段绑定，无需从A实体转换到B实体，强烈推荐此方式 var data = db.Query<Product>().Select(s => new Product() { _xx = s.ProductName }).First();
经验首页