Is it possible to get the parsed text of a SqlCommand with SqlParameters?

Question

What I am trying to do is create some arbitrary sql command with parameters, set the values and types of the parameters, and then return the parsed sql command - with parameters included. I will not be directly running this command against a sql database, so no connection should be necessary. So if I ran the example program below, I would hope to see the following text (or something similar):

WITH SomeTable (SomeColumn)
AS
(
    SELECT N':)'
    UNION ALL
    SELECT N'>:o'
    UNION ALL
    SELECT N'^_^'
)
SELECT SomeColumn FROM SomeTable

And the sample program is:

using System;
using System.Data;
using System.Data.SqlClient;

namespace DryEraseConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            const string COMMAND_TEXT = @"
WITH SomeTable (SomeColumn)
AS
(
    SELECT N':)'
    UNION ALL
    SELECT N'>:o'
    UNION ALL
    SELECT @Value
)
SELECT SomeColumn FROM SomeTable
";
            SqlCommand cmd = new SqlCommand(COMMAND_TEXT);
            cmd.CommandText = COMMAND_TEXT;
            cmd.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Value",
                Size = 128,
                SqlDbType = SqlDbType.NVarChar,
                Value = "^_^"
            });
            Console.WriteLine(cmd.CommandText);
            Console.ReadKey();
        }
    }
}

Is this something that is achievable using the .net standard libraries? Initial searching says no, but I hope I'm wrong.

Alveta

Answer 1

You have a mistaken notion of how parameterized queries work. The "parsed text" you speak of is never created, and parameter values are never substituted directly into the query string.

That's why it's so important to use parameterized queries — you have complete segregation of query data from query code. Data is data, code is code, and never the twain shall meet. Thus, there is no possibility for sql injection.

What it means is that if you have a CommandText like this:

SELECT SomeColumn FROM SomeTable WHERE ID= @ID

instead of ultimately running a query that looks like this:

SELECT SomeColumn FROM SomeTable WHERE ID= 123

you actually run something more like this:

DECLARE @ID Int
Set @ID = RetrieveQueryDataItem("@ID")
SELECT SomeColumn FROM SomeTable WHERE ID= @ID

Now, this isn't exactly what happens; the engine doesn't transform the code like that. Instead, it uses the sp_executesql procedure. But this should help you understand what's going on.

Theoretical

Thanks, this is exactly what I needed to hear. Knowing that sp_executesql is what is called behind the scenes explains everything. – Alveta

Answer 2

Joel Coehoorn is right, it's not just a simple string substitution or escape character adding, etc.

You can, however, view your parameters to see if your values are as you want them:

foreach (IDataParameter i in cmd.Parameters)
{
    Console.WriteLine(i.Value.ToString());
}

Nicety

Answer 3

THe SQLCommand object does not swap out the params for the value in the command text and run that. It calls the sp_execute sql with the exact text you supply and then supplies the list of paramaters. Use SQL profiler against a database and you will see what i mean.

What is it you are actually trying to acheive here?

Vic

I was trying to find an easy way to generate some sql command text (these commands would be executed upstream, not on the spot). The functionality of the queries would be far too complex for Linq-To-Sql to handle (don't think LINQ does bulk inserts, T-SQL, and the like). I was also hoping I could avoid doing the StringBuilder approach as well. – Alveta

Answer 4

I would be tempted to look into using LINQ as it will give you the control you want in your C# code.

Loathing

Unfortunately, Linq-To-Sql is an ORM for the most part, and that is not what I am doing. What I need to do is some advanced T-SQL generation (bulk inserts, for example). – Alveta

Answer 5

In SSMS view the > object explorer > XEvent Profiler > standard or TSQL

Maillot