C# 如何使用存储过程在 SQL 中插入多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9240288/
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 insert a multiple rows in SQL using stored procedures?
提问by Randel Ramirez
I'm able to insert the the items in a single statement but what I want to do is to have another version using a Stored Procedures. How do I do that. Here's my code:
我能够在单个语句中插入这些项目，但我想要做的是使用存储过程获得另一个版本。我怎么做。这是我的代码：
private void button1_Click(object sender, EventArgs e)
{
#region Get Values
string[] array = {textBox1.Text+":"+textBox5.Text,textBox2.Text+":"+textBox6.Text,textBox3.Text+":"+textBox7.Text,textBox4.Text+":"+textBox8.Text};
string query = "";
string product = "";
int qty = 0;
for (int i = 0; i < array.Length; i++ )
{
product = array[i].ToString().Substring(0,array[i].ToString().IndexOf(':'));
qty = int.Parse(array[i].ToString().Substring(array[i].ToString().IndexOf(':')+1));
if (string.IsNullOrEmpty(query))
{
query = "Insert Into MySampleTable Values ('"+product+"','"+qty+"')";
}
else
{
query += ",('" + product + "','" + qty + "')";
}
}
#endregion
string connect = "Data Source=RANDEL-PC;Initial Catalog=Randel;Integrated Security=True";
SqlConnection connection = new SqlConnection(connect);
connection.Open();
string insert = query;
SqlCommand command = new SqlCommand(query,connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
label5.Visible = true;
label5.Text = insert;
}
}
Sir/Ma'am, Your answers would be of great help and be very much appreciated. Thank you++
先生/女士，您的回答将有很大帮助，我们将不胜感激。谢谢++
回答by Alexey Raga
If you want to pass multiple values into a stored procedure you have two ways:
如果要将多个值传递给存储过程，您有两种方法：
And ugly one: pass your values as a separate string, split it in your store procedure, do bulk insert. You will find tonnes of examples of it in Google.
A clever one: use table-value parameters, the feature supported by both ADO.NET and SQL Server. Then you will be able to pass a parameter value and have it as a normal table variable in your stored procedure.
丑陋的一个：将您的值作为单独的字符串传递，在您的存储过程中拆分它，进行批量插入。你会在谷歌中找到大量的例子。
一个聪明的方法：使用表值参数，ADO.NET 和 SQL Server 都支持该功能。然后，您将能够传递参数值并将其作为存储过程中的普通表变量。
回答by Aaron Bertrand
In SQL Server 2008+ there are easier ways to insert multiple rows in a single statement. For example this syntax is valid:
在 SQL Server 2008+ 中，有更简单的方法可以在单个语句中插入多行。例如，此语法有效：
INSERT dbo.table(col1, col2) VALUES
(1, 2),
(2, 3),
(3, 4);
The above will insert three rows. On older versions you can do slightly more verbose things such as:
以上将插入三行。在旧版本上，您可以做一些更详细的事情，例如：
INSERT dbo.table(col1, col2)
SELECT 1, 2
UNION ALL SELECT 2, 3
UNION ALL SELECT 3, 4;
Of course your ExecuteNonQuerydoes not have to be a single command, you can pass this as a single string and it will still work:
当然，您ExecuteNonQuery不必是单个命令，您可以将其作为单个字符串传递，它仍然可以工作：
INSERT dbo.table(col1, col2) VALUES(1, 2);
INSERT dbo.table(col1, col2) VALUES(2, 3);
INSERT dbo.table(col1, col2) VALUES(3, 4);
If you want to do this in a stored procedure, you can easily perform a split on multi-valued parameters, for example if you pass in the following string:
如果您想在存储过程中执行此操作，您可以轻松地对多值参数执行拆分，例如，如果您传入以下字符串：
1,2;2,3;3,4
You could process those values using a function like the one I posted here:
您可以使用我在此处发布的函数来处理这些值：
Split value pairs and a create table using UDF
So your procedure might look like this:
因此，您的程序可能如下所示：
CREATE PROCEDURE dbo.AddOrderLineItems
@LineItems VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.OrderItems(Product, Quantity)
SELECT Product, Quantity FROM dbo.MultiSplit(@LineItems);
END
GO
And you would call it using the C# equivalent of:
您可以使用 C# 等效项调用它：
EXEC dbo.AddOrderLineItems @LineItems = '1,2;2,3;3,4';
Or you could use table-valued parameters as suggested by Alexey. A quick example:
或者您可以使用 Alexey 建议的表值参数。一个简单的例子：
CREATE TYPE OrderLineItem AS TABLE
(
Product INT,
Quantity INT
);
Then you can create a procedure:
然后你可以创建一个过程：
CREATE PROCEDURE dbo.AddOrderLineItems
@LineItems OrderLineItem READONLY
-- other parameters
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.OrderItems(Product, Quantity)
SELECT Product, Quantity FROM @LineItems;
END
GO
Then create the equivalent TVP in your C# code (I'm not the guy you want doing that; you can see an example here).
然后在您的 C# 代码中创建等效的 TVP（我不是您想要这样做的人；您可以在此处查看示例）。
However there are some caveats, please look at this question:
但是有一些警告，请看这个问题：
Creating a generalized type for use as a table value parameter
