How to prevent timeout error when executing a SQL Server stored procedure in ASP.NET MVC?

Question

I have a C# ASP.NET MVC program which runs a stored procedure while loading a page. If I run the stored procedure from Microsoft SQL Server Management Studio, it takes 1 minute. However, if I try to run the same stored procedure from code, it times out. I have added Connection Timeout=0 in web.config, but sometimes it works, sometimes not.

Answer 1

You can set the Timeout Command to 0 when you are calling stored procedure.

using (SqlConnection connection = new SqlConnection(connectionString)) 
{
   connection.Open();  
   SqlCommand cmd= new SqlCommand(queryString, connection);  
   // Setting command timeout to 0 second  
   cmd.CommandTimeout = 0;  
   try 
   {  
     cmd.ExecuteNonQuery(); 
   }  
   catch(Exception ex)
   {
      // log ex here
   }
}