Bulk Operations - BulkMerge - Real Life Scenarios - Merge with related child entities (DataTable) (NET Core)

CSharp · Console

/Program.cs

// Bulk Operations
// Doc: https://bulk-operations.net/bulk-merge

// @nuget: Z.BulkOperations
// @nuget: System.Data.SqlClient

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Z.BulkOperations;
				
public class Program
{
	public static void Main()
	{
		GenerateDatabase();
		
		SeedInvoices(2, 3);
		
		var dsGeneratedInvoices = GenerateInvoices(5, 3);		
		var dtInvoices = dsGeneratedInvoices.Tables[0];
		var dtInvoiceItems = dsGeneratedInvoices.Tables[1];
		
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			
			// SHOW data
			ShowData(connection, "Before");

			// SAVE Invoices
			using (var bulk = new BulkOperation(connection))
			{
				// easy to customize
				bulk.DestinationTableName = "Invoices";
				bulk.AutoMapOutputIdentity = true;

				bulk.BulkMerge(dtInvoices);
			}

			// SAVE InvoiceItems
			using (var bulk = new BulkOperation(connection))
			{	
				// SET foreign key value
				dtInvoiceItems.AsEnumerable().ToList().ForEach(x => x["InvoiceID"] = ((DataRow)x["InvoiceReference"])["InvoiceID"]);

				bulk.DestinationTableName = "InvoiceItems";

				bulk.BulkMerge(dtInvoiceItems);
			}
		
			// SHOW data
			ShowData(connection, "After");
		}
	}
	
	public static void ShowData(SqlConnection connection, string text)
	{
		// SHOW data
		using (var command = connection.CreateCommand())
		{
			command.CommandText = @"
SELECT * FROM Invoices
SELECT * FROM InvoiceItems
";
			var ds = new DataSet();
			var sqlDataAdapter = new SqlDataAdapter(command);
			sqlDataAdapter.Fill(ds);

			ds.Tables[0].TableName = "2 - Invoices " + text;
			ds.Tables[1].TableName = "3 - InvoiceItems " + text;

			FiddleHelper.WriteTable(ds.Tables[0]);
			FiddleHelper.WriteTable(ds.Tables[1]);
		}
	}
	
	public static DataSet GenerateInvoices(int countInvoice, int countInvoiceItem, bool useOld = false) 
	{
		DataSet dataSet = new DataSet();
		
		// Invoices
		var dtInvoices = new DataTable("Invoice");  
		
		dtInvoices.Columns.Add("InvoiceID", typeof(int));
  		dtInvoices.Columns.Add("Number", typeof(string)); 
		
		dataSet.Tables.Add(dtInvoices);
		
		// InvoiceItems
		var dtInvoiceItems = new DataTable("InvoiceItems");  
		
		dtInvoiceItems.Columns.Add("InvoiceItemID", typeof(int));
  		dtInvoiceItems.Columns.Add("ProductName", typeof(string)); 
		dtInvoiceItems.Columns.Add("InvoiceID", typeof(int));		
		dtInvoiceItems.Columns.Add("InvoiceReference", typeof(object));			
	
		dataSet.Tables.Add(dtInvoiceItems);
		
		// SEED table
		for(int i = 0; i < countInvoice; i++)
		{
			var drInvoice = dtInvoices.NewRow();  
			drInvoice["InvoiceID"] = (i + 1);  
			drInvoice["Number"] = (useOld ? "Old_" : "") + "Number_" + i;  
			dtInvoices.Rows.Add(drInvoice);  	
			
			for(int j = 0; j < countInvoiceItem; j++)
			{	
				var drInvoiceItem = dtInvoiceItems.NewRow();  
				drInvoiceItem["InvoiceItemID"] = (i * countInvoiceItem) + (j + 1);
				drInvoiceItem["ProductName"] = (useOld ? "Old_" : "") + "ProductName_" + i + "_" + j;
				drInvoiceItem["InvoiceReference"] = drInvoice;
				dtInvoiceItems.Rows.Add(drInvoiceItem);  	
			}
		}
		
		return dataSet;
	}
	
	public static void SeedInvoices(int countInvoice, int countInvoiceItem) 
	{
		var dsGeneratedInvoices = GenerateInvoices(countInvoice, countInvoiceItem, true);
		
		var dtInvoices = dsGeneratedInvoices.Tables[0];
		var dtInvoiceItems = dsGeneratedInvoices.Tables[1];
		
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
		
			// SAVE Invoices
			using (var bulk = new BulkOperation(connection))
			{
				// easy to customize
				bulk.DestinationTableName = "Invoices";
				bulk.ColumnMappings.Add("InvoiceID", ColumnMappingDirectionType.Output);
				bulk.ColumnMappings.Add("Number", ColumnMappingDirectionType.Input);

				bulk.BulkInsert(dtInvoices);
			}

			// SAVE InvoiceItems
			using (var bulk = new BulkOperation(connection))
			{	
				// SET foreign key value
				dtInvoiceItems.AsEnumerable().ToList().ForEach(x => x["InvoiceID"] = ((DataRow)x["InvoiceReference"])["InvoiceID"]);

				bulk.DestinationTableName = "InvoiceItems";

				bulk.BulkInsert(dtInvoiceItems);
			}
		}
	}
	
	public static void GenerateDatabase()
	{
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			using (var command = connection.CreateCommand())
			{
				command.CommandText = @"   
CREATE TABLE [Invoices]
(
	[InvoiceID] [INT] IDENTITY(1,1) NOT NULL,
	[Number] [NVARCHAR](MAX) NULL,
	CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED 
	(
		[InvoiceID] ASC
	)
)

CREATE TABLE [InvoiceItems]
(
	[InvoiceItemID] [INT] IDENTITY(1,1) NOT NULL,
	[ProductName] [NVARCHAR](MAX) NULL,
	[InvoiceID] [INT] NULL,
	CONSTRAINT [PK_InvoiceItems] PRIMARY KEY CLUSTERED 
	(
		[InvoiceItemID] ASC
	)
)

ALTER TABLE [InvoiceItems] WITH CHECK ADD  CONSTRAINT [FK_InvoiceItems_InvoiceID] FOREIGN KEY([InvoiceID])
REFERENCES [Invoices] ([InvoiceID])
 ";
				command.ExecuteNonQuery();
			}
		}
	}
}