SqlDataReader and SqlCommand

本文关键字：SqlCommand and SqlDataReader | 更新日期: 2023-09-27 18:00:37

我有以下代码。

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
   connection.Open();
   SqlCommand select = new SqlCommand("SELECT RTRIM(LTRIM(PART_NO)) AS PART_NO, record FROM [RMAData].[dbo].[IMPORTING_ORDER_EDI] WHERE sessionID = '" + Session.SessionID + "'", connection);
   SqlDataReader reader = select.ExecuteReader();
   if (reader.HasRows)
   {
      while (reader.Read())
      {
         if (!currentPart.IsActive)
         {
            // this part is not active, set the active flag in sql to 0
            SqlCommand update = new SqlCommand("UPDATE [RMAData].[dbo].[IMPORTING_ORDER_EDI] SET valid = 0, active = 0 WHERE record = " + reader["record"].ToString() + ";", connection);
            update.ExecuteNonQuery();
         }
         else
         {
            ///blah
         }
      }
      reader.Close();
   }
}

但这会导致以下异常。。。

System.InvalidOperationException：已经有一个打开的DataReader与必须首先关闭的此命令关联

我需要读取返回的每一行，对数据进行一些验证，并在必要时进行更新，然后继续到下一条记录。如果在循环使用reader.Read()时不能使用SqlCommand，如何实现这一点？

SqlDataReader and SqlCommand

可以像修改连接字符串一样简单：

addMultipleActiveResultSets=True到连接字符串

另一种选择是不添加MultipleActiveResultSets=True-这样做会带来较小的性能损失-因此类似于以下内容：

using (SqlConnection connection = new ...))
{
   connection.Open();
   SqlCommand select = new SqlCommand(...);
   SqlDataReader reader = select.ExecuteReader();
   var toInactivate = new List<string>();
   if (reader.HasRows)
   {
      while (reader.Read())
      {
         if (!currentPart.IsActive)
         {
            toInactivate.Add(reader["record"].ToString());
         }
         else
         {
            ///blah
         }
      }
      reader.Close();
   }
   SqlCommand update = new SqlCommand("UPDATE ... SET valid = 0, active = 0 " +
       "WHERE record IN(" + string.Join(",", toInactivate) +  ");", connection);
   update.ExecuteNonQuery();
}

其具有在单个SQL语句中更新所有所需记录的优点。

当然，使用EF和Linq，整个过程会整洁得多。

您需要创建连接的多个实例
由于一个连接通常只能执行一个命令
或

按照@grantThomas的建议去做
或者您可以使用如下的多重连接

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
   connection.Open();
   SqlCommand select = new SqlCommand("SELECT RTRIM(LTRIM(PART_NO)) AS PART_NO, record FROM [RMAData].[dbo].[IMPORTING_ORDER_EDI] WHERE sessionID = '" + Session.SessionID + "'", connection);
   SqlDataReader reader = select.ExecuteReader();
   if (reader.HasRows)
   {
      while (reader.Read())
      {
         if (!currentPart.IsActive)
         {
            // this part is not active, set the active flag in sql to 0
            using (SqlConnection connection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
           {
               SqlCommand update = new SqlCommand("UPDATE [RMAData].[dbo].[IMPORTING_ORDER_EDI] SET valid = 0, active = 0 WHERE record = " + reader["record"].ToString() + ";", connection1);

            update.ExecuteNonQuery();
           }
         }
         else
         {
            ///blah
         }
      }
      reader.Close();
   }
}
相关文章：