Using statement leaves SQL connection for a while

Question

I'm executing several queries and monitoring the server I see connections stay open for a while (3-5 minutes?) after the End Using statment.

Is this a normal behaviour of usingstatement?

My query execution uses a custom class bdConex and looks something like:

    Dim cnx As New bdConex({Id})
    Using cnx.conexionBD
        cnx.query("MyStoredProcedure")

        If cnx.dataReader.Read Then
            Me.Id = cnx.item(Of Integer)("fk_item")
            Me.Number= cnx.item(Of String)("number")
        End If
    End Using
    cnx.close()

Where:

Public Class bdConex
Public Property conexionBD As SqlConnection
Public Property dataReader As SqlDataReader

Public Sub query(ByVal ssql As String)

    If InStr(ssql, "select", CompareMethod.Text) <> 1 Then
        ssql = String.Concat("Exec ", parseSQL(ssql))
    End If

    If Me.ActivarLog = TipoDeLog.ErroresYConsultas Then
        logger.log(ssql, "bdConex.query()")
    End If

    Try
        Using Me.conexionBD
            Me.conexionBD = New SqlConnection(Me.cadena_de_conexion)
            Me.conexionBD.Open()

            Dim myCommand As SqlCommand = New SqlCommand(ssql, Me.conexionBD)

            Me.dataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            Me.HasRows= Me.dataReader.HasRows
        End Using
    Catch ex As Exception
        Me.HasRows = False
        'log error
    End Try
End Sub

Thanks

The `Using` statement closes the connection, but not the real, physical connection. With connection-pooling it just sets a flag that this connection can now be used. — Tim Schmelter, Feb 10 '15 at 12:41
My problem is there is a bit of code that, for only one user opens 19 connections, is this a normal number or it might represent a problem when multiple users will be online? — K. Weber, Feb 10 '15 at 12:55
The code above can be optimized by removing the fields `conexionBD` and `dataReader` but instead initialize them as local variables in the `Using`. Note that `SqlDataReader` also implements `IDisposable` and should also be disposed with a `Using`-statement. You also should not use the reader after the connection was disposed, consume it in the method. — Tim Schmelter, Feb 10 '15 at 13:04
`Using` is usually used to create a block in which a disposable item is created, used, then disposed: `Using connX As New SqlConnection(...)`. Thats not what you are doing: you are using it with vars of much larger scope. I'd be worried about the side effects of doing that, — Ňɏssa Pøngjǣrdenlarp, Feb 10 '15 at 13:57
here is an alternative: http://stackoverflow.com/a/28216964/1070452 Bascially because of connection polling, you shold create a new connection each time — Ňɏssa Pøngjǣrdenlarp, Feb 11 '15 at 13:16

Using statement leaves SQL connection for a while

0 Answers0