Geek Answers Handbook

Parameterized Queries (C #, Oracle): How to create a more readable view?

I am using parameterized queries in my C # code to interact with an Oracle database. What can I do to write statements in a more readable way?

Suppose I have a parameterized query like:

INSERT INTO PERSON (ID, NAME, BIRTHDATE) VALUES (:id, :name, :birthdate)

Ideally, I would like to see a log entry with all parameters replaced, so that I can 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 a parameterized query string and then iterate over all the parameters and use ToString (). It is a little difficult to read if there are many parameters. This will give something like:

INSERT INTO PERSON (ID, NAME, BIRTHDATE) VALUES (:id, :name, :birthdate) [:id=23, :name=Mike, birthdate=2004/07/09 00:00:00]

, , string.Replace() . , ?

.

1:

, .

(: 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();
        }

, oracle. (. ) , .

, API (, - OracleClient), . , . , .

+3
4

, , NHibernate source.

, GetCommandLogString ( IDbCommand), /: 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;
}
+3

, - , , . .. .

, , SQL ; , . ( , ), , , . , SQL-:)

+2

. ? string.format(). . . , , .

0

:

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

More articles:


All Articles