Failproof Bulk Upload SqlBulkCopy

• Download source - 34.5 KB

Introduction

As per Microsoft, SqlBulkCopy "Lets you efficiently bulk load a SQL Server table with data from another source". This tip demonstrates how to use SqlBulkCopy. Also the major reason for writing this tip is to demonstrate a simple yet efficient way to upload the data (except the row containing error), even when the Bulk Upload fails.

Using the Code

PART A - SQL Server (database creation)

The first part is to create a database and define a table for the bulk upload. To make it easy, I have used a database (SQL Server) which is on my local machine, also I have used the VS IDE. Please open the Visual Studio IDE and follow the steps below.

PART B: Collecting Data to be Bulk Stored

There are two csv files in the solution:

AddressWithError.csv and Address.csv (if you open these files, you would be able to notice that the Address field is more than 50 characters for the 5^th row in the AddressWithError.csv file (and this is what creates an error).

PART C: The Real Work

The main function for the code is below. It tries to do a bulk insert and if the same fails, it does a row by row insert.

/// <summary>
    /// Bulk copy the data.
    /// </summary>
    public void BulkCopy()
    {
        // Create the Datatable which contains the rows to be updated in the database.
        CreateDataTable();
        using (SqlConnection cn = new SqlConnection(@"Data Source=.;
            Initial Catalog=TestDB;Integrated Security=True;Pooling=False"))
        {
            try
            {
                cn.Open();
                using (SqlBulkCopy copy = new SqlBulkCopy(cn))
                {
                    copy.DestinationTableName = "Address";
                    //If this fails, there won't be any rows inserted to
                    //the database (no batchsize defined, so all rows tried together).
                    //In that case we would be doing a row by row insertion, refer catch block.
                    copy.WriteToServer(toBeInsertedDataTable);
                }
            }
            catch
            {
                if (toBeInsertedDataTable != null)
                {
                    for (int i = toBeInsertedDataTable.Rows.Count - 1; i >= 0; i--)
                    {
                        DataRow dr = toBeInsertedDataTable.Rows[i];
                        //Cloning is mandatory, this copies the schema to the temp table.
                        DataTable OneRowDT = toBeInsertedDataTable.Clone();

                        OneRowDT.ImportRow(toBeInsertedDataTable.Rows[i]);

                        using (var bulkCopy = new SqlBulkCopy(cn))
                        {
                            bulkCopy.DestinationTableName = "Address";
                            try
                            {
                                // Write from the source to the destination.
                                bulkCopy.WriteToServer(OneRowDT);
                            }
                            catch (Exception ex)
                            {
                                //Log the issues
                                MessageBox.Show(ex.Message);
                            }
                        }
                    }
                }
            }
        }
    }

D. Execute the Project

The application is provided with two buttons, one tries to insert the correct data, the other inserts the wrong data. When you run the application, you will see that in error cases, a step-wise data insertion approach is taken to upload all data (except the failed row).

History

• Initial version submitted