# DBUtil

**Repository Path**: jackletter/DBUtil

## Basic Information

- **Project Name**: DBUtil
- **Description**: 致力于提供一个快速的数据库操作工具，支持数据库有：SqlServer、Mysql、Postgresql、oracle、sqlit。运行环境：.net8.0
- **Primary Language**: C#
- **License**: MIT
- **Default Branch**: develop
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 14
- **Forks**: 11
- **Created**: 2020-06-03
- **Last Updated**: 2025-09-09

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# DBUtil

一款轻量化操作db的类库.

特点: 类DBHelper设计, 比dapper略重, 比EntityFramework/freesql/sqlsugar轻.

**功能列表:**
- 基础CURD(支持 lambda 解析);
- 创新的sql片段生成, 助力复杂sql拼接;
- 丝滑事务管理;
- 分布式锁;
- 树形查询;
- 强大的json支持;
- 元数据管理;

**更多功能介绍:** https://gitee.com/jackletter/DBUtil/tree/develop/docs

## 1. 安装包(以mysql为例)
```
dotnet add package DBUtil
dotnet add package DBUtil.Provider.MySql
```

## 2. 创建DBAccess
```csharp
using DBUtil;

//全局单例, 不要每次访问数据库都创建一个
var db = DBFactory.CreateDB("MySql", "Server=127.0.0.1;Database=test;Uid=root;Pwd=123456;AllowLoadLocalInfile=true;SslMode=none;AllowPublicKeyRetrieval=True;Charset=utf8mb4;");
```

## 3. 插入数据
**无实体插入:**
```csharp
var insert = db.Insert("t_user", [
        new Dictionary<string, object> {{ "name", "tom" },{ "age",20}},
        new Dictionary<string, object> {{ "name", "lisa" },{ "age",18}},
        new Dictionary<string, object> {{ "name", "jim" },{ "age",18}},
    ]);
var sql = insert.ToSql();
//insert into `t_user`(`name`,`age`) values
//    ('tom',20),
//    ('lisa',18),
//    ('jim',18);
```

**有实体插入:**
```csharp
var insert = db.Insert<PersonEntity>().SetEntity([
				new PersonEntity {Name="jack",Age=18,CreateTime=DateTime.Now,Sex=EnumSex.Male },
				new PersonEntity {Name="tom",Age=20,CreateTime=DateTime.Now,Sex=EnumSex.Male },
			]);
//生成sql: 返回插入的数据
var sql = insert.ToSql(EnumInsertToSql.ExecuteInserted);
//insert into `t_person`(`name`,`Age`,`Sex`,`CreateTime`,`UpdateTime`) values
//    ('jack',18,0,'2025-08-11 15:01:14.463739',null),
//    ('tom',20,0,'2025-08-11 15:01:14.46382',null);
//select `id` `Id`,`name` `Name`,`Age`,`Sex`,`CreateTime`,`UpdateTime` from `t_person` where `id`>=last_insert_id() order by `id` limit 2;	
```

## 4. 更新数据
**无实体更新:**
```csharp
var update = db.Update("t_user").WhereSeg<int>(id => id == 1)
    .SetColumn("name", "tom")
    .SetColumn("remark", "test-remark")
    .SetColumn("age", 20);
var sql = update.ToSql();
//sql:
//update `t_user` set 
//    `name` = 'tom',
//    `remark` = 'test-remark',
//    `age` = 20
//where `id` = 1;
```

**有实体更新**
```csharp
var update = db.Update<PersonEntity>()
    .SetColumn("name", "tom")
    .SetColumn(i => i.Age, i => i.Age + 1)
    .SetColumn(i => i.Sex, EnumSex.Male)
    .Where(i => i.Id == 1);
var sql = update.ToSql();
//sql:
//update `t_person` set
//    name = 'tom',
//    Age = `Age` + 1,
//    Sex = 0
//where `id` = 1;
```

## 5. 删除数据
**无实体删除:**
```csharp
var delete = db.Delete()
    .WhereSeg<int>(id => id == 1)
    .Where("active=0")
    .AsTable("t_user");

//生成sql: 返回影响的行数
var sql = delete.ToSql();
//sql:delete from `t_user` where (`id` = 1) and (active=0);
```

**有实体删除:**
```csharp
var sql = db.Delete<PersonEntity>().Where(i => i.Id == 1).ToSql();
//sql: delete from `t_person` where `id` = 1;
```


## 6. 查询数据
```csharp
//多种快速查询形式
var dt = await db.SelectDataTableAsync("select id,name,age from t_user limit 10");
var users2 = await db.SelectModelListAsync<UserEntity>("select id,name,age from t_user where age>@age", [db.CreatePara("age", 18)]);
var dics = await db.SelectDictionaryListAsync("select id,name,age from t_user limit 10");

var person = await db.Select<PersonEntity>().Where(i => i.Id == 1).FirstAsync();
var sql3 = db.Select<PersonEntity>().GroupBy(i => i.Age).Having(i => i.Key > 18).ToSqlList(i => new
{
    Age = i.Key,
    Count = i.Length,
    Names = i.Join(i => i.Name, ","),
});
//sql:
//select t.`Age`,count(1) `Count`,group_concat(t.`name2` separator ',') `Names`
//from `t_person` t
//group by t.`Age`
//having t.`Age` > 18;

//db.Select<PersonEntity>().LeftJoin<UserEntity>((i, j) => i.Id == j.Id);
```

## 7. 事务管理
```csharp
await db.RunInTransactionAsync(async () =>
{
	//do some thing
	//允许再次开启
	await db.RunInTransactionAsync(async () =>
	{
		//do some thing
	});
});
```

## 8. 分布式锁
```csharp
await db.RunInLockAsync("test.lockstr", async () =>
{
    //...
    //so easy
});
```

## 9. id和流水号生成
```csharp
var id = await db.NewIdAsync("t_user", "id");
var sno = await db.NewSNOAsync("t_user", "sno", SerialFormat.CreateFast("Test_User_"));
//sno可能是: Test_User_20250816000001
```

## 10. bulkcopy
```csharp
DataTable dt=null;//自行准备数据, 可以是百万级甚至以上
await db.BulkCopyAsync(dt, "t_user");
```

## 11. 树形查询(CTE)
```csharp
//AreaEntity上可能需要声明哪个是 ParentId 列, 哪个是 Children 列
var tree = await db.SelectTree<AreaEntity>()
    .Where(i => i.Name == "郑州")
    .SetSpreedMode(DBUtil.EnumTreeSpreedMode.Both)
    .ToListAsync();
```

## 12. 不支持的功能
### 12.1 CodeFirst
此功能不在计划中, 出于对数据的尊重和严谨, 不会提供根据c#的实体定义去修改表结构.

### 12.2 aop
对于aop暂时没有好的规划, 不过支持 sql 执行的监控.

### 12.3 一对多、多对多关系配置
此功能不在计划中, 为了尽量让Entity保持简洁.