CODEWITHSUNDEEP
asp.netvb.netsqlconnection
user3841736
user3841736

Reputation: 5

Do i need to Dispose?

I have an application and some of the code is written like the example. Is the Connection.Dispose needed? Would it be causing any problems? Thanks!

        Using Connection As New SqlConnection(ConfigurationManager.ConnectionStrings("DBCONSTRING").ConnectionString)
            Dim command As New SqlCommand("Select TextData from Table1 where key = 1", Connection)
            Connection.Open()
            Dim reader As SqlDataReader = command.ExecuteReader()
            Try
                While reader.Read()

                    Dim T1 As String = reader("TextData").ToString()

                End While
            Finally
                reader.Close()
            End Try
            Connection.Close()
            Connection.Dispose()
        End Using

UPDATED:

        Using Connection As New SqlConnection(ConfigurationManager.ConnectionStrings("DBCONSTRING").ConnectionString)
            Dim command As New SqlCommand("Select TextData from Table1 where key = 1", Connection)
            Connection.Open()
            Using reader As SqlDataReader = command.ExecuteReader
                While reader.Read()
                    Dim T1 As String = reader("TextData").ToString()
                 End While
            End Using
        End Using

Upvotes: 0

Views: 77

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460238

As others said: no, you don't need to add Close or Dispose if you use the Using-statement. Documentation quote:

A Using block behaves like a Try...Finally construction in which the Try block uses the resources and the Finally block disposes of them. Because of this, the Using block guarantees disposal of the resources, no matter how you exit the block. This is true even in the case of an unhandled exception

So you should use this:

Dim T1 As String = Nothing
Using Connection As New SqlConnection(ConfigurationManager.ConnectionStrings("DBCONSTRING").ConnectionString)
    Using Command As New SqlCommand("Select TextData from Table1 where key = 1", Connection)
        Connection.Open()
        Using reader As SqlDataReader = Command.ExecuteReader
            While reader.Read()
                T1 = reader("TextData").ToString()
            End While
        End Using
    End Using
End Using

In general: use the Using statement for everything that implements IDisposable.

By the way, since you only select a single value you could also use ExecuteScalar:

Using Connection As New SqlConnection(ConfigurationManager.ConnectionStrings("DBCONSTRING").ConnectionString)
    Using command As New SqlCommand("Select TOP 1 TextData from Table1 where key = 1", Connection)
        Connection.Open()
        Dim tmp As Object = command.ExecuteScalar()
        Dim T1 As String = If(Convert.IsDBNull(tmp), Nothing, DirectCast(tmp, String))
    End Using
End Using

Upvotes: 4

Related Questions