Can I pass a list to stored procedure?

I have the following list

ID | DESC | PRICE 
10 | SHOE | 5000
11 | LACE | 3000
12 | GAME | 2000
13 | TOAD | 3000

I am now passing individual rows in a foreach loop, and establishing a new connection all the time, which looks unconventional but I am hoping there is a faster way.

This is the code I have.

foreach(var item in tempList)
{
     using (connection)
     {
        SqlCommand command = new SqlComman("StoredProc", connection);
        command.Parameters.Add(new SqlParameter("id", item.id));
        command.Parameters.Add(new SqlParameter("desc", item.desc));
        command.Parameters.Add(new SqlParameter("price", item.price));
        ...
     }
}

So how do I pass a list to a stored procedure?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

To give a practical example of TVPs, in addition to the links (which are definitely worthwhile reading). Assuming SQL Server 2008 or better.

First, in SQL Server:

CREATE TYPE dbo.Items AS TABLE
(
  ID          INT,
  Description VARCHAR(32),
  Price       INT
);
GO

CREATE PROCEDURE dbo.StoredProcedure
  @Items AS dbo.Items READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO dbo.DestinationTable(ID, [DESC], Price)
    SELECT ID, Description, Price FROM @Items;
END
GO

Now in C#:

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID"));
tvp.Columns.Add(new DataColumn("Description"));
tvp.Columns.Add(new DataColumn("Price"));

foreach(var item in tempList)
{ 
    tvp.Rows.Add(item.id, item.desc, item.price); 
}

using (connection)
{
    SqlCommand cmd = new SqlCommand("StoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@Items", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    connection.Open();
    cmd.ExecuteNonQuery();
}

Method 2

You could declare a custom table data type in SQL Server, use that as a parameter to your stored procedure, use a DataTable in your code, and fill it with the rows.

Read more on MSDN: Table-Valued Parameters

Method 3

You could take a look at using Table-Valued Parameters to pass all the rows in one call as a single parameter:

Table-valued parameters provide an easy way to marshal multiple rows
of data from a client application to SQL Server without requiring
multiple round trips or special server-side logic for processing the
data. You can use table-valued parameters to encapsulate rows of data
in a client application and send the data to the server in a single
parameterized command. The incoming data rows are stored in a table
variable that can then be operated on by using Transact-SQL.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0