CODEWITHSUNDEEP
c#.netsqltimeout
BoltBait
BoltBait

Reputation: 11489

How to extend the timeout of a SQL query

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.

Upvotes: 45

Views: 103473

Answers (5)

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

You do this by setting the SqlCommand.CommandTimeout property.

Upvotes: 29

user2525415
user2525415

Reputation:

I think this might be a better way to do this (as of Enterprise Library 6.0):

SqlDatabase db = new SqlDatabase(connectionManager.SqlConnection.ConnectionString);
System.Data.Common.DbCommand cmd = db.GetStoredProcCommand(storedProc, parameterValues);
cmd.CommandTimeout = 600;
return db.ExecuteScalar(cmd);

Upvotes: 6

Ozgur
Ozgur

Reputation: 368

Try this one

SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(connection.ConnectionString);
connectionStringBuilder.ConnectTimeout = 180;
connection.ConnectionString = connectionStringBuilder.ConnectionString;

connection.Open();
SqlCommand command = new SqlCommand("sp_ProcedureName", connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = connection.ConnectionTimeout;
command.ExecuteNonQuery();
connection.Close();

Upvotes: 4

Chris Porter
Chris Porter

Reputation: 3687

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();

Upvotes: 38

n8wrl
n8wrl

Reputation: 19765

Mladen is right but if you have to do this you probably have a bigger problem with the proc itself. Under load it might take much longer than your new timeout. Might be worth spending some quality time with the proc to optimize.

Upvotes: -2

Related Questions