By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,887 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,887 IT Pros & Developers. It's quick & easy.

Oracle Provider Comparison

P: n/a
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

Apr 2 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.";
}

Apr 3 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.