Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Closing SqlConnection without Closing SQL reader - can it cause connection Leak

I have been using SQLDataReader for fetchig some data from a db. Once I used Reader with the the connection I am closing the connection only and not the Reader. Do we have any possibility of connection leak

Here is the code that I am using

 Public Sub Get_SomeData(ByVal sCon As String,ByRef ObjectToReturn As SomeClass)
        Dim sqlCon As SqlConnection = New SqlConnection(sCon)
        Dim sqlR As SqlDataReader = Nothing
        Dim sqlCmd As SqlCommand = New SqlCommand
        sqlCmd.CommandType = CommandType.StoredProcedure
        sqlCmd.Connection = sqlCon
        sqlCmd.CommandText = "get_SomeData"
        sqlCon.Open()
        sqlR = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)
        If sqlR.HasRows And sqlR.Read Then
            ObjectToReturn.Property1 = sqlR("Column1").ToString
            ObjectToReturn.Property1 = sqlR("Column1").ToString
            ObjectToReturn.Property1 = sqlR("Column1").ToString
            ObjectToReturn.Property1 = sqlR("Column1").ToString
        End If
        sqlCon.Close()
    End Sub
like image 359
santhosh Avatar asked Dec 02 '25 09:12

santhosh


1 Answers

No, closing the connection will suffice, but a better approach is through the Using statement

    Using sqlCon = New SqlConnection(sCon)
        Dim sqlR As SqlDataReader = Nothing
        Using sqlCmd = New SqlCommand
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlCon
            sqlCmd.CommandText = "get_SomeData"
            sqlCon.Open()
            Using sqlR = sqlCmd.ExecuteReader()
                If sqlR.HasRows And sqlR.Read Then
                    ObjectToReturn.Property1 = sqlR("Column1").ToString
                    .......
               End If
            End Using
       End Using
  End Using

The important part in MSDN docs about Using

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

Managed resources are disposed of by the .NET Framework garbage collector (GC) without any extra coding on your part. You do not need a Using block for managed resources. However, you can still use a Using block to force the disposal of a managed resource instead of waiting for the garbage collector.

like image 136
Steve Avatar answered Dec 03 '25 23:12

Steve



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!