VSM Cover Story

Call Oracle from ADO.NET

Learn to access enterprise data stored in Oracle database servers by calling PL/SQL stored procedures from ADO.NET client applications and discover implementation strategies for dealing with common business scenarios.

• By John Charles Olamendy Turruellas
• 09/01/2008

Oracle has a long history in Visual Studio and Windows shops, but getting Oracle, VS, and related Microsoft technologies to play together nicely can require a little bit of special effort on the developer's end. Some of the special issues are well documented; others, not so well documented.

In this article, I'll show you how to call Procedural Language/Structured Query Language (PL/SQL) from ADO.NET, explaining the most common business scenarios required to invoke stored procedures and functions. I'll also walk you through various implementation strategies through illustrative examples of server-side PL/SQL code hosted in an Oracle database 10g and a client-side application written in C#, ADO.NET 2.0, and the .NET Framework Data Provider for Oracle included in the redistributable package whose object model is hosted in the System.Data.OracleClient namespace.

It's remarkable that the .NET Framework Data Provider for Oracle requires that you install the Oracle Database 10g Client (OCI) suite on the client before you can connect to the underlying Oracle database system. All examples in this article use the default database ORCL, which comes with the Oracle database installation. Be sure to install the software listed under Additional Resources.

Before I walk you through the process of how to use these technologies together, it might be helpful to drill down on some of the specifics of the technologies this article addresses. PL/SQL, the Oracle extension to SQL, lets you write code for your solution logic through structures such as modules, functions, procedures, loops, decision branches, error handling, expressions, logical operators, declarations, types, and variables. A procedure is a module that performs actions that implement business logic. A function is also a module that returns a value. Unlike a procedure, a call to a function can exist as part of an executable, such as an element in an expression or the value assigned by default in the declaration of a variable.

ADO.NET is the implementation of Microsoft's universal data access strategy. It is part of the base class library within the .NET Framework, and it comprises two major subcomponents: the data provider and the dataset. The data provider allows you to access any data source, such as Microsoft SQL Server or Oracle, through the implementation of its own provider classes inherited from base classes such as Connection, Command, Parameter, DataAdapter, and DataReader. The untyped and strongly typed DataSet classes describe the data model or database schema associated with the data source. It is the primary way to specify and implement business entities in the business logic and data-access layer on the solution. ADO.NET 2.0, which is backward-compatible with ADO.NET 1.0, improves performance in XML serialization and connection pooling and eliminates unnecessary back-and-forth communication required to execute queries and receive resultsets.

Execute a Stored Procedure
Begin by calling an Oracle procedure, passing an input parameter, and retrieving the result by an output parameter. First, connect to ORCL using the scott/tiger account by going to the command window and running sqlplusw /nolog. When the SQL*Plus console appears, type connect scott/tiger. Next, write the server-side code for this example. Create the count_emp_by_dept stored procedure, which receives the department number (pin_deptno) as an input parameter and sends back the number of employees in this department as a result (pout_count):

create or replace procedure 
   count_emp_by_dept(
   pin_deptno number, 
   pout_count out number)¬
is
begin
   select count(*) into 
    pout_count
   from scott.emp
   where deptno=pin_deptno;
end count_emp_by_dept;

Now you can develop the client-side component. Create a console application in Visual Studio .NET 2005 by clicking on File | New | Project and selecting Visual Studio C# from the Project Types tree in the left pane and Console Application from the Templates list in the right pane. Enter a descriptive name for the project, such as Invoke_PLSQL_Code, and add a reference to the System.Data.OracleClient.dll assembly, which hosts the objects in the .NET Framework Data Provider for Oracle, by right-clicking on References within the project in the Solution Explorer window, selecting Add Reference, and choosing the System.Data.OracleClient assembly (see Figure 1).

Next, install the Oracle client suite (OCI) and use the Net Manager tool to configure net service names in the tnsnames.ora file in your local ORACLE_HOME\network\admin directory. Net Manager is a graphical user interface used to configure network artifacts such as naming, listeners, and network settings.

Go to Start and select All Programs | Oracle | Configuration and Migration Tools | Net Manager. Select Service Naming from the Oracle Net Configuration tree in the left pane, and click on the plus (+) icon in the toolbox on the left-hand side to start the Net Service Name Wizard. On the Welcome page, enter the service name as orcl. On the Protocol page, accept the default TCP/IP protocol, and on the Protocol Settings page, enter the host name of the database server machine and accept the default port number. On the Service page, enter orcl as the service name (this is the default name given in the installation). Finally, on the Test page, click on the Test button, check whether you have connectivity with the database server given the information you entered, and then click on Finish. Clicking on the newly created net service name displays the configuration screen (see Figure 2).

Use the using directive to import the objects' class residing in the System.Data.OracleClient namespace (see Listing 1). You must establish a connection to the server by creating one instance of OracleConnection (objConn). Note that the Data Source attribute in the connection string references the orcl service name created in the previous step. Next, set up the input and output parameters using the Add method on the parameter collection provided as the Parameters property for the OracleCommand object (objCmd), and set the CommandType.StoredProcedure value to the CommandType property. Finally, call the procedure using the ExecuteNonQuery method of the OracleCommand object (objCmd) and get the result through the output parameter pout_count.

Execute a Function
The next step is to use a function instead of a procedure to retrieve the number of employees by department. Create a function named get_count_emp_by_dept, which receives the department number as its input parameter and returns the number of employees in the department:

create or replace function 
   get_count_emp_by_dept(
   pin_deptno number)
     return number
is
     var_count number;
begin
     select count(*) into var_count
   from scott.emp
     where deptno=pin_deptno; 
     return var_count;
end get_count_emp_by_dept;

Listing 2 shows the application code that calls the function.

Notice that you need to define a return parameter (return_value) to get the result. The other part of the code is similar to calling a procedure.

You can also use the REF CURSOR data type in Oracle, where PL/SQL returns a resultset to client applications written in another language and external to the database. A REF CURSOR object represents the memory location of a query or set of rows on the database server. It also controls the successive retrieval of rows and processes individual rows.

Now assume you want to display a report with information about all the departments in the enterprise. To retrieve the resultset, you must define a REF CURSOR output parameter in a procedure or a function to pass the cursor back to your application.

To do this, you specify the package header and signature of the stored procedure, which opens and sends back a cursor variable to the application referencing a resultset. A package is the construct for bundling related items as a single logical entity. The package header defines which artifacts are public and contained within it:

create or replace package
   human_resources
as
   type t_cursor is ref cursor;
   procedure get_departments(
   cur_department out t_cursor);
end human_resources;

The package body contains the PL/SQL code, which implements the business logic associated with the defined artifacts in the package header. It also contains the private code, which is visible only within the package. Take a look at the PL/SQL code that creates the package body:

create or replace package body    
    human_resources
as
   procedure get_departments(
      cur_department out t_cursor)
   is
   begin
      open cur_department for 
         select * from dept;
   end get_departments;
end human_resources;

You invoke the get_departments procedure inside the human_resources package (see Listing 3).

Pay special attention to the name syntax [package_name].[procedure_name] for calling the procedure contained within a package. To get a cursor, you need to define a cursor parameter with ParameterDirection set to Output and call the ExecuteReader method in the OracleCommand instance (objCmd) to receive the cursor. The PrintReader package method lets you navigate the resultset referenced by the OracleDataReader instance (objReader) and print out individual rows and the underlying query metadata, such as column names.

Receive Multiple Cursors
Now you know how to get a single cursor. The next step is to get multiple cursors. The DataReader object must call the NextResult method to advance to the next cursor.

First, create the package header:

create or replace package
   human_resources
as
   type t_cursor is ref cursor;
   procedure get_employee_department(cur_employee out 
      t_cursor, cur_department out t_cursor);
end human_resources;

Next, implement the package and stored procedure:

create or replace package body human_resources
as
   procedure get_employee_department(cur_employee out 
      t_cursor, cur_department out
        t_cursor)
   is
   begin
          open cur_employee for select *
               from emp;
          open cur_department for select
               * from dept;
     end get_employee_department;
        end human_resources;

The client app code illustrates how to process the two cursors (see Listing 4). Notice the invocation of the NextResult method in the instance of OracleDataReader (objReader) in order to advance to the next cursor.

So far, these .NET solutions have invoked procedures and functions in Oracle in a connected environment. However, some business scenarios might require that you develop a solution for a disconnected environment instead. For example, some people work in a mobile computing environment without full-time network connectivity. Also, some solutions need to address nonfunctional requirements, such as scalabilities.

You can address these needs by working with the .NET DataSet and DataAdapter objects model. DataSet is a disconnected in-memory representation of data. It holds several tables that represent the persistent state of your business entities. The DataAdapter objects implement the underlying mechanism to access a particular database engine. Base objects are inherited from the consumed dataprovider components in order to implement the necessary logic to connect the database in an efficient and performance-driven manner. The .NET Framework currently comes with certain data providers: .NET Framework Data Provider for SQL Server, .NET Framework Data Provider for OLE DB, .NET Framework Data Provider for ODBC, and .NET Framework Data Provider for Oracle.

You can manage the persistent state of the business entity Employee by using the DataSet and DataAdapter objects. First, let's define the create, read, update, and delete (CRUD) data operations associated with this entity by creating the package header and body (see Listing 5).

Finally, develop the client-side application invoking the procedures defined in the human_resources package (see Listing 6).

Populate the data table object (dtEmp) through the use of the DataAdapter object (objAdapter) by setting up the OracleCommand instances (objSelectCmd, objInsertCmd, objUpdateCmd, and objDeleteCmd) and associating each CRUD operation with the underlying OracleCommand. The way you create and set each command is similar to the previous examples, but you need to add a new parameter to the Add method for the Parameters property in order to map the stored procedure parameter name and the names of the table columns. The names associated with the table columns are generated from the select column. For example this statement specifies that the p_empno parameter for the human_resources.insert_employee stored procedure is mapped to the empno column in the dtEmp in-memory table:

objInsertCmd.Parameters.Add(
   "p_empno", OracleType.Number, 4, 
   "empno");.

After you initialize the objects, create a DataTable object (dtEmp) to hold the instances of the employee business entity Employee in memory. The adapter object (objAdapter) populates this data table. You can show a meaningful message that details the total rows in the table, and add a new row that represents a new employee. You send this data to the backend database through the Update method in the adapter object and print out the updated rows persisted in the Oracle database as well as the total rows value.

You've now seen all the possible scenarios involving the invocation of stored procedures and functions in Oracle from ADO.NET. It should be relatively straightforward at this point to take the approaches described in this article and adapt them to your own situations.

• Get Code Download