SkillAgentSearch skills...

Dapper.Lite

一款Dapper扩展，单表查询和SQL拼接查询条件支持Lambda表达式。支持Oracle、MSSQL、MySQL、PostgreSQL、SQLite、Access、ClickHouse等数据库。如有问题加QQ群：497956447。

GenerateConvertImprove

Install / Use

/learn @0611163/Dapper.Lite
About this skill

Quality Score

0/100

Supported Platforms

Universal

README

Dapper.Lite

简介

一款Dapper扩展，同时支持SQL拼接和简单的Lambda表达式。支持Oracle、MSSQL、MySQL、PostgreSQL、SQLite、Access等数据库。

经典示例

DateTime? startTime = null;

var session = DapperLiteFactory.GetSession();

session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL

List<SysUser> list = session.Sql(@"
    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.Sql("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);

经典示例2

var session = DapperLiteFactory.GetSession();
List<BsOrder> list = session
    .Sql<BsOrder>(@"
        select o.*, u.user_name as OrderUserName, u.real_name as OrderUserRealName 
        from bs_order o
        left join sys_user u on u.id=o.order_userid")
    .Where(o => o.Status == 0
        && o.Remark.Contains("订单")
        && o.OrderTime >= new DateTime(2010, 1, 1)
        && o.OrderTime < DateTime.Now.Date.AddDays(1))
    .Where<SysUser>(u => new long[] { 8, 9, 10 }.Contains(u.Id))
    .Append("order by o.order_time desc, o.id asc")
    .ToList();

特点

  1. 支持Oracle、SQL Server、MySQL、PostgreSQL、SQLite五种数据库；另外只要ADO.NET支持的数据库，都可以很方便地通过实现IProvider接口支持，仅需写150行左右的代码
  2. 有配套的Model生成器
  3. 数据插入、更新、批量插入、批量更新，支持实体类、实体类集合，无需拼SQL；删除操作支持根据主键或查询条件删除；增删改支持联合主键
  4. 查询以原生SQL为主，查询Where条件可以拼接Lambda表达式
  5. 支持参数化查询，统一不同数据库的参数化查询SQL
  6. 支持连接多个数据源
  7. 支持手动分表
  8. 单表查询支持Lambda表达式

优点

  1. 比较简单，学习成本低
  2. 查询以原生SQL为主，Lambda表达式辅助
  3. 代码量仅4000多行，更容易修改和掌控代码质量

缺点

  1. 对Lambda表达式的支持比较弱
  2. 复杂查询不支持Lambda表达式(连表查询、子查询、分组统计查询、嵌套查询等不支持Lambda表达式写法)

建议

  1. 单表查询可以使用Lambda表达式
  2. 复杂查询建议使用原生SQL
  3. 如果出现不支持的Lambda表达式写法，请使用原生SQL替代

开发环境

  1. VS2022
  2. 目标框架：net461;netstandard2.0;net5.0
  3. 测试工程使用.NET Framework 4.5.2

配套Model生成器地址：

https://gitee.com/s0611163/ModelGenerator

支持 ClickHouse 数据库

https://gitee.com/s0611163/ClickHouseTest

这是一个示例，只要ADO.NET支持的数据库，您都可以通过实现IProvider接口尝试支持

.NET 6 环境下测试

https://gitee.com/s0611163/LiteSqlTest

作者邮箱

651029594@qq.com

使用步骤

  1. 安装Dapper.Lite
Install-Package Dapper.Lite -Version 1.8.28
  1. 安装对应的数据库引擎
Install-Package MySql.Data -Version 6.9.12
  1. 实现对应的数据库Provider

注意：各实现方法一定要加上override关键字以重写基类的方法

using Dapper.Lite;
using MySql.Data.MySqlClient;
using System.Data.Common;

namespace DAL
{
    public class MySQLProvider : MySQLProviderBase, IDbProvider
    {
        #region 创建 DbConnection
        public override DbConnection CreateConnection(string connectionString)
        {
            return new MySqlConnection(connectionString);
        }
        #endregion

        #region 生成 DbParameter
        public override DbParameter GetDbParameter(string name, object value)
        {
            return new MySqlParameter(name, value);
        }
        #endregion

    }
}
  1. 定义DapperLiteFactory类
using Dapper.Lite;
using System.Configuration;
using System.Threading.Tasks;

namespace DAL
{
    public class DapperLiteFactory
    {
        #region 变量
        private static IDapperLite _dapperLite = new DapperLite(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(), new MySQLProvider());

        public static IDapperLite Client => _dapperLite;
        #endregion

        #region 获取 IDbSession
        /// <summary>
        /// 获取 IDbSession
        /// </summary>
        /// <param name="splitTableMapping">分表映射</param>
        public static IDbSession GetSession(SplitTableMapping splitTableMapping = null)
        {
            return _dapperLite.GetSession(splitTableMapping);
        }
        #endregion

        #region 获取 IDbSession (异步)
        /// <summary>
        /// 获取 IDbSession (异步)
        /// </summary>
        /// <param name="splitTableMapping">分表映射</param>
        public static async Task<IDbSession> GetSessionAsync(SplitTableMapping splitTableMapping = null)
        {
            return await _dapperLite.GetSessionAsync(splitTableMapping);
        }
        #endregion

    }
}
  1. 依赖注入
var builder = WebApplication.CreateBuilder(args);

var db = new DapperLite(builder.Configuration.GetConnectionString("DefaultConnection"), new MySQLProvider());
var secondDB = new DapperLite<SecondDbFlag>(builder.Configuration.GetConnectionString("SecondConnection"), new MySQLProvider());

// Add services to the container.
// 注册数据库IDapperLite
builder.Services.AddSingleton<IDapperLite>(db);
// 注册第二个数据库IDapperLite
builder.Services.AddSingleton<IDapperLite<SecondDbFlag>>(secondDB);
// 注册数据库DbSession
builder.Services.AddScoped<IDbSession>(serviceProvider =>
{
    return serviceProvider.GetService<IDapperLite>().GetSession();
});
// 注册第二个数据库DbSession
builder.Services.AddScoped<IDbSession<SecondDbFlag>>(serviceProvider =>
{
    return serviceProvider.GetService<IDapperLite<SecondDbFlag>>().GetSession();
});

/// <summary>
/// 第二个数据库标识
/// </summary>
public class SecondDbFlag { }

配套Model生成器

使用Model生成器生成实体类

  1. 实体类放在Models文件夹中
  2. 扩展实体类放在ExtModels文件夹中
  3. 实体类和扩展实体类使用partial修饰，实际上是一个类，放在不同的文件中
  4. 如果需要添加自定义属性，请修改ExtModels，不要修改Models

实体类示例

using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

/// <summary>
/// 订单表
/// </summary>
[Serializable]
[Table("bs_order")]
public partial class BsOrder
{

    /// <summary>
    /// 主键
    /// </summary>
    [Key]
    public string Id { get; set; }

    /// <summary>
    /// 订单时间
    /// </summary>
    [Column("order_time")]
    public DateTime OrderTime { get; set; }

    /// <summary>
    /// 订单金额
    /// </summary>
    public decimal? Amount { get; set; }

    /// <summary>
    /// 下单用户
    /// </summary>
    [Column("order_userid")]
    public long OrderUserid { get; set; }

    /// <summary>
    /// 订单状态(0草稿 1已下单 2已付款 3已发货 4完成)
    /// </summary>
    public int Status { get; set; }

    /// <summary>
    /// 备注
    /// </summary>
    public string Remark { get; set; }

    /// <summary>
    /// 创建者ID
    /// </summary>
    [Column("create_userid")]
    public string CreateUserid { get; set; }

    /// <summary>
    /// 创建时间
    /// </summary>
    [Column("create_time")]
    public DateTime CreateTime { get; set; }

    /// <summary>
    /// 更新者ID
    /// </summary>
    [Column("update_userid")]
    public string UpdateUserid { get; set; }

    /// <summary>
    /// 更新时间
    /// </summary>
    [Column("update_time")]
    public DateTime? UpdateTime { get; set; }

}

修改扩展实体类

  1. 修改扩展实体类，添加自定义属性
  2. 下面的扩展实体类中，查询时OrderUserRealName会被自动填充，查询SQL：select t.*, u.real_name as OrderUserRealName from ......
  3. DetailList不会被自动填充，需要手动查询

扩展实体类示例

/// <summary>
/// 订单表
/// </summary>
public partial class BsOrder
{
    /// <summary>
    /// 订单明细集合
    /// </summary>
    [NotMapped]
    public List<BsOrderDetail> DetailList { get; set; }

    /// <summary>
    /// 下单用户姓名
    /// </summary>
    [NotMapped]
    public string OrderUserRealName { get; set; }

    /// <summary>
    /// 下单用户名
    /// </summary>
    [NotMapped]
    public string OrderUserName { get; set; }
}

增删改查示例

添加

public void Insert(SysUser info)
{
    var session = DapperLiteFactory.GetSession();
    session.Insert(info);  
}

添加并返回ID

public void Insert(SysUser info)
{
    var session = DapperLiteFactory.GetSession();
    long id = session.InsertReturnId(info, "select @@IDENTITY");   
}

批量添加

public void Insert(List<SysUser> list)
{
    var session = DapperLiteFactory.GetSession();
    session.Insert(list);   
}

修改

public void Update(SysUser info)
{
    var session = DapperLiteFactory.GetSession();
    session.Update(info);
}

批量修改

public void Update(List<SysUser> list)
{
    var session = DapperLiteFactory.GetSession();
    session.Update(list);
}

修改时只更新数据有变化的字段

var session = DapperLiteFactory.GetSession();

session.AttachOld(user); //附加更新前的旧数据，只更新数据发生变化的字段，提升更新性能

user.UpdateUserid = "1";
user.Remark = "测试修改用户" + _rnd.Next(1, 100);
user.UpdateTime = DateTime.Now;

session.Update(user);

var session = DapperLiteFactory.GetSession();

session.AttachOld(userList); //附加更新前的旧数据，只更新数据发生变化的字段，提升更新性能

foreach (SysUser user in userList)
{
    user.Remark = "测试修改用户" + _rnd.Next(1, 10000);
    user.UpdateUserid = "1";
    user.UpdateTime = DateTime.Now;
}

session.Update(userList);

删除

public void Delete(string id)
{
    var session = DapperLiteFactory.GetSession();
    session.DeleteById<SysUser>(id);
}

条件删除

var session = DapperLiteFactory.GetSession();
session.Sql("id>@Id", 20).Delete<SysUser>();

var session = DapperLiteFactory.GetSession();
session.Queryable<SysUser>().Where(t => t.Id > 20).Delete();

查询单个记录

public SysUser Get(string id)
{
    var session = DapperLiteFactory.GetSession();
    return session.QueryById<SysUser>(id);
}

var session = DapperLiteFactory.GetSession();
SysUser user = session.Query<SysUser>("select * from sys_user");

简单查询

var session = DapperLiteFactory.GetSession();
string sql = "select * from CARINFO_MERGE";
List<CarinfoMerge> result = session.QueryList<CarinfoMerge>(sql);

条件查询

public List<BsOrder> GetList(int? status, string remark, DateTime? startTime, DateTime? endTime)
{

0611163

View profile

View on GitHub
GitHub Stars13
CategoryData
Updated28d ago
Forks3
0611163/Dapper.Lite

Languages

C#

Security Score

90/100

Audited on Mar 13, 2026

No findings