基于.NET 6.0 自研轻量级ORM框架

郑子铭

发布于 2022-03-22 16:30:01

9500

发布于 2022-03-22 16:30:01

文章被收录于专栏：DotNet NB && CloudNative

Fast Framework

项目：https://gitee.com/China-Mr-zhong/Fast.Framework

一、前言

1、为了实现快速开发，省去编写大量Sql时间，更好的面向对象编程由此诞生了 Fast Framework

2、Fast Framework 是一个基于.NET 6.0 封装的轻量级 ORM 框架支持数据库 SqlServer Oracle MySql PostgreSql Sqlite 由于底层使用System.Data.Common 抽象类封装理论支持所有Ado.Net 实现的类库，差异部分可能需要额外处理。

3、框架

优点：体积小流畅API 使用更加简单性能高

缺点：不具备有自动建库建表迁移等复杂的功能由于不同数据库差异较大实现较为复杂所以暂时不考虑实现

二、项目明细（后缀为Test均为测试项目）

1、Fast.Framework

2、Fast.Framework.CustomAttribute

3、Fast.Framework.Extensions

4、Fast.Framework.Interfaces

5、Fast.Framework.Logging

6、Fast.Framework.Models

7、Fast.Framework.Utils

三、快速入门

1、手动创建

var options = new DefaultDbOptions() //数据选项
                {
                    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;AllowLoadLocalInfile=True;"
                };
            var ado = new Ado<DefaultDbOptions>(options);//原生Ado
            var db = new DbContext<DefaultDbOptions>(ado);//数据库上下文

2、依赖注入

var builder = WebApplication.CreateBuilder(args);

// 正式项目请用配置文件注入,为了测试方便直接引用实现类库

builder.Services.AddScoped<IAdo<DefaultDbOptions>, Ado<DefaultDbOptions>>();
builder.Services.AddScoped<IDbContext<DefaultDbOptions>, DbContext<DefaultDbOptions>>();

3、加载Json配置文件

builder.Services.Configure<DefaultDbOptions>(configuration.GetSection("DbFactory:MySqlDb"));

4、Json 格式

"DbFactory": {
    "MySQLDb": {
      "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;"
    },
    "SQLServerDb": {
      "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 =30;"
    },
    "OracleDb": {
      "DbType": "Oracle",
      "ProviderName": "Oracle.ManagedDataAccess.Client",
      "FactoryName": "Oracle.ManagedDataAccess.Client.OracleClientFactory,Oracle.ManagedDataAccess",
      "ConnectionStrings": "data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)));user id=system;password=Oracle2022;min pool size=3;max pool size=100;connect timeout =30;"
    },
    "PostgreSQLDb": {
      "DbType": "PostgreSQL",
      "ProviderName": "Npgsql",
      "FactoryName": "Npgsql.NpgsqlFactory,Npgsql",
      "ConnectionStrings": "host=localhost;database=test;userid=postgres;pwd=123456789;port=5432;"
    },
    "SQLiteDb": {
      "DbType": "SQLite",
      "ProviderName": "System.Data.SQLite",
      "FactoryName": "System.Data.SQLite.SQLiteFactory,System.Data.SQLite",
      "ConnectionStrings": "data source=mysqlite.db;pooling=true;"
    }
  }

5、Controller 构造方法注入已注册的DbContext对象

namespace Fast.Framework.Web.Test.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ProductController : ControllerBase
    {

        /// <summary>
        /// 数据库
        /// </summary>
        private readonly IDbContext<DefaultDbOptions> db;

        /// <summary>
        /// 日志
        /// </summary>
        private readonly ILogger<ProductController> logger;

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="db"></param>
        public ProductController(ILogger<ProductController> logger, IDbContext<DefaultDbOptions> db)
        {
            this.db = db;
            this.logger = logger;
        }
    }
}

6、示例

#region 增 删 改
//实体对象插入
{
    var result = await db.Insert(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" }).ExecuteAsync();
    Console.WriteLine(result);
}

//实体对象插入并返回自增ID 仅支持 SQLServer Mysql Sqlite
{
    var result = await db.Insert(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" }).ExecuteReturnIdentityAsync();
    Console.WriteLine(result);
}

//实体对象列表插入 列表插入不支持批量返回自增ID
{
    var list = new List<ProductModel>();
    list.Add(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" });
    list.Add(new ProductModel() { ProductCode = "1002", ProductName = "测试产品2" });
    var result = await db.Insert(list).ExecuteAsync();
    Console.WriteLine(result);
}

//匿名对象插入 必须使用 As 方法显示指定表名称
{
    var result = db.Insert(new { ProductCode = "1001", ProductName = "测试产品1" }).As("Product").ExecuteAsync();
    Console.WriteLine(result);
}

//匿名对象列表插入 必须使用As 显示指定表名称
{
    var list = new List<object>();
    list.Add(new { ProductCode = "1001", ProductName = "测试产品1" });
    list.Add(new { ProductCode = "1002", ProductName = "测试产品2" });
    var result = await db.Insert(list).As("Product").ExecuteAsync();
    Console.WriteLine(result);
}

//实体无条件删除
{
    var result = await db.Delete<ProductModel>().ExecuteAsync();
    Console.WriteLine(result);
}

//实体条件删除
{
    var result = await db.Delete<ProductModel>().Where(w => w.ProductId == 1).ExecuteAsync();
    Console.WriteLine(result);
}

//父类对象删除
{
    var result = db.Delete<object>().As("Product").ExecuteAsync();
    Console.WriteLine(result);
}

//匿名对象条件删除
{
    var obj = new { ProductCode = "测试" };
    var result = await db.Delete(obj).Where(w => w.ProductCode == obj.ProductCode).As("Product").ExecuteAsync();
    Console.WriteLine(result);
}

//实体对象无条件更新
{
    var result = await db.Update(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" }).ExecuteAsync();
    Console.WriteLine(result);
}

//实体对象条件更新
{
    var result = await db.Update(new ProductModel() { ProductCode = "1001", ProductName = "测试产品1" }).Where(w => w.ProductId == 1).ExecuteAsync();
    Console.WriteLine(result);
}

//实体对象列表更新
{
    var list = new List<ProductModel>();
    for (int i = 0; i < 10; i++)
    {
        list.Add(new ProductModel() { ProductId = 16395 + i, ProductCode = $"{16395 + i}", ProductName = $"{16395 + i}" });
    }
    var result = await db.Update(list).ExecuteAsync();
    Console.WriteLine(result);
}

//匿名对象无条件更新
{
    var result = await db.Update(new { ProductCode = "1001", ProductName = "测试产品1" }).As("Product").ExecuteAsync();
    Console.WriteLine(result);
}

//匿名对象条件更新
{
    var result = await db.Update(new { ProductId = 1, ProductCode = "1001", ProductName = "测试产品1" }).Where(w => w.ProductId == 1).As("Product").ExecuteAsync();
    Console.WriteLine(result);
}

//匿名对象列表更新 需要显示指定主键名称
{
    var list = new List<object>();
    for (int i = 0; i < 10; i++)
    {
        list.Add(new { ProductId = 16395 + i, ProductCode = $"{16395 + i}", ProductName = $"{16395 + i}" });
    }
    var result = await db.Update(list.ToList()).As("Product").Where("ProductId").ExecuteAsync();
    Console.WriteLine(result);
}

#endregion

#region 查

//返回单个对象
{
    var data = await db.Query<ProductModel>().Where(w => w.ProductId == 1).FirstAsync();
}

//返回列表
{
    var data = await db.Query<ProductModel>().ToListAsync();
}

//返回字典
{
    var data = await db.Query<ProductModel>().DictionaryAsync();
}

//返回字典列表
{
    var data = await db.Query<ProductModel>().DictionaryListAsync();
}

//分页查询
{
    var data = await db.Query<ProductModel>().ToPageListAsync(1, 10);
    Console.WriteLine(JsonSerializer.Serialize(data.Data));//页数据
    Console.WriteLine($"总数:{data.Count}");
}

//联表查询
{
    var data = await db.Query<ProductModel>().InnerJoin<ProductCategoryModel>((a, b) => a.CategoryId == b.CategoryId).ToListAsync();
}

//分组查询
{
    var data = await db.Query<ProductModel>().GroupBy(g => g.ProductName).Select(s => new { Count = 1.Count(), s.ProductName }).ToListAsync();
}

//排序查询
{
    var data = await db.Query<ProductModel>().OrderBy(o => o.ProductName).ToListAsync();
}

//动态条件表达式
{
    var obj = new { ProductName = "测试" };
    var ex = DynamicWhereExpression.Create<ProductModel>().AndIF(!string.IsNullOrWhiteSpace(obj.ProductName), a => a.ProductName == obj.ProductName);

    var data = await db.Query<ProductModel>().Where(ex.Build()).ToListAsync();
}

// 合并查询
{
    var query1 = db.Query<ProductModel>().Where(w => w.ProductId < 100);
    var query2 = db.Query<ProductModel>().Where(w => w.ProductId > 100);
    var data = await db.UnionAll(query1, query2).ToListAsync();
}


#endregion

#region 批量复制 建议数据量达到500以上使用这个方法 仅支持 SqlServer MySql Oracle PostgreSql
{
    var list = new List<ProductModel>();
    for (int i = 1; i <= 10000; i++)
    {
        list.Add(new ProductModel() { ProductCode = i.ToString(), ProductName = $"测试{i}" });
    }
    // 因为 ProductModel 类还有其它属性 所以需要使用 Select 扩展方法 显示指定要导入的属性名称和表名称
    var result = await db.BigData().BulkCopyAsync(list.Select(s => new { s.ProductCode, s.ProductName }), "Product");
    Console.WriteLine(result);
}
#endregion

方法太多写文档是个体力活更多请看源码

https://gitee.com/China-Mr-zhong/Fast.Framework

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-03-13，如有侵权请联系 cloudcommunity@tencent.com 删除

json