How to extend the timeout of a SQL query

Question

This is not a connection timeout as a connection to the database is made fine. The problem is that the stored procedure that I'm calling takes longer than, say, 30 seconds and causes a timeout.

The code of the function looks something like this:

SqlDatabase db = new SqlDatabase(connectionManager.SqlConnection.ConnectionString); return db.ExecuteScalar(Enum.GetName(typeof(StoredProcs), storedProc), parameterValues); 

The ExecuteScalar call is timing out. How can I extend the timeout period of this function?

For quick stored procedures, it works fine. But, one of the functions takes a while and the call fails. I can't seem to find any way to extend the timeout period when the ExecuteScalar function is called this way.

Answer 1

If you are using the EnterpriseLibrary (and it looks like you are) try this:

 Microsoft.Practices.EnterpriseLibrary.Data.Database db = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase("ConnectionString");  System.Data.Common.DbCommand cmd = db.GetStoredProcCommand("StoredProcedureName");  cmd.CommandTimeout = 600;  db.AddInParameter(cmd, "ParameterName", DbType.String, "Value");   // Added to handle paramValues array conversion  foreach (System.Data.SqlClient.SqlParameter param in parameterValues)   {      db.AddInParameter(cmd, param.ParameterName, param.SqlDbType, param.Value);  }   return cmd.ExecuteScalar(); 

Edited to handle the paramValues array directly based on the comments. I also included your ConnectionString value:

Microsoft.Practices.EnterpriseLibrary.Data.Database db = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase(connectionManager.SqlConnection.ConnectionString); System.Data.Common.DbCommand cmd = db.GetStoredProcCommand("StoredProcedureName", parameterValues); cmd.CommandTimeout = 600; return cmd.ExecuteScalar(); 

Answer 2

you do this by setting the SqlCommand.CommandTimeout property