Back to list
Views:   4.6K
Replies:  1
Archived
ADO.NET Data Access Class Issue
When using the ADO.NET data access class as per the Patterns in Action demo project, I am regularly getting issues with the connection not being closed which results in the following error:

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

When I explicitly close the connection, I no longer get this. Why is this happening as the using statement should release the connection?

This is only evident when VS projects are run in 'Release' configuration. 'Debug' configuration seems to hide this issue.

Please see attached code for not working and working example. Note the connection is closed in the second example.

Public Iterator Function Read(Of T)(ByVal sql As String, ByVal make As Func(Of IDataReader, T), ByVal ParamArray parms() As Object) As IEnumerable(Of T)
            Using connection = CreateConnection()
                Using command = CreateCommand(sql, connection, parms)
                    Using reader = command.ExecuteReader()
                        Do While reader.Read()
                            Yield make(reader)
                        Loop
                    End Using
                End Using
            End Using
        End Function

 
Public Iterator Function Read(Of T)(ByVal sql As String, ByVal make As Func(Of IDataReader, T), ByVal ParamArray parms() As Object) As IEnumerable(Of T)
            Using connection = CreateConnection()
                Using command = CreateCommand(sql, connection, parms)
                    Using reader = command.ExecuteReader()
                        Do While reader.Read()
                            Yield make(reader)
                        Loop
                    End Using
                End Using
               connection.Close()
            End Using
        End Function

 
Kevin Walker, Sep 30, 2015
Reply 1
Kevin: 

This is strange because the Using statement should dispose off the connection. 
From your message it appears that the compiler optimization prevents the implicit Dispose method from being called (and therefore the connection.Close method) .  

I have no explanation for this. We are looking into this.
In the meantime please continue using the connection.Close(); 

Jack
Jack Poorte, Oct 12, 2015
Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas