Bulk Operations - Request - Add returned value in another table

CSharp · Console

/Program.cs

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

// @nuget: Z.BulkOperations

using System;
using System.Data;
using Z.BulkOperations;
using System.Data.SqlClient;
using System.Collections.Generic;
					
public class Program
{
	public static void Main()
	{
		GenerateDatabase();
		
		var dtCustomers = GenerateCustomers(5);
		var dtOrders = GenerateOrders(3, dtCustomers);
		
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			
			// SAVE customers
			using (var bulk = new BulkOperation(connection))
			{
				bulk.DestinationTableName = "Customers";
				bulk.AutoMapOutputIdentity = true;
				bulk.BulkInsert(dtCustomers);
			}
			
			foreach(DataRow dr in dtOrders.Rows)
			{
				dr["CustomerID"] = ((DataRow)dr["CustomerRef"])["CustomerID"];
			}
			
			// SAVE orders
			using (var bulk = new BulkOperation(connection))
			{
				bulk.BulkInsert(dtOrders);
			}
			
			FiddleHelper.WriteTable(dtCustomers);
			FiddleHelper.WriteTable(dtOrders);
		}
	}
	
	public static DataTable GenerateCustomers(int count) 
	{
		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["Name"] = "Customer_" + i;  
			dtTable.Rows.Add(drCustomer);
		}	
		
		return dtTable;
	}
	
	public static DataTable GenerateOrders(int count, DataTable customers) 
	{
		DataTable dtTable = new DataTable("Orders");
		
		dtTable.Columns.Add("OrderID", typeof(int));
		dtTable.Columns.Add("CustomerID", typeof(int));
		dtTable.Columns.Add("OrderUpdateNotes", typeof(string));
  		dtTable.Columns.Add("CustomerRef", typeof(object)); 
	
		for(int i = 0; i < count; i++)
		{
			var drOrder = dtTable.NewRow();  
			drOrder["OrderUpdateNotes"] = "Order_" + i;
			drOrder["CustomerRef"] = customers.Rows[i % customers.Rows.Count];
			dtTable.Rows.Add(drOrder);
		}	
		
		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,
	CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
	(
		[CustomerID] ASC
	)
)";
				// Order table with customer ID
				command.CommandText += @"   
CREATE TABLE [Orders]
(
	[OrderID] [INT] IDENTITY(1,1) NOT NULL,
	  [CustomerID] [Int] Not NULL, 
	  [OrderUpdateNotes] [NVARCHAR](MAX) NULL,
	CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
	(
		[OrderID] ASC
	)
)";
				command.ExecuteNonQuery();
			}
		}
	}
}
;