Dapper simplecrud的使用

　　为了方便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");