如何在一个连接中创建两个命令? [英] How can I create two commands in one connection?
本文介绍了如何在一个连接中创建两个命令?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
例如,我想 INSERT
数据库中的数据,并且还要 UPDATE
另一个表。我的代码是这样的
For example I want to INSERT
data in database and also UPDATE
another table. My code is like this
SqlConnection con = new SqlConnection("**");
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "INSERT Borrowbook VALUES (@StudentID, @ISBN, @Title, @Date)";
SqlParameter p1 = new SqlParameter("@StudentID", SqlDbType.NChar);
p1.Value = textBox2.Text;
cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@ISBN", SqlDbType.NVarChar);
p2.Value = textBox4.Text;
cmd.Parameters.Add(p2);
SqlParameter p3 = new SqlParameter("@Title", SqlDbType.VarChar);
p3.Value = textBox3.Text;
cmd.Parameters.Add(p3);
SqlParameter p4 = new SqlParameter("@Date", SqlDbType.DateTime);
p4.Value = dateTimePicker1.Text;
cmd.Parameters.Add(p4);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("The books has been successfully borrowed!",
"Information ... ",
MessageBoxButtons.OK,
MessageBoxIcon.Information,
MessageBoxDefaultButton.Button1);
推荐答案
首先,您真的应该使用using语句,因此如果发生异常,您的连接将关闭
First of all you really should be using using statements so your connections get closed in the event of an exception
using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand()) //The create command can happen before the open
{
con.Open();
cmd.CommandText = "INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date)";
//(Snip adding parameters)
cmd.ExecuteNonQuery();
//You don't need to call close if you are using "using"
}
这样做有三种方法。
您可以将两个命令放在一个命令语句中。
You could put both commands in a single command statement.
using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = @"INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date);
INSERT INTO StudentActvitiy ([Student ID], Date) VALUES ( @StudentID, GETDATE())";
//(Snip adding parameters)
cmd.ExecuteNonQuery();
}
或者您可以更改命令文本并再次运行
or you could change the command text and run it again
using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = "INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date)";
//(Snip adding parameters)
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO StudentActvitiy ([Student ID], Date) VALUES ( @StudentID, GETDATE())"
cmd.ExecuteNonQuery();
}
或者您可以执行两个命令
or you could do two commands
using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand())
using(SqlCommand cmd2 = con.CreateCommand())
{
con.Open();
cmd.CommandText = "INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date)";
//(Snip adding parameters)
cmd.ExecuteNonQuery();
cmd2.CommandText = "INSERT INTO StudentActvitiy ([Student ID], Date) VALUES ( @StudentID, GETDATE())"
SqlParameter p21 = new SqlParameter("@StudentID", SqlDbType.NChar);
p21.Value = textBox2.Text;
cmd2.Parameters.Add(p21);
cmd2.ExecuteNonQuery();
}
要做 Tim的解决方案它是第一种和第三种的组合。
To do Tim's solution it is kind of a combination of the first and the 3rd.
using(SqlConnection con = new SqlConnection("**********************************************"))
using(SqlCommand cmd = con.CreateCommand())
using(SqlCommand cmd2 = con.CreateCommand())
{
con.Open();
cmd.CommandText = @"INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date);
SELECT CAST(SCOPE_IDENTITY AS INT);";
//(Snip adding parameters)
var resultId = (int)cmd.ExecuteScalar();
cmd2.CommandText = "INSERT INTO StudentActvitiy ([Student ID], Date, BorrowBookId) VALUES ( @StudentID, GETDATE(), @borrowBookId)"
SqlParameter p21 = new SqlParameter("@StudentID", SqlDbType.NChar);
p21.Value = textBox2.Text;
cmd2.Parameters.Add(p21);
SqlParameter p22 = new SqlParameter("@borrowBookId", SqlDbType.Int);
p22.Value = resultId;
cmd2.Parameters.Add(p22);
cmd2.ExecuteNonQuery();
}
这篇关于如何在一个连接中创建两个命令?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文