通过列表＆LT;＆GT;到SQL存储过程 [英] Passing List<> to SQL Stored Procedure

问题描述

我经常不得不多个项目加载到数据库中的特定记录。例如：一个网页显示项目包括一个单一的报告，所有这些都记录在数据库中（报告在报告表中的记录，项目在项目表中的记录）。用户是选择项通过Web应用程序的单一报告中，让我们说，他们选择3项，并提交。这一过程将通过添加记录的表名为ReportItems（ReportId，ITEMID）添加这些3项本报告。

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

目前，我会做这样的事情在code：

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

和这个存储过程：

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

当函数返回一个整数的一列的表。

Where the function returns a one column table of integers.

我的问题是：有没有更好的方式来处理这样的事情？请注意，我不要求有关数据库正火或类似的东西，我的问题与code具体涉及。

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.

推荐答案

如果将SQL Server 2008是一个选择，有一个被称为表值参数的新功能来解决这个确切的问题。

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.

检查出TVP 更多的细节这里和 href=\"http://www.mssqltips.com/tip.asp?tip=1483\">或只问谷歌SQL Server 2008中的表值参数 - 你会发现很多的信息和样品。

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.

强烈推荐 - 如果的你可以移动到SQL Server 2008 ...

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

这篇关于通过列表＆LT;＆GT;到SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！