title: Binding query parameters by name with ODP.NET
body: <p>I'm currently using the Microsoft ADO.NET provider for Oracle (<code>System.Data.OracleClient</code>). I'm aware that it is certainly not the best Oracle provider available and that it <a href="http://blogs.msdn.com/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx" rel="noreferrer">will soon be deprecated</a>, I should be using Oracle's ODP.NET instead. The reason why I still use the MS provider is because <strong>ODP.NET binds parameters by position</strong>, 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 :</p>
  
  <pre><code>SELECT A,B,C FROM FOO WHERE X = :PARAM_X OR :PARAM_X = 0
  </code></pre>
  
  <p>With ODP.NET, I have to add two parameters to the <code>OracleCommand</code>, which I think is stupid...</p>
  
  <p>ODP.NET's <code>OracleCommand</code> has a property to change that default behavior : <code>BindByName</code>. When set to true, the parameters are bound by name, which is what I want. Unfortunately this doesn't really help me, because :</p>
  
  <ul>
  <li>It is set to false by default</li>
  <li>I almost never use concrete ADO.NET classes explicitly, I prefer to use ADO.NET 2.0 abstraction layer (<code>DbProviderFactory</code>, <code>DbConnection</code>, <code>DbCommand</code>...) to reduce coupling to any specific RDBMS. So I don't have access to the <code>BindByName</code> property, unless I cast explicitly to <code>OracleCommand</code>, losing all the benefits or the abstraction.</li>
  <li>When using an ASP.NET SqlDataSource, I don't create the DbCommand myself, so I don't get a chance to set <code>BindByName</code> to true (I could do it in the Selecting event, but it really is a pain to do it for each SqlDataSource...)</li>
  </ul>
  
  <p>How am I supposed to handle that issue ? Is there a <code>BindByNameByDefault</code> setting somewhere ? (I didn't find anything like that, but I may have missed it...)</p>