How can I increase connection timeout of SQL connectionstring?

Question

private void button5_Click(object sender, EventArgs e)
{
    string filepath = textBox2.Text;
    string connectionString_i = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""",
Path.GetDirectoryName(filepath));

    using (OleDbConnection connection_i = new OleDbConnection(connectionString_i))
    {
        connection_i.Open();

        OleDbCommand command = new OleDbCommand("Select * FROM [" + Path.GetFileName(filepath) +"]", connection_i);

        using (OleDbDataReader dr = command.ExecuteReader())
        {
            string sqlConnectionString = MyConString;

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
            {
                SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
                bulkInsert.DestinationTableName = "Table in SQL DB";
                bulkInsert.WriteToServer(dr);
                MessageBox.Show("Upload to DB Successful!");
            }
        }
        connection_i.Close();
    }
}

Now I want to read file with large data and insert into SQL Table. the problem is that SQL connetion timeout is just 30 seconds. I want to increase timeout of SQL connection to about 2 or 3 minutes.

How do I do that.. Please help. I am uploading data from a csv file to SQL db.

Thanks.

It seems that this is 'not' about more time is needed but connection itself has problem. Try to close the reader before bulk command. — Kay Lee, Feb 24 '17 at 00:41
Are you using MySQL or MS SQL Server? (Don't tag products not involved.) — jarlh, Feb 24 '17 at 08:07

Cam Bruce · Answer 1 · 2017-02-23T23:50:31.777

You can increase the SqlBulkCopy.BulkCopyTimeout property. It defaults to 30 seconds. You additionally can increase the OleDbCommand.CommandTimeout of your SELECT command.

//set command timeout
 OleDbCommand command = 
     new OleDbCommand("Select * FROM [" + Path.GetFileName(filepath) +"]", connection_i);
 // 3 min
 command.CommandTimeout = 180;


 SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
 bulkInsert.BulkCopyTimeout = 180; // 3 min
 bulkInsert.DestinationTableName = "Table in SQL DB";
 bulkInsert.WriteToServer(dr);

score 0 · Answer 2 · answered Feb 23 '17 at 23:43

Aside from setting the connection timeout in your CONNECTION STRING, there is ALSO a timeout based on the actual SqlCommand. So you could have a connection timeout of 3 minutes, but if your SqlCommand has a default timeout of 30 seconds, your command would time-out first. You probably want to set BOTH

How can I increase connection timeout of SQL connectionstring?

2 Answers2