How to use SqlDataReader in a loop?

Question

I need to process the data from the list using SqlDataReader. To do this, I wrote a for loop in which the data for the query will be supplied and get into SqlDataReader. But after the first iteration, the loop breaks. An error is displayed that you need to close SqlDataReader.

List<string> days = new List<string>() { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday" };

SqlConnection cnn = new SqlConnection(@"Con");
cnn.Open();

SqlCommand cmd = cnn.CreateCommand();
List<string> data = new List<string>();

for (int i = 0; i < days.Count;i++)
{
    cmd.CommandText = $@"select * from Table where Day = '{days[i]}'";
    
    SqlDataReader reader = cmd.ExecuteReader();
    
    while (reader.Read())
    {
        data.Add(reader[0].ToString());
    }
}

Here, for example, I use the "days" list, but in the program itself the list is obtained from a query, so it can be larger. This is where the error occurs. I tried to close SqlDataReader, but the problem is that it cannot be opened back. In any case, I need to somehow get data from SqlDataReader in a loop.

Answer 1

Yes, you should Dispose instances that implement IDisposable: a quick patch, but not the best soultion is to add using:

...

for (int i = 0; i < days.Count; i++) {
  cmd.CommandText = $@"select * from Table where Day = '{days[i]}'";
    
  // using to Dispose reader
  using (SqlDataReader reader = cmd.ExecuteReader()) {
    while (reader.Read())
      data.Add(reader[0].ToString());
  }
}

A better aproach is to call query just once (SQL is expensive):

var data = new List<string>();

var days = new List<string>() { 
  "Monday", "Tuesday", "Wednesday", "Thursday", "Friday" 
};

using SqlConnection cnn = new SqlConnection(@"Con");

cnn.Open();

//TODO: change * into the only field(s) you want to fetch
// Since days are list of constants I haven't created bind variables:
//   - No sql injection
//   - Constants are specific and in specific order (workdays) so
//     I want the query be specific and not mixed with in (:param1, ... :paramN)
//     for arbitrary days 
string sql = 
  $@"select *
       from Table
      where day in ({string.Join(", ", days.Select(day => $"'{day}'"))})";

using SqlCommand cmd = cnn.CreateCommand(sql, cnn);

using SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read()) {
  data.Add(Convert.ToString(reader[0]));
}