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.

Answer 1

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

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());
}

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?