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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With