I am using parameterized queries in my C# code to interact with an Oracle database. What can I do to log the statements in a more readable fashion?
Suppose I have a parameterized query like:
INSERT INTO PERSON (ID, NAME, BIRTHDATE) VALUES (:id, :name, :birthdate)
Ideally I would like to see the log entry with all parameters replaced so I could copy and paste the statement for later use:
INSERT INTO PERSON (ID, NAME, BIRTHDATE) VALUES (23, ‘Mike’, TO_DATE('2003/07/09', 'yyyy/mm/dd')
My current approach is to print out the string of the parameterized query and then iterate over all parameters and use ToString(). This is a bit hard to read, if there are many parameters. It would yield something like:
INSERT INTO PERSON (ID, NAME, BIRTHDATE) VALUES (:id, :name, :birthdate) [:id=23, :name=Mike, birthdate=2004/07/09 00:00:00]
Another approach I am planning would be to use the string.Replace() function to replace the parameter placeholders. But maybe there some better way to do this?
Thanks in advance.
Edit 1:
I thought better to provide some code example.
I am using parameterized queries in this form (Note: I am using log4net):
using (OracleConnection connection = new OracleConnection(connectionString))
using (OracleCommand command = new OracleCommand(statement, connection))
{
command.Parameters.AddWithValue(":id", id);
command.Parameters.AddWithValue(":name", name);
command.Parameters.AddWithValue(":birthdate", birthdate);
command.Connection.Open();
log.DebugFormat("Executing statement: {0}.", command.CommandText);
// there I would add some more code to iterate over
// the parameters and print them out
command.ExecuteNonQuery();
command.Connection.Close();
}
I am looking for a way to log out the statement, which the oracle command object is using. My current approaches (see question) are yet not very satisfying, because not very readable.
I hoped that there would be some API (maybe something even in the OracleClient namespace) that would help to parse the parameterized query for me. I could do some more sophisticated string replacement or regex, but I wanted to collect some knowledge. I have already done some reasearch on it, but didn't found something.