EnterpriseLibraryContainer.Current.GetInstance does it close connection

Topics: Data Access Application Block, Enterprise Library Core
Mar 21, 2011 at 3:06 PM

I have EnterpriseLibraryContainer.Current.GetInstance<SqlDatabase>(ConnectionString) to connect to DB and execute SProcs with "ExecuteSprocAccessor".

I'm getting timeout frequently but not consistently. I'm trying to resolve this issue. I have a method which gets data using Select, then updates, then calls another Select.

I'm not sure whether the first select/ update connection is still open and causing timeout with second Select.

Does the EnterpriseLibraryContainer.Current.GetInstance close the connection after executing the SProc or do I have to explicitly release the connection?

Mar 22, 2011 at 12:35 AM

Calling EnterpriseLibraryContainer.Current.GetInstance doesn't open a connection.  When you call ExecuteSprocAccessor on a database instance, that's the time when a connection will be open and before it finishes, the connection will also automatically be closed.  Keep in mind that when you call any method against the IEnumerable object returned by the ExecuteSprocAccessor, that will cause a new connection to be opened.  This was mentioned in the documentation, specifically in this topic.  However, those connections are still being managed meaning they are automatically being opened and closed. 

Did you check your queries' performance itself by running them in SQL management studio and see how long does each actually take to finish?


Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.

Mar 22, 2011 at 4:31 PM

When the SProcs are run in SQL Studio, it takes about 70-90 secs.

Mar 23, 2011 at 1:20 AM

So that's probably the reason.  The default command time out is set to 30seconds.  After creating a DbCommand object, try setting the CommandTimeout property to 100 before executing it.


Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.

Mar 23, 2011 at 1:08 PM

DBA confirmed that the timeout is set as infinite. Still there is a timeout.

Mar 24, 2011 at 2:42 AM
I meant modification in your code specifically the commandtimeout property of your DbCommand instance. 
DbCommand command = db.GetStoredProcCommand(spName);
command.CommandTimeout = 100;

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.

Nov 9, 2011 at 6:39 PM

By default Data Access Application block does not take simple CommandTimeout parameter in method calls (there are many workaround samples available on net). To achieve the same with minimal changes, I have added a simple function named “WithCommandTimeOut” taking timeOutSecond parameter in “Microsoft.Practices.EnterpriseLibrary.Data.Database” class which returns same instance of “Database” class. Refer updated code snippet below for code changes.

//Class Level Static Variables
//Used to reset to default after assigning in "PrepareCommand" static method

//Default value when "WithCommandTimeOut" not called

public Database WithCommandTimeOut(int timeOutSeconds)
    return this;

protected static void PrepareCommand(DbCommand command, DbConnection connection)
    if (command == null) throw new ArgumentNullException("command");
    if (connection == null) throw new ArgumentNullException("connection");

    //Magical Code ........................................
    command.CommandTimeout = COMMAND_TIMEOUT_FOR_THIS_CALL;

    command.Connection = connection;

    //Resetting value to default as this is static and subsequent
    //db calls should work with default timeout i.e. 30


Database db = EnterpriseLibraryContainer.Current.GetInstance(Of Database)("SmartSoftware");

db.WithCommandTimeOut(0).ExecuteDataSet(CommandType.Text, query);