将列表传递给SQL存储过程

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

我经常不得不将多个项目加载到数据库中的一个特定记录中。例如:web页面显示单个报表要包含的项，所有这些项都是数据库中的记录(报表是报表中的记录，项目是项目表中的记录)。用户通过web应用程序选择要包含在一个报告中的项目，假设他们选择了3个项目并提交。通过向名为ReportItems (ReportId,ItemId)的表中添加记录，这个过程将向这个报告添加这3个项目。

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.

我的问题是:有更好的方法来处理这样的事情吗?注意，我不是在问数据库规范化之类的问题，我的问题与代码有特别的关系。

8 个解决方案

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

如果您可以选择使用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.

在这里和这里查看更多关于TVP的详细信息，或者直接向谷歌询问“SQL Server 2008表值参数”——您将找到大量的信息和示例。

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

强烈推荐-如果您可以转移到SQL Server 2008…

#2

Your string join logic can probably be simplified:

您的字符串连接逻辑可能可以简化:

string items = 
    string.Join("~", itemList.Select(item=>item.ToString()).ToArray());

That will save you some string concatenation, which is expensive in .Net.

这将为您节省一些字符串连接，这在。net中是非常昂贵的。

I don't think anything is wrong with the way you are saving the items. You are limiting trips to the db, which is a good thing. If your data structure was more complex than a list of ints, I would suggest XML.

我认为你保存物品的方式没有问题。你把行程限制在db上，这是一件好事。如果您的数据结构比int列表更复杂，我建议使用XML。

Note: I was asked in the comments if this would save us any string concatenation (it does indeeed). I think it is an excellent question and would like to follow up on that.

注意:在评论中有人问我这样做是否可以为我们保存任何字符串连接(它确实需要)。我认为这是一个很好的问题，我想继续下去。

If you peel open string.Join with Reflector you will see that Microsoft is using a couple of unsafe (in the .Net sense of the word) techniques, including using a char pointer and a structure called UnSafeCharBuffer. What they are doing, when you really boil it down, is using pointers to walk across an empty string and build up the join. Remember that the main reason string concatenation is so expensive in .Net is that a new string object is placed on the heap for every concatenation, because string is immutable. Those memory operations are expensive. String.Join(..) is essentially allocating the memory once, then operating upon it with a pointer. Very fast.

如果你剥开弦。加入Reflector，您将看到微软正在使用一些不安全的技术(在. net的意义上)技术，包括使用一个char指针和一个名为UnSafeCharBuffer的结构。他们所做的，当你真正地把它简化时，就是使用指针在一个空字符串中走过去并建立连接。记住，在。net中，字符串连接非常昂贵的主要原因是每次连接时都会在堆上放置一个新的字符串对象，因为字符串是不可变的。那些内存操作很昂贵。String.Join(.. .. .)本质上是分配一次内存，然后使用指针对其进行操作。非常快。

#3

One potential issue with your technique is that it doesn't handle very large lists - you may exceed the maximum string length for your database. I use a helper method that concatenates the integer values into an enumeration of strings, each of which is less than a specified maximum (the following implementation also optionally checks for and removes duplicates ids):

您的技术的一个潜在问题是它不能处理非常大的列表—您可能会超过数据库的最大字符串长度。我使用一个助手方法，将整数值连接到字符串的枚举中，每个字符串都小于指定的最大值(下面的实现还可选择地检查和删除重复的id):

public static IEnumerable<string> ConcatenateValues(IEnumerable<int> values, string separator, int maxLength, bool skipDuplicates)
{
    IDictionary<int, string> valueDictionary = null;
    StringBuilder sb = new StringBuilder();
    if (skipDuplicates)
    {
        valueDictionary = new Dictionary<int, string>();
    }
    foreach (int value in values)
    {
        if (skipDuplicates)
        {
            if (valueDictionary.ContainsKey(value)) continue;
            valueDictionary.Add(value, "");
        }
        string s = value.ToString(CultureInfo.InvariantCulture);
        if ((sb.Length + separator.Length + s.Length) > maxLength)
        {
            // Max length reached, yield the result and start again
            if (sb.Length > 0) yield return sb.ToString();
            sb.Length = 0;
        }
        if (sb.Length > 0) sb.Append(separator);
        sb.Append(s);
    }
    // Yield whatever's left over
    if (sb.Length > 0) yield return sb.ToString();
}

Then you use it something like:

然后你用它:

using(SqlCommand command = ...)
{
    command.Connection = ...;
    command.Transaction = ...; // if in a transaction
    SqlParameter parameter = command.Parameters.Add("@Items", ...);
    foreach(string itemList in ConcatenateValues(values, "~", 8000, false))
    {
        parameter.Value = itemList;
        command.ExecuteNonQuery();
    }
}

#4

Why not use a table-valued parameter? http://msdn.microsoft.com/en-us/library/bb675163.aspx

为什么不使用表值参数呢?http://msdn.microsoft.com/en-us/library/bb675163.aspx

#5

You either do what you've already got, pass in a delimited string and then parse out to a table value, or the other choice is passing in a wodge of XML and kinda much the same:

你要么做你已经得到的事情，传入一个带分隔符的字符串，然后解析为一个表值，要么另一个选择是传入一大堆XML，几乎是一样的:

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing -列表- sql - server - 2005 - xml - parameters.aspx

I haven't had a chance to look at SQL 2008 yet to see if they've added any new functionality to handle this type of thing.

我还没有机会查看SQL 2008，看看他们是否添加了新的功能来处理这类事情。

#6

See http://www.sommarskog.se/arrays-in-sql-2005.html for a detailed discussion of this issue and the different approaches that you could use.

请参阅http://www.sommarskog.se/arrays-in-sql-2005.html，以详细讨论此问题以及可以使用的不同方法。

#7

Here's a very clear-cut explanation to Table Valued Parameters from sqlteam.com: Table Valued Parameters

下面是对sqlteam.com中的表值参数的一个非常明确的解释:表值参数

#8

Query a Single Field for Multiple Values in a Stored Procedure
http://www.norimek.com/blog/post/2008/04/Query-a-Single-Field-for-Multiple-Values-in-a-Stored-Procedure.aspx

在存储过程http://www.norimek.com/blog/post/2008/04/query中查询单个字段中的多个值

#1