Kriya and Meditation for Software / IT Professionals. Conducted by Bipin Joshi in Thane. Read more...
Learn ASP.NET MVC, ASP.NET Core and ASP.NET Design Patterns. Courses conducted by Bipin Joshi on weekends. Click here for more details.

Optimistic Concurrency and DataS

Optimistic Concurrency and DataSet Updates

Introduction

In any disconnected environment the problem of concurrent updates is obvious. Updates via DataSet is no exception. There are various solutions to tackle with this issue and which one to use depends upon your application, requirements and database schema. In this article I am going to illustrate three commonly used ways to deal with concurrency issues.

Understanding the problem

Before we go ahead with the possible solutions, let's understand the problem.

  • You create a DataSet and populate it with data from database.
  • You perform updations and deletions on the data.
  • Now you are ready to update the data back to the database.
  • However, while you were processing the data somebody else updates the same data in the database.
  • When you issue the update the data (which is changed by somebody else!) is overwritten.

In other words the data at the time of update is not the same as it was at the time you fetched it. This situation is referred as concurrency violation and the concurrency is said to be "Optimistic".

There are few points that you should think of:

  • Is it OK with your application to overwrite such data (which is modified by somebody else)?
  • Do you want to cancel your updates if such violation is observed?
  • Do you want to ask the user whether he/she wants to overwrite the data?

Possible Solutions

Any solution to concurrency problem should make sure that the data at the time of update is the same as it was when you fetched it. Here are some common solutions that you can implement:

  • Update using ALL the fields in WHERE clause: In this approach you include all the fields from SELECT statement in the WHERE condition of UPDATE statement. This can be achieved in two ways either configuring the DataAdapter manually or using CommandBuilder.
  • Updating using SOME fields in WHERE clause: In this approach only few fields from SELECT statement are included in WHERE condition of UPDATE statement.
  • Updating based on a TIMESTAMP column:  In this approach you add a timestamp field to your table and compare its values before updating the row.

Sample Application

A sample application is available for download along with this article which illustrates all of the above solutions. You will need Northwind database of SQL server in order to work with the examples. Note that in order to work with timestamp example you need to add a column called TSID to the Employees table of Northwind database.

 


Bipin Joshi is a software consultant, an author and a yoga mentor having 22+ years of experience in software development. He also conducts online courses in ASP.NET MVC / Core and Design Patterns. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Meditation and Mindfulness to interested individuals. To know more about him click here.

Get connected : Twitter  Facebook  Google+  LinkedIn

Posted On : 07 November 2004


Tags : ADO.NET Data Access SQL Server