zqlovejyc/sqlbuilder

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

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

🌭 开源地址

• Gitee：https://gitee.com/zqlovejyc/SQLBuilder
• GitHub：https://github.com/zqlovejyc/SQLBuilder
• GitLab：https://gitlab.com/zqlovejyc/SQLBuilder
• NuGet：https://www.nuget.org/packages/Zq.SQLBuilder
• MyGet：https://www.myget.org/feed/zq-myget/package/nuget/Zq.SQLBuilder

🥥 框架扩展包

包类型	名称	版本	描述
	Zq.SQLBuilder		SQLBuilder 核心包
	Zq.SQLBuilder.Diagnostics		SQLBuilder Diagnostics扩展包

🚀 快速入门

• ➕ 新增

//新增
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注入

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

var builder = new ContainerBuilder();

//注入SqlBuilder仓储
builder.RegisterSqlBuilder("Base", (sql, parameter) =>
{
    //写入文本日志
    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;
});

//注入SqlBuilder日志诊断
builder.RegisterSqlBuilderDiagnostic(
    executeBefore: msg => Console.WriteLine(msg.Sql),
    executeAfter: msg => Console.WriteLine(msg.ElapsedMilliseconds),
    executeError: msg => Console.WriteLine(msg.Exception?.Message),
    executeDispose: msg => Console.WriteLine(msg.MasterConnection.State),
    disposeError: msg => Console.WriteLine(msg.Exception?.Message));

var container = builder.Build();

var repo = container.Resolve<Func<string, IRepository>>()(null);

var res = repo.Any<Log>(x => x.Id == 2633);

⚙ 数据库配置

//appSettings
<add key="ConnectionStrings" value="{'Base':['SqlServer','Server=.;Database=TestDb;Uid=test;Pwd=123;'],'OracleDb':['Oracle','数据库连接字符串'],'MySqlDb':['MySql','数据库连接字符串'],'SqliteDb':['Sqlite','数据库连接字符串'],'PgsqlDb':['PostgreSql','数据库连接字符串']}" />

//connectionStrings
<add name="ConnectionStrings" connectionString="{'Base':['SqlServer','Server=.;Database=TestDb;Uid=test;Pwd=123;'],'OracleDb':['Oracle','数据库连接字符串'],'MySqlDb':['MySql','数据库连接字符串'],'SqliteDb':['Sqlite','数据库连接字符串'],'PgsqlDb':['PostgreSql','数据库连接字符串']}"/>

📰 事务

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

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

    //提交事务
    trans.Commit();
}
catch (Exception)
{
    //回滚事务
    trans?.Rollback();
    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)

🧪 测试文档

• 单元测试 https://github.com/zqlovejyc/SQLBuilder/tree/master/SQLBuilder.UnitTest

🍻 贡献代码

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

sqlbuilder's Issues

支持Net Core么？

支持Net Core么？

复杂where查询还是有问题

core = LambdaToSql.SqlBuilder.Select<Models.AAA_TEST_A>().Where(x => (x.Id > 1000 && x.Id < 10) || x.Age.Equals(100))
                    .AndWhere(x => x.Name == "li").OrWhere(x => x.Price == 999f&&x.Id==1000000);
PrintSql(core);

这样，得出来的sql语句还是有问题，得出来的sql语句如下：
SELECT * FROM AAA_TEST_A AS A WHERE (A.Id > ?Param0 AND A.Id < ?Param1) OR A.Age = ?Param2 AND A.Name = ?Param3 OR A.Price = ?Param4 AND A.Id = ?Param5
正确的sql语句如下：
SELECT * FROM AAA_TEST_A AS A WHERE (A.Id > ?Param0 AND A.Id < ?Param1) OR A.Age = ?Param2 AND A.Name = ?Param3 OR( A.Price = ?Param4 AND A.Id = ?Param5)

我把BinaryExpressionResolve.cs的where函数开头的修改如下，测试貌似都OK，不知道会不会造成其他BUG，会多点括号，但是对于执行sql语句没有影响：

sqlPack += "(";
            var startIndex = sqlPack.Length;
            SqlBuilderProvider.Where(expression.Left, sqlPack);
            var signIndex = sqlPack.Length;
           
            SqlBuilderProvider.Where(expression.Right, sqlPack);
            
            sqlPack += ")";
            //表达式左侧为bool类型常量且为true时，不进行Sql拼接
            if (!(expression.Left.NodeType == ExpressionType.Constant && expression.Left.ToObject() is bool b && b))
            {
//其他代码，和之前一样
            }

文档

麻烦问下作者有没有相关说明文档，或者博客之类的 相关性参考文档

多条件生成的sql语句不对，麻烦帮忙看看修复

Console.WriteLine("复杂的where：");
var   core = SQLBuilder.SqlBuilder.Select<Models.AAA_TEST_A>().Where(x => x.Id > 1000 || (x.Id < 10 && x.Age.Equals(100)));
 PrintSql(core);
 Console.WriteLine("=====================");

输出的sql语句：
SELECT * FROM [AAA_TEST_A] AS A WHERE A.[Id] > @param0 OR A.[Id] < @param1 AND A.[Age] = @Param2
应该生成的sql语句：
SELECT * FROM [AAA_TEST_A] AS A WHERE A.[Id] > @param0 OR A.[Id] < (@param1 AND A.[Age] = @Param2)

我又来啦。测试生成的Mysql和Oracle的SQL语句，有以下问题，麻烦帮忙看看，谢谢！

生成的Oracle数据库的问题：
1.【BUG】生成的sql语句的表名和列带有引号，放到Oracle连接工具中执行会报错，表名带引号没问题，列不能带引号。例如生成的oracle SQL语句如下：
SELECT * FROM "Base_UserInfo" A WHERE A."userId" > :Param0 OR A."Email" <> :Param1
其中userId,Email都不能带引号，sql语句才能正确执行
2.【BUG】Console测试group by第一个的代码：

Print(
                SqlBuilder.Select<UserInfo>()
                          .Where(o => o.Name == "张强")
                          .GroupBy(u => u.Id),
                "GroupBy分组查询 用法1",
                "GroupBy"
            );

生成的SQL语句：SELECT * FROM AAA_TEST_A_COPY A WHERE A.Name = :Param0 GROUP BY A.Id,
最后多了一个逗号，执行会报错，测试Oracle,Mysql都会有这个问题。

3.【可能不是BUG】group by语句，oracle数据库的group by语句要求比较奇怪，要求select多少个字段，group by 后面必须跟多少个字段。否则会报错。示例：SQLBuilder设置数据库为Oralce生成的group by语句如下：
GroupBy分组查询 用法1
SELECT * FROM AAA_TEST_A_COPY A WHERE A.Name = :Param0 GROUP BY A.Id,A.C_Nvarchar2
[:Param0, 张强]
将这个语句放到oracle连接工具中执行，会报这个错误：ORA-00936: 缺失表达式。把SQL语句修改为：
SELECT A.Id,A.C_Nvarchar2 FROM AAA_TEST_A_COPY A WHERE A.Name = :Param0 GROUP BY A.Id,A.C_Nvarchar2
然后就不报错了
4.【BUG】oracle的insert语句，当插入的对象是数组时，生成的sql语句：
插入数据 用法6
INSERT INTO AAA_TEST_A_COPY (Name,Age,CreatedTime,Price,C_Number,C_Image,C_Char,C_Nvarchar2,C_TimeStamp) SELECT :Param0,:Param1,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM DUAL UNION ALL SELECT :Param2,:Param3,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM DUAL
[:Param0, 张三]
[:Param1, 2]
[:Param2, 张三]
[:Param3, 2]
dapper检测sql语句NG,原因：ORA-00918: 未明确定义列
将sql语句放到Oracle连接工具中执行也一样报这个错误：

生成的Mysql数据库的问题：
【可能不是BUG】
Mysql数据库没有full join关键字，所以用full join生成的mysql sql语句执行会报错。