5

I have a Users table, which has a Unique constraint on the username (for obvious reasons).

I am using an EF 4.0 DAL to populate the database, and in the process of coding the CreateUser() method.

Is it...

  1. Best to catch the SqlException thrown if I try to insert a username which already exist.
  2. Explicitly check for the username before I try to insert it to the database?

If you could also give reasons as to why, that would be great!

Darbio
  • 10,681
  • 12
  • 55
  • 98

4 Answers4

3

I would check if the record exists first. Unique key constraints are useful to guard against possible ways your application is allowing "bad" data through in the first place, but not the main stop for that. It's generally a bad idea to use exceptions as a control flow mechanism (validation in this case) when it's possible to avoid.

EDIT: To avoid confusion, I'm not saying don't have the unique index at all. It should be there, but it shouldn't be the primary means of checking for uniqueness.

vcsjones
  • 128,004
  • 28
  • 283
  • 274
  • Thanks vcsjones. Do you have any links as to why is is a bad idea to use exceptions as a control flow mechanism? – Darbio Nov 05 '10 at 03:13
  • Sure, I'll refer you to another SO discussion: http://stackoverflow.com/questions/1336094/using-try-catch-for-flow-control-net I particularly like this phrase "Exceptions are for exceptional cases, not for normal flow" In many cases, catching an exception is never as clear as what the intention is. What if the exception is thrown for a different reason? Will you check the exception and the message every time? What if Microsoft changes the exception message in a later version of SQL? – vcsjones Nov 05 '10 at 03:19
  • One more good discussion on it for good measure: http://stackoverflow.com/questions/729379/why-not-use-exceptions-as-regular-flow-of-control – vcsjones Nov 05 '10 at 03:25
  • Yes but every database I have ever used throws an exception when the user tries to insert a duplicate on a unique index. So basically you are going through hoops to avoid the (most likely well thought out) wishes of the makers of the database. – Samuel Nov 05 '10 at 12:26
2

I would say it is best to handle the exception. The database is designed to handle the uniqueness of the user name, so I imagine it can do it more efficiently than you can. Also it adds portability and cohesion to your system. If you add users in more than one place you will have to duplicate the username checking or create a method and basically you will end up rewriting what the database engine has already written.

Samuel
  • 15,583
  • 5
  • 54
  • 70
  • Good point re: rewriting functionality, however this is mitigated by the DAL which means that all user actions go through this. – Darbio Nov 05 '10 at 03:12
  • 1
    A well designed architecture wouldn't require duplicate checks. Just put the logic in a class of it's own, and use the class when you need to. It's that, or, catch the exception every time. Ideally there is only a single CreateUser within the application. – vcsjones Nov 05 '10 at 03:22
  • Correct - one CreateUser in the DAL which is used by the webservices which need it. – Darbio Nov 05 '10 at 03:36
1

Additionally to what Samuel said, you'd need to make sure that nobody enters a record that could conflict with yours between your check and adding the record to the database. You could achieve this with a lock, but then you've got to catch exceptions caused by the lock.

As for duplicating stuff in the business rules and the database, I'm in favour of the database habing as much consitency checking in place as is required, even if this does duplicate some stuff in the business layer. The more tightly locked your database is against invalid data the better. It protects you against access to your database via other tools than your app, such as a support guy making changes in the database using SSMS to correct a data problem reported by a user.

Kevin O'Donovan
  • 1,494
  • 1
  • 11
  • 21
0

I second what samuel said. The most efficient way is to leave it to database. All other options are more time and resource consuming....

Tejas
  • 272
  • 2
  • 7
  • 15