Dapper扩展推荐：Dapper.LiteSql 同时支持原生SQL和Lambda表达式

当前位置:

首页 > temp > C#教程 >

Dapper扩展推荐：Dapper.LiteSql 同时支持原生SQL和Lambda表达式

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

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

出处：https://www.cnblogs.com/s0611163/p/16641136.html

栏目列表