CODEWITHSUNDEEP
c#asp.netoracle-databaseodp.net
minty
minty

Reputation: 22421

SqlDataSource and Oracle DataProvider I can't seem to be able to use the sqlDataprovider with odp.net

I want to be able to run my SqlDataProvider against an oracle stored procedure. I can use Microsoft's Oracle Provider but that wouldn't allow me to call a stored procedure. has anyone been able to get this to work? I particularly want to be able to use declarative data binding. I have been able to programatically create a DataTable but I want to do this declaratively in the .aspx.

Upvotes: 0

Views: 3468

Answers (2)

Israel Margulies
Israel Margulies

Reputation: 8952

Here are the steps to return a table-like select statement::

1) You should return a cursor for the select, then add in the parameters io_cursor IN OUT CURSOR

2) When you Consume it add ANOTHER parameter 

<asp:Parameter Name="io_cursor" Direction="Output" />

3) Add event procedure for 'selecting' event

4) protected void SqlDataSource1_Selecting (object sender, SqlDataSourceSelectingEventArgs e) { ((System.Data.OracleClient.OracleParameter)e.Command.Parameters[0]).OracleType = System.Data.OracleClient.OracleType.Cursor; }

Now it would work fine.

Upvotes: 0

user46119
user46119

Reputation: 81

SqlDataProvider, SqlConnection and other classes prefixed Sql from the System.Data namespaces almost universally refer to SQL-Server specific implementations. It is, however, possible to invoke a Stored Procedure using the System.Data.oracleClient library Microsoft have released.

Please ensure that when constructing the OracleCommand you are passing in CommandType.StoredProcedure. Otherwise the database engine will default to 'table direct' access, and since it won't find a table with the name of your stored procedure, it'll fall over.

Here's some example code on how this would work behind the scenes:

using (OracleConnection conn = new OracleConnection("connection string here"))
{
    conn.Open();

    OracleCommand command = conn.CreateCommand();
    command.CommandType = CommandType.StoredProcedure;

    command.CommandText = "DATABASE_NAME_HERE.SPROC_NAME_HERE";
    // Call command.Parameters.Add to add your parameters.

    using (OracleReader reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            // Process each row
        }
    }

}

When using ASP .NET, you can use the SqlDataSource to acces the oracle client with a connection string defined like:

<add name="OracleConnectionString"
  connectionString="Data Source=YourServer;Persist 
    Security Info=True;Password="******";User ID=User1"
  providerName="System.Data.OracleClient" />

Note that we've got the OracleClient bit there. Then on the SqlDataSource set the Select CommandType on it to be StoredProcedure in your ASPX page, and the rest pretty much works like SQL Server (in fact, you actually have to do this to call the SQL Server version).

The result looks a bit like:

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>"
            ProviderName="<%$ ConnectionStrings:OracleConnectionString.ProviderName %>" SelectCommand='TEST_ONE' SelectCommandType="StoredProcedure" ></asp:SqlDataSource>

Upvotes: 2

Related Questions