Is there a way to get CommandText after replacing parameter values, programmatically?

Is there a way to get CommandText after replacing paramater values programmatically in .NET?

Assume we have a command:

let cmd = conn.CreateCommand()
cmd.CommandText <- "select * from smsqueue where smsqueueid=:pid"
cmd.CommandType <- CommandType.Text
开发者_JAVA百科cmd.Parameters.Add("pid", 0)

I want to get the prepared command text; which would be like this:

select * from smsqueue where smsqueueid=0

There are profilers to get this information, but I want to do this explicitly in code (e.g., F# or C#).

The replacement probably won't be performed in text - indeed it would be more hassle to do so. Why bother working out how to escape strings, represents dates in a particular text format etc when the driver can transmit them separately in a pre-planned binary format?

What goal are you trying to achieve? If this is for the purpose of logging, I would just log the command text and the parameters separately. Apart from being easier, that will make it clearer if you end up with a problem: you'll be able to tell whether the SQL was properly parameterized or not.

Not possible, because that command is not being built. The program sends the exact command text you provided, including the parameter names, to the database engine, along with the parameter values separately. It never physically replaces them to come up with a new string.

If you really need something like that, you'll have to write your own string replacement function, but it would be misleading since that's not what really happens.

Code below needs to be added before command.execute so you get print of query which can be executed in SQL.

for (int x = 0; x < cmd.Parameters.Count; x++)
{    
if (cmd.Parameters[x].SqlDbType.ToString() == "VarChar" || cmd.Parameters[x].SqlDbType.ToString() == "Char")
    {
        Response.Write("<BR><BR>Declare " + cmd.Parameters[x].ToString() + " as " + cmd.Parameters[x].SqlDbType.ToString() + "(" + cmd.Parameters[x].Size + ")");
    }
    else
    {
        Response.Write("<BR><BR>Declare " + cmd.Parameters[x].ToString() + " as " + cmd.Parameters[x].SqlDbType.ToString());
    }

    Response.Write("<BR>SET " + cmd.Parameters[x].ToString() + " = '" + cmd.Parameters[x].SqlValue.ToString() + "'");
}
Response.Write("<BR>" + cmd.CommandText.ToString());