C# SqlBulkCopy: Timeout problem

Hello Geeks!!

We had a situation where we needed to upload a local table with more than 500,000 records to an online SQL Server. We went through all unsuccessful efforts. SqlBulkCopy was the only one option left for us…

We decided to use a aspx page which reads the .csv file with table data and finally used SqlBulkCopy. Here is our code:

DataTable dt = new DataTable();
string line = null;
int i = 0;

using (StreamReader sr = File.OpenText(Server.MapPath("Cities.csv")))
{
    while ((line = sr.ReadLine()) != null)
    {
        string[] data = line.Split(',');
        if (data.Length > 0)
        {
            if (i == 0)
            {
                foreach (var item in data)
                {
                    dt.Columns.Add(new DataColumn());
                }

                i++;
            }

            DataRow row = dt.NewRow();
            row.ItemArray = data;
            dt.Rows.Add(row);
        }
    }
}

using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["TempServices"].ConnectionString))
{

    cn.Open();
    using (SqlBulkCopy copy = new SqlBulkCopy(cn))
    {
        copy.ColumnMappings.Add(0, 0);
        copy.ColumnMappings.Add(1, 1);
        copy.ColumnMappings.Add(2, 2);
        copy.ColumnMappings.Add(3, 3);
        copy.ColumnMappings.Add(4, 4);
        copy.ColumnMappings.Add(5, 5);
        copy.ColumnMappings.Add(6, 6);
        copy.ColumnMappings.Add(7, 7);
        copy.ColumnMappings.Add(8, 8);
        copy.DestinationTableName = "City";
        copy.WriteToServer(dt);
    }
}

Initially tested with 3000 records and keeping in mind the Timeout problem; all records were inserted into table successfully. Now the real problem occurred when we tried morethan 3000 records. Each time we got the same error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Upon searching different articles on internet and MSDN; we added 2 statements to the code…

copy.BatchSize = 3000;
copy.BulkCopyTimeout = 12000;

This reference from MSDN really worked for us. And the table is successfully loaded with the records finally.

BatchSize: Number of rows in each batch. At the end of each batch, the rows in the batch are sent to the server.

BulkCopyTimeout: Number of seconds for the operation to complete before it times out.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s