How can I handle oracle exceptions using Enterprise Library in C#?

Question

I'm building an ASP.NET web application using C#, Enterprise Library and Oracle for a database. This is the first time I'm using Oracle as the database and so far my experience hasn't been all that great (I prefer using MS SQL Server). So here's the scenario:

The users have the ability to enter and change the value of a unique field in the database (e.g.- username). If the user enters a value (in this context, a username) which has been already entered as a different record then the requirement is to the inform the user as such. Since the inserting and/or updating is done via stored procedure I decided to handle the exception within the store procedure. I did so by declaring an out parameter called erCode of type NUMBER and added the following the block to the stored procedure after the SQL statement.

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        erCode := SQLCODE

In the event the INSERT/UPDATE operation was successful I return erCode with value 0.

Naturally since there was a return value an exception was never caught in the try-catch block of the code in the data access layer (DAL) of the application. Therefore I returned the value of erCode from DAL to the business logic layer (BLL) and then to the UI layer where I handled it inside the try block by:

if (errorCode = -1)
{
    lblMsg.Text = "Username already exists";
}
else
{
    lblMsg.Text = "Username changed successfully";
}

I realise this is a horrible way to do this even if the oracle error code is a value apart from "-1", then the Username changed successfully will be shown which would be completely misleading.

I'm not supposed to use the System.Data.OracleClient.OracleException class since its not looked upon favourably when we use that class in adjacent with Enterprise Library at my work place. If we are using Enterprise Library, then we should use that alone for all database related functions.

This bring me to my question; how can I handle such a scenario? Is it possible to do it using Enterprise Library alone?

SHAKIR SHABBIR SHAKIR SHABBIR · Accepted Answer · 2012-07-06T21:10:33

If you need to take the code as what you are doing and still be on the safe side, then you can do like this:

When no error is encountered, then if I assume that ErrorCode returned will be a null value, you may do this:
```
if(erCode==null)
errorCode = 1;
```
Setting ErrorCode =1 will certainly tell that operation was success.

Modify your code as:

if (errorCode = -1)
{
    lblMsg.Text = "Username already exists";
}
else if(errorCode = 1)
{
    lblMsg.Text = "Username changed successfully";
}
else{
    lblMsg.Text = "Unknown error while updating!";
}

So if there will be any other error code, relevant message is shown!

Hope you find it worthy!

How can I handle oracle exceptions using Enterprise Library in C#?

1 Answers