Oracle Provider Comparison

[Evan M.]

Hello,

I'm creating a web app that needs to provide some simple, read-only
access to an Oracle database using SELECT statements. What I am
curious about is which provider (System.Data.OracleClient or ODP.NET)
would be a better using in this instance. The Oracle database itself
resides on a remote server hosted by our parent company across a WAN
connection, so, there is a network efficiancy concern.

The other problem I am having with respect to this app is that I am no
able to get a paramaterized SqlDataSource to work with the ODP.NET
provider. Trying to use the provider returns no results. Using the
included .NET provider (with no modifications to the SqlDataSource)
does not have any problems and returns the results as expected.
Example:

<asp:SqlDataSource ID="CustomersDS" runat="server"
ConnectionString="<%$ ConnectionStrings:OracleConn %>"
ProviderName="<%$ ConnectionStrings:OracleConn.ProviderName %>"
SelectCommand="SELECT CUSTOMER_NUMBER, CUSTOMER_NAME FROM
CUSTOMERS_V WHERE CUSTOMER_NUMBER LIKE :x AND CUSTOMER_NAME LIKE :y"
SelectCommandType="text">
<SelectParameters>
<asp:ControlParameter Name="x" ControlID="CustID"
PropertyName="Text" />
<asp:ControlParameter Name="y" ControlID="CustName"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="CustomersGrid" runat="server"
AutoGenerateColumns="false" DataSourceID="CustomersDS"
DataKeyNames="CUSTOMER_NUMBER"
OnRowCommand="CustomersGrid_RowCommand">
<Columns>
<asp:BoundField HeaderText="Customer ID"
DataField="CUSTOMER_NUMBER" ConvertEmptyStringToNull="false" />
<asp:BoundField HeaderText="Name" DataField="CUSTOMER_NAME"
ConvertEmptyStringToNull="false" />
<asp:ButtonField ButtonType="Button" Text="Select"
CommandName="SelectCustomer" />
</Columns>
</asp:GridView>

Your thoughts are greatly appreciated.
Thanks,
Evan

[James Irvine]

Evan M. wrote:

Hello,

I'm creating a web app that needs to provide some simple, read-only
access to an Oracle database using SELECT statements. What I am
curious about is which provider (System.Data.OracleClient or ODP.NET)
would be a better using in this instance. The Oracle database itself
resides on a remote server hosted by our parent company across a WAN
connection, so, there is a network efficiancy concern.

The other problem I am having with respect to this app is that I am no
able to get a paramaterized SqlDataSource to work with the ODP.NET
provider. Trying to use the provider returns no results. Using the
included .NET provider (with no modifications to the SqlDataSource)
does not have any problems and returns the results as expected.
Example:

<asp:SqlDataSource ID="CustomersDS" runat="server"
ConnectionString="<%$ ConnectionStrings:OracleConn %>"
ProviderName="<%$ ConnectionStrings:OracleConn.ProviderName %>"
SelectCommand="SELECT CUSTOMER_NUMBER, CUSTOMER_NAME FROM
CUSTOMERS_V WHERE CUSTOMER_NUMBER LIKE :x AND CUSTOMER_NAME LIKE :y"
SelectCommandType="text">
<SelectParameters>
<asp:ControlParameter Name="x" ControlID="CustID"
PropertyName="Text" />
<asp:ControlParameter Name="y" ControlID="CustName"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="CustomersGrid" runat="server"
AutoGenerateColumns="false" DataSourceID="CustomersDS"
DataKeyNames="CUSTOMER_NUMBER"
OnRowCommand="CustomersGrid_RowCommand">
<Columns>
<asp:BoundField HeaderText="Customer ID"
DataField="CUSTOMER_NUMBER" ConvertEmptyStringToNull="false" />
<asp:BoundField HeaderText="Name" DataField="CUSTOMER_NAME"
ConvertEmptyStringToNull="false" />
<asp:ButtonField ButtonType="Button" Text="Select"
CommandName="SelectCustomer" />
</Columns>
</asp:GridView>

Your thoughts are greatly appreciated.
Thanks,
Evan

I use the Enterprise Library, and use Oracle's providers to call Oracle
stored PL/SQL procedures with parameters like this:

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Common;
try
{
Database db = DatabaseFactory.CreateDatabase("epicurConnMonte");
DataSet dsOracleOutputCursor = new DataSet();
dsReturned =
db.ExecuteDataSet("monte.Pkg_Get_Sets.Proc_Get_Com pare_Set", pNDB_No1,
pNDB_No2, dsOracleOutputCursor);

msgBack = "Food Items dataset was loaded via Oracle call";
recCountBack = dsReturned.Tables[0].Rows.Count;
}

catch
{
msgBack = "datasets were not accessible.";
}