Zq.SQLBuilder.Core 2.3.3

.NET 5.0 This package targets .NET 5.0. The package is compatible with this framework or higher. .NET Standard 2.1 This package targets .NET Standard 2.1. The package is compatible with this framework or higher.
There is a newer version of this package available.
See the version list below for details. 
dotnet add package Zq.SQLBuilder.Core --version 2.3.3                

                    
                

            
NuGet\Install-Package Zq.SQLBuilder.Core -Version 2.3.3
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package. 
<PackageReference Include="Zq.SQLBuilder.Core" Version="2.3.3" />
For projects that support PackageReference, copy this XML node into the project file to reference the package. 
paket add Zq.SQLBuilder.Core --version 2.3.3                

                    
                

            
#r "nuget: Zq.SQLBuilder.Core, 2.3.3"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package. 
// Install Zq.SQLBuilder.Core as a Cake Addin
#addin nuget:?package=Zq.SQLBuilder.Core&version=2.3.3

// Install Zq.SQLBuilder.Core as a Cake Tool
#tool nuget:?package=Zq.SQLBuilder.Core&version=2.3.3

<p></p>

<p align="center"> <img src="https://zqlovejyc.gitee.io/zqutils-js/Images/SQL.png" height="80"/> </p>

<div align="center">

star fork GitHub stars GitHub forks GitHub license nuget

</div>

<div align="left">

.NET Standard 2.1、.NET 5 版本SQLBuilder，Expression表达式转换为SQL语句，支持SqlServer、MySql、Oracle、Sqlite、PostgreSql；基于Dapper实现了不同数据库对应的数据仓储Repository；

</div>

🌭 开源地址

🥥 框架扩展包

包类型 名称 版本 描述
nuget Zq.SQLBuilder.Core nuget SQLBuilder.Core 核心包
nuget Zq.SQLBuilder.Core.SkyWalking nuget SQLBuilder.Core SkyWalking 扩展包
nuget Zq.SQLBuilder.Core.ElasticApm nuget SQLBuilder.Core ElasticApm扩展包

🚀 快速入门

  • ➕ 新增
//新增
await _repository.InsertAsync(entity);

//批量新增
await _repository.InsertAsync(entities);

//新增
await SqlBuilder
        .Insert<MsdBoxEntity>(() =>
            entity)
        .ExecuteAsync(
            _repository);

//批量新增
await SqlBuilder
        .Insert<MsdBoxEntity>(() =>
            new[]
            {
                new UserInfo { Name = "张三", Sex = 2 },
                new UserInfo { Name = "张三", Sex = 2 }
            })
        .ExecuteAsync(
            _repository);
  • 🗑 删除
//删除
await _repository.DeleteAsync(entity);

//批量删除
await _repository.DeleteAsync(entitties);

//条件删除
await _repository.DeleteAsync<MsdBoxEntity>(x => x.Id == "1");

//删除
await SqlBuilder
        .Delete<MsdBoxEntity>()
        .Where(x =>
            x.Id == "1")
        .ExecuteAsync(
            _repository);

//主键删除
await SqlBuilder
        .Delete<MsdBoxEntity>()
        .WithKey("1")
        .ExecuteAsync(
            _repository);
  • ✏ 更新
//更新
await _repository.UpdateAsync(entity);

//批量更新
await _repository.UpdateAsync(entities);

//条件更新
await _repository.UpdateAsync<MsdBoxEntity>(x => x.Id == "1", () => entity);

//更新
await SqlBuilder
        .Update<MsdBoxEntity>(() =>
            entity,
            DatabaseType.MySql,
            isEnableFormat:true)
        .Where(x =>
            x.Id == "1")
        .ExecuteAsync(
            _repository);
  • 🔍 查询
//简单查询
await _repository.FindListAsync<MsdBoxEntity>(x => x.Id == "1");

//连接查询
await SqlBuilder
        .Select<UserInfo, UserInfo, Account, Student, Class, City, Country>((u, t, a, s, d, e, f) =>
            new { u.Id, UId = t.Id, a.Name, StudentName = s.Name, ClassName = d.Name, e.CityName, CountryName = f.Name })
        .Join<UserInfo>((x, t) =>
            x.Id == t.Id) //注意此处单表多次Join所以要指明具体表别名，否则都会读取第一个表别名
        .Join<Account>((x, y) =>
            x.Id == y.UserId)
        .LeftJoin<Account, Student>((x, y) =>
            x.Id == y.AccountId)
        .RightJoin<Student, Class>((x, y) =>
            x.Id == y.UserId)
        .InnerJoin<Class, City>((x, y) =>
            x.CityId == y.Id)
        .FullJoin<City, Country>((x, y) =>
            x.CountryId == y.Id)
        .Where(x =>
            x.Id != null)
        .ToListAsync(
            _repository);

//分页查询
var condition = LinqExtensions
                    .True<UserInfo, Account>()
                    .And((x, y) => 
                        x.Id == y.UserId)
                    .WhereIf(
                        !name.IsNullOrEmpty(), 
                        (x, y) => name.EndsWith("∞")
                        ? x.Name.Contains(name.Trim('∞'))
                        : x.Name == name);
var hasWhere = false;
await SqlBuilder
        .Select<UserInfo, Account>(
            (u, a) => new { u.Id, UserName = "u.Name" })
        .InnerJoin<Account>(
            condition)
        .WhereIf(
            !name.IsNullOrEmpty(),
            x => x.Email != null && 
            (!name.EndsWith("∞") ? x.Name.Contains(name.TrimEnd('∞', '*')) : x.Name == name),
            ref hasWhere)
        .WhereIf(
            !email.IsNullOrEmpty(),
            x => x.Email == email,
            ref hasWhere)
        .ToPageAsync(
            _repository.UseMasterOrSlave(false),
            input.OrderField,
            input.Ascending,
            input.PageSize,
            input.PageIndex);

//仓储分页查询
await _repository.FindListAsync(condition, input.OrderField, input.Ascending, input.PageSize, input.PageIndex);

//高级查询
Func<string[], string> @delegate = x => $"ks.{x[0]}{x[1]}{x[2]} WITH(NOLOCK)";

await SqlBuilder
        .Select<UserInfo, Account, Student, Class, City, Country>((u, a, s, d, e, f) =>
            new { u, a.Name, StudentName = s.Name, ClassName = d.Name, e.CityName, CountryName = f.Name },
            tableNameFunc: @delegate)
        .Join<Account>((x, y) =>
            x.Id == y.UserId,
            @delegate)
        .LeftJoin<Account, Student>((x, y) =>
            x.Id == y.AccountId,
            @delegate)
        .RightJoin<Class, Student>((x, y) =>
            y.Id == x.UserId,
            @delegate)
        .InnerJoin<Class, City>((x, y) =>
            x.CityId == y.Id,
            @delegate)
        .FullJoin<City, Country>((x, y) =>
            x.CountryId == y.Id,
            @delegate)
        .Where(u =>
            u.Id != null)
        .ToListAsync(
            _repository);
  • 🎫 队列
//预提交队列
_repository.AddQueue(async repo =>
    await repo.UpdateAsync<UserEntity>(
        x => x.Id == "1",
        () => new
        {
            Name = "test"
        }) > 0);

_repository.AddQueue(async repo =>
    await repo.DeleteAsync<UserEntity>(x =>
        x.Enabled == 1) > 0);

//统一提交队列，默认开启事务
var res = await _repository.SaveQueueAsync();

🌌 IOC注入

根据appsettions.json配置自动注入不同类型数据仓储，支持一主多从配置

//注入SQLBuilder仓储
services.AddSqlBuilder(Configuration, "Base", (sql, parameter) =>
{
    //写入文本日志
    if (WebHostEnvironment.IsDevelopment())
    {
        if (parameter is DynamicParameters dynamicParameters)
            _logger.LogInformation($@"SQL语句：{sql}  参数：{dynamicParameters
                .ParameterNames?
                .ToDictionary(k => k, v => dynamicParameters.Get<object>(v))
                .ToJson()}");
        else if (parameter is OracleDynamicParameters oracleDynamicParameters)
            _logger.LogInformation($@"SQL语句：{sql} 参数：{oracleDynamicParameters
                .OracleParameters
                .ToDictionary(k => k.ParameterName, v => v.Value)
                .ToJson()}");
        else
            _logger.LogInformation($"SQL语句：{sql}  参数：{parameter.ToJson()}");
    }

    //返回null，不对原始sql进行任何更改，此处可以修改待执行的sql语句
    return null;
});

⚙ 数据库配置

//appsettions.json
"ConnectionStrings": {
  "Base": [
    "Oracle",
    "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=name)));Persist Security Info=True;User ID=test;Password=123;",
    "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=name)));Persist Security Info=True;User ID=test;Password=123;",
    "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.102)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=name)));Persist Security Info=True;User ID=test;Password=123;",
  ],
  "Cap": [
    "MySql",
    "Server=127.0.0.1;Database=db;Uid=root;Pwd=123456;SslMode=None;"
  ],
  "Oracle": [ "Oracle", "数据库连接字符串" ],
  "MySql": [ "MySql", "数据库连接字符串" ],
  "Sqlserver": [ "SqlServer", "数据库连接字符串" ],
  "Sqlite": [ "Sqlite", "数据库连接字符串" ],
  "Pgsql": [ "PostgreSql", "数据库连接字符串" ]
}

📰 事务

//方式一
IRepository trans = null;
try
{
    //开启事务
    trans = await _repository.BeginTransactionAsync();

    //数据库写操作
    await trans.InsertAsync(entity);

    //提交事务
    await trans.CommitAsync();
}
catch (Exception)
{
    //回滚事务
    if(trans != null)
        await tran.RollbackAsync();
       
    throw;
}

//方式二
var res = await _repository.ExecuteTransactionAsync(async trans =>
{
    var retval = (await trans.InsertAsync(entity)) > 0;

    if (input.Action.EqualIgnoreCase(UnitAction.InDryBox))
        code = await _unitInfoService.InDryBoxAsync(dryBoxInput);
    else
        code = await _unitInfoService.OutDryBoxAsync(dryBoxInput);

    return code == ErrorCode.Successful && retval;
});

📯 仓储+切库

private readonly Func<string, IRepository> _handler;
private readonly IRepository _repository;

public MyService(Func<string, IRepository> hander)
{
    _handler = hander;

    //默认base数据仓储
    _repository = hander(null);
}

//base仓储
var baseRepository = _handler("Base");

//cap仓储
var capRepository = _handler("Cap");

🎣 读写分离

//方式一
_repository.Master = false;

//方式二
_repository.UseMasterOrSlave(master)

🔗 链路追踪

//注入SQLBuilder SkyWalking链路追踪
services.AddSqlBuilderSkyApm()

//使用SQLBuilder ElasticApm链路追踪
app.UseSqlBuilderElasticApm(Configuration)

🧪 测试文档

🍻 贡献代码

SQLBuilder.Core 遵循 Apache-2.0 开源协议，欢迎大家提交 PR 或 Issue。

Product Compatible and additional computed target framework versions.
.NET net5.0 is compatible.  net5.0-windows was computed.  net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.1 is compatible. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (7)

Showing the top 5 NuGet packages that depend on Zq.SQLBuilder.Core:

Package Downloads
Zq.SQLBuilder.Core.ElasticApm

ElasticApm扩展，实现sql链路追踪；
Zq.SQLBuilder.Core.SkyWalking

SkyWalking扩展，实现sql链路追踪；
Zq.SQLBuilder.Core.Diagnostics

SqlBuilder自定义Diagnostics扩展，实现sql自定义追踪监控；
Ydhp.Lib.Dapper

Package Description
Peak.Lib.Dapper

Package Description

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.4.0 1,284 2/19/2023
2.3.9 1,755 9/12/2022
2.3.8 1,882 5/15/2022
2.3.7 1,829 5/14/2022
2.3.6 1,794 5/7/2022
2.3.5 2,067 4/4/2022
2.3.4 1,933 3/29/2022
2.3.3 1,911 3/24/2022
2.3.2 1,912 3/21/2022
2.3.1 1,425 12/31/2021
2.3.0 1,144 12/30/2021
2.2.9 1,087 12/27/2021
2.2.8 1,484 11/9/2021
2.2.7 1,222 10/17/2021
2.2.6 1,207 9/17/2021
2.2.5 1,164 7/10/2021
2.2.4 1,107 6/25/2021
2.2.3 1,204 6/23/2021
2.2.2 1,147 6/15/2021
2.2.1 1,170 6/6/2021
2.2.0 1,042 6/1/2021
2.1.9 1,114 5/13/2021
2.1.8 1,134 4/15/2021
2.1.7 1,044 4/14/2021
2.1.6 1,150 4/8/2021
2.1.5 1,064 1/20/2021
2.1.4 675 1/14/2021
2.1.3 749 1/1/2021
2.1.2 934 11/15/2020
2.1.1 797 11/12/2020
2.1.0 720 11/11/2020
2.0.5 701 11/10/2020
2.0.4 732 11/9/2020
2.0.3 536 11/5/2020
2.0.2 525 11/5/2020
2.0.1 669 10/31/2020
2.0.0 580 10/30/2020
1.0.6.7 549 10/27/2020
1.0.6.6 594 10/23/2020
1.0.6.5 539 10/22/2020
1.0.6.4 537 10/16/2020
1.0.6.3 547 10/13/2020
1.0.6.2 568 10/13/2020
1.0.6.1 544 10/12/2020
1.0.6 592 9/30/2020
1.0.5.9 557 9/29/2020
1.0.5.8 744 9/19/2020
1.0.5.7 701 9/17/2020
1.0.5.6 845 8/14/2020
1.0.5.5 616 8/11/2020
1.0.5.4 657 8/11/2020
1.0.5.3 608 8/3/2020
1.0.5.2 642 8/1/2020
1.0.5.1 621 7/31/2020
1.0.5 573 7/24/2020
1.0.4.9 603 7/16/2020
1.0.4.8 684 6/10/2020
1.0.4.7 603 5/27/2020
1.0.4.6 590 5/9/2020
1.0.4.5 800 4/11/2020
1.0.4.4 642 4/10/2020
1.0.4.3 701 4/9/2020
1.0.4.2 642 4/2/2020
1.0.4.1 671 4/2/2020
1.0.4 734 3/31/2020
1.0.3.9 634 3/30/2020
1.0.3.8 660 3/29/2020
1.0.3.7 657 3/29/2020
1.0.3.6 713 3/23/2020
1.0.3.5 653 3/12/2020
1.0.3.4 734 12/30/2019
1.0.3.3 683 12/30/2019
1.0.3.2 717 12/5/2019
1.0.3.1 676 12/5/2019
1.0.3 688 10/8/2019
1.0.2.9 783 7/15/2019
1.0.2.8 634 7/13/2019
1.0.2.7 729 5/31/2019
1.0.2.6 703 5/24/2019
1.0.2.5 693 5/15/2019
1.0.2.2 727 4/26/2019
1.0.2.1 743 3/7/2019
1.0.2 923 9/27/2018
1.0.1.9 943 9/17/2018
1.0.1.7 897 9/14/2018
1.0.1.6 989 9/10/2018
1.0.1.5 968 9/10/2018
1.0.1.4 923 9/9/2018
1.0.1.3 1,020 9/5/2018
1.0.1.2 927 9/1/2018
1.0.1.1 947 9/1/2018
1.0.1 1,037 8/24/2018
1.0.0.9 1,005 8/24/2018
1.0.0.8 965 8/24/2018
1.0.0.7 998 8/10/2018
1.0.0.6 1,021 8/10/2018
1.0.0.5 1,069 8/9/2018
1.0.0.4 954 8/8/2018
1.0.0.3 859 8/8/2018
1.0.0.2 890 8/8/2018
1.0.0.1 888 8/8/2018
1.0.0 971 8/8/2018

1.优化仓储Any方法内部sql实现；