Best way to catch database constraint errors

Question

I call a stored procedure that inserts data into a sql server database from C #. I have a number of limitations in the table, such as a unique column, etc. I currently have the following code:

try { // inset data } catch (SqlException ex) { if (ex.Message.ToLower().Contains("duplicate key")) { if (ex.Message.ToLower().Contains("url")) { return 1; } if (ex.Message.ToLower().Contains("email")) { return 2; } } return 3; } 

Is it better to check if a column, etc. is unique, before inserting data into C # or into the storage procedure, or allow an exception and handle it as described above? I am not a fan of the above, but are looking for excellence in this area.

Answer 1

I see database limitations as a last resort. (Ie, of course, they should be present in your schema as a backup way to maintain data integrity.) But I would say that the data must really be valid before you try to store it in the database. If for any other reason, since providing feedback on incorrect input is a UI problem, and the data validation error really should not bubble up and down the entire tier of the level every time.

In addition, there are many statements that you want to make about the form of your data, which cannot be easily expressed using restrictions. (For example, state transitions of order. "Order can only be SHIPPED from PAID " or more complex scenarios.) That is, you will need to use procedures based on a procedural language that duplicate even more of your business logic, and then they also report some kind of error code and include even greater complexity in your application just for the sake of performing all your data validation in the schema definition.

Validation is inherently difficult to place in an application, since it concerns both the user interface and is associated with the model scheme, but I turn to the side that does this next to the user interface.

Answer 2

Here I see two questions, and here is my take ...

Are database restrictions good? For large systems, they are indispensable. Most large systems have more than one interface, and are not always compatible languages ​​where mid-level data logic or UI can be shared. They can also have batch processes only in Transact-SQL or PL / SQL. This is good to duplicate front-side validation, but in a multi-user application, the only way to really verify uniqueness is to insert a record and see what the database says. The same with foreign key constraints - you really don't know until you try to insert / update / delete.

If exceptions are allowed to throw or need to return values? Here's the code from the question:

  try { // inset data } catch (SqlException ex) { if (ex.Message.ToLower().Contains("duplicate key")) { if (ex.Message.ToLower().Contains("url")) { return 1; // Sure, that one good way to do it } if (ex.Message.ToLower().Contains("email")) { return 2; // Sure, that one good way to do it } } return 3; // EVIL! Or at least quasi-evil :) } 

If you can guarantee that the calling program will actually act on the basis of the return value, I think that return 1 and return 2 best left to your discretion. I prefer to rebuild a custom exception for such cases (like DuplicateEmailException ), but only me - return values ​​will do the trick too. After all, consumer classes can ignore exceptions as easily as they can ignore return values.

I am against return 3 . This means an unexpected exception (database down, bad connection, whatever). Here you have a vague error, and the only diagnostic information you have is: "3". Imagine a SO question that says I tried to insert a line, but the system said "3". Please advise. It will be closed in a few seconds :)

If you do not know how to handle an exception in a data class, there is no way that the consumer of the data class can handle it. At the moment, you have a lot of moves, so I say, I register an error, and then I exit as gracefully as possible with the message "Unexpected error".

I know a little to tell about the unexpected exception, but I handled too many support incidents, when the programmer just looked for database exceptions, and when something unexpected appeared, the application either failed or failed down, leaving zero diagnostic information. Very naughty .

Answer 3

I would prefer to store a stored procedure that checks for possible violations before just throwing data into SQL Server and letting this restriction throw an error. The reasons for this are related to performance:

http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling

http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/

Some people will argue that database-level constraints are not needed because your program can do anything. The reason I will not rely solely on your C # program to detect duplicates is because people will find ways to influence the data without going through your C # program. You can enter other programs later. Perhaps you have people who write their own scripts or interact directly with the database. Do you really want to leave the table unprotected because they do not follow your business rules? And I don’t think the C # program should just throw data at the table and hope for the best.

If your business rules change, do you really want to recompile your application (or all several applications)? I think it depends on how well your database is protected and how likely / often your business rules change.

Answer 4

I did something like this:

 public class SqlExceptionHelper { public SqlExceptionHelper(SqlException sqlException) { // Do Nothing. } public static string GetSqlDescription(SqlException sqlException) { switch (sqlException.Number) { case 21: return "Fatal Error Occurred: Error Code 21."; case 53: return "Error in Establishing a Database Connection: 53."; default return ("Unexpected Error: " + sqlException.Message.ToString()); } } } 

This allows you to reuse it and it will allow you to get error codes from SQL.

Then just do:

 public class SiteHandler : ISiteHandler { public string InsertDataToDatabase(Handler siteInfo) { try { // Open Database Connection, Run Commands, Some additional Checks. } catch(SqlException exception) { SqlExceptionHelper errorCompare = new SqlExceptionHelper(exception); return errorCompare.ToString(); } } } 

It then provides some specific errors for ordinary cases. But, as mentioned above; you really need to make sure that you have tested your data before entering it into your database. Therefore, there are no inappropriate restrictions or exists.

Hope he points you in a good direction.

Answer 5

Depending on what you are trying to do. Some things to think about:

• Where do you want to handle your error? I would recommend as close to the data as possible.
• Who do you want to know about the error? Your user should know that "you have already used this identifier" ...?
• and etc.

In addition, the limitations can be good - I do not agree 100% with the millionaire answer on this question - I mean that I do it should be / better, ideal - but really, if you do not have control over your developers / qc, and especially when it comes to complying with rules that may lead to the deletion of your database (or, otherwise, to objects that depend on a break, such as reports, etc., if a duplicate key should appear somewhere , you need some barrier against (for example) duplication of key input.