Passing List<> to SQL Stored Procedure


I've often had to load multiple items to a particular record in the database. For example: a web page displays items to include for a single report, all of which are records in the database (Report is a record in the Report table, Items are records in Item table). A user is selecting items to include in a single report via a web app, and let's say they select 3 items and submit. The process will add these 3 items to this report by adding records to a table called ReportItems (ReportId,ItemId).

Currently, I would do something like this in in the code:

public void AddItemsToReport(string connStr, int Id, List<int> itemList)
    Database db = DatabaseFactory.CreateDatabase(connStr);

    string sqlCommand = "AddItemsToReport"
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    string items = "";
    foreach (int i in itemList)
        items += string.Format("{0}~", i);

    if (items.Length > 0)
        items = items.Substring(0, items.Length - 1);

    // Add parameters
    db.AddInParameter(dbCommand, "ReportId", DbType.Int32, Id);
    db.AddInParameter(dbCommand, "Items", DbType.String, perms);

and this in the Stored procedure:

INSERT INTO ReportItem (ReportId,ItemId)
SELECT  @ReportId,
FROM     fn_GetIntTableFromList(@Items,'~')

Where the function returns a one column table of integers.

My question is this: is there a better way to handle something like this? Note, I'm not asking about database normalizing or anything like that, my question relates specifically with the code.

10/9/2013 2:56:19 AM

If going to SQL Server 2008 is an option for you, there's a new feature called "Table-valued parameters" to solve this exact problem.

Check out more details on TVP here and here or just ask Google for "SQL Server 2008 table-valued parameters" - you'll find plenty of info and samples.

Highly recommended - if you can move to SQL Server 2008...

12/10/2011 12:28:16 AM

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow