using System.Collections.Generic;
using System.Data.DataSetExtensions;
using System.Data.SqlClient;
public static void Main()
var dtCustomers = GenerateCustomers(5);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
ShowData(connection, "Before");
using (var bulk = new BulkOperation(connection))
bulk.DestinationTableName = "Customers";
var columnMapping = new ColumnMapping("CreatedDate");
columnMapping.IgnoreOnMergeUpdate = true;
bulk.ColumnMappings.Add("CustomerID", true);
bulk.ColumnMappings.Add("UpdatedDate");
bulk.ColumnMappings.Add("Name");
bulk.ColumnMappings.Add(columnMapping);
bulk.BulkMerge(dtCustomers);
ShowData(connection, "After");
public static void ShowData(SqlConnection connection, string text)
using (var command = connection.CreateCommand())
var sqlDataAdapter = new SqlDataAdapter(command);
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));
dtTable.Columns.Add("CreatedDate", typeof(DateTime));
dtTable.Columns.Add("UpdatedDate", typeof(DateTime));
var date = useOld ? DateTime.Now.AddDays(-2) : DateTime.Now;
for(int i = 0; i < count; i++)
var drCustomer = dtTable.NewRow();
drCustomer["CustomerID"] = (i + 1);
drCustomer["Name"] = (useOld ? "Old_" : "") + "Customer_" + i;
drCustomer["CreatedDate"] = date;
drCustomer["UpdatedDate"] = date;
dtTable.Rows.Add(drCustomer);
public static void SeedCustomers(int count)
var dtCustomers = GenerateCustomers(count, true);
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
using (var bulk = new BulkOperation(connection))
bulk.DestinationTableName = "Customers";
bulk.BulkInsert(dtCustomers);
public static void GenerateDatabase()
using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
using (var command = connection.CreateCommand())
[CustomerID] [INT] IDENTITY(1,1) NOT NULL,
[Name] [NVARCHAR](MAX) NULL,
[CreatedDate] [DateTime] NULL,
[UpdatedDate] [DateTime] NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
command.ExecuteNonQuery();