SqlDatabase ExecuteSprocAccessor - Doesn't use connection string Timeout value

Topics: Data Access Application Block
Nov 22, 2010 at 7:38 PM

I am trying to increase the default timeout value while connecting to a SQL Server 2008 R2 instance from Enterprise Library 5.0 DAAB's ExecuteSprocAccessor, but have only seen the connectionString value in app.config as a way to do this.  However, I am still getting the default time-out rather than the specified timeout that I desire.  If I use the SqlDatabase object's ConnectionString parameter, and print it out, it seems valid.  I have double-checked the string syntax and formatting against the documentation on MSDN:  Connection Timeout=<value> or Connect Timeout=<value>.  Neither work for me.

I would appreciate any suggestions on how to get this working short of rewriting my code not to use the accessor and to use a DbCommand object instead.

Nov 23, 2010 at 1:13 AM

I tested it by simply calling the CreateConnection method against a Database instance and inspecting the ConnectionTimeout property and got the expected result. 

Database db = DatabaseFactory.CreateDatabase();
DbConnection connection = db.CreateConnection();
Console.WriteLine(connection.ConnectionTimeout); //this shows the correct value as specified in the connection string in the config file

Or are you referring to the CommandTimeout property of the DbCommand objects?

 

Sarah Urmeneta
Global Technologies & Solutions
Avanade, Inc.
entlib.support@avanade.com

Nov 23, 2010 at 7:08 PM
Edited Nov 23, 2010 at 7:11 PM

If you create code like this, and the executing stored procedure lasts more than the default timeout it will throw a SqlException into your calling code with a timeout Message:

 

var database = EnterpriseLibraryContainer.Current.GetInstance<Database>("FooDB") as SqlDatabase;

// create a mapping from the columns in the result data to our data contract object
IRowMapper<Foo> rowMapper = ....Build();

// execute the stored procedure and use the mapping object to return an enumeration of reports
IEnumerable<Foo> results = database.ExecuteSprocAccessor<Foo>("_GetFoos", rowMapper);

var resultArray = results.ToArray();

 

What I am saying is that there doesn't seem to be any way to override the default timeout value to let the SQL server execute longer before the .NET SQL client throws a SqlException. The ConnectionString for the database doesn't seem to do that. Am I interpreting its function incorrectly? There doesn't seem to be any other timeout value to override.

I should note that the ConnectionTimeout property on the Database is set correctly after it is obtained by the first call, so that has nothing to do with this issue.

Nov 23, 2010 at 7:18 PM
Edited Nov 23, 2010 at 9:59 PM

Aha! What I want is on DbCommand, the property called CommandTimeout.  Is there a way for me to use this property in conjunction with using the Accessor extension methods in DAAB, either per command or as a global default?  Or do I need to rewrite my code to use the old way of doing things? 

It seems to me that ExecuteSprocAccessor (and its companion ExecuteSqlAccessor) should take in an optional parameter in a function overload that specifies a DbCommand object to use or at least a user provided timeout value (TimeSpan would be ideal, but int would suffice).

Nov 24, 2010 at 3:37 AM

I see, a workaround I could think of is creating a Database extension method similar to the ExecuteSprocAccessor except that it takes a DbCommand object rather than a command string.  You would then rely on copying the logic from the source code specifically on the Execute method of the SprocAccessor and CommandAccessor classes.

You can log a feature request in the Issue Tracker which would make it easier for you to accomplish what you want.

 

Sarah Urmeneta
Global Technologies & Solutions
Avanade, Inc.
entlib.support@avanade.com