有没有一种简单的方法来获取“sp_executesql"?.NET 为参数化查询生成的查询? [英] Is there a easy way to get the "sp_executesql" query .NET generates for a parametrized query?

问题描述

背景:

如果我有以下程序

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

执行时运行以下查询(根据 SQL Server Profiler)

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!'

<小时>

我的问题:

我想做的是如果我有以下内容

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 将返回字符串

"exec sp_executesql N'UPDATE Foo set Bar = @Text',N'@Text varchar(50)',@Text='Hello World!'"

我有能力编写一个解析器来遍历 Parameters 集合中的每个参数并构建字符串.但是，在我从头开始编写这个解析器之前，.NET 中是否有一些类或函数已经执行了我忽略的这个操作?

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?

如果我可以访问 MetaType 编写解析器将非常容易，但我不认为在生产应用程序中使用反射来访问 .NET 框架的未发布的内部 API.

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 的回答有点正确，但大部分是错误的.确实，没有您可以调用的 public 方法来获取此信息，但是有 private 方法(您无法调用)确实重新打包了 CommandText 和 SqlParameterCollection 作为对 sp_executesql 的存储过程调用，使用预先格式化的参数名称和数据类型列表作为该存储过程的第二个输入参数(请参阅请注意下面的 BuildParamList).

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).

虽然这是微软的源代码，但该代码也是开源 .NET Core 项目的一部分，主要发布在 MIT 许可证.意思是，您可以复制和粘贴您需要的部分:-).即使代码仅在 referencesource.microsoft.com 上，您仍然可以从中了解您需要的内容并使用它来验证您的版本在功能上是否与其一致.

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.

• Microsoft.com 上的原始代码位于:http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlCommand.cs,5400
• GitHub.com 上的 .NET Core 版本:https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlCommand.cs#L2820

似乎您需要的主要内容是 BuildParamList 方法(当然还有它调用的任何内容):

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屋！