如何正确和有效地重用C＃.NET（SQL Server）中的准备好的语句？ [英] How to correctly and efficiently reuse a prepared statement in C# .NET (SQL Server)?
问题描述
我看了很多问题，但是很明显我的 SO-fu 不能胜任这项任务，所以我来了。我试图有效地使用准备好的语句，而不仅仅是指对单个语句进行参数化，而是要对其进行多次编译以供重用。我的问题在于参数和重用以及如何正确实现。
I looked at lots of questions but evidently my SO-fu isn't up to the task, so here I am. I am trying to efficiently use prepared statements, and I don't just mean parameterizing a single statement, but compiling one for reuse many times. My question lies around the parameters and reuse and how to implement that correctly.
通常，我遵循此过程（人为的示例）：
Generally I follow this procedure (contrived example):
SqlConnection db = new SqlConnection(...);
SqlCommand s = new SqlCommand("select * from foo where a=@a", db);
s.Parameters.Add("@a", SqlDbType.VarChar, 8);
s.Prepare();
...
s.Parameters["@a"] = "bozo";
s.Execute();
超级好用。但是，我不想每次运行此查询时都执行所有这些步骤（或后四个步骤）。这似乎抵消了准备好的语句的整个想法。在我看来，我只需要更改参数并重新执行，但问题是如何执行？
Super, that works. However, I don't want to do all of these steps (or the latter four) every time I run this query. That seems like it's counteracting the whole idea of prepared statements. In my mind I should only have to change the parameters and re-execute, but the question is how to do that?
我尝试了 s。 Parameters.Clear（），但这实际上会删除参数本身，而不仅仅是值，因此我基本上需要重新添加并再次准备，这似乎也打破了重点。
I tried s.Parameters.Clear(), but this actually removes the parameters themselves, not just the values, so I would essentially need to re-Add the parameters and re-Prepare again, which would seem to break the whole point as well. No thanks.
这时，我需要遍历 s.Parameters 并将它们全部设置为null或其他一些价值。 这对吗？不幸的是，在我当前的项目中，我有约15个参数的查询，每次运行都需要执行约10,000次。我可以将这种迭代分解为一个方法，但是想知道是否有更好的方法（没有存储的proc）。
At this point I am left with iterating through s.Parameters and setting them all to null or some other value. Is this correct? Unfortunately in my current project I have queries with ~15 parameters which need to be executed ~10,000 times per run. I can shunt this iteration off into a method but was wondering if there is a better way to do this (without stored procs).
我目前的解决方法是扩展方法， SqlParameterCollection.Nullify ，将所有参数设置为null，这对于我的情况很好。我只是在执行后运行它。
My current workaround is an extension method, SqlParameterCollection.Nullify, that sets all the parameters to null, which is fine for my case. I just run this after an execute.
我发现了一些几乎相同但（IMHO）未解决的问题：
I found some virtually identical but (IMHO) unanswered questions:
SQLite / C＃连接池和预准备的语句混淆（Serge非常接近回答！）
SQLite/C# Connection Pooling and Prepared Statement Confusion (Serge was so close to answering!)
我能找到的最佳答案是（1）以上常识和（2）此页面：
The best answer I could find is (1) common sense above and (2) this page:
http://msdn.microsoft.com/en-us/magazine/cc163799.aspx
推荐答案
重新使用准备好的SqlCommand时，您肯定需要做的就是将参数值设置为新值？使用完后，您无需清除它们。
When re-using a prepared SqlCommand, surely all you need to do is set the parameter values to the new ones? You don't need to clear them out after use.
对于我自己来说，最近十年来我还没有看到DBMS的产生，从准备中可以得到任何明显的好处一条语句（我想如果DB Server可能达到其CPU的极限，但这不是典型的）。您确定必须进行准备吗？
For myself, I haven't seen a DBMS produced in the last 10 years which got any noticeable benefit from preparing a statement (I suppose if the DB Server was at the limits of its CPU it might, but this is not typical). Are you sure that Preparing is necessary?
除非您从外部来源上传，否则运行〜10,000次每次运行相同的命令对我来说有点难闻。在这种情况下，批量加载可能会有所帮助？每次跑步在做什么？
Running the same command "~10,000 times per run" smells a bit to me, unless you're uploading from an external source. In that case, Bulk Loading might help? What is each run doing?
欢呼-
这篇关于如何正确和有效地重用C＃.NET（SQL Server）中的准备好的语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
