In LLBLGen, violating a unique constraint throws a Query Exectution Exception.

As there could be multiple unique constraints on a table, how do you guys / girls tell the users in a friendly way that they have violated a unique constraint?

I trap for a QueryExecution Exception, then parse the string of the message to check and see if there is a phrase "Violation of UNIQUE KEY constraint" to determine if it is a UC violation, then I look for the name of the UC that was violated and return the proper message.

Does anyone have a more elegant way to do this?

I feel your pain. 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.

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, in which you could check for the error number and throw a new exception upwards to the gui which signals the gui what happened.

Thanks for the tip. I think that even looking at the SQL Error number wont help much, because if there were 2 unique constraints on a table, they would both return the same error number.

Then again, I suppose that one could argue that a table needing more than 1 unique constraint could be further normalized

Just thought I'd bring this topic back to see if anyone out there has a good way to deal with this problem. I'm one days worth of research into settling for text parsing to create template-style error messages to the user...like 'The [Employee] you are trying to delete is connected to an existing [Office]. You cannot delete this [Employee] until this link is removed.

or something like that...

Devildog74 wrote:

As there could be multiple unique constraints on a table, how do you guys / girls tell the users in a friendly way that they have violated a unique constraint?

You shouldn't be relying on exception handling to deal with this issue. You should query the db in the transaction before saving to see if the value is duplicated. This should be part of your entity validation.

Yes... it does add some traffic to the situation, but the payoff in usablity is greater than the loss of an additional request.

BOb

pilotboba wrote:

Devildog74 wrote:

As there could be multiple unique constraints on a table, how do you guys / girls tell the users in a friendly way that they have violated a unique constraint?

You shouldn't be relying on exception handling to deal with this issue. You should query the db in the transaction before saving to see if the value is duplicated. This should be part of your entity validation.

Yes... it does add some traffic to the situation, but the payoff in usablity is greater than the loss of an additional request.

BOb

The only problem I have with this approach is that the database data isn't guaranteed to be the same as the entity data in the split second between validation and UPDATE/INSERT/DELETE.

How about dealing with foreign key constraints? It doesn't seem like a good idea to query the database for dependent rows during validation (just because of the maintenance of the list of related tables).

Weel my approach is exactly the same as yours, I completely depend on the exceptions, and parse them as you do. I think this is the most effecient way.

I don't like to do the extra queries to the database for many reasons.

mikeg22 wrote:

The only problem I have with this approach is that the database data isn't guaranteed to be the same as the entity data in the split second between validation and UPDATE/INSERT/DELETE.

Yes that is possible. How likely is that in your environment?

I guess you could parse out the error message looking for the Index name or FK name and send the approriate message based on that.

But, I still do the check up front also. If the value already existis when the user enters that value better they know right away rather than waiting for after they press SAVE.

mikeg22 wrote:

How about dealing with foreign key constraints? It doesn't seem like a good idea to query the database for dependent rows during validation (just because of the maintenance of the list of related tables).

I still recommend following the same pattern. This is once again a business rule. Enforcing these interentity relations in the db is just a saftey net. Not something to rely on.

Once again, you can parse the FK name out of the error message in order to send the correct message to the UI tier. You should even use the FK name or Indexname as a key to a resource item.

The question I would ask is, if you check up front and then do your update/insert/delete there is a very small chance that in the few milliseconds between the check and the data change that the prechecked state would change.

If after doing this you are getting UC or FK violations exceptions at a high rate you can deal with it then. But, those db constraints (saftey net) will keep your data intact.

BTW: This is one of the few advantages you get by using stored procs.

BOb

mikeg22 wrote:

The only problem I have with this approach is that the database data isn't guaranteed to be the same as the entity data in the split second between validation and UPDATE/INSERT/DELETE.

Of course to mitigae this you could use serializable isolation. If you do this after you start your transaction once you do your

SELECT count(*) FROM MyTable WHERE MyField = @Value

No other transaction will be able to insert @Value until you commit or role back.

I tested this and it is the case. However, the caveat is that two transaction can do the select count at the same 'exact' time with the same value. What will happen is that one of the transactions will be killed as a deadlock. At least this is a better situation. In this case you can retry you logic and you will now get a count of 1 and will know that you have a duplicate.

BOb

pilotboba wrote:

Yes that is possible. How likely is that in your environment?

I guess you could parse out the error message looking for the Index name or FK name and send the approriate message based on that.

But, I still do the check up front also. If the value already existis when the user enters that value better they know right away rather than waiting for after they press SAVE.

I'll have to think about this. It sounds like a good idea except that it may need to re-run the duplicate key check on every validate, as it could be a long time before the user hits the SAVE button, and other users could easily have slipped in a new record behind the scenes.

pilotboba wrote:

I still recommend following the same pattern. This is once again a business rule. Enforcing these interentity relations in the db is just a saftey net. Not something to rely on.

Unfortunately, our group consists of some db guys who sometimes like to go behind the scenes and alter data directly in the table, causing us to need business rules in the database itself

Putting the logic in the code would be duplicating the business rules, which we try to avoid at all costs (as changing the rules cannot just be done in one place which can lead to maintenance issues).

pilotboba wrote:

Once again, you can parse the FK name out of the error message in order to send the correct message to the UI tier. You should even use the FK name or Indexname as a key to a resource item.

The question I would ask is, if you check up front and then do your update/insert/delete there is a very small chance that in the few milliseconds between the check and the data change that the prechecked state would change.

If after doing this you are getting UC or FK violations exceptions at a high rate you can deal with it then. But, those db constraints (saftey net) will keep your data intact.

BTW: This is one of the few advantages you get by using stored procs.

BOb

I agree that there is a very small chance that something could slip in in that fraction of a second, but its possibility would just be one more thing to remember when trying to debug our app in case of failure. I'm not saying that this should mean we shouldn't do it, just that I'd really like to figure out a better way

pilotboba wrote:

mikeg22 wrote:

The only problem I have with this approach is that the database data isn't guaranteed to be the same as the entity data in the split second between validation and UPDATE/INSERT/DELETE.

Of course to mitigae this you could use serializable isolation. If you do this after you start your transaction once you do your

SELECT count(*) FROM MyTable WHERE MyField = @Value

No other transaction will be able to insert @Value until you commit or role back.

I tested this and it is the case. However, the caveat is that two transaction can do the select count at the same 'exact' time with the same value. What will happen is that one of the transactions will be killed as a deadlock. At least this is a better situation. In this case you can retry you logic and you will now get a count of 1 and will know that you have a duplicate.

BOb

I had no idea this was possible. My only remaining problem with this solution is the maintenance of business rules in two places (code and db). As I mentioned in a previous post, the rules have to be in the db because our app is not the only entry point to the data.