Processing with C#, Oracle Transactions and Oracle Exceptions

Introduction

The main purpose of this article is to show one different way of cooperation between Oracle and C# in the process of developing a different Oracle database application with Microsoft .NET technology and C#. In this article, I will try to show you a new approach of programming and taking control over Oracle transaction and processing with PL/SQL exceptions. This "new" way of programming with Oracle database and Microsoft C# programming language will provide to every .NET developer one more flexible and more powerful way to control Oracle transactions and to "play" with errors which are generated as PL/SQL exception after executing wrong PL/SQL code or PL/SQL code which try to break existing database rules or relations as foreign keys for example.

Using the PL/SQL and C# Code

1. Firstly, we have to create a simple table with name "MYTEST" and a very simple primary key. This key will be our guarantee that the records into our table will be unique. We have to use the next PL/SQL code to create the table and the primary key.
   SQL

   /* Create table "MYTEST" */
   CREATE TABLE MYTEST
     (ID      INTEGER                      NOT NULL,
      NAME    VARCHAR2 (50)                NOT NULL,
      ADDRESS VARCHAR2 (150) DEFAULT 'N/A' NOT NULL
    )
   /
   
   /* Create primary key "PK_MYTEST_ID" */
   ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST_ID
     PRIMARY KEY (ID)
   /

2. Secondly, we have to create an Oracle package with one stored procedure which will try to insert a row with simple data into our previously created table. Here, it will be very important to notice the PL/SLQ code inside our "insert store procedure". In this part of our example will be the most important code which will start transaction, will commit or rollback the changes depending on whether the "PL/SQL Insert code" generates an error or not. Also in this part, we will interact and catch the Oracle errors and will return them as status flag to our C# co-operator code. The main goal is always to use the correct transaction state and never allow one not processing error to blow-up our database application. If any error appears in our "Insert procedure", we have to set the correct transaction, catch the error and send it as a variable outside of our "Insert procedure" to C# code from our application. The code below contains all necessary Pl/SQL positions to be made our main goal. Please take a look over it.
   SQL

    /* Create PACKAGE "OUR_TEST_INSERT_PACKAGE" */
    CREATE OR REPLACE PACKAGE OUR_TEST_INSERT_PACKAGE IS
    TYPE outputCursor IS REF CURSOR;
      PROCEDURE InsertIntoMYTEST(ID                  in  int,
                                 NAME                in  varchar2(50),
                                 ADDRESS             in  varchar2(150),
                                 Error_Sql_Code      out varchar2(10),
                                 Error_Description   out varchar2,
                                 Error_SQL_Value     out varchar2);
    END;
   /
   
   /* Create PACKAGE BODY "OUR_TEST_INSERT_PACKAGE" */
   CREATE OR REPLACE PACKAGE BODY OR_DESIGNS_INSERTS AS
   
   /* Create Store Procedure "InsertIntoMYTEST" */
   PROCEDURE InsertIntoMYTEST(ID                  in  int,
                              NAME                in  varchar2(50),
                              ADDRESS             in  varchar2(150),
                              Error_Sql_Code      out varchar2(10),
                              Error_Description   out varchar2,
                              Error_SQL_Value     out varchar2)
   AS BEGIN
    Error_Sql_Code      := '-1';
    Error_Description   := '-1';
    Error_SQL_Value     := '-1';
    INSERT INTO MYTEST (ID, NAME, ADDRESS) VALUES (ID, NAME, ADDRESS);
    COMMIT;
    EXCEPTION
       WHEN OTHERS THEN BEGIN
       ROLLBACK;
       Error_Sql_Code    := 'STOREP ROCEDURE "InsertIntoMYTEST" _
   	INSIDE PACKAGE "OUR_TEST_INSERT_PACKAGE" ERROR CODE: '||SQLCODE;
       Error_Description := 'STOREP ROCEDURE "InsertIntoMYTEST" _
   	INSIDE PACKAGE "OUR_TEST_INSERT_PACKAGE" ERROR BODY: _
   	'||SUBSTR(SQLERRM, 1, 255)||'!';
       Error_SQL_Value   := 'INSERT INTO MYTEST (ID, NAME, ADDRESS)  _
   	VALUES ('||ID||', '''||NAME||''', '''||ADDRESS||''');';
       END;
   END;
   
   END;
   /
   
   ALTER PACKAGE OUR_TEST_INSERT_PACKAGE COMPILE BODY 
   /

   You may see that I process the PL/SQL errors inside the stored procedure. This means that for our C# application, this stored procedure never will return an exception. Also in the body of Oracle stored procedure above, I always use "Commit" or "Rollback" transaction state in the correct order and my data is always clear.

3. Thirdly and the last step is that we have to take care always for these three "out" parameters which Oracle stored procedure returns to us the status for Oracle error code "Error_Sql_Code", Oracle error description stored into parameter "Error_Description" and the PL/SQL which we try to execute stored into parameter "Error_SQL_Value". The last option is very comfortable because the Oracle monitoring tool which provides information of what exactly PL/SQL was executed is not as useful as Microsoft SQL Server similar tool. The Microsoft C# code below is a good example of how you may work with these parameters.
   C#

   //
   // This is a simple Web application based over .NET v.1.1
   //
   using System;
   using System.Collections;
   using System.ComponentModel;
   using System.Drawing;
   using System.Web;
   using System.Web.SessionState;
   using System.Web.UI;
   using System.Web.UI.WebControls;
   using System.Web.UI.HtmlControls;
   using System.Configuration;
   using System.Data;
   using System.Data.OracleClient;
   
   namespace ORACLETEST
   {
       public class OraTestWebForm : System.Web.UI.Page
       {
               private void Page_Load(object sender, System.EventArgs e)
               {
   
                       string executeStatus = InsertIntoMYTEST().Trim();
   
                       if (executeStatus  == "-1")
                       {
                         Response.Write("The insert sql was executed without errors!");
                       }
                       else
                       {
                         Response.Write("Error: " + executeStatus);
                       }
               }
   
       public string InsertIntoMYTEST() 
       {
               string Error_msg     = "-1"; 
               string procedureName = "OUR_TEST_INSERT_PACKAGE.InsertIntoMYTEST";
   
               OracleParameter[] parameters = new OracleParameter[6];
   
               //
               // Add parameters
               //
               parameters[0]  = CreateParameter
   		("ID", OracleType.Number, 100) as OracleParameter;
               parameters[1]  = CreateParameter
   		("NAME", OracleType.Char, "Mitko test") as OracleParameter;
               parameters[2]  = CreateParameter("ADDRESS", OracleType.VarChar, 
   		"Bulgaria, Sofia city") as OracleParameter;
               parameters[3]  = CreateParameter
   		("Error_Sql_Code", OracleType.VarChar, String.Empty, 
   		10, ParameterDirection.Output)     as OracleParameter;
               parameters[4]  = CreateParameter("Error_Description", 
   		OracleType.VarChar, String.Empty, 255, ParameterDirection.Output) 
   		as OracleParameter;
               parameters[5]  = CreateParameter
   		("Error_SQL_Value", OracleType.VarChar, String.Empty, 500, 
   		ParameterDirection.Output)   as OracleParameter;
       
               //
               // Execute
               //
               ExecuteNonQuery(CommandType.StoredProcedure, procedureName, parameters);
   
               //
               // In case that parameters "Error_Sql_Code", 
               // "Error_Description" and "Error_SQL_Value" all of them have
               // value "-1" this means that we have a successfully inserted 
               // and committed data into our table.
               // Otherwise we have a mistake and we send it to method...
               //
               if ((parameters[3].Value.ToString().Trim() != "-1") && 
                   (parameters[4].Value.ToString().Trim() != "-1") && 
                   (parameters[5].Value.ToString().Trim() != "-1")
                  )
                  {
                       Error_msg = parameters[3].Value.ToString().Trim()  + " \n\r"
                                 + parameters[4].Value.ToString().Trim()  + " \n\r"
                                 + parameters[5].Value.ToString().Trim();
                  }
   
                  return Error_msg;        
       }
   
           protected string ConnectionString
           {
                   get
                   {
                       //
                       // Place this variable "OURTESTORACONNECTIONSTRING" 
   	           // into Web.config file
                       //
                       return ConfigurationSettings.AppSettings.Get
   				("OURTESTORACONNECTIONSTRING");
                   }
           }
   
           private void AddParameters(OracleCommand command,object[] parameters)
           {
   
                   if (command == null)
                   {                
                           throw new ApplicationException("null Command");
                   }
   
                   if(parameters != null)
                   {
                           for(int i = 0; i < parameters.Length; i++)
                           {
                                   command.Parameters.Add(parameters[i] 
   						as OracleParameter);
                           }
                   }
           }
   
           protected int ExecuteNonQuery
   	(CommandType commandType, string commandText, object[] parameters)
           {
                   using(OracleConnection connection = 
   			new OracleConnection(ConnectionString))
                   {
                           try
                           {
                                   connection.Open();
   
                                   using(OracleCommand command = 
   				new OracleCommand(commandText,connection))
                                   {
                                           command.CommandType = commandType;
   
                                           AddParameters(command,parameters);
   
                                           return command.ExecuteNonQuery();
                                   }
                           }
                           finally
                           {
                                   if(connection.State == ConnectionState.Open)
                                   {
                                      connection.Dispose();
                                   }
                           }
                   }
           }
   
           protected object CreateParameter
   	(string name, object dataType, object parameterValue)
           {
                   return this.CreateParameter
   		(name, dataType, parameterValue, -1, ParameterDirection.Input);
           }
   
           protected object CreateParameter(string name, object dataType, 
   				object parameterValue, int size)
           {
                   return this.CreateParameter(name, dataType, 
   			parameterValue, size, ParameterDirection.Input);
           }
   
           protected object CreateParameter(string name, object dataType, 
   		object parameterValue, int size, ParameterDirection direction)
           {
                   OracleParameter parameter = new OracleParameter
   					(name, (OracleType)dataType);
   
                   parameter.Value     = parameterValue;
                   parameter.Direction = direction;
   
                   if (size >= 0)
                   {
                      parameter.Size = size;
                   }
   
                   return parameter;
           }
   
           protected object CreateReturnParameter()
           {
   
                   OracleParameter returnParameter = new OracleParameter();
   
                   returnParameter.Direction       = ParameterDirection.ReturnValue;
   
                   return returnParameter;
           }
   
           #region Web Form Designer generated code
           override protected void OnInit(EventArgs e)
           {
                   InitializeComponent();
                   base.OnInit(e);
           }
   
           private void InitializeComponent()
           {    
                   this.Load += new System.EventHandler(this.Page_Load);
           }
           #endregion Web Form Designer generated code
       }
   }

   By this way of working, you never will have an ugly error which will blow-up your ASP.NET/C# code. In this case, our application does not need try{}catch{} position when we execute Oracle SQL code from our application. Developers always will have a feedback from Oracle what exactly is wrong or Ok for this wonderful database machine. Also the missing try{}catch{} passage in our application will save a lot of processor resources and will make the source code more readable.

Conclusion

In this article, I try to explain a slightly different way of working and playing with Oracle transactions and catching the Oracle exception directly in our stored procedures and processing them without any risk of blowing-up the ASP.NET/C# applications which interact with data stored in Oracle. Many developers and many applications use transactions management from C# or VB code. Also they prefer to use try{}catch{} exception management in C# or VB code inside our applications. The approach which I try to show you is one better way of playing with transactions and holding exceptions when we use Oracle DB. This way ensures one more flexible, powerful and clear way of understanding the development and coding process. This process provides more flexible and powerful applications.