How to insert multiple rows in SQL using stored procedures?

Question

I can insert elements into one statement, but what I want to do is have a different version using stored procedures. How to do it. Here is 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 will be of great help and will be greatly appreciated. Thanks ++

Answer 1

SQL Server 2008+ has easier ways to insert multiple rows into a single statement. For example, this syntax is valid:

 INSERT dbo.table(col1, col2) VALUES (1, 2), (2, 3), (3, 4); 

The above inserts three lines. In older versions, you can do a few more detailed 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 line, 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 split into multi-valued parameters, for example, if you pass the following line:

 1,2;2,3;3,4 

You can process these values ​​with a function similar to the one I posted here:

Separate value pairs and create a 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:

 EXEC dbo.AddOrderLineItems @LineItems = '1,2;2,3;3,4'; 

Or you can use the table parameters suggested by Alexey. 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 an equivalent TVP in C # code (I'm not the guy you want to make, you can see an example here ).

However, there are some reservations, please look at this question:

Creating a generic type to use table value as a parameter

Answer 2

If you want to pass multiple values ​​to a stored procedure, you have two ways:

• And ugly: pass your values ​​as a separate line, separate it in your store procedure, make a volume insert. You will find tons of examples from Google.

• Smart: Use table value options, a function supported by both ADO.NET and SQL Server. Then you can pass the value of the parameter and use it as a regular table variable in a stored procedure.