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