Click here to Skip to main content
Click here to Skip to main content

Processing with C#, Oracle Transactions and Oracle Exceptions

, 19 Mar 2007
Rate this:
Please Sign up or sign in to vote.
An article which describes an alternative way of cooperation between Oracle PL/SQL and C#

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.
    /* 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.
     /* 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.
    //
    // 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Dimitar Madjarov
Software Developer (Senior) http://www.uplandsoftware.com/
Canada Canada
Canada, Quebec, Laval,
Currnet position: Senior ASP.NET Developer
Company: Uplandsoftware Inc
Certificates: MCITP, MCP, MCTS

Comments and Discussions

 
GeneralFlexible Datatype PinmemberAjay Kale New11-Nov-10 0:34 
GeneralA simpler way to use Oracle from C#... http://splinter.com.au/blog/?p=17 Pinmemberchrishulbert15-Jun-08 19:06 
GeneralRe: A simpler way to use Oracle from C#... http://splinter.com.au/blog/?p=17 PinmemberDimitar Madjarov15-Jun-08 23:53 
GeneralRe: A simpler way to use Oracle from C#... http://splinter.com.au/blog/?p=17 PinmemberPaul Groetzner16-Jun-08 16:45 
RantRe: A simpler way to use Oracle from C#... http://splinter.com.au/blog/?p=17 [modified] PinmemberPaul Groetzner17-Jun-08 2:44 
GeneralHello:) PinmemberVirglio21-Apr-08 3:57 
GeneralTransactions [modified] PinmemberKrzemo26-Mar-07 20:35 
GeneralRe: Transactions PinmemberDimitar Madjarov27-Mar-07 20:45 
GeneralI have to disagree Pinmembermr_lasseter19-Mar-07 2:39 
GeneralRe: I have to disagree PinmemberDimitar Madjarov19-Mar-07 3:54 
GeneralRe: I have to disagree Pinmembermr_lasseter19-Mar-07 6:03 
GeneralRe: I have to disagree [modified] PinmemberDimitar Madjarov19-Mar-07 10:38 
GeneralControlling when to commit or rollback PinmemberPatrice Borne19-Mar-07 15:10 
GeneralRe: Controlling when to commit or rollback PinmemberDimitar Madjarov19-Mar-07 21:48 
AnswerLet's agree to disagree PinmemberPatrice Borne22-Mar-07 13:05 
GeneralRe: Let's agree to disagree PinmemberDimitar Madjarov22-Mar-07 21:04 
GeneralDiscussing the *substantially* more functional Oracle Data Provider would've been helpful... Pinmembersherifffruitfly13-Mar-07 14:11 
GeneralRe: Discussing the *substantially* more functional Oracle Data Provider would've been helpful... PinmemberDimitar Madjarov14-Mar-07 2:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 19 Mar 2007
Article Copyright 2007 by Dimitar Madjarov
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid