How to insert a multiple rows in SQL using stored procedures?

Question

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++

There are so many things wrong. I think you need to research a lot about data access layer, prevent sql injection, Enterprise Library Data Block (a lib to help you with your data access layer if you want), and after that I think you can do it right. I could answer your question but I prefer to really help you telling you things to study. — Bruno Costa, Feb 11 '12 at 12:52
As a side-note: you should use **parametrized queries** for your SQL inserts - you shouldn't just be concatenating together your SQL statements - that opens doors to SQL injection attacks. [See how to do parametrized queries here](http://www.4guysfromrolla.com/webtech/092601-1.shtml) — marc_s, Feb 11 '12 at 12:53

score 10 · Answer 1 · edited May 23 '17 at 10:28

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

score 4 · Answer 2 · answered Feb 11 '12 at 12:48

4

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.

answered Feb 11 '12 at 12:48

Alexey Raga

7,457
1
31
40

1

Do you have an example for the table-value parameters? Thank you. – Randel Ramirez Feb 13 '12 at 08:50

How to insert a multiple rows in SQL using stored procedures?

2 Answers2

Linked