Closing an SqlDataReader

Question

I have an ASP.Net 2.0 Web Forms Application using SQL Server 2008. The application has a UI layer and Data Access Layer. I use Enterprise Libray 5.0 to persist the data.

Recently my site has been running very slow, especially on pages where there are maybe 15-20 separate reads from the database. I am very concerned that my SqlDataReader database connections are not being closed properly. Below is an example of how code works. Please take a look and let me know if you see any issues with it in terms of leaking connections.

Data Access Class

Public Class DataAccess

    Private db As Database = DatabaseFactory.CreateDatabase()

    Public Function ExecuteDataReader(ByVal params() As SqlParameter, ByVal SProc As String) As SqlDataReader

        Dim i As Integer
        Dim dr As SqlDataReader = Nothing
        Dim cmd As DbCommand

        cmd = db.GetStoredProcCommand(SProc)
        cmd.CommandTimeout = 120

        For i = 0 To params.Length - 1
            db.AddInParameter(cmd, params(i).ParameterName.ToString, params(i).DbType, params(i).Value)
        Next

        dr = TryCast(DirectCast(db.ExecuteReader(cmd), RefCountingDataReader).InnerReader, SqlDataReader)

        Return dr

    End Function

UI Code Behind Page

Dim drSource As SqlDataReader = Nothing
        Try
            Dim params(0) As SqlParameter
            params(0) = New SqlParameter("@applicant_id", Session("ApplicantID"))
            drSource = DataAccess.ExecuteDataReader(params, "sp_get_date_last_login")
            If drSource.HasRows Then
                drSource.Read()
                'Do code
            End If
        Finally
            If Not (drSource Is Nothing) Then
                drSource.Close()
            End If
        End Try

I tried to put the code below into my ExecuteDataReader method, but this then closes the SqlDataReader before it gets a chance to do a Read

if (cmd.Connection.State == ConnectionState.Open)
            cmd.Connection.Close();

Can someone please look at the code above and let me know how to properly close my database connections, or maybe I am already doing it?

Thanks for your help folks.

David Osborne · Accepted Answer · 2012-10-25T13:21:26.517

2

Have you tried getting the underlying call to ExecuteReader to take the CommandBehavior.CloseConnection parameter? At least this will ensure that the connection is closed when the DataReader is also closed. This will rely on consumers of the DataReader passed back from ExecuteDataReader() to close it explicitly or dispose via a Using block.

Alternatively, try adding the following to the code behind after the drSource.Close() line:

 drSource.Connection.Close()

edited Oct 25 '12 at 13:21

answered Oct 25 '12 at 11:45

David Osborne

6,436
1
21
35

Saying this, I've just looked into the help for the ExecuteReader() method with the EnterpriseLibrary help and there's no way to do what I've suggested. Each overload of the method has the following caveat: 'It is the responsibility of the caller to close the connection and reader when finished.' You would need to go directly to ADO.NET to do what I've suggested. – David Osborne Oct 25 '12 at 12:46

Closing an SqlDataReader

1 Answers1