In DataWindow.NET 2.0, Sybase added support for access to Oracle through the Oracle managed data provider (ODP.NET). A managed data provider provides better performance and more support than a non-managed driver for database-specific functions, though not as much as the Oracle native driver.

On the other hand, the managed data provider can still utilize ADO features of the .NET Framework (including connection caching), which native drivers cannot. In addition, Oracle recently added support to ODP.NET for returning REF CURSORS from Oracle stored procedures. That opens up the possibility of using Oracle stored procedures as the data source for a DataWindow.NET DataWindowObject.

Unfortunately, the ADO.NET driver provided with DataWindow.NET currently doesn't support that functionality. Therefore, our demonstration to implement such functionality will still use the native drivers. At some point, when the ADO.NET driver supports this functionality, the techniques shown in this article should still be applicable.

Bruce Armstrong provided an excellent description of using the DataWindow with Oracle stored procedures in his article "Using Oracle Stored Procedures in Your DataWindows" (PBDJ, Vol. 10, issue 2). It has a detailed explanation of the REF CURSOR concept, which is the key to the solution. The main ideas of that article are still the same for both DataWindows in PowerBuilder and DataWindow.NET. Therefore, we won't discuss the theory, but instead focus on a practical example.

Oracle Stored Procedure Example
A standard Oracle installation has a special SCOTT scheme for demonstration and learning purposes. If you don't have it, the script for this scheme installation could be found in <ORACLE_DB>\RDBMS\ADMIN\scott.sql. In our example, we want to create a stored procedure that returns a list of employees for a given department.

REF CURSOR ORACLE TYPE
For our procedure we need a special Oracle type called a REF CURSOR. We can create this type in any package. An example of such a package is provided in Listing 1.

We need only one ref cursor because we can use the same ref cursor for all procedures; that's why we use an unconstrained ref cursor. At the end of Listing 1, we granted access to our package (and our ref cursor) to every database user.

STORED PROCEDURE
The Oracle stored procedure with the result set can contain any number of input parameters, but the last one should be declared as an IN OUT ref cursor. In Listing 2, we can see the procedure SCOTT.EMP_BY_DEPT. This procedure has two arguments. The first argument (P_DEPTNO) is a department number and it's declared as IN INTEGER. The second argument (P_RESULTSET) is declared as IN OUT SCOTT.MYCURSOR.CURTYPE. This is the type we created in Listing 1.

We can populate the cursor many different ways, but the simplest one is OPEN cursor FOR SELECT.

Sybase DataWindow.NET 2.0 Example
Let's create a simple WinForms application using our procedure. For this purpose, we can create a new project using the Windows Application template. Add a combo-box for the Department selection and a DataWindowControl to show the list of employees from the chosen department.

Sybase DataWindow.NET has two classes for a database connection: AdoTransaction and Transaction. The AdoTransaction class is used for managed and unmanaged ADO.NET providers. However, since it currently doesn't handle REF CURSOR from the stored procedure, we will be using the Transaction class only.

DataWindow
In the Sybase DataWindow Designer, we should connect to Oracle using either O10 Oracle 10g or O90 Oracle 9i interface. Then we can create a new DataWindow d_emp_by_dept based on our stored procedure and put it into demo.pbl library.

REFERENCES
Next we add the references shown in Table 1 to our project.

INSTANCE VARIABLES
Next, put instance variables to the Form1 class declaration.

public partial class Form1 : Form
{
    private string m_ConnectionString = null; // Connection string
    private DataSet m_DeptDS = new DataSet(); // Dataset for Departments
    private Transaction m_Trans = new Transaction();// Sybase transaction object

CONNECTION SETTINGS
It's not a good idea to place the hard-coded connection strings into code, but we do so to simplify our example.

private void Form1_Load(object sender, EventArgs e)
{
    // Connection string
    this.m_ConnectionString = "Data Source=;User ID=SCOTT;Password=TIGER;";

    // Connection parameters
    this.m_Trans.DbParameter = "ConnectString=\'" + this.m_ConnectionString + "\'";
    this.m_Trans.Dbms = DbmsType.Oracle10g;
    this.m_Trans.UserId = "SCOTT";
    this.m_Trans.Password = "TIGER";
    this.m_Trans.ServerName = ""; // put TNS-service name here

LIST OF DEPARTMENTS
We want to allow the user to select a Department from the list and use the Department Number as a first argument to our procedure. For this purpose we can use either standard WinForms Combobox or a DataWindow with a single column and a DropDownDataWindow (dddw) presentation style.

The second solution is better, because it's simpler and allows you to avoid the hard coding of Oracle classes, which in turn provides an opportunity to create a program that supports different database vendors. However, this is not as obvious to .NET developers as it is to PowerBuilder ones, so we will populate Combo-box with the example in Listing 3.

SHOW LIST OF EMPLOYEES FOR THE CHOSEN DEPARTMENT
To get the data we need to connect to the database, retrieve data and disconnect at the end. The script to do this is shown in Listing 4.

RESULT
We can see the result of our application in Figure 1.

Conclusion
It is possible to create a stored procedure-based DataWindow for Oracle as easily as it is for other database vendors. Using Sybase DataWindow.NET 2.0, you can make it transparent for developers and hide Oracle-specific details from the client side, so the same application can be used to access several different DBMS from different vendors.

References

• Armstrong, B. "Using Oracle Stored Procedures in Your DataWindows": http://pbdj.sys-con.com/read/42529.htm
• Rama Mohan G. "Oracle Data Provider for .NET": www.c-sharpcorner.com/Code/2004/Feb/ODP.NET.asp
• DataWindow.NET: www.sybase.com/products/developmentintegration/datawindownet
• Oracle Data Provider for .NET (ODP.NET): www.oracle.com/technology/software/tech/windows/odpnet/install10104.html