Parameterized Queries (C#, Oracle): How to produce

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

The trouble with trying to store this as a runnable statement for later use is that the parameter version runs differently to the hard-coded-string version, since the former doesn't require the typecasting inline, e.g. you don't have to put quotes around the string parameters etc. You'd need to do that manually depending on the type of each parameter.

I don't know what your log format is, but you might be better off writing to the log the SQL to run a parametized query instead; i.e., declare necessary variables, and assign values, then run the query. It will make for more lines (or longer lines, if you avoid the line breaks), but then you can just run the code as-is, and you don't have to worry about replacing the parameters yourself. Plus your log entries are resistant to SQL injection :)

Answer 2

Well use the replace function. Why not? Or you can use string.format(). Or regular expressions. Or a combination. All you want is string manipulation.

Answer 3

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;
}

Answer 4

You can use this way:

INSERT INTO PERSON (ID, NAME, BIRTHDATE)
   VALUES ('"+id+"', '"+name+"', '"+birthdate+"')