Grab SQL Server error with Exception Handling Application block

Topics: Exception Handling Application Block
Jul 24, 2007 at 8:43 AM
Hi All

Is it possible to grab the sql servers errors, bind them into the policy and present the error in our own ways?

For exmple, I want to trap duplicate key error while inserting , delete not allowed while it delete the detail tables first?

Could I do it through Exception Handling Application block?

Jul 24, 2007 at 1:25 PM
Hi Alex,

It's somewhat hard to understand your goal. With the EHAB you basically externalize how you handle the managed exceptions in your applications, but they are still exceptions in your application and you need to catch them and forward them to the block. The exceptions can be any kind of exception, including the SqlExceptions that would represent problems with database interaction through the ADO.NET API.

Please clarify what you mean by "grabbing the sql server errors" (we are talking about exceptions, aren't we), "bind them into the policy" and "present the error" (is this a UI issue?).

The EHAB topic on the integrated help describes the goals and main scenarios for the block. You can find useful information there.

Jul 24, 2007 at 3:55 PM
Thanks Fernando

My goal is simple, just want to trap the duplicate key error if user input duplicate record.

What assembly should I refer to which I choose the exception type?

Jul 24, 2007 at 4:47 PM
Jul 25, 2007 at 2:17 AM
Could I use it to trap the duplicate key error?

For example, if duplicate record found while insert the record, show the error message like "You have inserted duplicate record?"

And also,
if we want to delete a master records which contains child records, it also show a message like "This record is not allowed to be deleted since it contains the child data".

Jul 25, 2007 at 1:59 PM

The block will not trap exceptions for you. You need to catch them and forward them to the block, and based on its configuration the block will perform some handling actions.

As long as you get exceptions for the duplicate key and the parent/child constraints (and you catch them) you'll be able to handle them. However it's likely there isn't a specific exception for each problem and a more general exception with different data is thrown instead, so you may need some processing for dispatching the policies that is not currently available in the EHAB.

Why do you want to use the block instead of plain old exception handling? It seems you just want to show a message every time, and using the EHAB for this is overkill. Anyway, you can look at the EHAB quickstart where they have a custom handler that shows the error in a dialog box.

Jul 26, 2007 at 2:32 AM
Hello Frenando

It is because I want to setup a generic exception approach and re-throw an error message for the applications.

For example:-

If duplicate key is found in customer table, the error message should be "You couldn't insert this customer since this customer exists".

If duplicate key is found in supplier table, the error message should be "You couldn't insert this supplier since this supplier exists".

BTW, I got the idea on the approach and setup a generic error handling approach. Therefore, I could centralize to control the sql exceptions.

I will create a customer exception handler which trap and wrap the normal sql exception.

With this handler, it will check the sql error code, rephrase the error message and pass it back to the the program..

After that, the program could present this customerize error message.

However, there are two limitations :-

1. Only support SQL server database.
2. Need to use Session to store the error message since the normal exception class error message property is readonly.

And do you have other better suggestion?
If I try use plain old exception handling? Could I implement in this way?

Really Thanks for your help.
Jul 26, 2007 at 1:08 PM
Hi Alex,

You're not supposed to change the exceptions you handle, but you can create your own exceptions wrapping the original db exception. Look at the wrapping handler for an example. That should take care of your limitation #2.

As for #1, I'm not aware of a generic way to determine these fine grained errors. You can ask about this on the forums.

Jul 30, 2007 at 3:44 AM
Hi Fernando

I finally create a new exception handler and use the Data() property to store the error message.

The error message will be generated under this handler and pass back to the application.

Once the system notify it is a sql error, it will retrieve the error message from ex.Data("Error Message") property.

Jul 30, 2007 at 12:53 PM

You should really be using the InnerException property, set at construction time. Please see the WrapExceptionHandler class for an example (although this class uses reflection, and you probably won't).

Aug 1, 2007 at 1:43 PM
Hi Alex,

You might find this post from Tom helpful He implemented something similar to what we were discussing here.