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

Question

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
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#).

Jon Skeet · Accepted Answer · 2011-01-23 09:05:21Z

5

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.

answered Jan 23, 2011 at 9:05

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kaveh Shahbazian Over a year ago

This is totally rough! I use ExecuteCommand method of LINQ DataContext class to get a list of typed object (which I generate myself) and that (stupid?) method just gets string SQL; the problem is it does not replaces parameters if someone is using Oracle! [And I hate DataSet and Oracle has not LINQ and I did not found a good NHibernate code generator - besides that I work in such an environment that NHibernate, NLog, F#, ... are forbidden! SoI generate hand-written-like code >:( ]

Jon Skeet Over a year ago

@Kaveh: Is the problem that you're using named parameters but the driver only supports positional parameters? If you want to use LINQ with Oracle, you might want to look at the Entity Framework.

Kaveh Shahbazian Over a year ago

I think - as you and Joe Enos have stated - the problem is parameters never would be replaced before being sent. (And I will try not-named parameters - Thanks!)

Kaveh Shahbazian Over a year ago

With not named parameters, the problem is ExecuteCommand accepts just strings as values despite that the rest of parameters of the method (other than the 1st one) are of type Objects.

Jon Skeet Over a year ago

@Kaveh: I don't see what you mean. In particular, the docs of DataContext.ExecuteCommand show an example passing an integer...

|

Joe Enos · Accepted Answer · 2011-01-23 09:05:26Z

2

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.

answered Jan 23, 2011 at 9:05

Joe Enos

40.6k11 gold badges86 silver badges144 bronze badges

Comments

Pratik Gohil · Accepted Answer · 2013-05-27 11:24:22Z

1

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

edited May 27, 2013 at 11:24

answered May 27, 2013 at 10:48

Pratik Gohil

217 bronze badges

2 Comments

John Palmer Over a year ago

Please add some text to explain your code. Also, indent code by 4 spaces or using the {} button to improve the formatting.

John Palmer Over a year ago

Please add this to your answer, you can edit your post by clicking on the edit link below the post.

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related