Processing with C# and Oracle PL/SQL

Title:       "Processing with C# and Oracle PL/SQL "BIG" transactions"
Author:      Dimitar Nikolaev Madjarov 
Email:       madjarov_d_n@yahoo.com
Language:    MS Visual Studio 2003, 2005, ASP.NET, C# and Oracle DB
Platform:    Windows, .NET 3.0 etc
Technology:  ASP.NET, GDI+
Level:       Beginner, Intermediate, Advanced
Description: An article which describes an alternative way of cooperation between Oracle PL/SQL and C#
Section      SQL, PL/SQL, Database and C# programming
SubSection   General

Introduction

This article actually is an heir of my first article from this topic about programming with Oracle and C#. The first article is available for reading on next web address "Processing with C#, Oracle transactions and Oracle exceptions". In my first article I describe an alternative way of using of powerful Oracle transactions. By this way we use original PL/SQL code to control transactions we are not using C# code to manage transactions from the source code of our application. This working well when we use ours store procedures in Oracle one by one. In this case everyone procedure use a local/inside in the body of store procedure Commit and Rollback/ control over transactions. This is perfect when we have a simple application but unfortunately the real life is a little-bit more complicate. In the real business logic we have many different combinations of executing stored procedures in a complicate order /many times this order depend from executed status of previous store procedure/ and when all of them execute their piece of PL/SQL code we have to COMMIT the whole changes which we did or when in our execute list's order one of the stored procedures fail to execute it's Pl/SQL we have to ROLLBACK all changes and fire an event with suitable message about result, is our source code fail or succeed with executing of it's task.

The used PL/SQL

Firstly we have to create our demo tables and our demo package. Below is the whole PL/SQL code which we will use in our demonstration. In this section we are going to create our tables. Please notice that I created field 'CREATEDATE' from data type 'DATE'. I did this purposely with main idea to show you a small trick about how to working/exchange with 'DATE' type data between Oracle and C# when you build your PL/SQL into your C# code.

CREATE TABLE T1
  (
    ID         NUMBER   (4) NOT NULL,
    NAME       VARCHAR2 (100),
    CREATEDATE DATE DEFAULT SYSDATE NOT NULL
 )
/

ALTER TABLE T1 ADD CONSTRAINT PK_T1_ID
  PRIMARY KEY (ID)
/



CREATE TABLE T2
  (
   ID   NUMBER   (4) NOT NULL,
   NAME VARCHAR2 (100)
 )
/

ALTER TABLE T2 ADD CONSTRAINT PK_T2_ID
  PRIMARY KEY (ID)
/

In this section we are going to create our inserts stored procedures

CREATE OR REPLACE PACKAGE PACKAGEDEMO IS

PROCEDURE T1ADD(ID   in number,  
                Name in varchar2, 
                CRDATE DATE,
                error_sql_value out varchar2,
                DoTrans in number default 1);
PROCEDURE T2ADD(ID   in number,  
                Name in varchar2, 
                error_sql_value out varchar2,
                DoTrans in number default 1);

END PACKAGEDEMO;
/

create or replace package body PACKAGEDEMO is

PROCEDURE T1ADD(ID in number, Name in varchar2, CRDATE DATE, 
                error_sql_value out varchar2, DoTrans in number default 1)
AS BEGIN
    error_sql_value   := '-1';
    INSERT INTO T1 (ID, NAME, CREATEDATE) VALUES (ID, Name, CRDATE);
    IF (DoTrans = 1) THEN
         COMMIT;
        END IF;
    EXCEPTION
    WHEN OTHERS THEN BEGIN
      IF (DoTrans = 1) THEN
       ROLLBACK;
      END IF;
     error_sql_value := 'STOREP ROCEDURE "T1ADD" in PACKAGE "PACKAGEDEMO" ERROR: '||SUBSTR(SQLERRM, 1, 255)||', ERROR CODE: '||SQLCODE;
    END;
END;


PROCEDURE T2ADD(ID in number, Name in varchar2, 
                error_sql_value out varchar2, DoTrans in number default 1)
AS BEGIN
    error_sql_value   := '-1';
    INSERT INTO T2 (ID, NAME) VALUES (ID, Name);
     IF (DoTrans = 1) THEN
           COMMIT;
         END IF;
    EXCEPTION
    WHEN OTHERS THEN BEGIN
      IF (DoTrans = 1) THEN
        ROLLBACK;
      END IF;
     error_sql_value := 'STOREP ROCEDURE "T2ADD" in PACKAGE "PACKAGEDEMO" ERROR: '||SUBSTR(SQLERRM, 1, 255)||', ERROR CODE: '||SQLCODE;
    END;
END;
end PACKAGEDEMO;
/     

The main idea into PL/SQL terms

Now we are going to provide a brief description of how working mine idea about PL/SQL control over Oracle transactions

[I]. In this first point we will go to execute only one alone store procedure as Pl/SQL with help of 'ExecuteNonQuery' C# code. Of course you may use and other C# methods to execute a store procedure but I will use the 'ExecuteNonQuery' ADO.NET method because in next point when we have to build one complicate business logic I will going to use exactly this method.(ExecuteNonQuery)

The deal here is to build the Pl/SQL above into your C# code and to 
execute it as using 'ExecuteNonQuery' method. Into a try/catch passage 
you have to catch the error and manipulate it. This example is how to 
use a simple transaction. Executed store procedure is alone and 
transactions management as Commit/Rollback statements are ruled 
into body of our stored procedure 't2add'.

DECLARE
error_sql_value varchar2 (4000);  
BEGIN
PACKAGEDEMO.t2add(10,'Test_10',error_sql_value);
IF (error_sql_value <> '-1') THEN
 RAISE_APPLICATION_ERROR (-20000, error_sql_value);
END IF;
 EXCEPTION
    WHEN OTHERS THEN BEGIN
    ROLLBACK;
    RAISE_APPLICATION_ERROR (-20000, error_sql_value);
 END;
COMMIT;
END;       

[II]. In this second point we are going to execute a complicate scheme of two different store procedures. We will use the same ADO.NET method 'ExecuteNonQuery' but now we are not able to control transactions in everyone store procedure because the logic is if first procedure fail we have to rollback the whole changes. Also we have to do the same if first one passes but the second procedure fail we again have to do rollback of all changes. We have to set commit changes in case that and both of them pass.

The deal here is to build the Pl/SQL above into your C# code and to execute it as using 'ExecuteNonQuery' method. 

Into a try/catch passage you have to catch the error and manipulate it. 
Please notice that now parameter 'DoTrans" has value '0' and by this way we send a command to our store procedures
to stop manage transactions statements locally (inside the procedure body).

 Now we manage transactions as Pl/SQL in the SQL statement which we build inside of our C# code.

DECLARE
error_sql_value varchar2(4000);  
BEGIN
SAVEPOINT LOCALSP1;

PACKAGEDEMO.t1add(0,'Test_0', TO_DATE('17.04.2007 16:04:12','DD.MM.YYYY HH24:MI:SS'), error_sql_value,0);
IF (error_sql_value <> '-1') THEN
 RAISE_APPLICATION_ERROR (-20000, error_sql_value);
END IF;

PACKAGEDEMO.t2add(0,'Test_0',error_sql_value,0);
IF (error_sql_value <> '-1') THEN
 RAISE_APPLICATION_ERROR (-20000, error_sql_value);
END IF;

PACKAGEDEMO.t2add(1,'Test_1',error_sql_value,0);
IF (error_sql_value <> '-1') THEN
 RAISE_APPLICATION_ERROR (-20000, error_sql_value);
END IF;

 EXCEPTION
    WHEN OTHERS THEN BEGIN
    ROLLBACK TO LOCALSP1;
    RAISE_APPLICATION_ERROR (-20000, error_sql_value);
 END;
COMMIT;
END;       

The C# code

Up-to now we did the most valuable step in this article. I hope that you understand the main idea above. In this section I am going to do the easier task to use C# code to build our PL/SQL from point.II and execute this PL/SQL with ADO.NET method 'ExecuteNonQuery'. You may use my Oracle methods from my previous article to access Oracle database and manage data from it.

using System;
using System.Data;
using System.Configuration;
using System.Text;
using System.IO;

namespace ORacle.Logic
{
        public class ORATransactions 
    {   
               public ORATransactions()
               {
                 //
                 // An empty method
                 //
               }
               
                
        private string OraString(string inputStr)
        {
            if (inputStr == null)
            {
                return "NULL";
            }
                        
              //
             // Replacing oracle special characters
             //
            inputStr = inputStr.Replace("'", "''");
                        
            //
            // Adding ' to string
            //
            inputStr = string.Format("'{0}'", inputStr);
                        
            return inputStr;
        }
                

                
        private string OraDateTime(DateTime date)
        {
                return date.ToString("dd.MM.yyyy HH:MM:ss");
        }
                
                
        public string UpdateOraSQL(ourTestData data) 
        {
            string ExecuteSQL  = String.Empty;
            
            ExecuteSQL  = "DECLARE \n";
            ExecuteSQL += "error_sql_value varchar2(4000); \n";
            ExecuteSQL += "BEGIN \n";
            ExecuteSQL += "SAVEPOINT LOCALSP1; \n";  
                        
                        //
            // Bind PACKAGEDEMO.t1add store procedure
            //
            ExecuteSQL += String.Format("PACKAGEDEMO.t1add({0}, {1}, TO_DATE('{2}','DD.MM.YYYY HH24:MI:SS'), error_sql_value, 0); \n",
                                     data.ID[0].ToString(), OraString(data.NAME[0].ToString()), OraDateTime(data.CRDATE[0].CREATED)
                                    );
            ExecuteSQL += "IF (error_sql_value <> '-1') THEN \n";
            ExecuteSQL += "RAISE_APPLICATION_ERROR (-20000, error_sql_value||' '||error_sql_descr); \n";
            ExecuteSQL += "END IF; \n";
                        
            //
            // Bind PACKAGEDEMO.t2add store procedure
            //
            ExecuteSQL     += String.Format("PACKAGEDEMO.t2add({0},{1},error_sql_value,0); \n",
                                        data.ID[1].ToString(), OraString(data.Name[1].ToString())
                                                        );
            ExecuteSQL     += "IF (error_sql_value <> '-1') THEN \n";
            ExecuteSQL     += "RAISE_APPLICATION_ERROR (-20000, error_sql_value||' '||error_sql_descr); \n";
            ExecuteSQL     += "END IF; \n";                      
                                               
                        ExecuteSQL += "EXCEPTION \n";
            ExecuteSQL += "WHEN OTHERS THEN BEGIN \n";
            ExecuteSQL += "ROLLBACK TO LOCALSP1; \n";
            ExecuteSQL += "RAISE_APPLICATION_ERROR (-20000, error_sql_descr||' '||error_sql_value); \n";
            ExecuteSQL += "END; \n";
            ExecuteSQL += "COMMIT; \n";
            ExecuteSQL += "END; \n";

            return ExecuteSQL;
         }
                
                
         public string UpdateOra(ourTestData data) 
         {

            string ReturnValue  = "-1";
                   OracleBuisinessDalc dalc        = new OracleBuisinessDalc();
            
            try
            {
                     ReturnValue = dalc.Execute_Non_Query(UpdateOraSQL(data));
            }
            catch (Exception ex)
            {
                ReturnValue = ex.Message;
            }
            finally
            {
                    return ReturnValue;
            }        
        }
        }
}



Finally you my execute the method string UpdateOra(ourTestData data) into your 'Page-Load' method for an example and you will 
receive '-1' if all is Ok or the full error description in case that something was wrong. 
Of course this is also a simple example but by this way you may compose very complicate business logic and finally to generate a 'big' string
which contain the PL/SQL similar as this from point.II. 
</br>After that you may execute this string from your C#, VB.NET code and receive the status of executing SQL.

Conclusion

In this article I try to explain one a little bit different way of working and playing with Oracle transactions and catching the Oracle exception directly in our store procedures and processing them without any risk this to blow-up the ASP.NET/C# applications which interact with data stored in Oracle. I would like to say that this is a just different way to working with Oracle and C#, this is maybe not the perfect way but the author of this article know that, freedom to have a options, to have a choice is one of the great deal in this life.
I hope that you will evaluate this article and the option which it describes to you to work with Oracle transactions by one alternative way will be useful for you and your business.