1

I've seen 2 examples of SqlDataReader

The first one has a using which manages the disposal of the connection and the used memory, and the second has less code.

Which one is to prefer?

using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    myConnection.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT Name FROM User;", myConnection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                DoStuff((string)reader["Name"]); 
            }
        }
    }
}

second one :

using (SqlConnection mConnection = new SqlConnection(ConnectionString))
{
    mConnection.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT Name FROM User;", mConnection))
    {
        for (SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); )
        {
            DoStuff((string)reader["Name"]);
        }
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Toshi
  • 2,532
  • 4
  • 17
  • 45
  • 3
    Possible duplicate of [SqlConnection SqlCommand SqlDataReader IDisposable](http://stackoverflow.com/questions/16985876/sqlconnection-sqlcommand-sqldatareader-idisposable) – Pikoh Oct 20 '16 at 10:09
  • 4
    That `for` loop is an abomination in the face of man and God. Stick with the first one. – Jeroen Mostert Oct 20 '16 at 10:10
  • @JeroenMostert because? – Toshi Oct 20 '16 at 10:12
  • well the for loop should work anyway. But 2nd version doesn't close the reader which is required afaik – puls200 Oct 20 '16 at 10:13
  • 2
    @GodofSource: most prominently because it does not explicitly dispose, which things like Code Analysis will warn on (relying on `Connection` to do it is anything but obvious). Less prominently because it has no third statement (as advancing the reader and checking for termination are the same thing); degenerate `for` loops are just ugly. But that's a strictly personal preference. – Jeroen Mostert Oct 20 '16 at 10:17
  • @JeroenMostert - not strictly personal. It takes me a second longer to read/decode, so the 2nd one is more expensive in the long run. You write code once, read it a hundred times. – H H Oct 20 '16 at 11:11
  • minor comment: you can place the second and third using statements directly after eachother and only have one set of opening/closing braces, like so: http://stackoverflow.com/a/1329765/1380061 – Fredrik Oct 20 '16 at 11:17

2 Answers2

2

the second has less code.

Not really.

Which one is to prefer?

The first one, by far. But just for esthetic reasons.

In the second sample, the Reader will be closed when it's (owning) connection is Disposed but it is far better to do so explicitly in the code.

H H
  • 263,252
  • 30
  • 330
  • 514
0

The first one will be good.The unwanted resources will be automatically disposed just by calling explicitly

Jays
  • 90
  • 8