Is there a easy way to get the "sp_executesql" query .NET generates for a parametrized query?

Question

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.

Answer 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