如何在循环中使用SqlDataReader ?

Question

我需要使用SqlDataReader处理列表中的数据。为此，我编写了一个for循环，其中将提供查询的数据并进入SqlDataReader。但是在第一次迭代之后，循环就中断了。提示需要关闭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());
}
}

在这里，例如，我使用"days"列表，但在程序本身中，该列表是从查询中获得的，因此它可以更大。这就是错误发生的地方。我试图关闭SqlDataReader，但问题是它不能打开回来。在任何情况下，我都需要以某种方式在循环中从SqlDataReader获取数据。

Answer 1

是的，你应该Dispose实例实现IDisposable:一个快速的补丁，但不是最好的解决方案是添加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());
}
}

一个更好的方法是只调用一次query (SQL是昂贵的):

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]));
}