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.Entity().Table("Customers"); 

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

1.2 Insert Many:複数のエンティティを挿入する

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

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

1.3 Insert with relation(One to One)
1対1の関係を挿入するエンティティ

DapperPlusManager.Entity().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity().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().Table("Suppliers").Identity(x => x.SupplierID); 
DapperPlusManager.Entity().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().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)
1対1の関係を更新するエンティティ

DapperPlusManager.Entity().Table("Suppliers").Identity(x => x.SupplierID);
DapperPlusManager.Entity().Table("Products").Identity(x => x.ProductID);

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

1対の複数の関係を更新するエンティティ

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

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

3.2 Delete Many
複数のエンティティの削除

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

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

3.3 Delete with relation(One to One)
1対1の関係を削除するエンティティ

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

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

4.2 Merge Many

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

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

4.3 Merge with relation(One to One)

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