SqlBulkCopy with retry logic

Question

I had issue with inserting large set of records to the Sql server table. I resolve this issue by using SqlBulkCopy. Now i am able to insert record pretty quickly.

I have question that If any transaction failed while inserting records i have to retry the same operation 3 times. How we can achieve retry logic with SqlBulkCopy?

using (var connection = DatabaseOperations.CreateConnection(ConnectString))
    {
        connection.Open();
        var transaction = connection.BeginTransaction();
        try
        {
            var dt = new DataTable();
            dt.Columns.Add("EmployeeID");
            dt.Columns.Add("Name"); 
            for (var i = 1; i < 1000000; i++)    
                dt.Rows.Add(i + 1, "Name " + i + 1);

            using (var sqlBulk = new SqlBulkCopy(connection,SqlBulkCopyOptions.Default, transaction as SqlTransaction))
            {
                sqlBulk.DestinationTableName = "Employees";
                sqlBulk.BatchSize = 100000;
                sqlBulk.WriteToServer(dt);
            }
        }
        catch (SqlException ex)
        {
            transaction.Rollback();
            throw new CustomException("SQL Exception", ex);
        }
        catch (Exception e)
        {
            transaction.Rollback();
            throw;
        }
        transaction.Commit();
    }

Answer 1

Here is a tip: do NOT SqlBulkCopy into the target table. Simple like that.

Took me a day to solve this.

• Get the schema of the target table (which is simple to do using INFORMATION_SCHEMA)
• Generate a temporary table with the same fields.
• Insert into the temporary table
• Then use an INSERT INTO SELECT FROM to copy the data.

The whole SqlBulkCopy locking mechanism is broken beyond repair. It gets an exclusive table lock. Which it may not need, but ok.

The problem is: it tries to get one WITHOUT WAITING - any lock on any row blocks that. It retries X times, then gives up. On a busy table this will never (or rarely) work.

Oh, and it keeps that lock during the upload, which generally is slower than my solution insert.

My approach solves this. You can always get a table lock on your own temporary table. The insert into then waits in line. And the copy is faster than the network upload.

Oh, and you do NOT need a DataTable - you can program your own object table source using reflection quite easily (couple of hours). DataTAble is a memory hog.