代码之家 › 专栏 › 技术社区 › Ryan Abbott

将列表<>传递给SQL存储过程

stored-procedures tsql sql-server sql c#

Ryan Abbott · 技术社区 · 16 年前

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

其中函数返回一列整数表。

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

8 回复 | 直到 11 年前

Tim Cooper 13 年前

如果您可以选择使用SQLServer2008,那么有一个名为“表值参数”的新特性可以解决这个问题。

查看更多关于TVP的详细信息 here here 或者直接向Google询问“sqlserver2008表值参数”——您会发现大量的信息和示例。

强烈推荐- 您可以移动到SQL Server 2008。。。

Jason Jackson 16 年前

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

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

这将为您节省一些字符串连接,这在.Net中非常昂贵。

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

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

to StackOverflow 16 年前

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

erlando 11 年前

你要么做你已经得到的事情,传入一个带分隔符的字符串,然后解析出一个表值,要么另一个选择是传入一堆XML,大致相同:

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

Kolappan N t-clausen.dk 5 年前

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

Phillip Wells 16 年前

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

dotnetN00b 14 年前

下面是对sqlteam.com中表值参数的一个非常清晰的解释: Table Valued Parameters

Robert Harvey 13 年前

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