Sikiro.DapperLambdaExtension.MsSql 中文
This is an lambda extension of dapper, Chain style makes developers more elegant and intuitive.
You can run the following command to install the Sikiro.DapperLambdaExtension.MsSql in your project。
PM> Install-Package Sikiro.DapperLambdaExtension.MsSql
var con = new SqlConnection("Data Source=192.168.13.46;Initial Catalog=SkyChen;Persist Security Info=True;User ID=sa;Password=123456789");
[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; }
}
con.CommandSet<SysUser>().Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "[email protected]",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "chengong",
});
If not exists...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 according to the condition part field
con.CommandSet<SysUser>().Where(a => a.Email == "[email protected]").Update(a => new SysUser { Email = "[email protected]" });
You can also update the entity field information based on the primary key
User.Email = "[email protected]";
condb.CommandSet<SysUser>().Update(User);
Delete according to the condition
con.CommandSet<SysUser>().Where(a => a.Email == "[email protected]").Delete()
Get the first data by filtering condition
con.QuerySet<SysUser>().Where(a => a.Email == "[email protected]").Get()
You can also query qualified data list.
con.QuerySet<SysUser>().Where(a => a.Email == "[email protected]").OrderBy(b => b.Email).Top(10).Select(a => a.Email).ToList();
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);
First update then select
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]",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "xiaobenzhen",
});
});
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();
}
In addition to the above functions, there are aggregated queries.Such as Count、Sum、Exists
If you have good suggestions, please feel free to mention to me.