Friday, November 14, 2008

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
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
End While
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
Dim result As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While result.Read()
ci =
New BusObject(rs(1), rs(2))
DirectCast(ci, BusObject))
End While
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)
End Sub
Public ReadOnly Property Item(ByVal index As Integer) As T
Return DirectCast(List.Item(index), T)
End Get
End Property

No comments: