我可以从 .NET 获取由 SqlCommand 对象(带有 SQL 参数)生成的完整 SQL 字符串吗?

2022-01-23 00:00:00 reflection sql-server ado.net sqlcommand

从 .NET 环境中，我可以访问由 SqlCommand 对象?

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

注意:完整的 SQL 字符串会显示在 Intellisense 悬停中，在 VisualStudio 中，同时处于调试模式.

如果必须，我愿意使用反射技术.我敢肯定这里有人知道一种方法.

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

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

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.

更新 2:

鉴于下面的答案(和引用)表明在准备或执行期间内部没有生成完整的 SQL 字符串，我使用 .NET Reflector 做了一些探索.甚至内部连接类似乎也传递对象而不是将它们归结为字符串，例如:

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:

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

internal abstract void AddPreparedCommand(SqlCommand cmd);
Declaring Type: System.Data.SqlClient.SqlInternalConnection
Assembly: 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.

推荐答案

将所有参数名称替换为其值的简单循环将为您提供类似于最终结果的内容，但存在几个问题.

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. 由于从未使用参数值实际重建 SQL，因此不需要考虑换行符和引号等内容
  2. 注释中的参数名称从未真正处理过它们的值，而是保持原样

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

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:

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

不过，这里还有一个问题，那就是如果一个参数是一个字符串，那么最初看起来像这样的 SQL:

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:

SELECT * FROM yourtable WHERE table_code = @CODE

看起来像这样:

SELECT * FROM yourtable WHERE table_code = SOME CODE WITH SPACES

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

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

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

相关文章