传递列表<>；到SQL存储过程

本文关键字：SQL 存储过程 lt 列表 gt | 更新日期: 2023-09-27 17:47:47

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

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

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

其中，函数返回一个单列整数表。

我的问题是：有没有更好的方法来处理这样的事情？注意，我并不是在问数据库规范化之类的问题，我的问题具体涉及代码。

传递列表<>；到SQL存储过程

如果您可以选择使用SQL Server 2008，那么有一个名为"表值参数"的新功能可以解决这个确切的问题。

在这里和这里查看TVP的更多详细信息，或者只需向谷歌询问"SQLServer2008表值参数"，你就会发现大量的信息和示例。

强烈建议-如果您可以移动到SQL Server 2008…

您的字符串连接逻辑可能会被简化：

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

这将为您节省一些字符串串联，而这在.Net.中是昂贵的

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

注意：我在评论中被问到，这是否会为我们节省任何字符串串联（它确实是独立的）。我认为这是一个很好的问题，我想跟进这个问题。

如果你剥开绳子。加入Reflector，你会发现微软正在使用一些不安全的（在.Net这个词的意义上）技术，包括使用一个字符指针和一个名为UnSafeCharBuffer的结构。当你真正把它归结起来时，他们所做的就是使用指针遍历一个空字符串并建立连接。请记住，字符串串联在.Net中如此昂贵的主要原因是，每次串联都会在堆上放置一个新的字符串对象，因为字符串是不可变的。那些内存操作非常昂贵。String.Join（..）本质上是分配内存一次，然后用指针对其进行操作。非常快。

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

然后你用它类似于：

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

您要么执行现有操作，传入一个分隔字符串，然后解析为一个表值，要么另一种选择是传入一堆XML，大致相同：

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

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

为什么不使用表值参数？https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

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

以下是对sqlteam.com中表值参数的非常明确的解释：表值参数

在存储过程中查询单个字段的多个值
http://www.norimek.com/blog/post/2008/04/Query-a-Single-Field-for-Multiple-Values-in-a-Stored-Procedure.aspx

相关文章：