在c#中使用SqlCommand Prepare的利弊?

When i was reading books to learn C# (might be some old Visual Studio 2005 books) I've encountered advice to always use SqlCommand.Prepare everytime I execute SQL call (whether its' a SELECT/UPDATE or INSERT on SQL SERVER 2005/2008) and I pass parameters to it. But is it really so?

当我阅读学习c#(可能是一些旧的Visual Studio 2005书籍)的书籍时，我遇到了使用SqlCommand的建议。每次执行SQL调用时(无论是在SQL SERVER 2005/2008上执行SELECT/UPDATE还是INSERT)都要做好准备，并将参数传递给它。但真的是这样吗?

Should it be done every time? Or just sometimes?

应该每次都这么做吗?或者只是有时?
Does it matter whether it's one parameter being passed or five or twenty?

它是传递一个参数，还是传递五个或二十个参数，这有关系吗?
What boost should it give if any? Would it be noticeable at all (I've been using SqlCommand.Prepare here and skipped it there and never had any problems or noticeable differences).

如果有的话，会有什么促进作用呢?会不会很明显(我一直在使用SqlCommand)。准备这里，跳过那里，从来没有任何问题或明显的差异)。

For the sake of the question this is my usual code that I use, but this is more of a general question.

为了这个问题，这是我常用的代码，但这是一个一般性的问题。

public static decimal pobierzBenchmarkKolejny(string varPortfelID, DateTime data, decimal varBenchmarkPoprzedni, decimal varStopaOdniesienia) {
    const string preparedCommand = @"SELECT [dbo].[ufn_BenchmarkKolejny](@varPortfelID, @data, @varBenchmarkPoprzedni,  @varStopaOdniesienia) AS 'Benchmark'";
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP)) //if (varConnection != null) {
    using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) {
        sqlQuery.Prepare();
        sqlQuery.Parameters.AddWithValue("@varPortfelID", varPortfelID);
        sqlQuery.Parameters.AddWithValue("@varStopaOdniesienia", varStopaOdniesienia);
        sqlQuery.Parameters.AddWithValue("@data", data);
        sqlQuery.Parameters.AddWithValue("@varBenchmarkPoprzedni", varBenchmarkPoprzedni);
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                while (sqlQueryResult.Read()) {

                }
            }
    }
}

Additional clarification:

额外的澄清:

If i move sqlQuery.Prepare() like in code below exception is thrown that the size has to be explicitly declared, which basically leads me to thinking that having sqlQuery.Prepare() as first makes it useless? Can someone show the proper usage using my example?

如果我移动sqlQuery.Prepare()，比如在下面的代码中，就会抛出必须显式声明大小的问题，这基本上会导致我认为首先使用sqlQuery.Prepare()会使它变得无用?有人能用我的例子说明正确的用法吗?

public static decimal pobierzBenchmarkKolejny(string varPortfelID, DateTime data, decimal varBenchmarkPoprzedni, decimal varStopaOdniesienia) {
    const string preparedCommand = @"SELECT [dbo].[ufn_BenchmarkKolejny](@varPortfelID, @data, @varBenchmarkPoprzedni,  @varStopaOdniesienia) AS 'Benchmark'";
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP)) //if (varConnection != null) {
    using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) {

        sqlQuery.Parameters.AddWithValue("@varPortfelID", varPortfelID);
        sqlQuery.Parameters.AddWithValue("@varStopaOdniesienia", varStopaOdniesienia);
        sqlQuery.Parameters.AddWithValue("@data", data);
        sqlQuery.Parameters.AddWithValue("@varBenchmarkPoprzedni", varBenchmarkPoprzedni);
        sqlQuery.Prepare();
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                while (sqlQueryResult.Read()) {

                }
            }
    }
}

How would I do that? By adding .size next to parameters and doing varPortfel.Lenght if it's a string etc?

我该怎么做呢?通过在参数旁边添加.size并执行varPortfel。如果是一根绳子?

4 个解决方案

#1

From the MSDN Documentation:

从MSDN文档:

"Before you call Prepare, specify the data type of each parameter in the statement to be prepared. For each parameter that has a variable length data type, you must set the Size property to the maximum size needed. Prepare returns an error if these conditions are not met.

在调用Prepare之前，请在要准备的语句中指定每个参数的数据类型。对于具有可变长度数据类型的每个参数，必须将Size属性设置为所需的最大大小。如果没有满足这些条件，则准备返回一个错误。

If you call an Execute method after calling Prepare, any parameter value that is larger than the value specified by the Size property is automatically truncated to the original specified size of the parameter, and no truncation errors are returned.

如果在调用Prepare后调用Execute方法，则任何大于Size属性指定值的参数值将自动截断为参数的原始指定大小，并且不会返回截断错误。

Output parameters (whether prepared or not) must have a user-specified data type. If you specify a variable length data type, you must also specify the maximum Size."

输出参数(无论是否准备)必须具有用户指定的数据类型。如果指定了可变长度数据类型，则还必须指定最大大小。

Furthermore, "If the CommandType property is set to TableDirect, Prepare does nothing. If CommandType is set to StoredProcedure, the call to Prepare should succeed, ..."

此外，“如果CommandType属性被设置为TableDirect，那么Prepare什么都不做。”如果将CommandType设置为StoredProcedure，则调用Prepare应该成功……

This in general is used to make sure that the end user is not using a SQL Injection technique to add or remove information you do not want them too from the database.

这通常用于确保最终用户没有使用SQL注入技术来添加或删除您不希望它们也从数据库中删除的信息。

I looked into it and check out this article http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare.aspx. Your issue is you need to define your parameters before you run .Prepare() and then set your parameters after you run .Prepare(). Right now you are doing both before. I would try something like this (Note I didn't test it so my syntax might be a bit off).

我查看了本文http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare.aspx。您的问题是在运行. prepare()之前需要定义参数，然后在运行. prepare()后设置参数。现在你以前同时做这两件事。我将尝试这样的东西(注意，我没有测试它，所以我的语法可能有点错误)。

public static decimal pobierzBenchmarkKolejny(string varPortfelID, DateTime data, decimal varBenchmarkPoprzedni, decimal varStopaOdniesienia) {
    const string preparedCommand = @"SELECT [dbo].[ufn_BenchmarkKolejny](@varPortfelID, @data, @varBenchmarkPoprzedni,  @varStopaOdniesienia) AS 'Benchmark'";
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP)) //if (varConnection != null) {
    using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) {

        sqlQuery.Parameters.Add("@varPortfelID");
        sqlQuery.Parameters.Add("@varStopaOdniesienia");
        sqlQuery.Parameters.Add("@data");
        sqlQuery.Parameters.Add("@varBenchmarkPoprzedni");

        sqlQuery.Prepare();
        sqlQuery.ExecuteNonQuery();//This might need to be ExecuteReader()

        sqlQuery.Parameters[0].Value = varPortfelID;
        sqlQuery.Parameters[1].Value = varStopaOdniesienia;
        sqlQuery.Parameters[2].Value = data;
        sqlQuery.Parameters[3].Value = varBenchmarkPoprzedni;

        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                while (sqlQueryResult.Read()) {

                }
            }
    }
}

#2

The other benefit is that by doing this the SQL query plan is compiled, cached and re-used. This is not a big deal if for a low volume of calls to your query but if you have a lot there really are some significant performance advantages to doing this.

另一个好处是，通过这样做，SQL查询计划将被编译、缓存和重用。对于少量的查询调用来说，这并不是什么大问题，但是如果您有大量的查询，那么这样做确实有一些显著的性能优势。

#3

From my own experience: the performance boost is VERY significant. Some time ago I worked on a project where we used our own object-relational mapping. We utilized huge database as a persistent store of complex object model - with on-demand object loading and weak-referenced object life-time.

从我自己的经验来看:性能提升是非常重要的。不久前，我在一个项目中使用了我们自己的对象关系映射。我们使用巨大的数据库作为复杂对象模型的持久存储——具有按需对象加载和弱引用对象生命周期。

Using prepared commands was crucial for success of that application, as it alone made the system actually usable.

使用预先准备好的命令对于该应用程序的成功至关重要，因为只有它才能使系统真正可用。

In other words: if you execute many SQL commands - that are exactly same or differ only in parameter values -, you'll see huge performance boost.

换句话说:如果您执行许多SQL命令(它们在参数值上完全相同或不同)，您将看到巨大的性能提升。

I don't have exact numbers or links, but I can testify my own experience.

我没有确切的数字或链接，但我可以证明我自己的经验。

#4

-1

According to IDbCommand.Prepare Method documentation:

根据IDbCommand。准备方法的文档:

The server automatically caches plans for reuse as necessary; therefore, there is no need to call this method directly in your client application.

服务器自动缓存计划，以便在必要时进行重用;因此，不需要在客户端应用程序中直接调用此方法。

I also found this Databases Administrators answer which give you a lot of details about the prepare method and why I didn't get any significant improvement.

我还发现这个数据库管理员的回答提供了关于准备方法的许多细节，以及为什么我没有得到任何显著的改进。

About the SQL injection, you'll be protected against it because you have used a parameterized sqlCommand... not because you have called the prepare method.

关于SQL注入，您将受到保护，因为您使用了参数化的sqlCommand…不是因为你叫准备方法。

#1