To insert 10 Million Records. Following is the code to insert bulk data in to SQL Server Database in .net.
using System;
using System.Data;
using System.Data.SqlClient;
namespace BulkCopyInsert
{
class BlukCopy
{
static string DestinationDbConnection = "Data
Source=192.168.0.5;Initial Catalog=DatabaseName;Persist Security Info=True;User
ID=sa;Password=Password@123";
static void Main(string[] args)
{
DataTable dt = GetDataTable();
DataRow dr;
DateTime dtm = DateTime.Now;
//start
dummy data creation
for (long i = 0; i < 5000000; i++)
{
dr = dt.NewRow();
dr["Name"] = "nitin dhiman";
dr["Age"] = i;
dr["ManagerId"] = i;
dr["CreateDate"] = dtm;
dt.Rows.Add(dr);
}
//End
dummy data created
//Start
Bulk Insert
SqlBulkCopy objBulkCopy = new SqlBulkCopy(DestinationDbConnection);
objBulkCopy.DestinationTableName = "UserTable";//Table Name, must match with sql table
objBulkCopy.BatchSize = 10000;
objBulkCopy.BulkCopyTimeout = 600; //Seconds
foreach (DataColumn dc in dt.Columns)
{
objBulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
objBulkCopy.WriteToServer(dt);
//End
Bulk Insert
}
static DataTable GetDataTable()
{
try
{
//Columns
of table, name must match with sql table columns
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Department", typeof(string));
dt.Columns.Add("Age", typeof(int));
dt.Columns.Add("ManagerId", typeof(int));
dt.Columns.Add("CreateDate", typeof(DateTime));
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
