C#: There is already an open DataReader associated with this Command which must be closed first

เมื่อคุณเจอ error: There is already an open DataReader associated with this Command which must be closed first.
เช่นตอนวนลูป while ตัวอย่างเช่น

ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["MyConnectionString"];
string connString = connSettings.ConnectionString;
SqlConnection conn = new SqlConnection(connString);

string strSQL = "SELECT * FROM MyTable1 WHERE DelFlag=1";
SqlCommand cmd = new SqlCommand(strSQL, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
         string strSQL2 = "UPDATE MyTable2 SET DelFlag=1 WHERE RefId = '" + reader["Id"].ToString() + "'";
         SqlCommand cmd2 = new SqlCommand(strSQL2, conn);
         cmd2.ExecuteNonQuery();
         cmd2.Dispose();
}
reader.close();
cmd.Dispose();

run ไปจะเจอ error นี้

วิธีแก้

ดูตรงเปิด connection ด้านบน

SqlConnection conn = new SqlConnection(connString);

ให้เพิ่ม MultipleActiveResultSets=True ลงไป เป็น

SqlConnection conn = new SqlConnection(connString + "MultipleActiveResultSets=True");

แค่นี้ก็เรียบร้อย