Patterns for closing SQL Connections consumed by the SQL Data Reader.

On a recent contract engagement with a large company whose name I will not disclose I was surprised to witness many bad practices and the needless overlooking of SQL connection disposal. Although the origin of the code base dates back to .Net 1.0, I have a hard time in letting stuff like this slip by. I discussed this internally with our lead who replied nonchalantly about it. They indicated that there should be no reason to close the connection as per the statement of Microsoft linked and summarized below.

Microsoft states, that for version 1.1

If the SqlDataReader is created with CommandBehavior set to CloseConnection, closing the SqlDataReader closes the connection automatically.

Below is an example of the code of reference. Although the command behavior sets the connection to close during command instantiation, what if there is an exception? Simply put, there is no way that the the command object will close and hence neither will the database connection!

    Public Function GetListOfImageSizes _
    (ByVal someID As String) As IList
    Dim strSQL As String = ()
        Dim ci As FileImageInfo
    Dim alAnArrayList As ArrayList
    Dim cn As New SqlConnection(Blah)
    Dim cmd As New SqlCommand(strSQL, cn)
    cmd.Parameters.Add(Blah) = someID
    cn.Open()
    Dim rs As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    While result.Read()
        ci = New FileImageInfo(rs(1), rs(2))
        If IsNothing(alAnArrayList) Then
                alAnArrayList = New ArrayList
        End If
            alAnArrayList.Add(ci)
    End While
        rs.Close()
    Return CType(alAnArrayList, IList)
End Function
 

I am very much all about using patterns. More important, patterns that are well documented to work and that following industry best practice. I believe in order to excel at what you do, there needs to exist a high degree of pride and passion. The great developers lead by example and foster good ethics and practices of their craft!

So with some minor code re-factoring, the following solution was introduced and ran through the debugger flawlessly. I have not spent the time to implement this everywhere. I have however laid the groundwork for others to follow the pattern if they so choose. Perhaps over time it will be implemented as there are many points in this application where the pattern can replace poorly implemented practices!

    Public Function RevList(ByVal someID As String) As IList
    Dim strSQL As String = (Blah)
        Dim ci As BusObject
    Dim list As New GenList(Of BusObject)
    Dim cn As New SqlConnection(blah)
    Using cn
        Dim cmd As New SqlCommand(strSQL, cn)
        Using cmd
            cmd.Parameters.Add(blah) = someID
            cn.Open()
            Dim result As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            While result.Read()
                ci = New BusObject(rs(1), rs(2))
                list.Add(DirectCast(ci, BusObject))
            End While
                result.Close()
        End Using
            Return list
    End Using
    End Function

Helper class for dealing with lists generically.

Public Class GenList(Of T)
Inherits CollectionBase

Public Function Add(ByVal value As T) As Integer
        Return List.Add(value)
End Function
    Public Sub Remove(ByVal value As T)
    List.Remove(value)
End Sub
    Public ReadOnly Property Item(ByVal index As Integer) As T
    Get
            Return DirectCast(List.Item(index), T)
    End Get
    End Property
End Class