从.NET可以获得由SqlCommand对象生成的完整SQL字符串（使用SQL参数）吗？

From the .NET environment can I get access to the full SQL string that is generated by a SqlCommand object?

从.NET环境中,我可以访问由SqlCommand对象生成的完整SQL字符串吗?

Note: The full SQL string shows up in Intellisense hover, in VisualStudio, while in debug mode.

注意:在调试模式下,完整的SQL字符串显示在VisualStudio中的Intellisense悬停中。

I'm willing to use reflection techniques if I must. I'm sure somebody here knows a way to get at it.

如果必须,我愿意使用反射技术。我相信这里有人知道一种方法来实现它。

---

Update 1:
I'm calling a stored procedure having parameters with cmd.CommandType = CommandType.StoredProcedure and am trying to acquire the full SQL generated and run. I wonder if the cmd.Prepare() method might not prove useful in this circumstance, if it might store the full string in a state field or something like that.

更新1:我正在调用具有cmd.CommandType = CommandType.StoredProcedure参数的存储过程,并且我正在尝试获取生成并运行的完整SQL。我想知道cmd.Prepare()方法在这种情况下是否有用,如果它可能将完整的字符串存储在状态字段或类似的东西中。

---

Update 2:

In light of answers below (and referenced) that indicate no complete SQL string is generated internally during preparation or execution, I did a bit of poking around using .NET Reflector. Even the internal connection classes seem to pass objects rather than boiling them down to strings, for example:

根据下面的答案(和引用)表明在准备或执行过程中没有内部生成完整的SQL字符串,我做了一些使用.NET Reflector。即使是内部连接类似乎也会传递对象而不是将它们归结为字符串,例如:

internal abstract void AddPreparedCommand(SqlCommand cmd);
Declaring Type: System.Data.SqlClient.SqlInternalConnection
Assembly: System.Data, Version=2.0.0.0

内部抽象void AddPreparedCommand(SqlCommand cmd);声明类型:System.Data.SqlClient.SqlInternalConnection程序集:System.Data,Version = 2.0.0.0

---

In general, thanks to everybody for the level of detail you got into to prove what can be done and show what's actually happening. Much appreciated. I like thorough explanations; they add surety and lend credence to the answers.

总的来说,感谢每个人的细节水平,以证明可以做什么,并展示实际发生的事情。非常感激。我喜欢彻底的解释;他们增加了保证,并为答案提供了信任。

5 个解决方案

#1

A simple loop replacing all the parameter names with their values will provide you with something similar to what the end result is, but there are several problems.

一个简单的循环用它们的值替换所有参数名称将为您提供类似于最终结果的东西,但是有几个问题。

1. Since the SQL is never actually rebuilt using the parameter values, things like newlines and quotes don't need to be considered

由于SQL实际上从未使用参数值进行重建,因此不需要考虑换行和引号等内容

2. Parameter names in comments are never actually processed for their value, but left as-is

注释中的参数名称从不实际处理其值,而是保持原样

With those in place, and taking into account parameter names that starts with the same characters, like @NAME and @NAME_FULL, we can replace all the parameter names with the value that would be in the place of that parameter:

有了这些参数,并考虑了以相同字符开头的参数名称,如@NAME和@NAME_FULL,我们可以将所有参数名称替换为该参数所在的值:

string query = cmd.CommandText;
foreach (SqlParameter p in cmd.Parameters.OrderByDescending(p => p.ParameterName.Length))
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}

there is one problem left with this, however, and that is if a parameter is a string, then the SQL that initially looks like this:

但是,还有一个问题,那就是如果参数是一个字符串,那么SQL最初看起来像这样:

SELECT * FROM yourtable WHERE table_code = @CODE

will look like this:

将如下所示:

SELECT * FROM yourtable WHERE table_code = SOME CODE WITH SPACES

This is clearly not legal SQL, so we need to account for some parameter-types as well:

这显然不是合法的SQL,所以我们也需要考虑一些参数类型:

DbType[] quotedParameterTypes = new DbType[] {
    DbType.AnsiString, DbType.Date,
    DbType.DateTime, DbType.Guid, DbType.String,
    DbType.AnsiStringFixedLength, DbType.StringFixedLength
};
string query = cmd.CommandText;

var arrParams = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(arrParams, 0);

foreach (SqlParameter p in arrParams.OrderByDescending(p => p.ParameterName.Length))
{
    string value = p.Value.ToString();
    if (quotedParameterTypes.Contains(p.DbType))
        value = "'" + value + "'";
    query = query.Replace(p.ParameterName, value);
}

#2

There have been a couple of similar questions here.

这里有几个类似的问题。

The most compelling answer was provided to this question: How to get the generated SQL-Statment from a SqlCommand-Object?

这个问题提供了最引人注目的答案:如何从SqlCommand-Object获取生成的SQL-Statment?

and the answer was:

答案是:

You can't, because it does not generate any SQL.

你不能,因为它不会生成任何SQL。

The parameterized query (the one in CommandText) is sent to the SQL Server as the equivalent of a prepared statement. When you execute the command, the parameters and the query text are treated separately. At no point in time a complete SQL string is generated.

参数化查询(CommandText中的查询)作为预准备语句的等效项发送到SQL Server。执行命令时,将分别处理参数和查询文本。在任何时候都不会生成完整的SQL字符串。

You can use SQL Profiler to take a look behind the scenes.

您可以使用SQL事件探查器来了解幕后。

#3

The CommandText property (or calling ToString()) on your command will give you all of the SQL, with a small exception. It will definitely give you anything you see in the debugger. Note that this won't give you parameter values, but it will give you the actual command.

命令上的CommandText属性(或调用ToString())将为您提供所有SQL,但有一个小例外。它肯定会给你在调试器中看到的任何东西。请注意,这不会为您提供参数值,但它会为您提供实际命令。

The only caveat is that when CommandType is Text, the ADO.NET framework will often (in fact, almost always) use sp_executesql to execute the command rather than executing the command directly against the connection. In that sense, it's not possible to obtain the exact SQL that gets executed.

唯一需要注意的是,当CommandType是Text时,ADO.NET框架通常(事实上,几乎总是)使用sp_executesql来执行命令,而不是直接对连接执行命令。从这个意义上说,不可能获得执行的确切SQL。

#4

I haven't tried this, but you may be able to use Capture Mode if you are willing to use SMO:

我没试过这个,但是如果你愿意使用SMO,你可以使用捕获模式:

http://msdn.microsoft.com/en-us/library/ms162182(v=sql.120).aspx

#5

I like Jesus Ramos answer, but I needed support for output parameters. (I also used a string builder to generate the content.)

我喜欢Jesus Ramos的回答,但我需要支持输出参数。 (我还使用字符串生成器来生成内容。)

Declare Parameter for output parameters

声明输出参数的参数

 foreach (SqlParameter p in arrParams.Where(x => x.Direction == ParameterDirection.Output || x.Direction == ParameterDirection.InputOutput))
    {
        // todo : I only needed a couple of types supported, you could add addition types
        string dbtype = string.Empty;
        switch (p.DbType)
        {
            case DbType.Guid:
                dbtype = "uniqueidentifier";
                break;
            case DbType.Int16:
            case DbType.Int64:
            case DbType.Int32:
                dbtype = "int";
                break;
            case DbType.String:
                dbtype = "varchar(max)";
                break;
        }

        query.Append(string.Format(" Declare {0}_ {1}\n", p.ParameterName, dbtype));
    }

Build Main Parameter Area

建立主要参数区域

foreach (SqlParameter p in arrParams)
    {
        bool isLast = p == last;
        string value = p.Value.ToString();
        if (quotedParameterTypes.Contains(p.DbType))
            value = "'" + value + "'";
        if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
        {
            query.Append(string.Format("{0} = {0}_ out{2}\n", p.ParameterName, value, isLast ? "" : ","));
        }
        else
        {
            query.Append(string.Format("{0} = {1}{2}\n", p.ParameterName, value, isLast ? "" : ","));
        }
    }

List Output Parameter results

列出输出参数结果

foreach (SqlParameter p in arrParams.Where(x => x.Direction == ParameterDirection.Output || x.Direction == ParameterDirection.InputOutput))
    {
        query.Append(string.Format(" select {0}_ {1}\n", p.ParameterName, p.ParameterName.Substring(1)));
    }

Full Code:

        public static string GetProcedureDebugInformation(SqlCommand cmd, [System.Runtime.CompilerServices.CallerMemberName] string caller = null, [System.Runtime.CompilerServices.CallerFilePath] string filePath = null, [System.Runtime.CompilerServices.CallerLineNumber] int? lineNumber = null)
    {
        // Collection of parameters that should use quotes
        DbType[] quotedParameterTypes = new DbType[] {
            DbType.AnsiString, DbType.Date,
            DbType.DateTime, DbType.Guid, DbType.String,
            DbType.AnsiStringFixedLength, DbType.StringFixedLength
        };

        // String builder to contain generated string
        StringBuilder query = new StringBuilder();

        // Build some debugging information using free compiler information
        query.Append(filePath != null ? filePath : ""
        + (lineNumber.HasValue ? lineNumber.Value.ToString() : "")
        + (lineNumber.HasValue || !string.IsNullOrWhiteSpace(filePath) ? "\n\n" : ""));
        query.Append("\n\n");

        var arrParams = new SqlParameter[cmd.Parameters.Count];
        cmd.Parameters.CopyTo(arrParams, 0);

        // Declare Parameter for output parameters
        foreach (SqlParameter p in arrParams.Where(x => x.Direction == ParameterDirection.Output || x.Direction == ParameterDirection.InputOutput))
        {
            // todo : I only needed a couple of types supported, you could add addition types
            string dbtype = string.Empty;
            switch (p.DbType)
            {
                case DbType.Guid:
                    dbtype = "uniqueidentifier";
                    break;
                case DbType.Int16:
                case DbType.Int64:
                case DbType.Int32:
                    dbtype = "int";
                    break;
                case DbType.String:
                    dbtype = "varchar(max)";
                    break;
            }

            query.Append(string.Format(" Declare {0}_ {1}\n", p.ParameterName, dbtype));
        }

        // Set Exec Text
        query.Append(string.Format("\n exec {0}\n", cmd.CommandText));
        var last = arrParams.LastOrDefault();

        //Build Main Parameter Area
        foreach (SqlParameter p in arrParams.OrderByDescending(p => p.ParameterName.Length))
        {
            bool isLast = p == last;
            string value = p.Value.ToString();
            if (quotedParameterTypes.Contains(p.DbType))
                value = "'" + value + "'";
            if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
            {
                query.Append(string.Format("{0} = {0}_ out{2}\n", p.ParameterName, value, isLast ? "" : ","));
            }
            else
            {
                query.Append(string.Format("{0} = {1}{2}\n", p.ParameterName, value, isLast ? "" : ","));
            }
        }

        // List Output Parameter results
        foreach (SqlParameter p in arrParams.Where(x => x.Direction == ParameterDirection.Output || x.Direction == ParameterDirection.InputOutput))
        {
            query.Append(string.Format(" select {0}_ {1}\n", p.ParameterName, p.ParameterName.Substring(1)));
        }

        return query.ToString();
    }