How to use SQLBulkCopy in C#

This tutorial will show you how to copy or transfer a large amounts of data using SQLBulkCopy in C#.NET.

The SQLBulkCopy class that helps you copy data from the source table to the destination table in SQL Server.

public void BulkCopy()
{
    string connectionString = @"Server=.;Database=Northwind;Trusted_Connection=true";
    using (SqlConnection srcCon = new SqlConnection(connectionString))
    {
        SqlCommand srcCmd = new SqlCommand("SELECT * FROM Product1");
        srcCmd.Connection = srcConnection;
        srcCon.Open();
        SqlDataReader reader = srcCmd.ExecuteReader();
        // open the destination data
        using (SqlConnection destCon = new SqlConnection(connectionString))
        {
            // open the connection
            destCon.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destCon.ConnectionString))
            {
                bulkCopy.BatchSize = 500;
                bulkCopy.DestinationTableName = "Product2";
                bulkCopy.WriteToServer(reader);
            }
        }
        reader.Close();
    }
}

You can also save your connection string in the app.config file

<configuration>
    <connectionStrings>
        <add name="cn" connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Add a reference to the System.Configuration.dll, then you can get your connection string from the app.config file as the following.

var connection = 
    System.Configuration.ConfigurationManager.
    ConnectionStrings["cn"].ConnectionString;

Remember, The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server or any data source can be used.