Click here to Skip to main content
13,093,097 members (82,965 online)
Click here to Skip to main content
Add your own
alternative version


6 bookmarked
Posted 29 Nov 2013

Failproof Bulk Upload SqlBulkCopy

, 29 Nov 2013
Rate this:
Please Sign up or sign in to vote.
Demonstrate a simple yet efficient way to upload the data (SqlBulkCopy)


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 5th 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.
        using (SqlConnection cn = new SqlConnection(@"Data Source=.;
            Initial Catalog=TestDB;Integrated Security=True;Pooling=False"))
                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.
                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();


                        using (var bulkCopy = new SqlBulkCopy(cn))
                            bulkCopy.DestinationTableName = "Address";
                                // Write from the source to the destination.
                            catch (Exception ex)
                                //Log the issues

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).


  • Initial version submitted


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
India India
More about me on my website

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170813.1 | Last Updated 29 Nov 2013
Article Copyright 2013 by lalitsrana
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid