EntLib 4.1 Command Timeout for ExecuteNonQuery

Topics: Data Access Application Block
Oct 2, 2010 at 3:18 PM

Hey Guys,

I currently use EntLib 4.1 in one of my projects, but recently hit an issue where an update takes 1 minute to run (despite simply updating a bit column and selecting the row by id).  The issue is on the SQL side, and something I plan to investigate with a DBA soon.  However, I'd like to increase the timeout value for my application too, just to be safe should the issue show up again, as for my situation a longer timeout won't cause any side effect issues, so there's no downside. 

At present, the code looks something like this:

Microsoft.Practices.EnterpriseLibrary.Data.Database database =  Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase(DATABASE_NAME);

public void FlagProcessed(long id)
{
    this.database.ExecuteNonQuery(SP_FLAG_PROCESSED, id);
}

 From what I can tell, to add a timeout, I'd need to use the following code:

public void FlagProcessed(long id)
{
	using (DbConnection connection = this.database.CreateConnection())
	{
		DbCommand command = connection.CreateCommand();
		command.CommandType = CommandType.StoredProcedure;
		command.CommandText = SP_FLAG_PROCESSED;
		command.CommandTimeout = LONG_TIMEOUT;
		this.database.ExecuteNonQuery(command);
	}
}

This seems like a lot of extra code to add just to get a timeout though, and I'm not sure if the using statement will affect the connection pooling (e.g. will it cause the connection to be disposed of, rather than just returned to the pool once the operation completes)?

So my question is; is the above the correct / best way to add a command timeout to an ExecuteNonQuery method call, or is there a better way which doesn't involve changing the underlying entlib code (which I'm keen to leave untamperred to help whoever ends up mainting this app (probably me!))?

Thanks in advance,

JB

Oct 4, 2010 at 1:45 AM

You need not instantiate a DbConnection object.  You only need to set the CommandTimeout property of the DbCommand.

DbCommand command = database.GetStoredProcCommand(SP_FLAG_PROCESSED, id);
command.CommandTimeout = LONG_TIMEOUT;
database.ExecuteNonQuery(command);

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.

entlib.support@avanade.com