How do I prevent a timeout error when executing a store procedure using a SqlCommand?

Question

I have a C# program which runs a stored procedure. If I run the stored procedure from Microsoft sql server management studio, it works fine. It does take about 30 seconds to execute. However, if I try to run the same stored procedure from a C# program, it times out, even though I have set the timeout in the connection string to 10 minutes.

using (connection1 = new SqlConnection("user id=user_id_goes_here;password=password_goes_here;initial catalog=database_name_goes_here;data source=server_name_goes_here;connection timeout=600))

It seem to time out after about 30 seconds, even though I have set it to allow 10 minutes (for testing purposes).

Answer 1

The timeout on the connection is for connecting to the database only.

There is a separate CommandTimeout property of the SqlCommand class, use this property to specify the execution timeout.

Ie.

using (SqlCommand cmd = new SqlCommand())
{
  cmd.Connection = connection1;
  cmd.CommandTimeout = 240; //in seconds
  //etc...
}