DBUtil.Provider.MySql 8.0.0-preview2

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

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. 插入数据

无实体插入:

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);

有实体插入:

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. 更新数据

无实体更新:

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;

有实体更新

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. 删除数据

无实体删除:

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);

有实体删除:

var sql = db.Delete<PersonEntity>().Where(i => i.Id == 1).ToSql();
//sql: delete from `t_person` where `id` = 1;

6. 查询数据

//多种快速查询形式
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. 事务管理

await db.RunInTransactionAsync(async () =>
{
	//do some thing
	//允许再次开启
	await db.RunInTransactionAsync(async () =>
	{
		//do some thing
	});
});

8. 分布式锁

await db.RunInLockAsync("test.lockstr", async () =>
{
    //...
    //so easy
});

9. id和流水号生成

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

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

11. 树形查询(CTE)

//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保持简洁.