Using SqlBulkcopy Object to insert large data into database.

Problem:  I had a situation in which we want to copy the data from log files to database. So first challenge was to parse the log files and second to insert them in database.

Solution:

The solution, I choose was to first parse the files and insert the data into a DataTable and then send the datatable to SqlBulkcopy object which inserts the whole datatable into database Table. SqlBulkCopy is more efficient compared to executing multiple insert statements to database when volume of data to insert is large.

1.      So First step is to create a DataTable and define columns for it. However, it is not mandatory to define the column name and size.

DataTable dTable = new DataTable();

       DataColumn dc;

       dc = new DataColumn("FilePath");

       dc.DataType = System.Type.GetType("System.String");

       dc.MaxLength = 250;

dTable.Columns.Add(dc);

      dc = new DataColumn("DateTime");

      dc.DataType = System.Type.GetType("System.String");

      dTable.Columns.Add(dc);

      dc = new DataColumn("UserName");

      dc.DataType = System.Type.GetType("System.String");

      dc.MaxLength = 50;

      dTable.Columns.Add(dc);

     

2.       Next Step is to add the rows into the datatable that contains the data you want to insert into database.

DataRow newRow = dTable.NewRow();

       newRow[0] = file.Name;

       newRow[1] = file.createdDate;

       newRow[2] = userloggedin;

      

       dTable.Rows.Add(newRow);

3.      Once the data is populated in datatable you can define your SqlBulkcopy object.

//Get the connection string to connect to database

 String ConnectionString = ConfigurationManager.AppSettings["DataBaseConnString"];

//create connection object

        using (SqlConnection conn = new SqlConnection(ConnectionString)) 

        {

          SqlBulkCopy bulkCpy = new SqlBulkCopy(

                                                  conn,

                                                  SqlBulkCopyOptions.TableLock |

                                                  SqlBulkCopyOptions.FireTriggers |

                                                  SqlBulkCopyOptions.UseInternalTransaction,

                                                  null

                                               );

   bulkCpy.DestinationTableName = "databaseTableName"; //Table in which you need to insert data

          //Not mandatory. Required only if you want to divide the insert operation into batches.

   bulkCpy.BatchSize = 1000;

          bulkCpy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("FilePath", "FilePath"));

          bulkCpy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("DateTime", "DateTime"));

          bulkCpy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("UserName", "UserName"));

          conn.Open(); //open  database connection

          bulkCpy.WriteToServer(dTable); //Statement writes the contents of datatable to database.

          dTable.Clear();

          conn.Close();         

      }

              

·         To sqlBulkcopy we pass connection object and set of SqlBulkCopyOptions – this are flags that indicates different setting sql server will use while inserting data into database. (you can refer msdn for more details on this options)

                                                 i.            SqlBulkCopyOptions.TableLock - indicates to lock table during operation.

                                               ii.            SqlBulkCopyOptions.FireTriggers – fires triggers when row is inserted.

                                             iii.            SqlBulkCopyOptions.UseInternalTransaction – indicate that each batch will perform as transaction.

·         Last parameter takes SqlTransaction object which we set to null.

·         You can avoid SqlBulkCopyColumnMapping. It is needed only when the number of columns you are passing into datatable is less than number of columns in database Table.

Thanks,