Is row checking affecting the number after the database completes (insert, update, delete)?

Question

Recently, in the applications I developed, I checked the number of rows affected by inserting, updating, deleting the database and logging the error if the number is unexpected. For example, if you simply insert, update, or delete one row, if any number of rows other than one is returned from the call to ExecuteNonQuery (), I will read the error and log it. In addition, I understand that when I print this, I don’t even try to cancel the transaction if this happens, which is not the best practice and should definitely be considered. Anyway, here is the code to illustrate what I mean:

I will have a data layer function that calls a db call:

public static int DLInsert(Person person) { Database db = DatabaseFactory.CreateDatabase("dbConnString"); using (DbCommand dbCommand = db.GetStoredProcCommand("dbo.Insert_Person")) { db.AddInParameter(dbCommand, "@FirstName", DbType.Byte, person.FirstName); db.AddInParameter(dbCommand, "@LastName", DbType.String, person.LastName); db.AddInParameter(dbCommand, "@Address", DbType.Boolean, person.Address); return db.ExecuteNonQuery(dbCommand); } } 

Then the business layer calls the data layer function:

 public static bool BLInsert(Person person) { if (DLInsert(campusRating) != 1) { // log exception return false; } return true; } 

And in the code behind or in the view (I do both web forms and mvc projects):

 if (BLInsert(person)) { // carry on as normal with whatever other code after successful insert } else { // throw an exception that directs the user to one of my custom error pages } 

The more I use this type of code, the more I feel like it's overkill. Especially in the encoding / representation. Is there a legitimate reason to think that simply inserting, updating, or deleting won't actually change the correct number of rows in the database? Is it more plausible to just worry about catching the actual SqlException and then handling it instead of doing a monotonous check for the rows affected each time?

Thanks. I hope you all can help me.

---

UPDATE

Thanks to everyone for taking the time to respond. I still have not 100% decided which setting I will use in the future, but here's what I removed from all your answers.

• Trust the database and .Net libraries to process the request and do your work as they are created.
• Use transactions in my stored procedures to roll back the request for any errors, and potentially use raiseerror to throw these exceptions back into the .Net code as a SqlException that could handle these errors with try / catch. This approach will replace the problem return code verification.

Will there be a problem with the second number of the marker that I am missing?

Answer 1

I think the question will be: "Why are you checking this?" If this is simply because you do not trust the database to execute the query, then this is probably overkill. However, there may be a logical reason for this check.

For example, I worked for the company once when this method was used to check concurrency errors. When a record has been taken from the database for editing in the application, it will have a LastModified . Then standard CRUD operations at the data access level will include a WHERE LastMotified=@LastModified when performing UPDATE and checking the registered number of records. If the record has not been updated, it is assumed that a concurrency error has occurred.

I felt that it was careless for checking concurrency (especially the part about the alleged nature of the error), but it got a job for the business.

For me more in your example, this is the structure of how this is done. 1 or 0 returned from the data access code is a magic number. This should be avoided. It skips the implementation detail from the data access code to the business logic code. If you want to continue using this check, I would recommend moving the check to the data access code and throwing an exception if it does not work. In general, return codes should be avoided.

Edit: I just noticed a potentially dangerous error in your code related to my last paragraph above. What if several records are changed? This probably won't happen on INSERT , but it could easily happen on UPDATE . Other parts of the code may assume that != 1 means that the entry has not been modified. This can make debugging very problematic :)

Answer 2

On the one hand, in most cases, everything should behave as you expect, and at this time additional checks add nothing to your application. On the other hand, if something goes wrong without knowing it, then the problem can become quite big before you notice it. In my opinion, a little extra protection is worth a little extra effort, especially if you perform rollbacks on failure. It looks like an airbag in your car ... it really doesn't serve the purpose if you never crash, but if you do, it will save your life.

Answer 3

I always preferred raiserror in my sproc and handle exceptions rather than for counting. That way, if you update sproc to do something else, like logging / auditing, for example, you don't have to worry about checking the number of lines.

Although, if you like the second check of your code or you prefer not to deal with / raiserror , I saw that the commands return 0 on successful sproc runs for each sproc in db and return a different number otherwise.

Answer 4

This is absolutely overkill. You must believe that your main platform (.Net libraries, Sql Server) is working correctly - you should not worry about this.

Now there are several related instances where you can test, for example, if transactions are correctly rolled back, etc.

Answer 5

If there is a need for this check, why not do this check inside the database itself? You save yourself from a round trip, and this is done at a more "centralized" stage. If you register in the database, you can be sure that it is applied sequentially from any application that falls into this database. If you have entered the logic in the user interface, then you need to make sure that any user interface application that falls into this particular database applies the correct logic and does it sequentially.