c# - 2 SQL 命令插入和更新到不同的表

Question

有 2 个 sql 命令，其中包含针对不同表的命令。这是我的代码。

  private void button1_Click(object sender, EventArgs e)
    {
        string txtbx9 = textBox9.Text.ToString();
        string cmbbx2 = comboBox2.SelectedItem.ToString();
        string name = textBox1.Text.ToString();
        string surname = textBox2.Text.ToString();
        string company = textBox3.Text.ToString();
        string txtbx8 = textBox8.Text.ToString();
        string sts = "In House";

            try
            {
                connection.Open();


                MessageBox.Show("Payment approved.");
                richTextBox1.Text = richTextBox1.Text + "The hotel received " + txtbx9 + " from this guest";
                 string rtb = richTextBox1.Text.ToString();

            OleDbCommand command = new OleDbCommand();
                command.Connection = connection;
                command.CommandText = "INSERT INTO billing(g_name,g_surname,g_company,g_totalrate, g_paid, g_typepaid, info, u_add, u_tadd, g_ad, g_dd, g_amountofdays) VALUES('" + name + "','" + surname + "','" + company + "','" + txtbx8 + "', '" + txtbx9 + "', '" + cmbbx2 + "', '" + rtb + "', '" + label12.Text.ToString() + "', '" + this.dateTimePicker1.Value +"','"+textBox4.Text.ToString()+"','"+textBox5.Text.ToString()+"','"+textBox6.Text.ToString()+"')"; ;
                command.ExecuteNonQuery();
                command.CommandType = CommandType.Text;
                command.CommandText = "UPDATE guestreg SET g_paidstatus='Paid '"+txtbx9+"'' where g_name ='"+name+"' and g_status = '"+sts"'";

                command.Connection = connection;
                connection.Open();
                command.ExecuteNonQuery();

        }

我如何同时执行这 2 个命令？该程序执行第一个 sql 命令，但不执行第二个命令

Answer 1

你可以在命令中执行两个命令:

try
{
      connection.Open();   

      MessageBox.Show("Payment approved.");
      richTextBox1.Text = richTextBox1.Text + "The hotel received " + txtbx9 + " from this guest";
      string rtb = richTextBox1.Text.ToString();
      command.Connection = connection;
      command.CommandText = "INSERT INTO billing(g_name,g_surname,g_company,g_totalrate, g_paid, g_typepaid, info, u_add, u_tadd, g_ad, g_dd, g_amountofdays) VALUES('" + name + "','" + surname + "','" + company + "','" + txtbx8 + "', '" + txtbx9 + "', '" + cmbbx2 + "', '" + rtb + "', '" + label12.Text.ToString() + "', '" + this.dateTimePicker1.Value +"','"+textBox4.Text.ToString()+"','"+textBox5.Text.ToString()+"','"+textBox6.Text.ToString()+"')";
      command.CommandText += "\nUPDATE guestreg SET g_paidstatus='Paid '"+txtbx9+"'' where g_name ='"+name+"' and g_status = '"+sts"'";
      command.ExecuteNonQuery();
}

或者只是一个接一个地执行它们:

try
{
      connection.Open();   

      MessageBox.Show("Payment approved.");
      richTextBox1.Text = richTextBox1.Text + "The hotel received " + txtbx9 + " from this guest";
      string rtb = richTextBox1.Text.ToString();
      command.Connection = connection;
      command.CommandText = "INSERT INTO billing(g_name,g_surname,g_company,g_totalrate, g_paid, g_typepaid, info, u_add, u_tadd, g_ad, g_dd, g_amountofdays) VALUES('" + name + "','" + surname + "','" + company + "','" + txtbx8 + "', '" + txtbx9 + "', '" + cmbbx2 + "', '" + rtb + "', '" + label12.Text.ToString() + "', '" + this.dateTimePicker1.Value +"','"+textBox4.Text.ToString()+"','"+textBox5.Text.ToString()+"','"+textBox6.Text.ToString()+"')";
      command.ExecuteNonQuery();
      command.CommandText = "UPDATE guestreg SET g_paidstatus='Paid '"+txtbx9+"'' where g_name ='"+name+"' and g_status = '"+sts"'";
      command.ExecuteNonQuery();
}

编辑:

正如史蒂夫提到的(他是绝对正确的)，参数应该作为 SqlParameters 传递。这样做的好处是可以更好地防止 SQL 注入(inject)，而且您可以肯定，像“O'Neil”这样的意外输入不会破坏您的代码(参数名称可能会更好):

try
{
      connection.Open();   

      MessageBox.Show("Payment approved.");
      richTextBox1.Text = richTextBox1.Text + "The hotel received " + txtbx9 + " from this guest";
      string rtb = richTextBox1.Text.ToString();
      command.Connection = connection;
      command.CommandText = "INSERT INTO billing(g_name,g_surname,g_company,g_totalrate, g_paid, g_typepaid, info, u_add, u_tadd, g_ad, g_dd, g_amountofdays) VALUES(@name,@surname,@company,@txtbx8,@txtbx9,@cmbbx2,@rtb,@label12Text,@dateTimePicker1Value,@textBox4Text,@textBox5Text,@textBox6Text')";
      command.Parameters.Add(new SqlParameter("@name",name));
      command.Parameters.Add(new SqlParameter("@surname",surname));
      command.Parameters.Add(new SqlParameter("@company",company));
      command.Parameters.Add(new SqlParameter("@txtbx8",txtbx8));
      command.Parameters.Add(new SqlParameter("@txtbx9",txtbx9));
      command.Parameters.Add(new SqlParameter("@cmbbx2",cmbbx2));
      command.Parameters.Add(new SqlParameter("@rtb",rtb));
      command.Parameters.Add(new SqlParameter("@label12Text",label12.Text.ToString()));
      command.Parameters.Add(new SqlParameter("@dateTimePicker1Value",this.dateTimePicker1.Value.ToString()));
      command.Parameters.Add(new SqlParameter("@textBox4Text",textBox4.Text.ToString()));
      command.Parameters.Add(new SqlParameter("@textBox5Text",textBox5.Text.ToString()));
      command.Parameters.Add(new SqlParameter("@textBox6Text",textBox6.Text.ToString()));
      command.ExecuteNonQuery();
      command.CommandText = "UPDATE guestreg SET g_paidstatus=@paidStatus where g_name =@name and g_status = @status";
      command.Parameters.Add(new SqlParameter("@paidStatus","Paid " + txtbx9));
      command.Parameters.Add(new SqlParameter("@name",name));
      command.Parameters.Add(new SqlParameter("@status",sts));
      command.ExecuteNonQuery();
}