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.

so you want to get the string that gets run from the `SqlCommand` object? it seems like it'd be easy enough to do a `string.Format()` to achieve the raw SQL string... — DLeh, Mar 19 '15 at 19:49
@DLeh I agree, that's why I said I could do it myself. The only part that could be tricky is translating `Parameter.DbType`, `Parameter.Size` and `Parameter.Scale` correctly. That is why I wanted to know if .NET had a spot to do this for me so I would not need to do the work of writing that transformation. — Scott Chamberlain, Mar 19 '15 at 19:53
try this link unless you're speaking about the same thing that's captured in sql servers profiler -http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object — MethodMan, Mar 19 '15 at 19:54
@MethodMan No, I actually want the parameters. What this will be used for is a set of queries are being stored in a DB then executed later. Currently all the queries are non-parameterized (using a technique similar to the answer in your link). My goal is to be able to execute the queries in parameterized form to help utilize things like the plan cache in SQL. — Scott Chamberlain, Mar 19 '15 at 19:55

Solomon Rutzky · Accepted Answer · 2016-09-28T14:55:51.430

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

Ohh, I did not think about revisiting this once .NET core was released. — Scott Chamberlain, Sep 28 '16 at 13:25
@ScottChamberlain I just updated my answer to clarify things a little. But even without the project on GitHub you could still get the info you needed to feel confident that your code was behaving as it should :). — Solomon Rutzky, Sep 28 '16 at 14:58

score 1 · Answer 2 · answered Mar 19 '15 at 20:05

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.

score -1 · Answer 3 · answered Sep 28 '15 at 22:30

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.

You are making the same mistake [MethodMan made in the comments](http://stackoverflow.com/questions/29153589/is-there-a-easy-way-to-get-the-sp-executesql-query-net-generates-for-a-parame/32832825#comment46528605_29153589). I am not attempting to "deparametrize" the query. My question was is there a easy way to build up the `sp_executesql` call that would have been generated by .NET code. Specificly if there was a easy way to generate the types that would need to be passed into the `@params` parameter without hand coding each of the possible Sql Data types. — Scott Chamberlain, Sep 28 '15 at 22:55
Basicly, is there a way, without reflection, to get the value of [`MetaType.GetMetaTypeFromSqlDbType(dbType, isMultiValued).TypeName`](http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlEnums.cs,b121d8970c3285ca) — Scott Chamberlain, Sep 28 '15 at 23:02

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

3 Answers3

Linked