All Projects → zqlovejyc → SQLBuilder.Core

zqlovejyc / SQLBuilder.Core

Licence: Apache-2.0 license
.NET Standard 2.1、.NET 5、.NET 6 版本SQLBuilder,Expression表达式转换为SQL语句,支持SqlServer、MySql、Oracle、Sqlite、PostgreSql;基于Dapper实现了不同数据库对应的数据仓储Repository;

Programming Languages

C#
18002 projects

Projects that are alternatives of or similar to SQLBuilder.Core

SqlBatis
A high performance Micro-ORM supporting SQL Server, MySQL, Sqlite etc..
Stars: ✭ 34 (-60%)
Mutual labels:  expression, dapper
Microorm.dapper.repositories
CRUD for Dapper
Stars: ✭ 424 (+398.82%)
Mutual labels:  repository, dapper
dapper-repositories
CRUD for Dapper
Stars: ✭ 523 (+515.29%)
Mutual labels:  repository, dapper
Banana
🍌 The collection of CRUD helpers for Dapper.
Stars: ✭ 61 (-28.24%)
Mutual labels:  repository, dapper
GraphQL.RepoDB
A set of extensions for working with HotChocolate GraphQL and Database access with micro-orms such as RepoDb (or Dapper). This extension pack provides access to key elements such as Selections/Projections, Sort arguments, & Paging arguments in a significantly simplified facade so this logic can be leveraged in the Serivces/Repositories that enca…
Stars: ✭ 25 (-70.59%)
Mutual labels:  repository, dapper
Dapper.AmbientContext
Ambient context implementation for Dapper.NET
Stars: ✭ 31 (-63.53%)
Mutual labels:  repository, dapper
meta-git
git plugin for meta
Stars: ✭ 22 (-74.12%)
Mutual labels:  repository
ToolGood.Algorithm
Support four arithmetic operations, Excel formulas, and support custom parameters. 支持四则运算、Excel公式语法,并支持自定义参数。
Stars: ✭ 77 (-9.41%)
Mutual labels:  expression
VSNancyDemo
A small demo web app (api) on .NET Core, NancyFX & Dapper
Stars: ✭ 17 (-80%)
Mutual labels:  dapper
mmd-gitlab-backuper
a package to backup from all projects that you have on gitlab
Stars: ✭ 27 (-68.24%)
Mutual labels:  repository
docker-debian-repository
A local repository for publishing deb files for use with apt.
Stars: ✭ 49 (-42.35%)
Mutual labels:  repository
cranlike
Manage files in a CRAN-like repository
Stars: ✭ 24 (-71.76%)
Mutual labels:  repository
eyepetizer kotlin
一款仿开眼短视频App,分别采用MVP、MVVM两种模式实现。一、组件化 + Kotlin + MVP + RxJava + Retrofit + OkHttp 二、组件化 + Kotlin + MVVM + LiveData + DataBinding + Coroutines + RxJava + Retrofit + OkHttp
Stars: ✭ 83 (-2.35%)
Mutual labels:  repository
xoai
OAI-PMH Java Toolkit
Stars: ✭ 28 (-67.06%)
Mutual labels:  repository
laravository
Simplified Repository pattern implementation in Laravel
Stars: ✭ 14 (-83.53%)
Mutual labels:  repository
color-math
Expressions to manipulate colors.
Stars: ✭ 18 (-78.82%)
Mutual labels:  expression
web-fonts-repository
A simple webfont hosting. Google Fonts alternative for your own fonts.
Stars: ✭ 99 (+16.47%)
Mutual labels:  repository
QuerySpecification
Abstract package for building query specifications in your domain model.
Stars: ✭ 18 (-78.82%)
Mutual labels:  repository
gsrd
GitHub Starred Repos Downloader
Stars: ✭ 23 (-72.94%)
Mutual labels:  repository
github-interact-cli
🎩 Interact with GItHub right inside your terminal
Stars: ✭ 43 (-49.41%)
Mutual labels:  repository

star fork GitHub stars GitHub forks GitHub license nuget

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

🌭 开源地址

🥥 框架扩展包

包类型 名称 版本 描述
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扩展包
nuget Zq.SQLBuilder.Core.Diagnostics nuget SQLBuilder.Core 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注入

根据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

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].