CSharp · Console
// 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();
}
}
}
}