[Table("Users")]//真实表名 publicclass User { [Key] publicint UserId { get; set; } [Column("strFirstName"] //真实列名 publicstring FirstName { get; set; }//列别名 publicstring LastName { get; set; } publicint Age { get; set; } } var user = connection.Get<User>(1);
改动后该查询相当于sql
Select UserId, strFirstName as FirstName, LastName, Age from[Users]where UserId =@UserID
2.GetList方法
publicstatic IEnumerable<T> GetList<T>(this IDbConnection connection)
publicclass User { publicint Id { get; set; } publicstring Name { get; set; } publicint Age { get; set; } }
查询全部
var user = connection.GetList<User>();
相当于Sql
Select*from[User]
使用条件实体查询
publicclass User
{
publicint Id { get; set; }
publicstring Name { get; set; }
publicint Age { get; set; }
}
var user = connection.GetList<User>(new { Age = 10 });
Select*from[User]where Age =@Age
//使用字符串条件查询
publicclass User
{
publicint Id { get; set; }
publicstring Name { get; set; }
publicint Age { get; set; }
}
var user = connection.GetList<User>("where age = 10 or Name like '%Smith%'");
//相当于SQL
Select*from[User]where age =10or Name like'%Smith%'
//分页查询:
publicstatic IEnumerable<T> GetListPaged<T>(this IDbConnection connection, int pageNumber, int rowsPerPage, string conditions, stringorderby)
publicclass User
{
publicint Id { get; set; }
publicstring Name { get; set; }
publicint 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)
//插入方法
publicstaticint Insert(this IDbConnection connection, object entityToInsert)
[Table("Users")]
publicclass User
{
[Key]
publicint UserId { get; set; }
publicstring FirstName { get; set; }
publicstring LastName { get; set; }
publicint Age { get; set; }
//Additional properties not in database [Editable(false)]
publicstring FullName { get { returnstring.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
Insertinto[Users] (FirstName, LastName, Age) VALUES (@FirstName, @LastName, @Age)
//更新方法
[Table("Users")]public class User{
[Key]publicint UserId { get; set; }
[Column("strFirstName")]public string FirstName { get; set; }
public string LastName { get; set; }
publicint Age { get; set; }
//Additional properties notindatabase[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 intDelete<T>(this IDbConnection connection, int Id)
public class User{
publicint Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
publicint Age { get; set; }
}
connection.Delete<User>(newid);
或
public static intDelete<T>(this IDbConnection connection, T entityToDelete)
public class User{
publicint Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
publicint Age { get; set; }
}
connection.Delete(entity);
相当于SQl
DeleteFrom[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 = "")
varcount= connection.RecordCount<User>("Where age >20");