How to Bulk Insert records to T-SQL Table from a DataTable in C#?

This article will tell you two method of inserting bulk records into a T-SQL table from a DataTable in C#:

1. Bulk insert using the SqlBulkCopy class

2. Bulk insert by passing XML data table to the stored procedure

The first method is used ONLY to insert the data to the database and is simple as compared to the second method. The second method can be used to do some complex processing while inserting/updating the data in the SQL table.

In the first method we will make use of the SqlBulkCopy class provided in .NET. Following code illustrates the use of the SqlBulkCopy class for bulk insert:

#region references
using System.Data;
using System.Data.SqlClient;
#endregion references

namespace SQLBulkInsert
{
    class Program
    {
        static void Main(string[] args)
        {
            // T-SQL Connection
            string connection = "Data source=.; Initial Catalog= MyDatabase; SSPI=true";
            DataTable dtData = new DataTable();

            // Get the data into the DataTable
            //dtData = GetData(...);

            // Create an object of SqlBulkCopy
            SqlBulkCopy objSBC = new SqlBulkCopy(connection);
            // Specify the destination table
            objSBC.DestinationTableName = "Table_Name";
            // Write the data to the SQL Server
            objSBC.WriteToServer(dtData);
        }
    }
}

You can find the implementation of the second method in my article on CodeProject.com

Hope this helps you!