参数化查询（C#、Oracle）：如何生成更具可读性的表示？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/453849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parameterized Queries (C#, Oracle): How to produce a more readable representation?
提问by Theo Lenndorff
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?
我在 C# 代码中使用参数化查询与 Oracle 数据库进行交互。我该怎么做才能以更具可读性的方式记录语句？
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:
我目前的方法是打印出参数化查询的字符串，然后遍历所有参数并使用 ToString()。如果有很多参数，这有点难以阅读。它会产生类似的东西：
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?
我计划的另一种方法是使用 string.Replace() 函数来替换参数占位符。但也许有更好的方法来做到这一点？
Thanks in advance.
提前致谢。
Edit 1:
编辑1：
I thought better to provide some code example.
我认为最好提供一些代码示例。
I am using parameterized queries in this form (Note: I am using log4net):
我正在使用这种形式的参数化查询（注意：我使用的是 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.
我正在寻找一种方法来注销 oracle 命令对象正在使用的语句。我目前的方法（见问题）还不是很令人满意，因为不是很可读。
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.
我希望有一些 API（甚至可能在 OracleClient 命名空间中）可以帮助我解析参数化查询。我可以做一些更复杂的字符串替换或正则表达式，但我想收集一些知识。我已经对它进行了一些研究，但没有找到任何东西。
采纳答案by Brendan Kowitz
Maybe it's worth looking at the way its done in the NHibernate source.
也许值得看看它在 NHibernate源代码中的完成方式。
Find the function called "GetCommandLogString(IDbCommand command)" which you could almost copy/paste :p
找到您几乎可以复制/粘贴的名为“GetCommandLogString(IDbCommand command)”的函数：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;
}
回答by Geoff
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 :)
我不知道您的日志格式是什么，但是您最好将 SQL 写入日志以运行参数化查询；即，声明必要的变量并赋值，然后运行查询。它将产生更多行（或更长的行，如果您避免换行），但是您可以按原样运行代码，您不必担心自己替换参数。此外，您的日志条目可以抵抗 SQL 注入 :)
回答by tuinstoel
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.
很好地使用替换功能。为什么不？或者您可以使用 string.format()。或者正则表达式。或者组合。你想要的只是字符串操作。
回答by Brendan Kowitz
You can use this way:
你可以这样使用：
INSERT INTO PERSON (ID, NAME, BIRTHDATE)
VALUES ('"+id+"', '"+name+"', '"+birthdate+"')
