这篇文章主要讲 Dapper Plus,它使用用来操作大数量的一些操作的。比如插入1000条,或者10000条的数据时,再使用Dapper的Execute方法,就会比较慢了。这时候,可以使用Dapper Plus中的方法进行操作,提高速度。
主要包括下面:
使用之前,需要在Nuget中,安装 Z.Dapper.Plus
1. Bulk Insert:批量插入
1.1 Insert Single : 使用Bulk插入单个实体
DapperPlusManager.Entity<Customer>().Table("Customers");
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkInsert(new List<Customer>() { new Customer() { CustomerName = "ExampleBulkInsert", ContactName = "Example Name :" + 1}});
}
1.2 Insert Many :插入多个实体
DapperPlusManager.Entity<Customer>().Table("Customers");
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkInsert(customers);
}
1.3 Insert with relation(One to One)
插入一对一关系的实体
DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkInsert(suppliers).ThenForEach(x => x.Product.SupplierID = x.SupplierID).ThenBulkInsert(x => x.Product);
}
1.4 Insert with relation (One to Many)
插入一对多关系的实体
DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkInsert(suppliers).ThenForEach(x => x.Products.ForEach(y => y.SupplierID = x.SupplierID)).ThenBulkInsert(x => x.Products);
}
2. Bulk Update
2.1 Update Single 和 Update Many
DapperPlusManager.Entity<Customer>().Table("Customers");
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkUpdate(customers);
}
2.2 Update with relation(One to One) 和 Update with relation(One to Many)
更新一对一关系的实体
DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkUpdate(suppliers, x => x.Product);
}
更新一对多关系的实体
DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkUpdate(suppliers, x => x.Products);
}
3. Bulk Delete
3.1 Delete Single
删除单个实体
DapperPlusManager.Entity<Customer>().Table("Customers").Key("CustomerID");
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkDelete(connection.Query<Customer>("Select * FROM CUSTOMERS WHERE CustomerID in (53,57) ").ToList());
}
3.2 Delete Many
删除多个实体
DapperPlusManager.Entity<Customer>().Table("Customers").Key("CustomerID");
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkDelete(connection.Query<Customer>("Select * FROM CUSTOMERS WHERE CustomerID in (53,57) ").ToList());
}
3.3 Delete with relation(One to One)
删除一对一关系的实体
DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkDelete(suppliers.Select(x => x.Product)).BulkDelete(suppliers);
}
3.4 Delete with relation(One to Many)
删除一对多关系的实体
DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkDelete(suppliers.SelectMany(x => x.Products)).BulkDelete(suppliers);
}
4.Bulk Merge
4.1 Merge Single
DapperPlusManager.Entity<Customer>().Table("Customers");
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkMerge(new List<Customer>() { new Customer() { CustomerName = "ExampleBulkMerge", ContactName = "Example Name :" + 1}});
}
4.2 Merge Many
DapperPlusManager.Entity<Customer>().Table("Customers");
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkMerge(customers);
}
4.3 Merge with relation(One to One)
DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkMerge(suppliers).ThenForEach(x => x.Product.SupplierID = x.SupplierID).ThenBulkMerge(x => x.Product);
}
4.4 Merge with relation(One to Many)
DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
connection.BulkMerge(suppliers).ThenForEach(x => x.Products.ForEach(y => y.SupplierID = x.SupplierID)).ThenBulkMerge(x => x.Products);
}