StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPassing List<> to SQL Stored Procedure
text
Body
copied!<p>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).</p> <p>Currently, I would do something like this in in the code:</p> <pre><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); } </code></pre> <p>and this in the Stored procedure:</p> <pre><code>INSERT INTO ReportItem (ReportId,ItemId) SELECT @ReportId, Id FROM fn_GetIntTableFromList(@Items,'~') </code></pre> <p>Where the function returns a one column table of integers.</p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload