Bulk Operations - BulkMerge - Description (DataTable)

CSharp · Console

/Program.cs

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

// @nuget: Z.BulkOperations

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.DataSetExtensions;
using System.Data.SqlClient;
using System.Linq;
using Z.BulkOperations;
				
public class Program
{
	public static void Main()
	{
		GenerateDatabase();
		
		SeedCustomers(2);
		
		var dtCustomers = GenerateCustomers(4);
		
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			
			// SHOW data
			ShowData(connection, "Before");
			
			// SAVE customers
			using (var bulk = new BulkOperation(connection))
			{
				// easy to use
				bulk.DestinationTableName = "Customers";

				bulk.BulkMerge(dtCustomers);
			}
			
			dtCustomers = GenerateCustomers(5);

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

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

			ds.Tables[0].TableName = "1 - Customers " + text;
			
			FiddleHelper.WriteTable(ds.Tables[0]);
		}
	}
	
	public static DataTable GenerateCustomers(int count, bool useOld = false) 
	{
		DataTable dtTable = new DataTable("Customers");
		
		dtTable.Columns.Add("CustomerID", typeof(int));
  		dtTable.Columns.Add("Name", typeof(string)); 
	
		for(int i = 0; i < count; i++)
		{
			var drCustomer = dtTable.NewRow();  
			drCustomer["CustomerID"] = (i + 1);  
			drCustomer["Name"] = (useOld ? "Old_" : "") + "Customer_" + i;  
			dtTable.Rows.Add(drCustomer);
		}
		
		return dtTable;
	}
	
	public static void SeedCustomers(int count) 
	{
		var dtCustomers = GenerateCustomers(count, true);
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			
			// SAVE customers
			using (var bulk = new BulkOperation(connection))
			{
				// easy to use
				bulk.DestinationTableName = "Customers";

				bulk.BulkInsert(dtCustomers);
			}
		}
	}
	
	public static void GenerateDatabase()
	{
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			using (var command = connection.CreateCommand())
			{
				command.CommandText = @"   
CREATE TABLE [Customers]
(
	[CustomerID] [INT] IDENTITY(1,1) NOT NULL,
	[Name] [NVARCHAR](MAX) NULL,
	CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
	(
		[CustomerID] ASC
	)
)";
				command.ExecuteNonQuery();
			}
		}
	}
}