Dapper simplecrud的使用，dappersimplecrud

Dapper simplecrud的使用，dappersimplecrud

　　為了方便Dapper操作可以使用Dapper的相關擴展dapper simplecrud。

　　1.首先點擊管理NuGet

2.在管理頁面中搜索 Dapper.SimpleCRUD並安裝

然後就可以使用該包下的擴展

經過好久的尋找找到該文章，隨便吐槽一下百度真的十分坑爹，找的無用信息太多

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