correct point to close a data reader in vb.net

Question

I have this code in vb.net:

reader = myCommand.ExecuteReader
If reader.HasRows Then
    While reader.read

    End While
End If

should i use reader.close after the End While or after the End If

Answer 1

The best way is to use the Using-statement which ensures that unmanaged resources are disposed(even on error). This also closes the reader.

Using reader = myCommand.ExecuteReader()
   If reader.HasRows Then
      While reader.read

      End While
    End If 
End Using

Is it necessary to manually close and dispose of SqlDataReader?

Answer 2

It makes no difference where you put the .Close. It is more important to make sure you dispose of the command object (you should dispose of any object the implements IDisposable. From the code you posted it is not clear if you are doing this or not.

The easiest way to do this is to wrap it in a using block

If you look at the example on MSDN. This is the pattern they use:

   Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        ' Call Read before accessing data.
        While reader.Read()

        End While

        ' Call Close when done reading.
        reader.Close()
    End Using

But as the SqlDataReader also implements IDisposable - so it would good practise to also wrap the reader in a using block.

At the end of the using block, the reader is closed and disposed for you meaning you don't have to worry about it:

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()

        Using reader As SqlDataReader = command.ExecuteReader()
            While reader.Read()

            End While
        End Using

    End Using

Note that you don't need to check if reader.HasRows because the reader.Read will return False if there are no rows. See this answer for more details: Should if call SqlDataReader.HasRows if I am calling SqlReader.Read

Answer 3

Another way to do is

Try
    reader = myCommand.ExecuteReader
    If reader.HasRows Then
        While reader.read

        End While
    End If
Catch ex as Exception
Finally
    reader.Close()
End Try