.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()

        E开发者_JAVA技巧xit 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

---

Probably open due to connection pooling.

ADO.NET Connection Pooling & SQLServer

---

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

---

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