oledb/ado.net: Get the command's text, with al

Question

Is it possible to get the text of an OleDbCommand with all parameters replaced with their values? E.g. in the code below I'm looking for a way to get the query text

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

after I finished assigning the 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 have to iterate through the parameters collection yourself, doing a string.Replace() to get the equivalent. It's particularly painful when you have to use the ? syntax rather than the @parametername syntax.

The reason for this is that the full string is never assembled. The parameters and sent to the server and treated as data, and are never included in the string.

All the same, I for one understand your pain. It would have been nice if they included some kind of .ComposeSQL() method you could call for debugging purposes, that perhaps also produces a compiler warning to help avoid use in production.

Answer 2

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