Introduction to SqlBulkCopy
I have demonstrated simple example of SqlBulkCopy and how it can be used. Data transfer will be easy with the help of SqlBulkCopy. Everytime it initializes a new instance of the SqlBulkCopy. Manual data transfer can be automated using this code.
Description:
What is SqlBulkCopy?
If we want to transfer large amount of data from production to testing or for any other purpose then SqlBulkCopy is provision to mention destination table
details and copy data from SqlDataReader.
It can be done thorugh WriteToServer method.
// You have to initialize an SqlBulkCopy object
SqlBulkCopy samplebulk = new SqlBulkCopy("server=.;database=SampleDatabaseName;" +
"Integrated Security=SSPI");
// Copying data to destination
samplebulk.DestinationTableName = "your table name";
samplebulk.WriteToServer(sqldatareadername);
The connection remains open after the SqlBulkCopy instance is closed. Namespace has to be included is System.Data.SqlClient.
Different overload options are present such as SqlBulkCopy(SqlConnection)
SqlBulkCopy(String)
SqlBulkCopy(String, SqlBulkCopyOptions)
SqlBulkCopy(SqlConnection,SqlBulkCopyOptions,SqlTransaction) You have to take care of following when options include UseInternalTransaction and the externalTransaction argument is not null, that time there is possibility of an InvalidArgumentException is thrown.