Handling unique values when updating ERLB

Rsmith posted on Tuesday, November 17, 2009

I am using an ERLB to manage a list of categories in my application.

The objects are quite simple; there is an ER class with two properties - an integer ID representing an identity column in a database table and a string Description. There is also an ERLB to allow the user to maintain the ER's using a DGV.

This is working fine except that I want to make the descriptions unique. How should I handle the exception thrown when saving the ER in the case that an item with the same description has already been entered?

The application is multiuser so the possibility exists however unlikely that another user could simultaneously add an item with the same description.

If anyone can suggest a better method for maintaining a list of categories I am open to feedback.

JoeFallon1 replied on Tuesday, November 17, 2009

You could try adding a validation rule that does a database llokup of the description. If it passes then you could try to save the BO. Note that I said try! Another user could still slip in that same description in the milliseconds between when you check the DB for it and when you save your BO. There is no way to avoid this. So you need to trap the exception in any case and tell your user to try a different description.

 

Rsmith replied on Tuesday, November 17, 2009

Thanks for the suggestion.

It's occurred to me that i'm over complicating this though and I can get what I need by using a dataset with the DGV.

rsbaker0 replied on Wednesday, November 18, 2009

JoeFallon1:

You could try adding a validation rule that does a database llokup of the description. If it passes then you could try to save the BO. ...

 

I use this technique throughout the business layer and it seems to work quite well. The database hit is only incurred when the property is changed, and the message I can display is nicer than "unique constraint violated" or whatever the backend database will kick out. You also get the error indicator on the screen or in the grid, which tells the user which field has been duplicated.

As you noted, it doesn't cover the case when another user change collides with the current user's, but those are rare and handled with a centralized exception strategy.