asp.netsqlstored-procedures

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?

Solution

  • 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