CODEWITHSUNDEEP
asp.netsqlstored-procedures
MrM
MrM

Reputation: 21989

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?

Upvotes: 2

Views: 3076

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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();
}

Upvotes: 7

Chris Fulstow
Chris Fulstow

Reputation: 41872

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.

Upvotes: 0

Justin Niessner
Justin Niessner

Reputation: 245389

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

Upvotes: 0

Related Questions