通过名单<>到 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).

目前，我会在代码中做这样的事情:

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,'~')

函数返回一列整数表.

我的问题是:有没有更好的方法来处理这样的事情?请注意，我不是在问数据库规范化或类似问题，我的问题与代码特别相关.

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 的更多详细信息 这里 和 这里 或者只是向 Google 询问SQL Server 2008 表-valued 参数" - 您会找到大量信息和示例.

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

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