引用在循环中创建的上一个对象以更新SQL

本文关键字：一个对象 更新 SQL 循环 创建 引用 | 更新日期: 2023-09-27 17:58:18

我有一个设置表单的循环，它是以下代码（在表单加载事件中）。这会显示一个带有人名的复选框。如果位字段为1，则选中复选框。

int xAxisCheckbox = 130;
int yAxisCheckbox = 30;
for (int i = 0; i < selectDS.Tables[0].Rows.Count; i++)
{
    this.myCheckBox = new CheckBox();
    myCheckBox.Location = new Point(xAxisCheckbox, yAxisCheckbox);
    myCheckBox.Size = new Size(120, 20);
    myCheckBox.Text = selectDS.Tables[0].Rows[i]["FullName"].ToString();
    myCheckBox.Checked = (bool)selectDS.Tables[0].Rows[i]["InOperation"];
    yAxisCheckbox = yAxisCheckbox + 80;
}

稍后在代码中（对于保存按钮单击事件），它会运行相同的选择，但也会加载更新，以根据勾号将InOperation字段设置为true/false。如果您被添加到操作中，它还会重置OperationOrder。

for (int i = 0; i < selectDataSet.Tables[0].Rows.Count; i++)
{
     userID = (int)selectDataSet.Tables[0].Rows[i]["UserID"];
     if (myCheckBox.Checked)
     {
         connection.runUpdate("UPDATE Users SET InOperation = 1, OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation=1 THEN OperationOrder ELSE (SELECT COUNT(*)+1 FROM Users WHERE InOperation=1 AND OperationOrder > 0) END END WHERE UserID=" + userID);
         connection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder END WHERE InOperation=1");
      }
      else
      {
          connection.runUpdate("UPDATE Users SET InOperation = 0, OperationOrder = 0 WHERE UserID=" + userID);
          connection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder -1 END WHERE InOperation=1");
       }
   }

这样做的问题是，它会根据最后创建的对象更新每一行（例如，如果有5行，则只有底部的复选框才算运行SQL，并且它适用于所有行）。我如何更新每一行，有没有一种方法可以引用我创建的每个对象，而不仅仅是最后一个创建的对象？

更新：以下是一些导致错误的新代码。公共分部类SelectUsers:Form{public int userID；public List myBoxes；

    public SelectUsers()
    {
        InitializeComponent();
    }
    private void SelectUsers_Load(object sender, EventArgs e)
    {
        DataSet ds = myconnection.runSelect(new DataSet(), "THE SELECT");
        int xAxisCheckbox = 40;
        int yAxisCheckbox = 50;
        myBoxes = new List<CheckBox>();
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            this.myCheckBox = new CheckBox();
            myCheckBox.Location = new Point(xAxisCheckbox, yAxisCheckbox);
            myCheckBox.Size = new Size(120, 20);
            myCheckBox.Text = ds.Tables[0].Rows[i]["FullName"].ToString();
            myCheckBox.Checked = (bool)ds.Tables[0].Rows[i]["InOperation"];
            yAxisCheckbox = yAxisCheckbox + 80;
            myBoxes.Add(myCheckBox);
        }
    }
    private void saveBtn_Click(object sender, EventArgs e)
    {
        DataSet ds = myconnection.runSelect(new DataSet(), "THE SELECT");
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            userID = (int)ds.Tables[0].Rows[i]["UserID"];
            if (myBoxes[i].Checked)
            {
                myconnection.runUpdate("UPDATE Users SET InOperation = 1, OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation=1 THEN OperationOrder ELSE (SELECT COUNT(*)+1 FROM Users WHERE InOperation=1 AND OperationOrder > 0) END END WHERE UserID=" + userID);
                myconnection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder END WHERE InOperation=1");
            }
            else
            {
                myconnection.runUpdate("UPDATE Users SET InOperation = 0, OperationOrder = 0 WHERE UserID=" + userID);
                myconnection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder -1 END WHERE InOperation=1");
            }
        }
    }

引用在循环中创建的上一个对象以更新SQL

您应该保留一个复选框数组，而不仅仅是一个单独的复选框

int xAxisCheckbox = 130;
int yAxisCheckbox = 30;
List<CheckBox> myBoxes = new List<CheckBox>();
for (int i = 0; i < selectDS.Tables[0].Rows.Count; i++)
{
    this.myCheckBox = new CheckBox();
    myCheckBox.Location = new Point(xAxisCheckbox, yAxisCheckbox);
    myCheckBox.Size = new Size(120, 20);
    myCheckBox.Text = selectDS.Tables[0].Rows[i]["FullName"].ToString();
    myCheckBox.Checked = (bool)selectDS.Tables[0].Rows[i]["InOperation"];
    yAxisCheckbox = yAxisCheckbox + 80;
    myBoxes.Add(myCheckBox);
}

稍后你的for循环：

for (int i = 0; i < selectDataSet.Tables[0].Rows.Count; i++)
{
     userID = (int)selectDataSet.Tables[0].Rows[i]["UserID"];
     if (myBoxes[i].Checked)
     {
         connection.runUpdate("UPDATE Users SET InOperation = 1, OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation=1 THEN OperationOrder ELSE (SELECT COUNT(*)+1 FROM Users WHERE InOperation=1 AND OperationOrder > 0) END END WHERE UserID=" + userID);
         connection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder END WHERE InOperation=1");
      }
      else
      {
          connection.runUpdate("UPDATE Users SET InOperation = 0, OperationOrder = 0 WHERE UserID=" + userID);
          connection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder -1 END WHERE InOperation=1");
       }
   }

这样就可以了。

不用说，直接在表单中执行SQL语句不是一个好主意，但这是另一回事

如果你能简单地循环表单上的所有复选框，从那里构建一个SQL更新脚本，并在最后对所述脚本执行.runUpdate（），这不是会快很多吗？

脑海中浮现的事物：

  • 确保使用字符串生成器
  • 第一部分仅更新InOperation字段
  • 然后在1个update中更新OperationOrder

您需要一种方法将复选框与相关的UserId链接起来。

Carlos Grappa的回答是说使用数组，这为我指明了正确的方向，但设置错误。在阅读了编程书籍之后，我发现您创建了一个数组，如下所示：

// Global
CheckBox[] myCheckBox;
DataSet ds;
private void SelectUsers_Load(object sender, EventArgs e) {
    ds = myconnection.runSelect(new DataSet(), "THE SELECT");
    int xAxisCheckbox = 40;
    int yAxisCheckbox = 50;
    // Create the CheckBox array (amount based on number of rows)
    myBoxes = new CheckBox[ds.Tables[0].Rows.Count];
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        // Create a new Checkbox
        myCheckBox[i] = new CheckBox();
        myCheckBox.Location = new Point(xAxisCheckbox, yAxisCheckbox);
        myCheckBox.Size = new Size(120, 20);
        myCheckBox.Text = ds.Tables[0].Rows[i]["FullName"].ToString();
        myCheckBox.Checked = (bool)ds.Tables[0].Rows[i]["InOperation"];
        yAxisCheckbox = yAxisCheckbox + 80;
        // Add the Checkbox
        Controls.Add(myCheckBox[i]);
    }
}
private void saveBtn_Click(object sender, EventArgs e) {
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        userID = (int)ds.Tables[0].Rows[i]["UserID"];
        if (myBoxes[i].Checked)
        {
            myconnection.runUpdate("UPDATE Users SET InOperation = 1, OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation=1 THEN OperationOrder ELSE (SELECT COUNT(*)+1 FROM Users WHERE InOperation=1 AND OperationOrder > 0) END END WHERE UserID=" + userID);
            myconnection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder END WHERE InOperation=1");
        }
        else
        {
            myconnection.runUpdate("UPDATE Users SET InOperation = 0, OperationOrder = 0 WHERE UserID=" + userID);
            myconnection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder -1 END WHERE InOperation=1");
        }
    }
}

Deroby你可能是对的，但是我不知道该怎么做。

相关文章：