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

c# .net sql-server sqlclient
oshirowanen picture oshirowanen · Sep 23, 2014 · Viewed 27k times · Source

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

Patrick Allwood picture Patrick Allwood · Sep 23, 2014

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

Related questions

C# Equivalent of SQL Server DataTypes

For the following SQL Server datatypes, what would be the corresponding datatype in C#? Exact Numerics bigint numeric bit smallint decimal smallmoney int tinyint money Approximate Numerics float real Date and Time date datetimeoffset datetime2 smalldatetime datetime time Character Strings …

Read More
What is the equivalent of bigint in C#?

What am I supposed to use when handling a value in C#, which is bigint for an SQL Server database?

Read More
C# equivalent of the IsNull() function in SQL Server

In SQL Server you can use the IsNull() function to check if a value is null, and if it is, return another value. Now I am wondering if there is anything similar in C#. For example, I want to do …

Read More