SQL Bulk copy in ADO.NET using C#

Sometimes you need to copy a large numbers of rows from any resources to sql server database. ASP.NET provides a class for that purpose in ADO.NET SqlBulkCopy.

Sometimes we need to copy a table data from one database to a table of another database.
To do so, ADO.NET provides SqlBulkCopy class


we can use this class to copy data from one resource to sql server.

I use following code to satisfy my need.

the namespace used for this

using System.Data.SqlClient;


code to copy bulk data


// code to establish connection to source datasource and fetching the data in DataTable.

        DataTable dtSource = new DataTable();
        string Sourceconstr = @"Data Source=IITCS1;Initial Catalog=Test;Integrated Security=SSPI;";
        SqlConnection Sourcecon = new SqlConnection(Sourceconstr);
        SqlDataAdapter daSource = new SqlDataAdapter("Select * from product", Sourcecon);
        daSource.Fill(dtSource);

 // Initializing an SqlBulkCopy object

string Destconstr="Data Source=IITCS2;Initial Catalog=ProductionTest;Integrated Security=SSPI";
SqlConnection Destcon = new SqlConnection(Destconstr);
SqlBulkCopy oSqlBulkCopy  = new SqlBulkCopy(Destcon);

// Copying data to destination

oSqlBulkCopy.DestinationTableName = "Test1";
oSqlBulkCopy.WriteToServer(dtSource);

// Closing connection and the others

oSqlBulkCopy.Close();

you can provide 
DataRow[] or
DataTable or
SqlDataReader


to the WriteToServer method of SQLBULKCOPY class as input parameter




Hope this code helps to find sort out your problem.

Enjoy coding..............................