C# 使用 ODP.NET 按名称绑定查询参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1046632/
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
Binding query parameters by name with ODP.NET
提问by Thomas Levesque
I'm currently using the Microsoft ADO.NET provider for Oracle (System.Data.OracleClient). I'm aware that it is certainly not the best Oracle provider available and that it will soon be deprecated, I should be using Oracle's ODP.NET instead. The reason why I still use the MS provider is because ODP.NET binds parameters by position, not by name. This can really be a PITA when you use many parameters in a query, because you have to be careful to add them in the right order, which can easily lead to bugs. It's also annoying when you use the same parameter multiple times in the same query, for instance :
我目前正在使用适用于 Oracle 的 Microsoft ADO.NET 提供程序 ( System.Data.OracleClient)。我知道它肯定不是最好的 Oracle 提供程序，而且很快就会被弃用，我应该改用 Oracle 的 ODP.NET。我仍然使用 MS 提供程序的原因是因为ODP.NET 按位置绑定参数，而不是按名称。当您在查询中使用许多参数时，这确实是一个 PITA，因为您必须小心地以正确的顺序添加它们，这很容易导致错误。当您在同一个查询中多次使用相同的参数时，这也很烦人，例如：
SELECT A,B,C FROM FOO WHERE X = :PARAM_X OR :PARAM_X = 0
With ODP.NET, I have to add two parameters to the OracleCommand, which I think is stupid...
使用 ODP.NET，我必须向 . 添加两个参数OracleCommand，我认为这很愚蠢......
ODP.NET's OracleCommandhas a property to change that default behavior : BindByName. When set to true, the parameters are bound by name, which is what I want. Unfortunately this doesn't really help me, because :
ODP.NETOracleCommand具有更改默认行为的属性：BindByName。设置为 true 时，参数按名称绑定，这正是我想要的。不幸的是，这并没有真正帮助我，因为：
- It is set to false by default
- I almost never use concrete ADO.NET classes explicitly, I prefer to use ADO.NET 2.0 abstraction layer (
DbProviderFactory,DbConnection,DbCommand...) to reduce coupling to any specific RDBMS. So I don't have access to theBindByNameproperty, unless I cast explicitly toOracleCommand, losing all the benefits or the abstraction. - When using an ASP.NET SqlDataSource, I don't create the DbCommand myself, so I don't get a chance to set
BindByNameto true (I could do it in the Selecting event, but it really is a pain to do it for each SqlDataSource...)
- 默认设置为 false
- 我几乎从来不使用混凝土ADO.NET类明确，我更喜欢使用ADO.NET 2.0抽象层（
DbProviderFactory，DbConnection，DbCommand...），以减少连接到任何特定的RDBMS。所以我无权访问该BindByName属性，除非我明确地强制转换为OracleCommand，从而失去所有好处或抽象。 - 使用 ASP.NET SqlDataSource 时，我不会自己创建 DbCommand，因此我没有机会设置
BindByName为 true（我可以在 Selecting 事件中进行设置，但对于每个SqlDataSource...)
How am I supposed to handle that issue ? Is there a BindByNameByDefaultsetting somewhere ? (I didn't find anything like that, but I may have missed it...)
我该如何处理这个问题？BindByNameByDefault某处有设置吗？（我没有找到类似的东西，但我可能错过了......）
采纳答案by Theo
I think you can create your own provider that uses the defaults you want to use. You could create that provider easily by inheriting all the classes from odp.net, just adjust some properties like BindByName.
我认为您可以创建自己的提供程序，使用您想要使用的默认值。您可以通过从 odp.net 继承所有类来轻松创建该提供程序，只需调整一些属性，例如 BindByName。
The DbProviderfactory will create your classes instead of the normal odp.net classes.
DbProviderfactory 将创建您的类，而不是普通的 odp.net 类。
回答by Mac
As for the discontinuation of the Microsoft ADO .NET provider for Oracle :
至于停止为 Oracle 提供 Microsoft ADO .NET 提供程序：
- I will go on using it instead of ODP .NET, your problem being only one of the numerous issues with it. And as it goes, it will still be available in .NET 4.0, though unsupported.
- If Oracle manages to render this provider unusable, I will probably go with a commercial alternative such as DataDirect ADO.NET Data Provider for Oracleor dotConnect for Oracle, that fully integrate into the ADO .NET framework. And they already support the Entity Framework, by the way (I believe Oracle stated ODP .NET would not).
- 我将继续使用它而不是 ODP .NET，您的问题只是它的众多问题之一。现在，它仍然可以在 .NET 4.0 中使用，但不受支持。
- 如果 Oracle 设法使该提供程序无法使用，我可能会采用完全集成到 ADO .NET 框架中的商业替代方案，例如DataDirect ADO.NET Data Provider for Oracle或dotConnect for Oracle。顺便说一下，他们已经支持实体框架（我相信 Oracle 表示 ODP .NET 不会）。
ODP .NET took too much of my time already.
ODP .NET 已经占用了我太多的时间。
回答by Anthony Mastrean
Use indirection and inheritance! If you're performing data access through an abstract Database class, require the Database implementation handle parameter binding.
使用间接和继承！如果您通过抽象 Database 类执行数据访问，则需要 Database 实现句柄参数绑定。
public abstract class Database
{
private readonly DbProviderFactory factory;
protected Database(DbProviderFactory factory)
{
this.factory = factory;
}
public virtual DbCommand CreateCommand(String commandText)
{
return CreateCommand(CommandType.Text, commandText);
}
public virtual DbCommand CreateCommand(CommandType commandType, String commandText)
{
DbCommand command = factory.CreateCommand();
command.CommandType = commandType;
command.Text = commandText;
return command;
}
public virtual void BindParametersByName(DbCommand command)
{
}
}
And choose to create an Oracle specific implementation that overrides default command creation or provides the option to bind parameters by name.
并选择创建一个特定于 Oracle 的实现来覆盖默认命令创建或提供按名称绑定参数的选项。
public class OracleDatabase : Database
{
public OracleDatabase()
: base(OracleClientFactory.Instance)
{
}
public override DbCommand CreateCommand(CommandType commandType, String commandText)
{
DbCommand command = base.CreateCommand(commandType, commandText);
BindParametersByName(command);
return command;
}
public override void BindParametersByName(DbCommand command)
{
((OracleCommand)command).BindByName = true;
}
}
Code based on the Data Access Application Blockin the Enterprise Library.
基于代码的数据访问应用程序块的在企业库。
