如何执行2 Sql语句与一个按钮单击
本文关键字:按钮 单击 一个 Sql 何执行 执行 语句 | 更新日期: 2023-09-27 18:08:29
我已经使用wamp运行了这个查询。
INSERT INTO guest (guestno,familyname)
VALUES(NULL,'Damn');
INSERT INTO reservation (reservationno, guestno)
VALUES(NUll,LAST_INSERT_ID())
然而,如果我分别执行这两个插入语句,我将有一个外键约束。我认为这两个需要同时执行。
我的问题是:
- 如何将此纳入我的c# winform代码?
- 是否有可能在一个按钮上有2个插入语句?
当用户按下"添加预订"时,我希望执行两个MySQl查询。
下面是我的insert语句:
private void button7_Click(object sender, EventArgs e)
{
string connectionString =
"Server=localhost;" +
"Database=sad;" +
"User ID=root;" +
"Password=root;" +
"Pooling=false";
IDbConnection dbcon;
dbcon = new MySqlConnection(connectionString);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
string sql = "<insert statement>";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
reader.Read();
}
更新版本(没用)
string connectionString =
"Server=localhost;" +
"Database=sad;" +
"User ID=root;" +
"Password=root;" +
"Pooling=false";
Form3 f3 = new Form3();
IDbConnection dbcon;
dbcon = new MySqlConnection(connectionString);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
string sql = "insert into guest (guestno, familyname) values (null, '" + textBox6.Text + "'); insert into reservation (reservationno, guestno) values (null, LAST_INSERT_ID())";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
reader.Read();
MessageBox.Show("Added Guest Reservation Successfully");
f3.guestList();
f3.reservationList();
更新3号(仍然没有工作)
string connectionString =
"Server=localhost;" +
"Database=sad;" +
"User ID=root;" +
"Password=root;" +
"Pooling=false";
IDbConnection dbcon;
dbcon = new MySqlConnection(connectionString);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
dbcmd = new MySqlCommand("CreateGuestAndReservation", dbcon);
dbcmd.CommandType = CommandType.StoredProcedure;
dbcmd.Parameters.AddWithValue("familyName", "foo");
dbcmd.ExecuteNonQuery();
enter code here
不能在一个MySqlCommand上执行多条语句。
你最好的选择(可维护性,性能,可读性)是:
- 为你的2个SQL语句创建一个MySQL存储过程。
- 使用
ExecuteNonQuery()
调用存储进程。
DELIMITER //
CREATE PROCEDURE CreateGuestAndReservation
(
IN familyName VARCHAR(255)
)
BEGIN
insert into guest (guestno, familyname)
values (null, familyName);
insert into reservation (reservationno, guestno)
values (null, LAST_INSERT_ID());
END//
DELIMITER ;
在你的WinForms代码中像这样调用它:
dbcon.Open();
cmd = new MySqlCommand("CreateGuestAndReservation", dbcon);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("?familyName", "foo");
cmd.Parameters.Add("?familyName", MySqlDbType.VarChar,255).Value = "foo";
cmd.ExecuteNonQuery();
下面的代码应该工作,但我怀疑你可能已经尝试过了,因为你正在寻求帮助?
string sql = "INSERT INTO guest (guestno,familyname) VALUES(NULL,'Damn'); INSERT INTO reservation (reservationno, guestno) VALUES(NUll,LAST_INSERT_ID())";
如果你需要参数,试试这个:
string sql = "INSERT INTO guest (guestno,familyname) VALUES(NULL,?familyName); INSERT INTO reservation (reservationno, guestno) VALUES(NUll,LAST_INSERT_ID())";
...
dbcmd.Parameters.Add("@familyName", MySqlDbType.VarChar, 80).Value = _familyName;
编辑:您可能需要运行两个插入命令。看到这里。
我建议有一种方法来获得id而不是依赖于自动id生成,如mysql和sql server的自动增量,这是非常有限的。如果使用HILO id生成器,首先获得id,然后在单个事务中执行几次插入,这没有问题,因为您事先知道父id。
它不会立即解决您的问题,但它将在将来对您的应用程序有极大的帮助,特别是在经常存储父子数据的情况下。
试试这个,它会工作的:
private void button56_Click(object sender, EventArgs e) {
con.Open();
SqlCommand cmd = new SqlCommand("insert into stholidays values('" + dateTimePicker12.Text + "','" + dateTimePicker20.Text + "','" + dateTimePicker13.Text + "','" + mbk + "','" + dateTimePicker14.Text + "','" + dateTimePicker15.Text + "','" + lt + "','" + dateTimePicker16.Text + "','" + dateTimePicker17.Text + "','" + ebk + "','" + dateTimePicker18.Text + "','" + dateTimePicker19.Text + "','" + textBox105.Text + "','" + textBox106.Text + "','" + textBox107.Text + "','" + dd + "','" + textBox104.Text + "')", con);
SqlCommand cmd1 = new SqlCommand("insert into holidays values('" + dd + "','" + ms + "','" + day + "','" + textBox104.Text + "')", con);
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
con.Close();
}