.NET and Closing SQL Server connections

Posted by user307076 on Stack Overflow See other posts from Stack Overflow or by user307076
Published on 2010-04-27T02:44:54Z Indexed on 2010/04/27 3:03 UTC
Read the original article Hit count: 389

Filed under:
|

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

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about ASP.NET