Hi, i will try to explain the case and question through my real time scenario. I have a Student table that is derived from a User table. Student is sub-type of User. StudentID in Student table is a part of the primary key or foreign key in many other tables. Between User and Student tables i have a cascade delete defined. But it doesnt go any further. If an entry in User table is deleted, the same id-ed student in Student table will also be deleted "IF" it is not used in other tables as a foreign or private key yet. And will give me a foreign key constraint error if it is used in other tables.

What i am trying to do is: call the delete statement on User table and if no error is returned let user know that student is deleted. If an error returns from database due to a foreign key constraint ask user to enter a leave date for the student and update students records with the newly entered leave date info instead of deleting him/her.

The question is: would it be possible to catch this error through llblgen and act accordingly? When i call student.Delete method an exception will occur. Is there a way to know that it is due to a foreign key constraint problem?

I am not sure if this is a full llblgen related question. Forgive me if it is not. thanks in advance

-shane

Hi shane,

I see these options:

A. There is not a common database exception construct unfortunately. So what would be ideal is that at the source of the problem, the generic query executing code, the exceptions already was wrapped in the correct exception (Query execution exception)

What you could do is to grab the inner exception, which is the database specific exception, and rethrow that. It will then end up in your specific catch clause for that particular exception (YourCustomException), in which you could check for the error number and throw a new exception upwards to the GUI which signals the gui what happened.

B. Validate before commit the DELETE. You could fetch the data before persist to see if everything is OK.

David, thank you for the options. would it be possible if you could give me an example for option A?

about option B; Student table is related to many tables and this number will go up. Should i check every single table Student table is related to before i call delete? or is there an easier way to it that will work even after the number of related tables increase?

thank you -shane

daelmo wrote:

Hi shane,

I see these options:

A. There is not a common database exception construct unfortunately. So what would be ideal is that at the source of the problem, the generic query executing code, the exceptions already was wrapped in the correct exception (Query execution exception)

What you could do is to grab the inner exception, which is the database specific exception, and rethrow that. It will then end up in your specific catch clause for that particular exception (YourCustomException), in which you could check for the error number and throw a new exception upwards to the GUI which signals the gui what happened.

B. Validate before commit the DELETE. You could fetch the data before persist to see if everything is OK.

David, thank you for the options. would it be possible if you could give me an example for option A?

The following is how I did it before, I used to depend on the database ErrorNumber to define the exception type:

            catch(ORMQueryExecutionException ex)
            {
                // 547 is the code for the database FK violation exception.
                if (ex.DbSpecificExceptionInfo[ExceptionInfoElement.ErrorNumber].ToString() == "547")
                {
                    // The entity is referenced by another entity, show a friendly message.
                    Message.Text = "Employee could not be deleted; some items are assigned to him/her.";
                }
            }

I think this is the easiest solution.