Hello Guys,
It's very long time I am here to write something interesting. Actually there was some busy scheduled in my life. But now I am back with some new and out of box ideas..
Here I am going to explain you something special about Data Import by using SQLBULKCOPY. I know you must have heard about this. I hope you will enjoy the topic, need some feedback/ suggestion about this.
Here I have created my Function DataImport inside that I have written the below code:
String connection = "Data Source=localhost;Initial Catalog=myDB;Integrated Security=True";
SqlConnection dbCon = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand("SELECT * FROM myTable");
cmd.Connection = dbCon;
dbCon.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
SqlBulkCopy bulkCopy = new SqlBulkCopy(strConnection);
bulkCopy.BatchSize = 500;
bulkCopy.NotifyAfter = 1000;
bulkCopy.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(OnCopiedRows);
bulkCopy.DestinationTableName = "mTable_Backup"; //here is our destination table
bulkCopy.WriteToServer(dt);
dt.Dispose();
da.Dispose();
dbCon.Close();
Event Handler for SQLBulkCopy..
private void OnCopiedRows(object sender, SqlRowsCopiedEventArgs e)
{
// if you need your progress bar write you code here...
Response.Write("Copied rows..."+ e.RowsCopied);
}
The main advantage of the above code in comparison with normal SQL Insert is it's very much fast.
Thanks.
Comments