从.NET中，我可以获取由SqlCommand对象生成的完整SQL字符串（带有SQL参数）吗？

Question

在.NET环境中，我能否访问由SqlCommand对象生成的完整SQL字符串？

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

如果必须使用反射技术，我愿意这样做。 我相信这里有人知道如何获取它。

---

更新 1：
我正在调用一个带有参数的存储过程，使用 cmd.CommandType = CommandType.StoredProcedure，并尝试获取生成和运行的完整 SQL。我想知道在这种情况下，cmd.Prepare() 方法是否有用，它是否会将完整字符串存储在状态字段中或类似于此的位置。

---

更新2：

考虑到下面的答案（和引用）表明在准备或执行过程中内部并不会生成完整的SQL字符串，因此我使用.NET Reflector进行了一些探索。即使是内部连接类似乎也传递对象而不是将它们简化为字符串，例如：

internal abstract void AddPreparedCommand(SqlCommand cmd);
声明类型：System.Data.SqlClient.SqlInternalConnection
程序集：System.Data, Version=2.0.0.0

---

总的来说，感谢大家提供如此详细的内容来证明可以做什么并展示实际情况。非常感激。我喜欢详尽的解释；它们增加了确定性并为答案提供了可信度。

- John K

---

你是生成内联SQL还是调用存储过程？ 无论哪种方式，你是否正在添加参数？ - DOK

我正在添加参数。通过看到一些初始答案，我意识到答案可能因为是否有参数而不同。如果SQLParameter问题没有解决，我会保留这个问题，并稍后开一个新的问题。 - John K

我已经尝试过很多次——为了测试和/或记录而捕获SQL。您可以从应用程序中获取SQL的近似值，但是直到它到达数据库引擎之前，该命令才被组合成单个SQL语句。因此，如果您可以访问Profiler或类似的数据库工具，则可以从那里进行检查。否则，恐怕您只能使用近似值。 - DOK

实际上，即使在数据库引擎中，SQL语句也没有以任何形式“组合”在一起，除了参数作为单独的实体。代码被编译为一个带有查询提示的参数化代码片段，详细说明将使用的标准参数值，然后在指定参数的情况下进行“调用”。因此，除非您自己构建它，否则无法获取注入参数值的SQL语句。 - Lasse V. Karlsen

值得查看 https://github.com/Microsoft/referencesource/blob/master/System.Data/System/Data/SqlClient/TdsParser.cs 中的 TdsExecuteRPC。 - Rory

Answer 1

通过一个简单的循环将所有参数名称替换为它们的值，可以提供与最终结果类似的东西，但是存在一些问题。

1. 由于SQL实际上从未使用参数值重新构建，因此不需要考虑换行符和引号等内容
2. 注释中的参数名称实际上永远不会被处理其值，而是保留原样

在考虑到以相同字符开头的参数名称（例如 @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());
}

然而，这还有一个问题，即如果参数是字符串，则最初看起来像这样的SQL:

SELECT * FROM yourtable WHERE table_code = @CODE

将会长成这个样子：

SELECT * FROM yourtable WHERE table_code = SOME CODE WITH SPACES

这显然不是合法的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);
}

- Jesus Ramos

---

这与发送到服务器的内容完全不同，并且无论如何都会导致SQL无效。 - Adam Robinson

我以调试作为一个例子，来说明我何时看到SQL，所以我不需要任何关于调试的东西。我要找的是一个包含实际生成并在数据库上运行的SQL的字符串。你说得对，参数集合可以被反转，但由于我创建了参数集合，我已经知道其中的内容。 - John K

这就是你能够看到 SQL 代码的最接近方式，正如 Adam 所说，ADO.NET 以不同于你发送的方式执行 SQL，因此你发送的和实际执行的可能会有所不同。 - Jesus Ramos

@Jesus，它可能仍然不完整，但我认为它无法更接近“实际执行的SQL”，除非您（也就是OP）编写一个完整的SQL解析器并使用100％的工作SQL进行重写。 - Lasse V. Karlsen

@Lasse，这真的取决于OP想要什么，如果他想确保他的SQL语句是他真正想要的，那么这应该没问题，但如果他真的需要执行的确切SQL，他就必须再努力一些，个人而言，我从来没有遇到过太多关于我发布的第一段代码片段的问题，我通常使用它来确保我的格式正确。 - Jesus Ramos

Answer 2

这里有几个类似的问题。

最有说服力的答案是在这个问题中提供的：如何从SqlCommand对象获取生成的SQL语句？

答案是：

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

参数化查询（在CommandText中的查询）被发送到SQL Server作为准备好的语句的等效物。当你执行命令时，参数和查询文本被分别处理。在任何时候都没有生成完整的SQL字符串。

你可以使用SQL Profiler来查看幕后情况。

- DOK

Answer 3

CommandText属性（或调用ToString()）将给出所有SQL语句，但有一个小例外。它肯定会提供您在调试器中看到的任何内容。请注意，这不会提供参数值，但它会提供实际命令。

唯一的注意事项是，在CommandType为Text时，ADO.NET框架通常会（事实上，几乎总是）使用sp_executesql来执行命令，而不是直接针对连接执行命令。在这个意义上，不可能获得执行的精确 SQL。

- Adam Robinson

---

那么你到底是在说什么？除非有特殊情况，否则你将得到即将显示的内容，对吗？ - Lasse V. Karlsen

@Lasse V：在我编辑问题并提供有关在我的查询中使用SQL参数的其他详细信息之前，Adam的答案已经被添加了。 - John K

@Lasse：我的意思是，无论是'ToString()'还是'CommandText'都不会自动呈现参数值的字符串表示形式，以防这是OP正在寻找的部分。 - Adam Robinson

Answer 4

我喜欢Jesus Ramos的答案，但我需要输出参数的支持。（我还使用了一个字符串构建器来生成内容。）

声明输出参数的参数

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

构建主参数区

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 ? "" : ","));
        }
    }

列出输出参数结果

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

完整代码：

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

- ScottFoster1000

Answer 5

我没有尝试过这个，但如果您愿意使用SMO，可能可以使用Capture Mode：

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

- Even Mien