Dapper学习(四)之Dapper Plus的大数据量的操作

这篇文章主要讲 Dapper Plus,它使用用来操作大数量的一些操作的。比如插入1000条,或者10000条的数据时,再使用Dapper的Execute方法,就会比较慢了。这时候,可以使用Dapper Plus中的方法进行操作,提高速度。

主要包括下面:

  • Bulk Insert
  • Bulk Update
  • Bulk Delete
  • Bulk Merge

使用之前,需要在Nuget中,安装 Z.Dapper.Plus 

1. Bulk Insert:批量插入

1.1 Insert Single : 使用Bulk插入单个实体

DapperPlusManager.EntityCustomer().Table("Customers"); 

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.BulkInsert(new ListCustomer() { new Customer() { CustomerName = "ExampleBulkInsert", ContactName = "Example Name :" +  1}});
}

1.2 Insert Many :插入多个实体

DapperPlusManager.EntityCustomer().Table("Customers"); 

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.BulkInsert(customers);
}

1.3 Insert with relation(One to One)

插入一对一关系的实体

DapperPlusManager.EntitySupplier().Table("Suppliers").Identity(x = x.SupplierID);
DapperPlusManager.EntityProduct().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.EntitySupplier().Table("Suppliers").Identity(x = x.SupplierID); 
DapperPlusManager.EntityProduct().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.EntityCustomer().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.EntitySupplier().Table("Suppliers").Identity(x = x.SupplierID);
DapperPlusManager.EntityProduct().Table("Products").Identity(x = x.ProductID);

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{    
    connection.BulkUpdate(suppliers, x = x.Product);
}

更新一对多关系的实体

DapperPlusManager.EntitySupplier().Table("Suppliers").Identity(x = x.SupplierID);
DapperPlusManager.EntityProduct().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.EntityCustomer().Table("Customers").Key("CustomerID");

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.BulkDelete(connection.QueryCustomer("Select * FROM CUSTOMERS WHERE CustomerID in (53,57) ").ToList());
}

3.2 Delete Many

删除多个实体

DapperPlusManager.EntityCustomer().Table("Customers").Key("CustomerID");

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.BulkDelete(connection.QueryCustomer("Select * FROM CUSTOMERS WHERE CustomerID in (53,57) ").ToList());
}

3.3 Delete with relation(One to One)

删除一对一关系的实体

DapperPlusManager.EntitySupplier().Table("Suppliers").Identity(x = x.SupplierID);
DapperPlusManager.EntityProduct().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.EntitySupplier().Table("Suppliers").Identity(x = x.SupplierID);
DapperPlusManager.EntityProduct().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.EntityCustomer().Table("Customers"); 

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.BulkMerge(new ListCustomer() { new Customer() { CustomerName = "ExampleBulkMerge", ContactName = "Example Name :" +  1}});
}

4.2 Merge Many

DapperPlusManager.EntityCustomer().Table("Customers"); 

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.BulkMerge(customers);
}

4.3 Merge with relation(One to One)

DapperPlusManager.EntitySupplier().Table("Suppliers").Identity(x = x.SupplierID);
DapperPlusManager.EntityProduct().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.EntitySupplier().Table("Suppliers").Identity(x = x.SupplierID);
DapperPlusManager.EntityProduct().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);
}

 

转载于:https://www.cnblogs.com/Vincent-yuan/p/11521373.html

最新回复(0)
/jishuyVqJrqhu9mUUytRBJ8MTC2FEx_2BcXsBfAQ1_2FGeQ_3D_3D
8