If you come across a situation where you may need to insert large data quickly to the database, then the easiest & quickest way is SqlBulkCopy.
You need to take the data into ADO.Net table from the source, mention destination table and pass the data table to the SqlBulkCopy method. And of course, you can then run stored procedure to process data further. No need to create and maintain SSIS package for dumping the data.
We will take the example of CSV file as source to dump its data into database. We will dump more than ten thousand of the records in the database in less than a second. See the two screenshots below:
Around millions of records are also matter of a second. Data table can have maximum 16,777,216 rows in it hence no need to worry about large data sources.
The table in the database is empty and looks like below:
Now here is the piece of code:
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
var timer = new Stopwatch();
timer.Start();
Console.WriteLine("Operation started: " + timer.Elapsed.ToString());
var dataTable = new DataTable();
Console.WriteLine("Reading CSV: " + timer.Elapsed.ToString());
using (StreamReader sr = new StreamReader("C:\\Users\\Swapnil\\Desktop\\MyRecords.csv"))
{
var headers = sr.ReadLine().Split(',');
foreach (var header in headers)
{
dataTable.Columns.Add(header);
}
while (!sr.EndOfStream)
{
var rows = sr.ReadLine().Split(',');
var dataRow = dataTable.NewRow();
for (int i = 0; i < headers.Length; i++)
{
dataRow[i] = rows[i];
}
dataTable.Rows.Add(dataRow);
}
}
Console.WriteLine("CSV data is now in data table: " + timer.Elapsed.ToString());
Console.WriteLine("Dumping data table to database's table: " + timer.Elapsed.ToString());
var connectionString = "Data Source=swapnil-pc;Initial Catalog=example;Integrated Security=True";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var sqlBulkCopy = new SqlBulkCopy(connection))
{
sqlBulkCopy.DestinationTableName = "dbo.MyTable";
sqlBulkCopy.WriteToServer(dataTable);
}
}
Console.WriteLine("Operation ended: " + timer.Elapsed.ToString());
Console.ReadLine();
You can also map column to column using ColumnMappings.
Below is the timestamp of each phase during the operation:
Now database table looks like below:
Conclusion:
SqlBulkCopy is hasle free & quick way to dump data from ADO.Net's data table to database table. You may need to bring data from sources such as CSV, Excel to data table first. You can further perform operations by calling stored procedure once data is dumped.
Top comments (0)