如何插入使用存储过程在SQL中多行? [英] How to insert a multiple rows in SQL using stored procedures?
问题描述
我能插在一个声明中的项目,但我想要做的是有一个使用存储过程的另一个版本。我怎么做。这里是我的代码:
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++
推荐答案
在SQL Server的2008+也有更简单的方法在一个单独的语句来插入多行。例如这个语法是有效的:
In SQL Server 2008+ there are easier ways to insert multiple rows in a single statement. For example this syntax is valid:
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;
当然你的的ExecuteNonQuery
不必是一个命令,你可以通过这个作为一个字符串,它仍然可以工作:
Of course your ExecuteNonQuery
does not have to be a single command, you can pass this as a single string and it will still work:
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:
所以你的过程可能是这样的:
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
和你会使用C#相当于调用它:
And you would call it using the C# equivalent of:
EXEC dbo.AddOrderLineItems @LineItems = '1,2;2,3;3,4';
或阿列克谢的建议你可以使用表值参数。一个简单的例子:
Or you could use table-valued parameters as suggested by Alexey. A quick example:
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
然后在你的C#代码创建了相当于TVP(我又不是你想这样做的家伙,你可以在这里看到一个例子。一>)
但也有一些注意事项,请看看这个问题:
However there are some caveats, please look at this question:
这篇关于如何插入使用存储过程在SQL中多行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!