Scott Chamberlain Scott Chamberlain - 2 months ago 5
SQL Question

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

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

The accepted 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. 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 :-).

It seems like the main thing you need is the BuildParamList method (and, of course, whatever it calls):

Comments