SqlBulkCopy in ADO.NET 2.0 , SqlBulkCopy Class in C#, SqlBulkCopy help ,SqlBulkCopy guide,sql Bulk Copy class,SqlBulkCopy tutorial

BatchSize: This integer value specifies the number of rows that should be copied in each attempt to copy to database. This value has direct effect on the number of accesses to database.
BulkCopyTimeOut: The number of seconds that system should wait to let SqlBulkCopy to copy rows.
ColumnMappings: A ReadOnly SqlBulkCopyColumnMappingCollection. You need to use its Add() method to add a new SqlBulkCopyColumnMapping object to its collection.

DestinationTableName: String value of destination table's name.
NotifyAfter: SqlRowsCopied event handler will be called when the number of rows specified in this property has been copied.

This object also has four overloads. You can pass a SqlConnection (or a connection string) plus an optional SqlBulkCopyOptions and SqlTransaction to its constructor. Latest two parameters can change the behavior of SqlBulkCopy object. Using SqlBulkCopyOptions enumerator you can specify that for example SqlBulkCopy keeps identities or check constraints and some other options. Using SqlTransaction you can pass an external SqlTransaction and your SqlBulkCopy uses this transaction in all parts of the process.

SqlBulkCopy also has a SqlRowsCopied event handler that triggers when the specific number of DataRows that have been copies. You specified this value via NotifyAfter property. This handler is helpful when you want to be aware of your process (for instance showing it via a ProgressBar to end user).
Useful Method

The last thing that should be mentioned about SqlBulkCopy object is its WriteToServer() method. This method can get an array of DataRows, a DataTable or a DataReader and copies their content to destination table in database.

SqlBulkCopyColumnMapping

SqlBulkCopyColumnMapping is the object that maps your source columns to destination columns in a bulk copy. A SqlBulkCopyColumnMapping can get the source and destination column names or ordinals via its properties or its constructor. It has these properties:
SourceColumn: String value of source column's name.
SourceOrdinal: Integer value of source column's index.
DestinationColumn: String value of destination column's name.
DestinationOrdinal: Integer value of destination column's index.

One of SourceColumn and SourceOrdinal and one of DestinationColumn and DestinationOrdinal should be set. Also you can set these properties via constructor which is an easier way.

Note that if your source and destination columns have same names, it's not required to use SqlBulkCopyColumnMapping objects because SqlBulkCopy can do its job automatically.

Write a Sample Application

Let's have look on example. I think the sample application you'll see in a moment can cover all you need to use SqlBulkCopy.

NOTE: If u have same table column on both sides mean source and Destination. Then I will suggest that u shout not mention SqlBulkCopyColumnMapping properties. Because it perform all tasks automatically.

PerformBulkCopy() method is used because both side column are same.

"MsoNormal">
 Code

public class mySqlBulkCopy
{
private static void PerformBulkCopy()
{
    string connectionString =
            @"Server=localhost;Database=Northwind;Trusted_Connection=true";
    // get the source data

    using (SqlConnection sourceConnection = 
            new SqlConnection(connectionString))
    {
        SqlCommand myCommand =
            new SqlCommand("SELECT * FROM Customer", sourceConnection);
        sourceConnection.Open();
        SqlDataReader reader = myCommand.ExecuteReader();

        // open the destination data

        using (SqlConnection destinationConnection =
                    new SqlConnection(connectionString))
        {
            // open the connection

            destinationConnection.Open();

            using (SqlBulkCopy bulkCopy =
            new SqlBulkCopy(destinationConnection.ConnectionString))
            {
                bulkCopy.BatchSize = 500;
                bulkCopy.NotifyAfter = 1000;
                bulkCopy.SqlRowsCopied +=
                    new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                bulkCopy.DestinationTableName = " Customer_Backup";
                bulkCopy.WriteToServer(reader);
            }
        }
        reader.Close();
    }
}
// this method is used if u want to mape column with different names

// u can also use the index of column by using SourceOrdinal

private static void PerformBulkCopyMyDifferentSchema()
{
    string connectionString = @"Server=
        localhost;Database=Northwind;Trusted_Connection=true";
    DataTable sourceData = new DataTable();
    // get the source data

    using (SqlConnection sourceConnection =
                    new SqlConnection(connectionString))
    {
        SqlCommand myCommand =
            new SqlCommand("SELECT * 
            FROM Customer", sourceConnection);
        sourceConnection.Open();
        SqlDataReader reader = myCommand.ExecuteReader();
        // open the destination data

        using (SqlConnection destinationConnection =
                    new SqlConnection(connectionString))
        {
            // open the connection

            destinationConnection.Open();
            using (SqlBulkCopy bulkCopy =
                new SqlBulkCopy(destinationConnection.ConnectionString))
            {
                bulkCopy.ColumnMappings.Add("CustomerID","ID");
                bulkCopy.ColumnMappings.Add("CustomerName", "Name");
                bulkCopy.ColumnMappings.Add("CustomerPoint", "Points");
                bulkCopy.DestinationTableName = " CustomerPoints";
                bulkCopy.WriteToServer(reader);
            }
        }
        reader.Close();
    }
}

}

}

"MsoNormal">


Summary
If u are familiar with  SQLHELPER class than u can improve ur performance more efficiently 
In this article I talked about newly added feature in ADO.NET 2.0, SqlBulkCopy, which helps you to bulk copy 
large amounts of data
between data source and data table and improve your performance