你的位置:首页 > ASP.net教程

[ASP.net教程]Dapper simplecrud的使用


  为了方便Dapper操作可以使用Dapper的相关扩展dapper simplecrud。

  1.首先点击管理NuGet

然后就可以使用该包下的扩展

经过好久的寻找找到该文章,随便吐槽一下百度真的十分坑爹,找的无用信息太多

https://github.com/ericdc1/Dapper.SimpleCRUD/

为了方便以后查找故对方法进行总结


以下是对方法使用的总结

1.Get方法

public static T Get<T>(this IDbConnection connection, int id)

 首先建立和表相对应的实体类

public class User{  public int Id { get; set; }  public string Name { get; set; }  public int Age { get; set; }}

然后查询至实体

var user = connection.Get<User>(1); 

相当于sql:

Select Id, Name, Age from [User] where Id = 1 

下面我们对实体类进行一下相关改动

[Table("Users")]//真实表名  public class User  {    [Key]    public int UserId { get; set; }    [Column("strFirstName"] //真实列名    public string FirstName { get; set; }//列别名    public string LastName { get; set; }    public int Age { get; set; }  }  var user = connection.Get<User>(1); 

改动后该查询相当于sql

Select UserId, strFirstName as FirstName, LastName, Age from [Users] where UserId = @UserID

2.GetList方法

public static IEnumerable<T> GetList<T>(this IDbConnection connection)

实体:

public class User{  public int Id { get; set; }  public string Name { get; set; }  public int Age { get; set; }}

查询全部

var user = connection.GetList<User>(); 

相当于Sql

Select * from [User]

使用条件实体查询

public class User{  public int Id { get; set; }  public string Name { get; set; }  public int Age { get; set; }}var user = connection.GetList<User>(new { Age = 10 }); 

相当于SQL

Select * from [User] where Age = @Age

使用字符串条件查询

public class User{  public int Id { get; set; }  public string Name { get; set; }  public int Age { get; set; }}var user = connection.GetList<User>("where age = 10 or Name like '%Smith%'"); 

相当于SQL

Select * from [User] where age = 10 or Name like '%Smith%'

分页查询:

public static IEnumerable<T> GetListPaged<T>(this IDbConnection connection, int pageNumber, int rowsPerPage, string conditions, string orderby)
public class User{  public int Id { get; set; }  public string Name { get; set; }  public int Age { get; set; }}var user = connection.GetListPaged<User>(1,10,"where age = 10 or Name like '%Smith%'","Name desc"); 

相当于SQl:SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Name desc) AS PagedNumber, Id, Name, Age FROM [User] where age = 10 or Name like '%Smith%') AS u WHERE PagedNUMBER BETWEEN ((1 - 1) * 10 + 1) AND (1 * 10)



插入方法

public static int Insert(this IDbConnection connection, object entityToInsert)
[Table("Users")]public class User{ [Key] public int UserId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public int Age { get; set; } //Additional properties not in database [Editable(false)] public string FullName { get { return string.Format("{0} {1}", FirstName, LastName); } } public List<User> Friends { get; set; } [ReadOnly(true)] public DateTime CreatedDate { get; set; }}var newId = connection.Insert(new User { FirstName = "User", LastName = "Person", Age = 10 });

相当于SQL
Insert into [Users] (FirstName, LastName, Age) VALUES (@FirstName, @LastName, @Age)

 


更新方法

[Table("Users")]public class User{  [Key]  public int UserId { get; set; }  [Column("strFirstName")]  public string FirstName { get; set; }  public string LastName { get; set; }  public int Age { get; set; }  //Additional properties not in database  [Editable(false)]  public string FullName { get { return string.Format("{0} {1}", FirstName, LastName); } }  public List<User> Friends { get; set; }}connection.Update(entity);

相当于SQL
Update [Users] Set (strFirstName=@FirstName, LastName=@LastName, Age=@Age) Where ID = @ID


删除方法

public static int Delete<T>(this IDbConnection connection, int Id)public class User{  public int Id { get; set; }  public string FirstName { get; set; }  public string LastName { get; set; }  public int Age { get; set; }}connection.Delete<User>(newid);或public static int Delete<T>(this IDbConnection connection, T entityToDelete)public class User{  public int Id { get; set; }  public string FirstName { get; set; }  public string LastName { get; set; }  public int Age { get; set; }}connection.Delete(entity);
相当于SQl
Delete From [User] Where ID = @ID

 


删除多条

1.根据实体删除public static int DeleteList<T>(this IDbConnection connection, object whereConditions, IDbTransaction transaction = null, int? commandTimeout = null)connection.DeleteList<User>(new { Age = 10 });2.根据条件删除public static int RecordCount<T>(this IDbConnection connection, string conditions = "")connection.DeleteList<User>("Where age > 20");

统计条数

public static int RecordCount<T>(this IDbConnection connection, string conditions = "")var count = connection.RecordCount<User>("Where age > 20");