SqlDataSource and Oracle DataProvider I can't seem to use sqlDataprovider with odp.net
I want to be able to run my SqlDataProvider against an oracle stored procedure. I can use the Microsoft Oracle Provider, but that will prevent me from calling the stored procedure. Anyone could get this to work? I especially want to use declarative data binding. I was able to programmatically create the DataTable, but I want to do it declaratively in .aspx.
source to share
SqlDataProvider, SqlConnection, and other Sql-prefixed classes from the System.Data namespaces almost universally refer to specific SQL-Server implementations. However, you can call the stored procedure using the System.Data.oracleClient library released by Microsoft.
Please make sure you pass in the CommandType when building the OracleCommand. StoredProcedure . Otherwise, the default database engine will have access to the "straight table", and since it cannot find the table with the name of your stored procedure, it will crash.
Here's a sample code of 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 SqlDataSource to access oracle client with connection string defined as:
<add name="OracleConnectionString"
connectionString="Data Source=YourServer;Persist
Security Info=True;Password="******";User ID=User1"
providerName="System.Data.OracleClient" />
Note that we have the OracleClient bit. Then on the SqlDataSource, set the CommandType to CommandType to StoreProcedure in the ASPX page, and the rest pretty much work like SQL Server (in fact, you actually need to do this to invoke the SQL Server version).
The result is a bit similar:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>"
ProviderName="<%$ ConnectionStrings:OracleConnectionString.ProviderName %>" SelectCommand='TEST_ONE' SelectCommandType="StoredProcedure" ></asp:SqlDataSource>
source to share
Following are the steps to return a tabular select statement:
1) You have to return the cursor to select and then add io_cursor IN OUT CURSOR parameters
2) When you use it, add the parameter OTHER
<asp:Parameter Name="io_cursor" Direction="Output" />
3) Add an event procedure for the "Select" event
4) protected void SqlDataSource1_Selecting (object sender, SqlDataSourceSelectingEventArgs e) {((System.Data.OracleClient.OracleParameter) e.Command.Parameters [0]). OracleType = System.Data.OracleClient.OracleType.Cursor; }
Everything will be fine now.
source to share