Should I close the SQL connection before looping through the reader?

Question

I am wondering what is the best practice and why. Can't find anything that clearly explains advantages and disadvantages.

FYI... I am using .Net

Which is better?

Open a SQLConnection
execute my SQL with a datareader
loop through the results
have my using statement close the reader
have the using statement close the connection

OR

Open a SQLConnection
execute my SQL with a datareader
have the using statement close the connection
loop through the results
close the reader

My thoughts on the second one is if you have a large result sets you aren't holding the connection open longer than you should. Is that correct and is that what I want to be doing.

edit

I have something similar to this.

using sq as new sqlconnection
   >>connection and sproc stuff<<
   using rd as new sqlreader
        while rd.read
            do some work
        end while
    end using
end using

doing this works too

dim rd as datareader
using sq as new sqlconnection
   >>connection and sproc stuff<<
   rd = sq.executereader
end using

while rd.read
   do work
end while

Have you tried closing the connection while leaving the reader open? The reader is reading (streaming) the results from the connection. — madreflection, Dec 20 '19 at 19:17
yes I have. The reader does contain the results even after closing the connection — Danny Pomeroy, Dec 20 '19 at 19:18
That has not been my experience. My tests show the closing/disposing the connection closes the active reader. *"Invalid attempt to call Read when reader is closed."* — madreflection, Dec 20 '19 at 19:21
Anyway, don't dispose the connection until the reader has been exhausted, even if that sequence is working for you. The connection may be released to the pool and another one taken if you reuse the connection object. You don't need to manage that yourself. — madreflection, Dec 20 '19 at 19:24
I have something similar to this. using sq as new sqlconnection >>connection and sproc stuff<< using rd as new sqlreader while rd.read do some work end while end using end using — Danny Pomeroy, Dec 20 '19 at 19:26
I would say the first option. let the Using close the reader after you done with getting all data and then close the connection with the using block. — Deleted, Dec 20 '19 at 19:29
Something's mismatched in your code. I just did the second block (and the query is just `SELECT 1`, nothing that needs to be read from disk on the server) and the `end using` on the connection still changes `rd.IsClosed` to `True`, resulting in an exceptoin. I even did it in VB instead of C# in case VB was doing something wonky (official technical term) to the scope of the `using` block. Anyway, the concensus on closing the connection before using the reader is turning out to be **"don't"**, even if it seems to work (for you). — madreflection, Dec 20 '19 at 19:45
And to be thorough, I've now tested closing the connection before calling `Read` on the data reader with `System.Data.SqlClient`, `Microsoft.Data.SqlClient`, `Oracle.ManagedDataAccess`, `Npgsql`, `System.Data.SQLite`, `Microsoft.Data.Sqlite`, `MySql.Data`, and `MySqlConnector`. The exception message varied, and `Npgsql` waits until calling one of the `Get*` methods to throw it, but the result was always an exception in the end. — madreflection, Dec 20 '19 at 19:51

score 4 · Answer 1 · answered Dec 20 '19 at 19:42

4

Load a DataTable and then close and dispose your connection. You can work with the data without an open connection. You second block of code will not work.

Private Sub OpCode()
    Dim dt As New DataTable
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand("Select * From SomeTable;")
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using 'Command and Connection are closed and disposed
    For Each row In dt.Rows
        'Do work here
    Next
End Sub

answered Dec 20 '19 at 19:42

Mary

14,926
3
18
27

Downvote for select *. You are teaching the OP bad coding practices. – allmhuran Jul 19 '20 at 10:15
@allmhuran Wow! Maybe 6 months ago I didn't know any better. Also I didn't know the names of the fields that he wanted. Using * might get additional unneeded fields but it won't damage the database like not using parameters. Thanks for the heads up. :-) – Mary Jul 23 '20 at 03:09

score 3 · Answer 2 · answered Dec 20 '19 at 19:51

Your first option is better. Close the connection after you are done reading the data. Reason for this is because the SQLDataReader object doesn't read all the data from the query. If you are querying a large set of data, it will page the data and only load what it needs at the time. If you close the connection before you are done looping through you data set, you will generate an error as others have pointed out. See this post from 2 years ago for more information.

Should I close the SQL connection before looping through the reader?

2 Answers2