Run Oracle Stored Procedures From C#
Last Update: 18 February 2005
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
that apply 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.