Dapper.LiteSql提供的功能
- 数据插入、更新、批量插入、批量更新,支持实体类、实体类集合,不用拼SQL。
- 分页查询。
- 数据库字段名和实体类属性名不一致的映射。
- 使用Lambda表达式查询。
- 分库分表。
- 统一不同数据库的参数化查询SQL。
- 拼接子查询。
- 数据库连接池。
Dapper.LiteSql支持的数据库
- Oracle、MSSQL、MySQL、PostgreSQL、SQLite、Access
- 任意ADO.NET支持的数据库(自己实现IProvider接口,仅需写130行左右的代码)
查询示例
int? status = 0;
string remark = "测试";
DateTime? startTime = null;
DateTime? endTime = DateTime.Now;
var session = LiteSqlFactory.GetSession();
session.OnExecuting = (s, p) => Console.WriteLine(s);
ISqlString sql = session.CreateSql(@"
select t.*, u.real_name as OrderUserRealName
from bs_order t
left join sys_user u on t.order_userid=u.id
where 1=1")
.Append(" and t.status=@status", status);
.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like @remark", "%" + remark + "%");
.AppendIf(startTime.HasValue, " and t.order_time >= @startTime ", startTime);
.AppendIf(endTime.HasValue, " and t.order_time <= @endTime", endTime);
.Append(" order by t.order_time desc, t.id asc ");
long total = sql.QueryCount();
List<BsOrder> list = sql.QueryPage<BsOrder>(null, pageModel.PageSize, pageModel.CurrentPage);
Lambda表达式和原生SQL混写
DateTime? startTime = null;
var session = LiteSqlFactory.GetSession();
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
List<SysUser> list = session.Queryable<SysUser>() //Lambda写法
//拼SQL写法
.Append<SysUser>(@" where t.create_userid = @CreateUserId
and t.password like @Password
and t.id in @Ids",
new
{
CreateUserId = "1",
Password = "%345%",
Ids = session.ForList(new List<int> { 1, 2, 9, 10, 11 })
})
.Where(t => !t.UserName.Contains("管理员")) //Lambda写法
.Append<SysUser>(@" and t.create_time >= @StartTime", new { StartTime = new DateTime(2020, 1, 1) }) //拼SQL写法
.Where<SysUser>(t => t.Id <= 20) //Lambda写法
.AppendIf(startTime.HasValue, " and t.create_time >= @StartTime ", new { StartTime = startTime }) //拼SQL写法
.Append(" and t.create_time <= @EndTime ", new { EndTime = new DateTime(2022, 8, 1) }) //拼SQL写法
.QueryList<SysUser>(); //如果上一句是拼SQL写法,就用QueryList
//.ToList(); //如果上一句是Lambda写法,就用ToList
long id = session.Queryable<SysUser>().Where(t => t.Id == 1).First().Id;
Assert.IsTrue(id == 1);
foreach (SysUser item in list)
{
Console.WriteLine(ModelToStringUtil.ToString(item));
}
Assert.IsTrue(list.Count > 0);
查询示例2:
DateTime? startTime = null;
var session = LiteSqlFactory.GetSession();
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
List<SysUser> list = session.CreateSql(@"
select * from sys_user t where t.id <= @Id", new { Id = 20 })
.Append(@" and t.create_userid = @CreateUserId
and t.password like @Password
and t.id in @Ids",
new
{
CreateUserId = "1",
Password = "%345%",
Ids = session.ForList(new List<int> { 1, 2, 9, 10, 11 })
})
.AppendIf(startTime.HasValue, " and t.create_time >= @StartTime ", new { StartTime = startTime })
.Append(" and t.create_time <= @EndTime ", new { EndTime = new DateTime(2022, 8, 1) })
.QueryList<SysUser>();
long id = session.CreateSql("select id from sys_user where id=@Id", new { Id = 1 })
.QuerySingle<long>();
Assert.IsTrue(id == 1);
foreach (SysUser item in list)
{
Console.WriteLine(ModelToStringUtil.ToString(item));
}
Assert.IsTrue(list.Count > 0);
附:分组统计查询、查询部分字段
var session = LiteSqlFactory.GetSession();
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
List<SysUser> list = session.Queryable<SysUser>(
t => new
{
t.RealName,
t.CreateUserid
})
.Select("count(id) as Count")
.Where(t => t.Id >= 0)
.GroupBy<SysUser>("t.real_name, t.create_userid")
.Having<SysUser>("real_name like @Name1 or real_name like @Name2", new
{
Name1 = "%管理员%",
Name2 = "%测试%"
})
.ToList();
foreach (SysUser item in list)
{
Console.WriteLine(ModelToStringUtil.ToString(item));
}
Assert.IsTrue(list.Count > 0);
子查询
var session = LiteSqlFactory.GetSession();
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
List<SysUser> list = session.CreateSql<SysUser>() //说明:CreateSql不传参数则创建一个空SQL
.Select(t => new
{
t.RealName,
t.CreateUserid
})
.Select(session.CreateSql(@"(
select count(1)
from bs_order o
where o.order_userid = t.id
and o.status = @Status
) as OrderCount", new { Status = 0 }))
.Where(t => t.Id >= 0)
.ToList();
foreach (SysUser item in list)
{
Console.WriteLine(ModelToStringUtil.ToString(item));
}
Assert.IsTrue(list.Count > 0);
上面的查询也可以这样写:
var session = LiteSqlFactory.GetSession();
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
var subSql = session.Queryable<BsOrder>(o => "count(1)")
.WhereJoin<SysUser>((o, t) => o.OrderUserid == t.Id)
.Where<BsOrder>(o => o.Status == 0);
List<SysUser> list = session.Queryable<SysUser>(
t => new
{
t.RealName,
t.CreateUserid
})
.Select("({0}) as OrderCount", subSql)
.Where(t => t.Id >= 0)
.ToList();
foreach (SysUser item in list)
{
Console.WriteLine(ModelToStringUtil.ToString(item));
}
Assert.IsTrue(list.Count > 0);
子查询2
var session = LiteSqlFactory.GetSession();
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
var subSql = session.Queryable<SysUser>(t => new { t.Id }).Where(t => !t.RealName.Contains("管理员"));
var subSql2 = session.Queryable<SysUser>(t => new { t.Id }).Where(t => t.Id <= 20);
var sql = session.Queryable<SysUser>()
.Where(t => t.Password.Contains("345"))
.Append(" and id in ", subSql)
.Append<SysUser>(@" and t.create_time >= @StartTime", new { StartTime = new DateTime(2020, 1, 1) })
.Append<SysUser>(" and id in ", subSql2)
.Where(t => t.Password.Contains("234"));
var sql2 = session.Queryable<SysUser>().Where(t => t.RealName.Contains("管理员"));
sql.Append(" union all ", sql2);
List<SysUser> list = sql.QueryList<SysUser>();
foreach (SysUser item in list)
{
Console.WriteLine(ModelToStringUtil.ToString(item));
}
Assert.IsTrue(list.Count > 0);
Assert.IsTrue(list.Count(t => t.RealName.Contains("管理员")) > 0);
Assert.IsTrue(list.Count(t => t.Id > 20) == 0);
直接使用Dapper查询
var session = LiteSqlFactory.GetSession();
session.SetTypeMap<SysUser>(); //设置数据库字段名与实体类属性名映射
using (var conn = session.GetConnection()) //此处从连接池获取连接,用完一定要释放,也可以不使用连接池,直接new MySqlConnection
{
DynamicParameters dynamicParameters = new DynamicParameters();
dynamicParameters.Add("id", 20);
List<SysUser> list = conn.Conn.Query<SysUser>(@"
select *
from sys_user
where id < @id", dynamicParameters).ToList();
foreach (SysUser item in list)
{
Console.WriteLine(ModelToStringUtil.ToString(item));
Assert.IsTrue(!string.IsNullOrWhiteSpace(item.UserName));
}
}
附:工作中的一个示例
久而久之,我也不想写SQL了,尽量不写SQL
var query = _session.Queryable<KpTaskRun>()
.Select<KpTask>(u => u.TaskName, t => t.TaskName) //取KpTask表的TaskName字段赋值给KpTaskRun实体类的扩展字段TaskName
.LeftJoin<KpTask>((t, u) => t.TaskId == u.Id); //关联KpTask表
if (_date != null)
{
query = query.Where(t => t.RunTime >= _date.Value.Date && t.RunTime < _date.Value.Date.AddDays(1));
}
_total = (int)(await query.CountAsync());
_list = await query.OrderByDescending(t => t.RunTime).ToPageListAsync(_page, _pageSize);
foreach (KpTaskRun kpTaskRun in _list)
{
kpTaskRun.RunResultCount = (int)(await _session.Queryable<KpTaskResult>().Where(t => t.TaskRunId == kpTaskRun.Id).CountAsync());
}
更多示例
https://gitee.com/s0611163/Dapper.LiteSql/blob/main/README.md
https://gitee.com/s0611163/Dapper.LiteSql/wikis
NuGet地址:
https://www.nuget.org/packages/Dapper.LiteSql
如有问题加QQ群:497956447。
Dapper.LiteSql源码地址:
https://gitee.com/s0611163/Dapper.LiteSql
配套实体类生成器地址:
https://gitee.com/s0611163/ModelGenerator
Dapper.LiteSql支持ClickHouse
https://gitee.com/s0611163/ClickHouseTest
查询ClickHouse数据库实战代码1:
DateTime dayStart = day.Date.AddHours(7);
DateTime dayEnd = day.Date.AddHours(9).AddSeconds(-1);
List<Vehicle> list = session.CreateSql<Vehicle>(@"
select distinct t.plate_no, t.tollgate_name3, t.pass_time
from shiny.vehicle_full_replica_dist t
").Where(t => t.PlateNo != "无车牌")
.Where(t => t.PassTime >= startTime && t.PassTime <= endTime)
.Where(t => t.PassTime >= dayStart && t.PassTime <= dayEnd)
.Append("and (")
.Append(@"(t.""tollgate_name3"" like @A1 and t.""tollgate_name3"" like @B1)", new { A1 = "%六安路%", B1 = "%寿春路%" })
.Append(@"or (t.""tollgate_name3"" like @A2)", new { A2 = "%龚湾路%" })
.Append(@"or (t.""tollgate_name3"" like @A3 and t.""tollgate_name3"" like @B3)", new { A3 = "%大龙山路%", B3 = "%望江西路%" })
.Append(@"or (t.""tollgate_name3"" like @A4 and t.""tollgate_name3"" like @B4)", new { A4 = "%大龙山路%", B4 = "%云飞路%" })
.Append(@"or (t.""tollgate_name3"" like @A5 and t.""tollgate_name3"" like @B5)", new { A5 = "%习友路%", B5 = "%徽毫路%" })
.Append(@"or (t.""tollgate_name3"" like @A6 and t.""tollgate_name3"" like @B6)", new { A6 = "%祁门路%", B6 = "%徽毫路%" })
.Append(@"or (t.""tollgate_name3"" like @A7 and t.""tollgate_name3"" like @B7)", new { A7 = "%长沙路%", B7 = "%西藏路%" })
.Append(@"or (t.""tollgate_name3"" like @A8 and t.""tollgate_name3"" like @B8)", new { A8 = "%洞庭湖路%", B8 = "%西藏路%" })
.Append(@"or (t.""tollgate_name3"" like @A9 and t.""tollgate_name3"" like @B9)", new { A9 = "%洞庭湖路%", B9 = "%玉龙路%" })
.Append(@"or (t.""tollgate_name3"" like @A10 and t.""tollgate_name3"" like @B10)", new { A10 = "%长沙路%", B10 = "%玉龙路%" })
.Append(")")
.QueryList<Vehicle>();
查询ClickHouse数据库实战代码2:
List<Vehicle> list = session.CreateSql<Vehicle>(@"
select plate_no, pass_date as pass_time, max(TravelCount) as TravelCount
from (
select plate_no, toDate(pass_time) as pass_date, tollgate_name3, count(*) as TravelCount
from (
select distinct t.plate_no, t.pass_time, t.tollgate_name3
from shiny.vehicle_full_replica_dist t
").Where(t => t.PlateNo != "无车牌")
.Where(t => t.PassTime >= startTime && t.PassTime <= endTime)
.Where(t => plateNoList.Contains(t.PlateNo))
.Where(@"(
(formatDateTime(t.pass_time ,'%H:%M:%S') >= '07:00:00' and formatDateTime(t.pass_time ,'%H:%M:%S') <= '08:59:59') or
(formatDateTime(t.pass_time ,'%H:%M:%S') >= '14:00:00' and formatDateTime(t.pass_time ,'%H:%M:%S') <= '20:59:59')
)")
.Append(@")")
.GroupBy("plate_no, pass_date, tollgate_name3")
.Append(@")
group by plate_no, pass_time
")
.QueryList<Vehicle>();