从.NET我可以通过一个SqlCommand对象（与SQL参数）生成完整的SQL字符串？ [英] From .NET can I get the full SQL string generated by a SqlCommand object (with SQL Parameters)?
问题描述
从.NET环境我可以访问由一个的的SqlCommand对象？
From the .NET environment can I get access to the full SQL string that is generated by a SqlCommand object?
注：完整的SQL字符串出现在智能感知悬停，在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：
在光线下面的答案（和参考），表明在preparation或执行内部不产生完整的SQL字符串，我做了一些闲逛使用.net反射的。即使是内部连接类似乎传递对象，而不是煮下来到字符串，例如：
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:
内部抽象无效添加preparedCommand（的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
在一般情况下，感谢大家为你坐进详细程度来证明什么可以做，并显示什么实际发生的事情。许多AP preciated。我喜欢全面的讲解;他们补充担保和贷款可信的答案。
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.
- 由于SQL是从来没有实际使用的参数值重建，像换行和报价并不需要考虑
- 在注释参数名称从未真正处理了自己的价值，而是保持原样
使用那些地方，考虑到具有相同的字符开头，如 @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
看起来是这样的：
will look like this:
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);
}
这篇关于从.NET我可以通过一个SqlCommand对象（与SQL参数）生成完整的SQL字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
