编写自己的dapper lambda扩展-使用篇 - 好文

前言

这是针对dapper的一个扩展，支持lambda表达式的写法，链式风格让开发者使用起来更加优雅、直观。现在暂时只有MsSql的扩展，也没有实现事务的写法，将会在后续的版本补充。

这是个人业余的开源小项目，如果大家有更好的实现方式和好的建议欢迎拍砖

本项目已经在github上开源了：Sikiro.DapperLambdaExtension.MsSql
<https://github.com/SkyChenSky/Sikiro.DapperLambdaExtension.MsSql>

去年写了《整理自己的.net工具库》 <https://www.cnblogs.com/skychen1218/p/7298332.html>
，里面提供的源码重新发布到了github并用新的项目名Sikiro.Tookits
<https://github.com/SkyChenSky/Sikiro.Tookits>

这两个项目都发布到Nuget上了，可以在Nuget搜索Sikiro可以全部查看到

另外该项目会用到一些表达式树的知识，如果有兴趣的朋友可以先去了解，我之前也写过一篇简单的文章《表达式树的解析.》
<https://www.cnblogs.com/skychen1218/p/5730316.html>

下面是简单的使用介绍

开始

Nuget

你可以运行以下下命令在你的项目中安装 Sikiro.DapperLambdaExtension.MsSql。
PM> Install-Package Sikiro.DapperLambdaExtension.MsSql
SqlConnection
var con = new SqlConnection("Data Source=192.168.13.46;Initial
Catalog=SkyChen;Persist Security Info=True;User ID=sa;Password=123456789");
定义User
[Table("SYS_USER")] public class SysUser { /// <summary> /// 主键 /// </summary>
[Key] [Required] [StringLength(32)] [Display(Name = "主键")]
[Column("SYS_USERID")] public string SysUserid { get; set; } /// <summary> ///
创建时间 /// </summary> [Required] [Display(Name = "创建时间")]
[Column("CREATE_DATETIME")] public DateTime CreateDatetime { get; set; } ///
<summary> /// 邮箱 /// </summary> [Required] [StringLength(32)] [Display(Name =
"邮箱")] [Column("EMAIL")] public string Email { get; set; } /// <summary> ///
USER_STATUS /// </summary> [Required] [Display(Name = "USER_STATUS")]
[Column("USER_STATUS")] public int UserStatus { get; set; } }
Insert
con.CommandSet<SysUser>().Insert(new SysUser { CreateDatetime = DateTime.Now,
Email = "[email protected]", SysUserid = Guid.NewGuid().ToString("N"), UserName
= "chengong", });
当不存在某条件记录Insert
con.CommandSet<SysUser>().IfNotExists(a => a.Email ==
"[email protected]").Insert(new SysUser { CreateDatetime = DateTime.Now, Email =
"[email protected]", SysUserid = Guid.NewGuid().ToString("N"), UserName =
"chengong", });
UPDATE

您可以根据某个条件把指定字段更新
con.CommandSet<SysUser>().Where(a => a.Email == "[email protected]").Update(a
=> new SysUser { Email = "[email protected]" });
也可以根据主键来更新整个实体字段信息
User.Email = "[email protected]"; condb.CommandSet<SysUser>().Update(User);
DELETE

您可以根据条件来删除数据
con.CommandSet<SysUser>().Where(a => a.Email == "[email protected]").Delete()
QUERY

GET

获取过滤条件的一条数据（第一条）
con.QuerySet<SysUser>().Where(a => a.Email == "[email protected]").Get()
TOLIST

当然我们也可以查询出符合条件的数据集
con.QuerySet<SysUser>().Where(a => a.Email == "[email protected]").OrderBy(b =>
b.Email).Top(10).Select(a => a.Email).ToList();
PAGELIST

还有分页
con.QuerySet<SysUser>().Where(a => a.Email == "[email protected]") .OrderBy(a
=> a.CreateDatetime) .Select(a => new SysUser { Email = a.Email, CreateDatetime
= a.CreateDatetime, SysUserid = a.SysUserid }) .PageList(1, 10);
UPDATESELECT

先更新再把结果查询出来
con.QuerySet<SysUser>().Where(a => a.Email == "[email protected]") .OrderBy(a
=> a.CreateDatetime) .Select(a => new SysUser { Email = a.Email })
.UpdateSelect(a => new SysUser { Email = "[email protected]" });
事务功能
con.Transaction(tc => { var sysUserid = tc.QuerySet<SysUser>().Where(a =>
a.Email == "[email protected]").Select(a => a.SysUserid).Get();
tc.CommandSet<SysUser>().Where(a => a.SysUserid == sysUserid).Delete();
tc.CommandSet<SysUser>().Insert(new SysUser { CreateDatetime = DateTime.Now,
Email = "[email protected]", Mobile = "13536059332", RealName = "大笨贞", SysUserid
= Guid.NewGuid().ToString("N"), UserName = "fengshuzhen", UserStatus = 1,
UserType = 1, Password = "asdasdad" }); });
最后来一个完整的DEMO
using (var con = new SqlConnection("Data Source=192.168.13.46;Initial
Catalog=SkyChen;Persist Security Info=True;User ID=sa;Password=123456789")) {
con.CommandSet<SysUser>().Insert(new SysUser { CreateDatetime = DateTime.Now,
Email = "[email protected]", SysUserid = Guid.NewGuid().ToString("N"), UserName
= "chengong", }); var model = con.QuerySet<SysUser>().Where(a => a.Email ==
"[email protected]").Get(); con.CommandSet<SysUser>().Where(a => a.SysUserid ==
model.SysUserid) .Update(a => new SysUser { Email = "[email protected]" });
con.CommandSet<SysUser>().Where(a => a.SysUserid == model.SysUserid).Delete(); }
其他

除了简单的CURD还有Count、Sum、Exists

结束

第一个版本有未完善的地方，如果大家有很好的建议欢迎随时向我提，希望得到大家的建议后能良好的改善升级

热门工具换一换