Can single connection object be used in Enterprise Library?

Topics: Data Access Application Block
Jul 25, 2007 at 5:27 PM
I am using Microsoft Enterprise library for connecting to SQLServer in console application.

Following is the code snippet:

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Practices.EnterpriseLibrary.Data.Sql
Module Module1
Public SDBInstance As SqlDatabase
Public DBCommand As Common.DbCommand
Sub Main()
Dim CmdText As String
Dim objCustId As Object = Nothing
SDBInstance = New SqlDatabase("Server=xx;Database=yy;user id=aa;password=bb")
DBCommand = SDBInstance.GetSqlStringCommand(CmdText)
DBCommand.CommandTimeout = 1200
objCustId = SDBInstance.ExecuteScalar(DBCommand)
End Sub
End Module

Like these there are many sql statements (insert/update/delete/select) invoked in the above fashion in the console application..

On doing this, everytime when we execute the sql statement, Enterprise library will create a new connection object and that connection object will be closed after the execution of the same. I am facing a drastic performance issue on doing this.

Is there any way to have the single connection object opened and execute all the sql statements through the enterprise library component. (Note: I may not want these statments to be executed in the transaction mode). Please help.
Jul 25, 2007 at 8:17 PM
You shouldn't have any performance problem from this at all, as ADO.NET caches connections.

So under the hood, it really does use a single connection object.

Try it.
Jul 26, 2007 at 7:33 PM

Keeping a connection open is strongly discouraged. Please take a look at the ADO.NET guidelines available at for further information. You should leverage the connection pooling features already available.

Jul 30, 2007 at 4:42 PM
I understand the point saying "keeping a connection open is strongly discouraged". Here is my situation. I am converting VB to VB.NET console application.

I have VB code where the connection is opened first and kept alive till the end. All subsequent sql queries uses this connection object and is executed. After all the queries get executed, then the connection will be closed.

Whereas in VB.NET, I am using Enterprise Library Jan -2006. (Note: My first note on this thread has code snippet).
I found one particular SQL query is taking more time in VB.NET than VB.
select field4 from table
where field1 = 1
and FIELD2 = ****
and Field3 > 0
This SQL query is running several times in the code which is causing the difference.
VB is executing this query with the same connection(ADODB.connection) whereas in VB.NET this query is executing with different connections.

There is NO index available for FILED2. I agree the fact that creating the index for field2 will increase the performance.
But the concern here is 'VB is faster than VB.NET' without any index on FIELD2

As mentioned by DerekS, though Enterprise Library uses one connection object at a time, it is not using the same connection object.. It changes its connection(spid) when the query is executed next time.

Any views on this???
Jul 30, 2007 at 6:06 PM

I'm not sure how you want to resolve this situation. The DAAB attempts to implement the Data Access best practices, which include the early disposal of connections, so you won't be able to use the DAAB and keep a connection open as you requested in your original post. This also means there will be some connection handling overhead, partially mitigated by the use of connection pools, that will influence the time spent executing queries; I wouldn't state that VB is faster than VB.NET, as the connection handling is not the same in the cases you are describing it.

If the difference in data access speed after properly implementing connection pools is too large, and following the best practices in connection management is not important enough in your scenario (maybe you have only so many clients and don't need high scalability), then you can implement you data access code using the raw ADO.NET classes. Keep in mind that this decision will have an impact in performance if your application eventually needs to scale.

Aug 9, 2007 at 8:49 PM
Thanks Fernando for your response! I understand your point,
VB situation: Having one connection opened and executing one particular query several times with that connection.
VB.NET situation: Per Microsoft recommendation, Connection is not kept opened till the end. Connection will be opened and then it will be closed immediately after the SQL query is executed. (This is done through Enterprise Library). Hence several queries are executed with different connections opened and closed.

Question: Please help me in understanding this! Is the query exectuion plan varying between the above two situations, since the query execution time for that particular query in VB takes less time when compared to .NET?
Aug 10, 2007 at 7:48 AM

vijaypvb wrote:
Connection will be opened and then it will be closed immediately after the SQL query is executed. (This is done through Enterprise Library). Hence several queries are executed with different connections opened and closed.

While I unfortunately don't know enough about your problem to be able to solve it, the statement you made here should not be true. Even though the recommended ADO.NET patterns do involve opening and closing the SqlConnection object for each query, the underlying connection will not normally be closed. This is because ADO.NET will pool connections by default - so when you say connection.Close(), what this really means is connection.ReturnToPool.

If you haven't done anything unusual to change the connection pooling behaviour (using the connection string), I would suspect that the performance issue you are seeing is unrelated to connection management.

Hope this helps