Is there a easy way to get the “sp_executesql” que

问题:

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.

回答1:

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

回答2:

Currently, there is nothing here. The command object sends the parameterized text and all parameters to SQL Server, which then marries them using the sp_executesql stored procedure. There is nothing in the SQL Server .NET objects that parses the query with parameters, so you can't pull what is run on SQL Server.

Even in SQL Server, you have commands like sp_prepare, which will prepare the SQL query, but it does not return the text. Instead, it returns a handle to the compiled query, with parameters. I would imagine, with a bit of investigation, you can find where the compiled query is, but it would not be efficient to use SQL Server to do this type of work for you. And that is only IF you could get back the compiled query and revert to the statement.

In older versions of SQL Server, you could use the sp_helptext to pull the text from system sprocs, but it does not work anymore. It could show you how they do it, but it would not be any better than building your own parser.

回答3:

An attempt to create a string from SQL and parameters would make the initial, good query method bad.

sp_executesql creates a parameterized query on SQL Server and re-uses the query plan if a repeated call has the same SQL string and signature (but possibly different parameter values). The SQL Profiler output with sp_executesql is actually sent like this; it can be copied and executed in SSMS. Concatenating parameter values into an SQL string would create a new query and query plan for each call, as if it had been concatenated in the beginning (including performance penalty and SQL injection risk).

sp_prepare, and DbCommand.Prepare() in ADO.NET, are outdated in my opinion, because the application has to keep a handle to the query and can use it only within limited scope (connection), while sp_executesql re-uses query plans whenever SQL and signature strings (param names and types) are equal, no matter how the application gets them.