C# 如何在单个 SQL 连接中运行多个 SQL 命令？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13677318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to run multiple SQL commands in a single SQL connection?
提问by user1831272
I am creating a project in which I need to run 2-3 SQL commands in a single SQL connection. Here is the code I have written:
我正在创建一个项目，在该项目中我需要在单个 SQL 连接中运行 2-3 个 SQL 命令。这是我写的代码：
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\project.mdf;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("select * from " + mytags.Text + " ", con);
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
con.Close();
con.Open();
SqlCommand cmd1 = new SqlCommand("insert into " + mytags.Text + " values ('[email protected]','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','"+mytags.Text+"')", con);
cmd1.ExecuteNonQuery();
label.Visible = true;
label.Text = "Date read and inserted";
}
else
{
con.Close();
con.Open();
SqlCommand cmd2 = new SqlCommand("create table " + mytags.Text + " ( session VARCHAR(MAX) , Price int , Description VARCHAR(MAX), Date VARCHAR(20),tag VARCHAR(10))", con);
cmd2.ExecuteNonQuery();
con.Close();
con.Open();
SqlCommand cmd3 = new SqlCommand("insert into " + mytags.Text + " values ('" + Session + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + mytags.Text + "')", con);
cmd3.ExecuteNonQuery();
label.Visible = true;
label.Text = "tabel created";
con.Close();
}
I have tried to remove the error and I got that the connection is not going to else condition. Please review the code and suggest if there is any mistake or any other solution for this.
我试图消除错误，但我发现连接不会进入其他状态。请检查代码并建议是否有任何错误或任何其他解决方案。
回答by lumberHyman4
Just change the SqlCommand.CommandTextinstead of creating a new SqlCommandevery time. There is no need to close and reopen the connection.
只需更改SqlCommand.CommandText而不是SqlCommand每次都创建一个新的。无需关闭并重新打开连接。
// Create the first command and execute
var command = new SqlCommand("<SQL Command>", myConnection);
var reader = command.ExecuteReader();
// Change the SQL Command and execute
command.CommandText = "<New SQL Command>";
command.ExecuteNonQuery();
回答by abatishchev
The following should work. Keep single connection open all time, and just create new commands and execute them.
以下应该工作。始终保持单个连接打开，只需创建新命令并执行它们。
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command1 = new SqlCommand(commandText1, connection))
{
}
using (SqlCommand command2 = new SqlCommand(commandText2, connection))
{
}
// etc
}
回答by Mr Moose
This is likely to be attacked via SQL injection by the way. It'd be worth while reading up on that and adjusting your queries accordingly.
顺便说一下，这很可能是通过SQL注入攻击的。仔细阅读并相应地调整您的查询是值得的。
Maybe look at even creating a stored proc for this and using something like sp_executesqlwhich can provide some protection against this when dynamic sql is a requirement (ie. unknown table names etc). For more info, check out this link.
也许看看甚至为此创建一个存储过程并使用诸如sp_executesql 之类的东西，当需要动态 sql 时（即未知表名等），它可以提供一些保护。有关更多信息，请查看此链接。
回答by Mujah Maskey
I have not tested , but what the main idea is: put semicolon on each query.
我没有测试过，但主要思想是：在每个查询上加上分号。
SqlConnection connection = new SqlConnection();
SqlCommand command = new SqlCommand();
connection.ConnectionString = connectionString; // put your connection string
command.CommandText = @"
update table
set somecol = somevalue;
insert into someTable values(1,'test');";
command.CommandType = CommandType.Text;
command.Connection = connection;
try
{
connection.Open();
}
finally
{
command.Dispose();
connection.Dispose();
}
Update:you can follow Is it possible to have multiple SQL instructions in a ADO.NET Command.CommandText property?too
回答by Hamed_gibago
Just enable this property in your connection string:
只需在您的连接字符串中启用此属性：
sqb.MultipleActiveResultSets = true;
sqb.MultipleActiveResultSets = true;
This property allows one open connection for multiple datareaders.
此属性允许多个数据读取器打开一个连接。
回答by vlatko606
Here you can find Postgre example, this code run multiple sql commands (update 2 columns) within single SQL connection
在这里您可以找到 Postgre 示例，此代码在单个 SQL 连接中运行多个 sql 命令（更新 2 列）
public static class SQLTest
{
public static void NpgsqlCommand()
{
using (NpgsqlConnection connection = new NpgsqlConnection("Server = ; Port = ; User Id = ; " + "Password = ; Database = ;"))
{
NpgsqlCommand command1 = new NpgsqlCommand("update xy set xw = 'a' WHERE aa='bb'", connection);
NpgsqlCommand command2 = new NpgsqlCommand("update xy set xw = 'b' where bb = 'cc'", connection);
command1.Connection.Open();
command1.ExecuteNonQuery();
command2.ExecuteNonQuery();
command2.Connection.Close();
}
}
}
回答by kolaval
Multiple Non-query example if anyone is interested.
如果有人感兴趣，则多个非查询示例。
using (OdbcConnection DbConnection = new OdbcConnection("ConnectionString"))
{
DbConnection.Open();
using (OdbcCommand DbCommand = DbConnection.CreateCommand())
{
DbCommand.CommandText = "INSERT...";
DbCommand.Parameters.Add("@Name", OdbcType.Text, 20).Value = "name";
DbCommand.ExecuteNonQuery();
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@Name", OdbcType.Text, 20).Value = "name2";
DbCommand.ExecuteNonQuery();
}
}
回答by live-love
No one has mentioned this, but you can also separate your commands using a ; semicolon in the same CommandText:
没有人提到过这一点，但您也可以使用 ; 分隔您的命令。同一个 CommandText 中的分号：
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandText = @"update table ... where myparam=@myparam1 ; " +
"update table ... where myparam=@myparam2 ";
comm.Parameters.AddWithValue("@myparam1", myparam1);
comm.Parameters.AddWithValue("@myparam2", myparam2);
conn.Open();
comm.ExecuteNonQuery();
}
}
