Oracle Provider Comparison

Discussion in 'ASP .Net' started by Evan M., Apr 2, 2007.

  1. Evan M.

    Evan M. Guest

    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
     
    Evan M., Apr 2, 2007
    #1
    1. Advertising

  2. Evan M.

    James Irvine Guest

    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_Compare_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.";
    }
     
    James Irvine, Apr 3, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. BS
    Replies:
    3
    Views:
    2,073
    Alvin Bruney [MVP]
    Jun 17, 2004
  2. sloan
    Replies:
    5
    Views:
    1,519
    sloan
    Jun 4, 2006
  3. Replies:
    0
    Views:
    754
  4. sloan
    Replies:
    1
    Views:
    521
    Chad Scharf
    Jul 3, 2007
  5. Feyruz
    Replies:
    4
    Views:
    2,243
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page