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

Question

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.

Answer 1

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>