Run Oracle Stored Procedures From C#

Note: This article assumes a basic knowledge of Oracle stored procedures and how to create and access them.

While a developer can write practically anything in C#, there are times when it would be nice to have the ability to alter a process without recompiling and redeploying an application. Let's say, for instance, that we are coding for an insurance business and an insured's premium is based upon certain classification codes. Of course, these classification codes are always changing from year to year and how we determine the premium classification is determined on these codes. The easiest way to solve this problem is implement the processing logic within the C# code. Another way is to build a cross-reference table within a database so that when we feed it certain parameters as keys, the query returns the cross-referenced value. One more way is to implement a stored procedure with the logic for a particular business rule. This article will explore the two ways that do not require altering C# code: a cross reference table and a stored procedure. This article will also show how it is much more advantageous to use a stored procedure for rules that could change regularly.

A Business Rule

What do we mean by a business rule? A business rule is a term to reference how input data gets processed based upon the way a company operates. In other words, for certain types of data there are specific rules thatapply in their usage for intended outputs. Before there were such things as stored procedures and stored functions, we would program our rule logic right inside the application code. This means that when a business changes (as they do constantly), we have to make modifications to the source code, recompile, test, debug, and re-deploy. Not only that, modern enterprise information systems are so complex that we could make a programming change in one program without knowing the effects of that change on other programs. The appeal of stored procedures is that we can encapsulate a rules-based processing code-snippet into a single database object.

Lets say that we are working on an insurance claims processing application and we need to classify subscribers into certain divisions based upon some input criteria. Our rule logic in pseudocode is this:

If code1 = 17 Then If code2 = 03 or (code2 >= 1A and code2 <= 1Z) Then division = "Division 1" Else division = "Division 2" End If Else If code1 = 47 Then If code2 = 03 or (code2 >= 1A and code2 <= 1Z) Then division = "Division 3" Else division = "Division 4" End If Else If code2 = 03 or (code2 >= 1A and code2 <= 1Z) Then division = "Division 5" Else division = "Division 6" End If End If End If

In the above pseudocode segment, the rule is this: You are given two codes named code1 and code2. By passing code1 and code2 through the logic above, you will be returned a division, "Division 1" through "Division 6." Your task is to implement this business rule into program code and within a software application. Your choices are to use an "exploded table" model or implement via an Oracle stored procedure.

The Exploded Table Model

What do we mean by an "Exploded Table Model?" We basically mean rule implementation through a cross-reference table. For a minute, take a look at the above example and try to create a cross-reference table with a combination of code1 and code2 as the primary key. We will just take the case of code1 = 17 for example. Our "exploded" cross-reference table would look like the following:

Key	Division
Default	Division 2
1703	Division 1
171A	Division 1
171B	Division 1
171C	Division 1
171D	Division 1
...	Division 1
171Z	Division 1

For the sake of space, we only listed "..." in the first column for key values 171E through 171Y. However, we see that we would need 27 rows to handle the rule of code1 = 17. In order to use this table, we would build a key in our C# program of a concatenation of code1 plus code2 and pass it to our retrieval algorithm as a key in order to return the division name.

Now this would work fine once you have it all set up. And remember that we must have a row for each individual case which would be simple mathematics to determine how many rows we need for all possible conditions. All is well until one day we have to make a change. Lets say that the policy totally changed on how to process input codes 1 and 2 or the division names changed. Someone would have to go into the table and change each row affected by the new rule. Wouldn't it be much easier to sort of "script" the rule by code and you now only have to change the logic in one place. This is where an Oracle stored procedure really comes in handy.

The Oracle Stored Procedure

The other alternative is to create an Oracle stored procedure. We could implement our stored procedure with the following syntax:

CREATE OR REPLACE PROCEDURE ASSIGNDV(CODE1 IN VARCHAR, CODE2 IN VARCHAR, DIV OUT VARCHAR) IS BEGIN IF CODE1 = '17' THEN IF CODE2 = '03' OR (CODE2 >= '1A' AND CODE2 <= '1Z') THEN DV := 'Division 1'; ELSE DV := 'Division 2'; END IF; ELSIF CODE1 = '47' THEN IF CODE2 = '03' OR (CODE2 >= '1A' AND CODE2 <= '1Z') THEN DV := 'Division 3'; ELSE DV := 'Division 4'; END IF: ELSIF CODE2 = '03' OR (CODE2 >= '1A' AND CODE2 <= '1Z') THEN DV := 'Division 5'; ELSE DV := 'Division 6'; END IF; END;

Now, you actually have the business rule "scripted" as an object inside the Oracle database. There is no need to pre-build a result table and if there are any changes to the rule, we only have to change the logic inside the procedure. For the purposes of this example, we name the stored procedure ASSIGNDV.

C# Code Implementation

Just how do we call an Oracle stored procedure from C#? First of all, you need a few prerequisites. For this example, we are running on an Oracle 9i database. There is an important download that you must install prior to implementation of C# code to call Oracle stored procedures. You need the Oracle Developer Tools suite which includes the Oracle Data Provider for .NET. This download can be found at:

http://www.oracle.com/technology/software/tech/dotnet/odt_index.html

Now to implement the code: The first thing we want to do is use the Oracle.DataAccess.Client dll:

using Oracle.DataAccess.Client;

Then, we create a connection object of type OracleConnection, open the connection, and declare an OracleCommand object using the stored procedure name as an input argument. One of the properties we want to set in the OracleCommand object cmd is the CommandType which will be CommandType.StoredProcedure.

OracleConnection conn = new OracleConnection( "Persist Security Info=False;User ID=SCOTT;Password=TIGER;Data Source=MYSERVER;"); conn.Open(); OracleCommand cmd = new OracleCommand("ASSIGNDV",conn); cmd.CommandType = CommandType.StoredProcedure;

Now we want to declare the input and output parameters to and from the stored procedure. We use a class of type OracleParameter to do this. The arguments to the constructor for OracleParameter are the parameter name and the Oracle database type (OracleDbType). As a property to our parameter objects, we give the direction of input or output (ParameterDirection.Input, ParameterDirection.Output). Finally, we execute the stored procedure through the ExecuteNonQuery method on the cmd object and close the connection. The return value from the stored procedure can be found in the "dv" parameter of the cmd object prm3 if all is successful.

OracleParameter prm1 = new OracleParameter("Code1",OracleDbType.Varchar2); prm1.Direction = ParameterDirection.Input; prm1.Value = sCode1; cmd.Parameters.Add(prm1); OracleParameter prm2 = new OracleParameter("Code2",OracleDbType.Varchar2); prm2.Direction = ParameterDirection.Input; prm2.Value = sCode2; cmd.Parameters.Add(prm2); OracleParameter prm3 = new OracleParameter("dv",OracleDbType.Varchar2,10); prm3.Direction = ParameterDirection.Output; cmd.Parameters.Add(prm3); cmd.ExecuteNonQuery(); conn.Close(); Console.WriteLine("Division is: " + cmd.Parameters["dv"].Value);

Summary

What is really nice about Oracle stored procedures is that they are compiled objects. Their code does not have to be recompiled at runtime for each call. Therefore they can be about as fast as compiled C# code. One must remember that the stored procedures are part of a database and their performance is subjective to it. Now, if the rule to determine DV in the above example changes, we only have to go into the stored procedure and change that. There is no need to check out code, make the change, and recompile. Our application programs can stay more in a fixed state.