已经是一个打开的DataReader.MARS设置为true

本文关键字：DataReader MARS 设置 true 一个 | 更新日期: 2023-09-27 18:19:46

在ASP.NET中使用SQL时，当试图在不同的命令和结果集之间来回切换时，我总是遇到这个问题。代码：

foreach (string user in usernames)
{
    command.CommandText = "select case system_role when 'Z' then 'none' else 'none' end as 'system_role', case institution_roles_pk1 when 1 then 'student' when 2 then 'faculty' end as 'institution_role',batch_uid,user_id,student_id as 'passwd',firstname,middlename,lastname,email,student_id,case row_status when 0 then 'ENABLED' when 2 then 'DISABLED' end as 'row_status' from users where user_id = '" + user + "'";
    SqlDataReader reader = command.ExecuteReader();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            file.WriteLine(user + "|" + status);
            if (overrideFramework == 0)
            {
               command.CommandText = "use sis_comparison_data insert into person_override values ('" + reader["system_role"].ToString() + "','" + reader["institution_role"].ToString() + "','" + reader["batch_uid"].ToString() + "','" + reader["user_id"].ToString() + "','" + reader["passwd"].ToString() + "','" + reader["firstname"].ToString() + "','" + reader["middlename"].ToString() + "','" + reader["lastname"].ToString() + "','" + reader["email"].ToString() + "','" + reader["student_id"].ToString() + "','" + overrideIndicator + "')";
               command.ExecuteNonQuery();
            }
            validPersonCounter++;
            using (StreamWriter historyFile = new StreamWriter(@"c:'bb_updates'history.txt", true))
            {
                historyFile.WriteLine(this.firstName + status.ToLower() + " user ID for " + user + ", SIS Integration Override: " + didOverride + ", Date: " + mn + "/" + dy + "/" + yy + " Time: " + DateTime.Now.ToString("h:mm:ss tt"));
            }
        }
    }
    else
    {
        invalidPersons.Add(user);
    }                                  
}

我的连接字符串启用了MultipleActiveResultSets=true;，我尝试在command.ExecuteNonQuery()之前添加一个关闭，然后我得到错误

读取器关闭时读取尝试无效

如果我把reader.Close()放在其他地方，我会得到已经打开的数据读取器错误。请告知。

已经是一个打开的DataReader.MARS设置为true

您不应该重用command对象。如果这样做，则会干扰ExecuteReader调用的结果。在while循环中创建一个新的SqlCommand。

请尝试以下操作。它正确地处理了reader，而不重用command。

 foreach (string user in usernames)
 {
     command.CommandText = "select case system_role when 'Z' then 'none' else 'none' end as 'system_role', case institution_roles_pk1 when 1 then 'student' when 2 then 'faculty' end as 'institution_role',batch_uid,user_id,student_id as 'passwd',firstname,middlename,lastname,email,student_id,case row_status when 0 then 'ENABLED' when 2 then 'DISABLED' end as 'row_status' from users where user_id = '" + user + "'";
     using (var reader = command.ExecuteReader())
     {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                file.WriteLine(user + "|" + status);
                if (overrideFramework == 0)
                {
                    var cmd = new SqlCommand();
                    cmd.CommandText = "use sis_comparison_data insert into person_override values ('" + reader["system_role"].ToString() + "','" + reader["institution_role"].ToString() + "','" + reader["batch_uid"].ToString() + "','" + reader["user_id"].ToString() + "','" + reader["passwd"].ToString() + "','" + reader["firstname"].ToString() + "','" + reader["middlename"].ToString() + "','" + reader["lastname"].ToString() + "','" + reader["email"].ToString() + "','" + reader["student_id"].ToString() + "','" + overrideIndicator + "')";
                    cmd.ExecuteNonQuery();
                }
                validPersonCounter++;
                using (StreamWriter historyFile = new StreamWriter(@"c:'bb_updates'history.txt", true))
                {
                    historyFile.WriteLine(this.firstName + status.ToLower() + " user ID for " + user + ", SIS Integration Override: " + didOverride + ", Date: " + mn + "/" + dy + "/" + yy + " Time: " + DateTime.Now.ToString("h:mm:ss tt"));
                }
            }
        }
        else
        {
            invalidPersons.Add(user);
        }  
     }
 }
相关文章：