C# 将 List<> 传递给 SQL 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/209686/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing List<> to SQL Stored Procedure
提问by Ryan Abbott
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 应用程序选择要包含在单个报告中的项目，假设他们选择了 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.
我的问题是：有没有更好的方法来处理这样的事情？请注意，我不是在问数据库规范化或类似的问题，我的问题与代码特别相关。
回答by Kev
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 并大致相同：
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，看看他们是否添加了任何新功能来处理这种类型的事情。
回答by Jason Hymanson
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 的行程，这是一件好事。如果您的数据结构比整数列表更复杂，我建议使用 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 Reflectoryou 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.
如果你打开 string.Join with Reflector，你会发现微软正在使用一些不安全的（在 .Net 的意义上）技术，包括使用一个字符指针和一个名为 UnSafeCharBuffer 的结构。当您真正将其归结时，他们正在做的是使用指针遍历空字符串并建立连接。请记住，在 .Net 中字符串连接如此昂贵的主要原因是每次连接都会在堆上放置一个新的字符串对象，因为字符串是不可变的。这些内存操作是昂贵的。String.Join(..) 本质上是分配一次内存，然后用一个指针对其进行操作。非常快。
回答by Joe
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();
}
}
回答by Phillip Wells
See http://www.sommarskog.se/arrays-in-sql-2005.htmlfor a detailed discussion of this issue and the different approaches that you could use.
请参阅http://www.sommarskog.se/arrays-in-sql-2005.html以详细讨论此问题以及您可以使用的不同方法。
回答by Robert C. Barth
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-a-Single-Field-for-Multiple-Values-in-a-Stored-Procedure .aspx
回答by marc_s
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 hereand hereor just ask Google for "SQL Server 2008 table-valued parameters" - you'll find plenty of info and samples.
在此处和此处查看有关 TVP 的更多详细信息，或者只是向 Google 询问“SQL Server 2008 表值参数”——您会找到大量信息和示例。
Highly recommended - ifyou can move to SQL Server 2008...
强烈推荐 -如果您可以迁移到 SQL Server 2008...
回答by GaTechThomas
Why not use a table-valued parameter? https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
为什么不使用表值参数？ https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
回答by dotnetN00b
Here's a very clear-cut explanation to Table Valued Parameters from sqlteam.com: Table Valued Parameters
这是 sqlteam.com 中对表值参数的非常明确的解释：表值参数
