Zq.SQLBuilder.Core 2.3.8

.NET 5.0 .NET Standard 2.1
Install-Package Zq.SQLBuilder.Core -Version 2.3.8
dotnet add package Zq.SQLBuilder.Core --version 2.3.8
<PackageReference Include="Zq.SQLBuilder.Core" Version="2.3.8" />
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.8
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: Zq.SQLBuilder.Core, 2.3.8"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. 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.8

// Install Zq.SQLBuilder.Core as a Cake Tool
#tool nuget:?package=Zq.SQLBuilder.Core&version=2.3.8
The NuGet Team does not provide support for this client. Please contact its maintainers for support.

<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 开源协议,欢迎大家提交 PRIssue

Product Versions
.NET net5.0 net5.0-windows net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows
.NET Core netcoreapp3.0 netcoreapp3.1
.NET Standard netstandard2.1
MonoAndroid monoandroid
MonoMac monomac
MonoTouch monotouch
Tizen tizen60
Xamarin.iOS xamarinios
Xamarin.Mac xamarinmac
Xamarin.TVOS xamarintvos
Xamarin.WatchOS xamarinwatchos
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (4)

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

Package Downloads
Zq.SQLBuilder.Core.SkyWalking

SkyWalking扩展,实现sql链路追踪;

Zq.SQLBuilder.Core.ElasticApm

ElasticApm扩展,实现sql链路追踪;

Zq.SQLBuilder.Core.Diagnostics

SqlBuilder自定义Diagnostics扩展,实现sql自定义追踪监控;

Ydhp.Lib.Dapper

Package Description

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.3.8 176 5/15/2022
2.3.7 139 5/14/2022
2.3.6 171 5/7/2022
2.3.5 301 4/4/2022
2.3.4 274 3/29/2022
2.3.3 318 3/24/2022
2.3.2 287 3/21/2022
2.3.1 548 12/31/2021
2.3.0 409 12/30/2021
2.2.9 330 12/27/2021
2.2.8 604 11/9/2021
2.2.7 575 10/17/2021
2.2.6 550 9/17/2021
2.2.5 605 7/10/2021
2.2.4 489 6/25/2021
2.2.3 587 6/23/2021
2.2.2 531 6/15/2021
2.2.1 552 6/6/2021
2.2.0 469 6/1/2021
2.1.9 470 5/13/2021
2.1.8 557 4/15/2021
2.1.7 469 4/14/2021
2.1.6 511 4/8/2021
2.1.5 495 1/20/2021
2.1.4 402 1/14/2021
2.1.3 458 1/1/2021
2.1.2 623 11/15/2020
2.1.1 479 11/12/2020
2.1.0 440 11/11/2020
2.0.5 438 11/10/2020
2.0.4 456 11/9/2020
2.0.3 337 11/5/2020
2.0.2 350 11/5/2020
2.0.1 458 10/31/2020
2.0.0 387 10/30/2020
1.0.6.7 348 10/27/2020
1.0.6.6 385 10/23/2020
1.0.6.5 321 10/22/2020
1.0.6.4 321 10/16/2020
1.0.6.3 327 10/13/2020
1.0.6.2 349 10/13/2020
1.0.6.1 351 10/12/2020
1.0.6 395 9/30/2020
1.0.5.9 367 9/29/2020
1.0.5.8 552 9/19/2020
1.0.5.7 456 9/17/2020
1.0.5.6 571 8/14/2020
1.0.5.5 430 8/11/2020
1.0.5.4 455 8/11/2020
1.0.5.3 421 8/3/2020
1.0.5.2 423 8/1/2020
1.0.5.1 430 7/31/2020
1.0.5 390 7/24/2020
1.0.4.9 393 7/16/2020
1.0.4.8 438 6/10/2020
1.0.4.7 403 5/27/2020
1.0.4.6 401 5/9/2020
1.0.4.5 552 4/11/2020
1.0.4.4 431 4/10/2020
1.0.4.3 479 4/9/2020
1.0.4.2 436 4/2/2020
1.0.4.1 475 4/2/2020
1.0.4 529 3/31/2020
1.0.3.9 413 3/30/2020
1.0.3.8 430 3/29/2020
1.0.3.7 448 3/29/2020
1.0.3.6 496 3/23/2020
1.0.3.5 433 3/12/2020
1.0.3.4 495 12/30/2019
1.0.3.3 480 12/30/2019
1.0.3.2 475 12/5/2019
1.0.3.1 461 12/5/2019
1.0.3 477 10/8/2019
1.0.2.9 535 7/15/2019
1.0.2.8 435 7/13/2019
1.0.2.7 522 5/31/2019
1.0.2.6 487 5/24/2019
1.0.2.5 514 5/15/2019
1.0.2.2 531 4/26/2019
1.0.2.1 501 3/7/2019
1.0.2 660 9/27/2018
1.0.1.9 706 9/17/2018
1.0.1.7 670 9/14/2018
1.0.1.6 709 9/10/2018
1.0.1.5 732 9/10/2018
1.0.1.4 691 9/9/2018
1.0.1.3 775 9/5/2018
1.0.1.2 684 9/1/2018
1.0.1.1 704 9/1/2018
1.0.1 796 8/24/2018
1.0.0.9 711 8/24/2018
1.0.0.8 696 8/24/2018
1.0.0.7 723 8/10/2018
1.0.0.6 783 8/10/2018
1.0.0.5 824 8/9/2018
1.0.0.4 714 8/8/2018
1.0.0.3 631 8/8/2018
1.0.0.2 663 8/8/2018
1.0.0.1 648 8/8/2018
1.0.0 717 8/8/2018

1.优化WHERE是否包含判断;
2.新增SubstringIgnoreCase扩展方法;
3.优化字符串忽略大小写相等比较;
4.优化null空值判断,修复WhereIf参数Expression<T,bool>为null时引发的空异常bug;
5.升级nuget包;