c# - Connection was not closed, Connection's current state is open error in foreach 循环

Question

我有一个使用数据绑定(bind)复选框列表的网络应用程序。我有一个放在 foreach 循环中的更新存储过程。如果选中一个 CheckboxList，则更新没问题，但如果我选中了多个 CheckboxList，则会抛出连接打开错误。我尝试了 try{}catch{}finally{} 但它仍然给我同样的错误

CultureInfo provider = CultureInfo.InvariantCulture;
System.Globalization.DateTimeStyles style = DateTimeStyles.None;
DateTime dt;
DateTime.TryParseExact(datepicker.Text, "mmddy", provider, style, out dt);
int i = Int32.Parse(amount.Text);

SqlConnection conn = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;


 foreach (ListItem item in CheckBoxList1.Items)
 {
     if(item.Selected)
     {

          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = "[dbo].[AccountCode_Update]";
          cmd.Parameters.AddWithValue("@Batch_Num", SqlDbType.Int).Value = i;
          cmd.Parameters.AddWithValue("@Batch_Date", SqlDbType.DateTime).Value = dt;
          cmd.Parameters.AddWithValue("@Account_Code", SqlDbType.VarChar).Value = BatchCodeList.SelectedValue;
          conn.Open();
          cmd.ExecuteNonQuery();
     }


  }
  conn.Close();

SQL

CREATE TABLE AccountTable
(
  RowID int IDENTITY(1, 1),
  AccountID varchar(2),
  AccountName varchar(50),
  SeqNum int,
  SeqDate datetime
)

CREATE PROCEDURE [AccountCode_Update]
(
  @Batch_Num int,
  @Batch_Date datetime,
  @Account_Code varchar(2)
)

 AS 
 SET NOCOUNT ON
 BEGIN
  UPDATE AccountTable
   SET SeqNum = @Batch_Num, SeqDate = @Batch_Date
   WHERE AccountID = @Account_Account_Code
 END

Answer 1

将 conn.Open(); 调用移到 foreach 循环之前。

SqlConnection conn = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

conn.Open();

foreach (ListItem item in CheckBoxList1.Items)
{
    if(item.Selected)
    {

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "[dbo].[AccountCode_Update]";
        cmd.Parameters.AddWithValue("@Batch_Num", SqlDbType.Int).Value = i;
        cmd.Parameters.AddWithValue("@Batch_Date", SqlDbType.DateTime).Value = dt;
        cmd.Parameters.AddWithValue("@Account_Code", SqlDbType.VarChar).Value = BatchCodeList.SelectedValue;

        cmd.ExecuteNonQuery();
    }
}

conn.Close();

发生的事情是您正在调用一个已经打开的连接的 conn.Open() 并且它会抛出一个错误。这就是为什么第一个调用有效而下一个调用失败的原因。

看看 MSDN documentation对于 Open() 方法。它有一些会导致异常的示例。

在这种情况下

InvalidOperationException

Cannot open a connection without specifying a data source or server. or The connection is already open.