viewing exact sql after parameter substitution C#

Question

Is there a way to access the CommandText just after it gets executed? I have the following code:

cmd = new OracleCommand(sql.ToString(), conn);
cmd.Parameters.Add(new OracleParameter("@parm", parmValue));


OracleDataAdapter da = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

What I need to know is the sql command string executed in the database that already has parmValue's value contained in the sql. In other words, I must not be able to see the string "@parm" inside the query but the value of it instead.

I need this so that I could push this sql in a log database for future reference.

Thanks in advance.

Even when it is actually sent to the sql server it is parametrized; the variables are declared and then used inside the query. — Muhammad Hasan Khan, Oct 27 '11 at 15:19
possible duplicate of [How to get the generated SQL-Statement from a SqlCommand-Object?](http://stackoverflow.com/questions/265192/how-to-get-the-generated-sql-statement-from-a-sqlcommand-object) — Eoin Campbell, Oct 27 '11 at 15:26
Darn! That was my answer! You found the dupe while I was typing mine up. — David, Oct 27 '11 at 15:28

score 2 · Answer 1 · answered Oct 27 '11 at 19:54

I am not certain if Oracle works exactly in this way, but for most other DB providers, a parameterized query is not sent to the server as a plain text query, but is instead sent as a an Execute command with proper parameters. In other words there is no substitution happening at the level of your application, only in the DB itself. To view the actual command as they are being executed on the server, you may want to look at a trace/log of the database itself.

score 1 · Accepted Answer · edited May 30 '12 at 02:09

I'm not sure about Oracle, but in the RDBMS's I work with personally this can't be done.

When I've needed to get the parameters and view the resulting SQL Query I've always done it by looping through the parameter collection in the code and extracting the Name/Value pairs to log the resulting query or something like this into our error logging database:

StoredProc=InsertCallRecord: Parameters: CallId=1234;Custid=651;PersonCalling=Dave...

I only do this on error logging because it's a performance drag to do it on every call, but it could work in a test scenario as well.

viewing exact sql after parameter substitution C#

2 Answers2