Oledb / ado.net: get command text, replacing all parameters

Question

Is it possible to get the text OleDbCommand with all the parameters replaced by their values? For instance. in the code below i'm looking for a way to get the request text

 SELECT * FROM my_table WHERE c1 = 'hello' and c2 = 'world' 

after I finished assigning parameters.

 var query = "SELECT * FROM my_table WHERE c1 = ? and c2 = ?"; var cmd = new OleDbCommand(query, connection); cmd.Parameters.Add("@p1", OleDbType.WChar).Value = "hello"; cmd.Parameters.Add("@p2", OleDbType.WChar).Value = "world"; 

Answer 1

No: you need to iterate through a selection of parameters by executing string.Replace () to get the equivalent. Is this especially painful when you need to use syntax ? , not the @parametername syntax.

The reason for this is that the complete string is never collected. Parameters and are sent to the server and processed as data and are never included in the string.

However, I alone understand your pain. It would be nice if they included some kind of .ComposeSQL() method, which you could call for debugging purposes, which probably also leads to a compiler warning to avoid being used in production.

Answer 2

If you just need to see what query has been executed and not need to work with it programmatically, you can use SQL Profiler.