Saturday, September 12, 2015

Bulk Insert C# DataTable into SQL Server

Here is the sample code to insert C# DataTable into SQL.


        public bool BulkInsertDataTable(string ConnectionString , DataTable dataTable)
        {
            bool isSuccuss;
            try
           {
                SqlConnection SqlConnectionObj = new SqlConnection( ConnectionString );
                SqlConnectionObj.Open();
                SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlConnectionObj,
                                                         SqlBulkCopyOptions.TableLock
                                                       | SqlBulkCopyOptions.FireTriggers
                                                       | SqlBulkCopyOptions.UseInternalTransaction
                                                       , null);
                bulkCopy.DestinationTableName = "tableName";
                SQLUtil.SqlTableCreator sqlTabCre = new SQLUtil.SqlTableCreator(SqlConnectionObj);
                sqlTabCre.CreateFromDataTable(dataTable);
                bulkCopy.WriteToServer(dataTable);
                SqlConnectionObj.Close();
                isSuccuss = true;
            }
            catch (Exception ex)
            {
                isSuccuss = false;
                throw new Exception("Error when inserting table " + tableName + " into SQL using connection string '" + ConnectionString + "'. Error Details : " + ex.ToString() + ".");
            }
            return isSuccuss;
        }

The utility to create SQL Table (SqlTableCreator) can be found here.

Hope this saves you some time.

Happy coding.

Cheers
Adam

No comments:

Post a Comment

All Blogs so far ...