Click here to Skip to main content
Click here to Skip to main content

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)

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

License

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

About the Author

lalitsrana
Software Developer (Senior)
India India
More about me on my website
http://lalit.50webs.com

Comments and Discussions

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