mrhanqr/sikiro.dapperlambdaextension.mssql

This is an lambda extension of dapper, Chain style makes developers more elegant and intuitive.

Sikiro.DapperLambdaExtension.MsSql 中文

This is an lambda extension of dapper, Chain style makes developers more elegant and intuitive.

Getting Started

Nuget

You can run the following command to install the Sikiro.DapperLambdaExtension.MsSql in your project。

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

Defining User Entity

[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",
});

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

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

Delete according to the condition

con.CommandSet<SysUser>().Where(a => a.Email == "[email protected]").Delete()

QUERY

GET

Get the first data by filtering condition

con.QuerySet<SysUser>().Where(a => a.Email == "[email protected]").Get()

TOLIST

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

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

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]" });

ExpressionBuilder

var where = ExpressionBuilder.Init<SysUser>();

if (string.IsNullOrWhiteSpace(param.Email))
    where = where.And(a => a.Email == "[email protected]");

if (string.IsNullOrWhiteSpace(param.Mobile))
    where = where.And(a => a.Mobile == "18988565556");

con.QuerySet<SysUser>().Where(where).OrderBy(b => b.Email).Top(10).Select(a => a.Email).ToList();

Transaction

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",
    });
});

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

Others

In addition to the above functions, there are aggregated queries.Such as Count、Sum、Exists

End

If you have good suggestions, please feel free to mention to me.