CODEWITHSUNDEEP
asp.netsql-server-2005
kasabb
kasabb

Reputation: 49

.NET and Closing SQL Server connections

I am having a hard time figuring out why the following constructor will not close connnections. When I view the active connections. Here is the code that I have.

Public Sub New(ByVal UserID As Integer)

Dim oConn As New SqlConnection(ConfigurationManager.ConnectionStrings("connStr").ToString())
Dim cmd As New SqlCommand("stored proc", oConn)
Dim sdr As SqlDataReader

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@userID", UserID)
oConn.Open()
sdr = cmd.ExecuteReader()

Try
    If Not sdr.HasRows Then
        sdr.Close()

        If Not oConn Is Nothing Then
            If oConn.State <> ConnectionState.Closed Then
                oConn.Close()
            End If
        End If
        cmd.Dispose()

        Exit Sub
    End If
    'User has account in WATS, proceed to load account information
    While sdr.Read
         _firstname = Convert.ToString(sdr("First Name"))
        _lastname = Convert.ToString(sdr("Last Name"))
    End While

Catch ex As Exception
    'Throw New Exception("User Error: " + ex.Message)
Finally
    sdr.Close()

    If Not oConn Is Nothing Then
        If oConn.State <> ConnectionState.Closed Then
            oConn.Close()
        End If
    End If
    cmd.Dispose()
End Try

End Sub

Upvotes: 1

Views: 884

Answers (3)

Dennis Walter
Dennis Walter

Reputation: 21

Dim oConn As SqlConnection
Dim cmd As SqlCommand
Dim sdr As SqlDataReader

Try
    oConn = New SqlConnection(ConfigurationManager.ConnectionStrings("connStr").ToString())
    cmd = New SqlCommand("stored proc", oConn)

    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@userID", UserID)
    oConn.Open()
    sdr = cmd.ExecuteReader()
Catch ex As Exception

Finally

    If Not IsNothing(sdr) Then
        If Not sdr.IsClosed Then sdr.Close()
    End If

    If Not IsNothing(oConn) Then
        If oConn.State = ConnectionState.Open Then oConn.Close()
    End If

End Try

Upvotes: 1

John Saunders
John Saunders

Reputation: 161773

You need to be implementing Using blocks. Partial fix:

Using oConn As New SqlConnection(ConfigurationManager.ConnectionStrings("connStr").ToString()) 
    Using cmd As New SqlCommand("stored proc", oConn) 

        cmd.CommandType = CommandType.StoredProcedure 
        cmd.Parameters.AddWithValue("@userID", UserID) 
        oConn.Open() 
        Using sdr As SqlDataReader = cmd.ExecuteReader() 
        End Using
    End Using
End Using

Upvotes: 2

Raj Kaimal
Raj Kaimal

Reputation: 8304

Probably open due to connection pooling.

ADO.NET Connection Pooling & SQLServer

Upvotes: 2

Related Questions