C# Discussion Boards - CodeProject

10-Jul-13 16:07

What's the best way to notify the front end of a business rule or possible FK violation? For example, I have a lookup called PayType, and so far my DeletePayType looks like this:

public static void DeletePayType(int PayTypeId)
{
    using (var dc = getDataContext())
    {

        bool inUse = isPayTypeInUse(PayTypeId);

        if (inUse)
        {
            //TODO: Handle this differently
            throw new Exception("Pay Type is in use and cannot be deleted");
        }
        else
        {
            var payType = (from ca in dc.PayTypes
                            where ca.PayTypeId == PayTypeId
                            select ca).FirstOrDefault();

            if (payType != null)
            {
                dc.PayTypes.DeleteOnSubmit(payType);

                try
                {
                    dc.SubmitChanges();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
    }
}

Throwing an exception doesn't seem right, but I need the client to know that the pay type is in use and can't be deleted.

Thanks

If it's not broken, fix it until it is

Mycroft Holmes10-Jul-13 17:16

10-Jul-13 17:16

Yup programming by error - try and delete the record and then inform the user when it fails - start looking for some other employment.

Here is one reason I ALWAYS use a stored proc, the delete procedure check the FK table and only deletes the record if there is no constraint.

Never underestimate the power of human stupidity
RAH

Kevin Marois10-Jul-13 17:31

10-Jul-13 17:31

Mycroft Holmes wrote:
Yup programming by error - try and delete the record and then inform the user
when it fails - start looking for some other employment.

I guess I don't get what you mean.

Deleting the record is possible if it's not in use. This isn't programming by error, it's making sure that a FK violation won't occur.

Mycroft Holmes wrote:
Here is one reason I ALWAYS use a stored proc, the delete procedure check the FK
table and only deletes the record if there is no constraint.

Using a sproc won't solve the issue - just move the code that deletes it.

Ant any rate, the user needs to be notified if they cannot delete the record. This is a perfectly normal business rule.

If it's not broken, fix it until it is

Mycroft Holmes10-Jul-13 18:22

10-Jul-13 18:22

Ok a little more detail is required. you have 2 options with this scenario.

You can try and delete the record and hit the constraint and then deal with it by informing the user. This imposes an error on the DB.
You can also query the DB to see if the record can be deleted using an if EXIST query on the related table(s).

The first method is programming by error and is generally used by lazy sods who have no craftsmanship.

The second requires 2 or more round trips to the database and IMHO is a waste of effort. I write a delete procedure that does the checking before it deletes (or deletes the FK children BEFORE it tries to delete the master record). The proc returns the number of master records deleted (1) or failed (0) or if it is not deleting the children and one exists then it returns (-1). This moves the decision into the database and requires one 1 round trip and supplies the feedback to the user if there is an active constraint.

Never underestimate the power of human stupidity
RAH

Kevin Marois10-Jul-13 18:58

10-Jul-13 18:58

You're still missing what I'm aiming for...

I want to find out IF the PayType record can be deleted. If its PK is in use in another table, then I don't want to try to delete it. I DO however want to tell the user it's in use.

If it's not broken, fix it until it is

Mycroft Holmes10-Jul-13 19:34

10-Jul-13 19:34

So how are going to find out if the PayType records is in use by another table(s)? Query the related tables and see if the ID is in use!

You could write a generic procedure that queries sysobjects to identify the PKs and their related fields and then query the tables and return a boolean if you can delete the record. A matter of fact that sounds like an excellent widget to build, why is there not one of those?

Never underestimate the power of human stupidity
RAH

Kevin Marois10-Jul-13 19:57

10-Jul-13 19:57

All that's fine, and is what I am already doing... querying the tables that use the PK.

My original question still is "What's the best way to notify the front end of a business rule or possible FK violation?"

Raise an exception? Return a string?

How does the UI find out that the backend can't delete the row?

If it's not broken, fix it until it is

Freak3010-Jul-13 21:23

Freak30

10-Jul-13 21:23

I would create a special exception type, which is only used for potential business rule violations the user needs to be informed of. Then you can catch that exception at the appropriate location (where the user started the invalid action) and inform the user using a message box or email or whatever.

The good thing about pessimism is, that you are always either right or pleasently surprised.

jschell11-Jul-13 8:47

jschell

11-Jul-13 8:47

Mycroft Holmes wrote:
Here is one reason I ALWAYS use a stored proc, the delete procedure check the FK

Then you must work in an unusual environment.

Normally the applications that are in use (the code external to the database) should present the requests that even allow that situation.

Thus checking the FK first every time is a waste of bandwidth and adds nothing but complexity.
And allowing the exception in such cases is quite appropriate.

Mycroft Holmes wrote:
and only deletes the record if there is no constraint.

But presumably it does in fact still throw an error. If the caller is attempting to delete something and it does not in fact get deleted but still exists then the caller must be told that it did not get deleted. It cannot appear as though it did.

Mycroft Holmes11-Jul-13 12:50

11-Jul-13 12:50

Actually I do manage this in the VM layer but on the rare occasions where I need to delete a master record there is a stored proc and it never relies on the exception, just the way I build it I guess. Seems many are comfortable with the exception being thrown!

Never underestimate the power of human stupidity
RAH

Keith Barrow10-Jul-13 22:38

Keith Barrow

10-Jul-13 22:38

A couple of options spring to mind immediately.

First, you can create a custom exception type (e.g. PayTypeInUseAndCannotBeDeletedException) and throw that, then catch this type in the UI. This has the advantage of not changing the method signature, but if feels like exception as business logic.
Personally I'd return a status on the method. Probably you can get away with a boolean pass/fail as you only have one failure mode.

public static bool DeletePayType(int PayTypeId)
{
    using (var dc = getDataContext())
    {

        bool inUse = isPayTypeInUse(PayTypeId);

        if (inUse)
        {
            return false;
        }
        else
        {
            var payType = (from ca in dc.PayTypes
                            where ca.PayTypeId == PayTypeId
                            select ca).FirstOrDefault();

            if (payType != null)
            {
                dc.PayTypes.DeleteOnSubmit(payType);

                try
                {
                    dc.SubmitChanges();
                    return true;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
    }
}

I'd also get rid of the try/catch around the submit. It isn't achieving anything other than removing potentially useful stack trace (See here[^] if you don't know what I mean)), unless this was intended of course.

PB 369,783 wrote:
I just find him very unlikeable, and I think the way he looks like a prettier version of his Mum is very disturbing.[^]

Pete O'Hanlon10-Jul-13 23:51

Pete O'Hanlon

10-Jul-13 23:51

In general, it's a bad idea in a database system to test for the existence of a value and then perform an action based on it as a separate operation. This is because you are working in a multi-user environment where you could have a race condition. So, assuming that you don't want to rely on the database throwing an exception and you having to deal with it, it's best to put the test as part of your delete statement. Then, test to see if there were any rows affected or not - this can then be returned up the chain to indicate that the key was in use at this point.

I'm not a big fan of exceptions to control business logic - they should really just indicate that something was wrong, so I'd just pass the result back up the chain.

I was brought up to respect my elders. I don't respect many people nowadays.

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Keith Barrow11-Jul-13 1:45

Keith Barrow

11-Jul-13 1:45

Pete O'Hanlon wrote:
I'm not a big fan of exceptions to control business logic

You'd love our code base. And the several hundred custom exceptions to handle such exceptional things as a user not having set up a payment method yet. Dead | X|

PB 369,783 wrote:
I just find him very unlikeable, and I think the way he looks like a prettier version of his Mum is very disturbing.[^]

Shameel11-Jul-13 1:06

Shameel

11-Jul-13 1:06

I'd start by creating a DeletePayTypeResult class and having a flag that indicates if the Pay Type was deleted and a string field containing the reason for not being able to delete. And then I would return this type from the method.

class DeletePayTypeResult {
    public bool Deleted { get; set; }
    public string ReasonNotDeleted { get; set; }
}

public static DeletePayTypeResult DeletePayType(int PayTypeId)
{
}