是否有一种简单的方法来获取“ sp_executesql”？ .NET为参数化查询生成查询？ [英] Is there a easy way to get the "sp_executesql" query .NET generates for a parametrized query?
问题描述
背景：
如果我有以下程序
公共类程序
{
public static void Main（）
{
using（var connection = new SqlConnection（ Server =（local）; Database = Testing; Trusted_Connection = True））
使用（var命令= connection.CreateCommand（））
{
connection.Open（）;
command.CommandText =更新Foo设置栏= @Text;
command.Parameters.Add（ @ Text，SqlDbType.VarChar，50）.Value = Hello World！;
命令.ExecuteNonQuery（）;
}
}
}
执行时，以下查询为运行（根据SQL Server Profiler）
exec sp_executesql N'UPDATE Foo set Bar = @Text '，N'@ Text varchar（50）'，@ Text ='Hello World！'
我的问题：
我想做的是，如果我有以下情况
command.CommandText = UPDATE Foo set Bar = @Text;
command.Parameters.Add（ @ Text，SqlDbType.VarChar，50）.Value = Hello World！;
字符串查询= GenerateQuery（命令）;
GenerateQuery 将返回字符串
exec sp_executesql N'UPDATE Foo set Bar = @ Text'，N'@ Text varchar（50）'，@ Text ='Hello世界！
我有能力编写一个解析器，该解析器可以通过 Parameters 集合并构建字符串。但是，在我开始从头开始编写此解析器之前，.NET中是否有某些类或函数已经执行了我要忽略的操作？
如果我可以访问编写解析器的参数的 MetaType 会非常容易，但是在生产应用程序中使用反射来访问.NET框架的未发布的内部API时，我觉得不值得。
格雷戈里的答案有点正确，但大多数都是错误的。的确，没有可以调用的 public 方法，但是有 private 一个（您可以t调用）的确将 CommandText 和 SqlParameterCollection 重新打包为对 sp_executesql的存储过程调用，其中预格式化了参数名称和数据类型的列表作为该存储过程的第二个输入参数（请参阅下面有关 BuildParamList 的注释）。 / p>
虽然这是Microsoft源代码，但该代码也是开放源.NET Core项目的一部分，该项目主要在 MIT许可证。意思是，您可以复制并粘贴所需的部分:-)。即使该代码仅位于referencesource.microsoft.com上，您仍然可以从中学习所需信息，并使用它来验证您的版本在功能上与之保持一致。
- Microsoft.com上的原始代码，位于： http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlCommand.cs,5400
- 。 NET Core版本在GitHub.com上，位于： https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlCommand.cs#L2820
似乎您需要的主要是 BuildParamList 方法（ ，当然，无论它叫什么）：
- http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlCommand。 cs，5526
- https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlCommand .cs＃L2867
Background:
If I had the following program
public class Program
{
public static void Main()
{
using(var connection = new SqlConnection("Server=(local);Database=Testing;Trusted_Connection=True"))
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "UPDATE Foo set Bar = @Text";
command.Parameters.Add("@Text", SqlDbType.VarChar, 50).Value = "Hello World!";
command.ExecuteNonQuery();
}
}
}
When executed the following query is run (according to SQL Server Profiler)
exec sp_executesql N'UPDATE Foo set Bar = @Text',N'@Text varchar(50)',@Text='Hello World!'
My Question:
What I am trying to do is if I had the following
command.CommandText = "UPDATE Foo set Bar = @Text";
command.Parameters.Add("@Text", SqlDbType.VarChar, 50).Value = "Hello World!";
string query = GenerateQuery(command);
GenerateQuery would return the string
"exec sp_executesql N'UPDATE Foo set Bar = @Text',N'@Text varchar(50)',@Text='Hello World!'"
It is within my ability to write a parser that goes through each parameter in the Parameters collection and build up the string. However, before I start writing this parser up from scratch, is there some class or function in .NET that already performs this action I am overlooking?
If I had access to the MetaType of the parameter writing the parser would be extremely easy, but I don't feel conferrable using reflection in a production app to access unpublished internal API's of the .NET framework.
Gregory's answer is a little bit correct, but mostly incorrect. True, there is no public method you can call to get this, BUT there is private one (that you can't call) that does indeed repackage the CommandText and SqlParameterCollection as a stored procedure call to sp_executesql with the pre-formatted list of parameter names and datatypes as the second input parameter to that stored procedure (see the note about BuildParamList below).
While this is Microsoft source code, the code is also part of the open source .NET Core project which is mainly released under the MIT license. Meaning, you can copy and paste the parts that you need :-). And even if the code was only on referencesource.microsoft.com, you would still be able to learn what you need from it and use it to verify that your version is functionally consistent with it.
- Original code on Microsoft.com at: http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlCommand.cs,5400
- .NET Core version of it on GitHub.com at: https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlCommand.cs#L2820
It seems like the main thing you need is the BuildParamList method (and, of course, whatever it calls):
- http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlCommand.cs,5526
- https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlCommand.cs#L2867
这篇关于是否有一种简单的方法来获取“ sp_executesql”？ .NET为参数化查询生成查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
