Passing List<> to SQL Stored Procedure

Question

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);     db.ExecuteNonQuery(dbCommand); } 

and this in the Stored procedure:

INSERT INTO ReportItem (ReportId,ItemId) SELECT  @ReportId,           Id 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.

Answer 1

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