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;
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:
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:
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
Then create the equivalent TVP in your C# code (I'm not the guy you want doing that; you can see an example here).
However there are some caveats, please look at this question:
Creating a generalized type for use as a table value parameter