参数化查询（C＃中，Oracle）：如何产生更具可读性的表示？ [英] Parameterized Queries (C#, Oracle): How to produce a more readable representation?

问题描述

我用我的C＃代码参数化查询与Oracle数据库进行交互。 ？我能做些什么来记录语句更可读的方式

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')

我目前的做法是打印出来的参数化查询的字符串，然后遍历所有的参数和使用的ToString（）。这是一个有点难以阅读，如果有许多参数，它会产生这样的：

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]

另一种方法，我打算将使用与string.replace（）函数来替换参数占位符。但是，也许有一些更好的方式来做到这一点。

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?

在此先感谢

修改1？：

我想更好地提供一些代码示例。

I thought better to provide some code example.

我使用这个参数化查询形式（注：我使用log4net的）：

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

我要寻找一种方式来退出的声明，这在Oracle命令对象正在使用。我目前的做法（见问题）尚未不是很满意，因为没有很好的可读性。

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.

我希望会有一些API（也许甚至在OracleClient命名空间中的东西）这将有助于解析参数化查询我。我可以做一些更复杂的字符串替换或正则表达式，但我想收集一些知识。我已经做了一些关于它的reasearch，但没有发现什么。

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.

推荐答案

也许这是值得看的方式对其做在NHibernate的源。

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

找到名为GetCommandLogString（IDbCommand的命令）的功能，你几乎可以复制/粘贴：P

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

这篇关于参数化查询（C＃中，Oracle）：如何产生更具可读性的表示？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！