Parameterized Queries (C#, Oracle): How to produce a more readable representation?

Question

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.

Answer 1

Maybe it's worth looking at the way its done in the NHibernate source.

Find the function called "GetCommandLogString(IDbCommand command)" which you could almost copy/paste :p

protected string GetCommandLogString(IDbCommand command)
{
    string outputText;

    if (command.Parameters.Count == 0)
    {
        outputText = command.CommandText;
    }
    else
    {
        StringBuilder output = new StringBuilder();
        output.Append(command.CommandText);
        output.Append("; ");

        IDataParameter p;
        int count = command.Parameters.Count;
        for (int i = 0; i < count; i++)
        {
            p = (IDataParameter) command.Parameters[i];
            output.Append(string.Format("{0} = '{1}'", p.ParameterName, p.Value));

            if (i + 1 < count)
            {
                output.Append(", ");
            }
        }
        outputText = output.ToString();
    }
    return outputText;
}