 |
Database »
Database »
General
Intermediate
License: The Code Project Open License (CPOL)
Processing with C#, Oracle transactions and Oracle exceptionsBy Dimitar MadjarovAn article which describes an alternative way of cooperation between Oracle PL/SQL and C# |
SQL, C# 1.0, C# 2.0, C# 3.0, VB 7.x, VB 8.0, VB 9.0.NET 1.0, .NET 1.1, .NET 2.0, Win2K, WinXP, Win2003, Vista, .NET 3.0, ASP.NET, SQL Server, WebForms, VS.NET2003, VS2005, DBA, Dev
|
||||||||||
|
Advanced Search Add to IE Search |
|
|
|
||||||||||||||||
Title: Processing with C#, Oracle transactions and Oracle exceptions Author: Dimitar Nikolaev Madjarov Email: madjarov_d_n@yahoo.com Environment: MS Visual Studio 2003, 2005, ASP.NET, C# and Oracle DB Keywords: Oracle, Transaction, Exceptions, C# Level: Intermediate 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
The main purpose of this article is to show one different way of cooperation between Oracle and C# in process of developing different Oracle database application with Microsoft Dot.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 everyone Dot.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.
I. 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) /
II. Secondly we have to create an Oracle package with one store procedure which will try to insert a row with simple data into our previously created table. Please here 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 depend from that is the "PL/SQL Insert code" generate 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 correct transaction state and never allow one not processing error to blow-up our database application. If any error appear in our "Insert procedure" we have to set 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 done 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 store procedure. This means that for our C# application this store procedure never will return an exception. Also in the body of Oracle store procedure above me always use "Commit" or "Rollback" transaction state in correct order and my data are always clear.
III. Thirdly and the last step we have to take care always for these three "out" parameters which Oracle store procedure return 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 one option is very comfortably because the Oracle monitoring tool which provide 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 how you may works with these parameters.
//
// This is a simple Web application based over Dot.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 commited 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 to blow-up your ASP.NET/C# code. In this case our application have not needs from try{}catch{} position when we execute Oracle SQL code from ours application. Developers always will have a feedback from Oracle what exactly is wrong or Ok for this wonderful database machine. Also the missing of try{}catch{} passage in our application will save a lot of processor resources and will make the source code more readable.
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. 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 hold of the exceptions when we use Oracle DB. This way ensure one more flexible, powerful and clear for understanding development and coding process. This process provides more flexible and powerful applications.
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 19 Mar 2007 Editor: |
Copyright 2007 by Dimitar Madjarov Everything else Copyright © CodeProject, 1999-2009 Web21 | Advertise on the Code Project |
