Bulk Operations - BulkInsert - Performance Comparison (DataTable) (NET Core)

CSharp · Console

/Program.cs

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

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

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using Z.BulkOperations;

public class Program
{
	public static List<BenchmarkResult> BenchmarkResults = new List<BenchmarkResult>();
	
	public static void Main()
	{
		GenerateDatabase();
		JustInTime_Compile();
		
		// Generate X customers
		var dtCustomers = GenerateCustomers(1000);
		
		var clockBulkInsert = new Stopwatch();
		
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			
			// BulkInsert
			using (var bulk = new BulkOperation(connection))
			{
				bulk.DestinationTableName = "Customers";
				bulk.AutoMapOutputDirection = false; // performance can be improved with options
				
				clockBulkInsert.Start();
				bulk.BulkInsert(dtCustomers); 
				clockBulkInsert.Stop();
				
				BenchmarkResults.Add(new BenchmarkResult() { Action = "BulkInsert (EF Extensions)", Entities = dtCustomers.Rows.Count, Performance = clockBulkInsert.ElapsedMilliseconds + " ms" });
			}
		}
		
		FiddleHelper.WriteTable(BenchmarkResults);
	}
		 
	public static void JustInTime_Compile()
	{
		var dtCustomers = GenerateCustomers(20);
	
		
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			
			// BulkInsert
			using (var bulk = new BulkOperation(connection))
			{
				bulk.DestinationTableName = "Customers";
				bulk.AutoMapOutputDirection = false;
				
				bulk.BulkInsert(dtCustomers);
				
				// Clean
				bulk.BulkDelete(dtCustomers);
			}
		}
	}
	
	public static DataTable GenerateCustomers(int count) 
	{
		DataTable dtTable = new DataTable("Customers");
		
		dtTable.Columns.Add("CustomerID", typeof(int));
  		dtTable.Columns.Add("Name", typeof(string)); 
  		dtTable.Columns.Add("Description", typeof(string)); 
  		dtTable.Columns.Add("IsActive", typeof(Boolean)); 
	
		for(int i = 0; i < count; i++)
		{
			var drCustomer = dtTable.NewRow();  
			drCustomer["Name"] = "Customer_" + i;  
			drCustomer["Description"] = "Description_" +  i;  
			drCustomer["IsActive"] =i % 2 == 0;  
			
			dtTable.Rows.Add(drCustomer);
		}
		
		return dtTable;
	}

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,
	[Description] [NVARCHAR](MAX) NULL,
	[IsActive] [BIT] NOT NULL,
	CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
	(
		[CustomerID] ASC
	)
)
 ";
				command.ExecuteNonQuery();
			}
		}
	}
	
	public class BenchmarkResult
	{
		public string Action { get; set; }
		public int Entities { get; set; }
		public string Performance { get; set; }
	}
}
;