Sql server row version data type in .net application

Today I'm going to discuss one of the most critical problems but which is not being focused by most of developers.

Here this is the scenario: let’s say we have a Leave Management system, employee has requested a leave and it goes to approval stage. Every manager can see all leaves, and two managers has loaded the same request REQ1 in there computer. What will happen if both managers approved the same request or done some different option (OR something different). It means we have to check the request states (weather it is already approved) before saving it.

 Solution 1:

We have to check the records before saving it. This approach is good but has to be done with lot of data checking with the data base. (OR have to do lot of data checking with the database)

Solution 2: (suggested)

In sql server there is a data type called timespan or row version (sql server 2008). Here is the link about the row version row version. 

The real advantage is, when   we made an update to the table the row version column changes its status to another, just like its status is updated to next sequence. In development we can flag the status easily. But the problem is that it's not in readable format e.g. .0x0000000000018F17.we have to convert this value to bit array in c#. As an alternative for that we can cast row version into sql server big int.

Here is the example

Scripts:

Create a database name test and run below scripts

create TABLE [dbo].[Requests](

 [ReqID] [int] IDENTITY(1,1) NOT NULL,

 [EmpID] [int] NOT NULL,

 [IsApproved] [bit] NULL,

 [Remarks] [varchar](50) NULL,

 [rv] [timestamp] NOT NULL,

 )

insert into Requests values(10,null,'Remarks 1',null)

select * from Requests

The RV column is the row version and sees the results. Change the employee table and see the results.

select *, cast(rv as bigint) from Requests

Run the above select and see the different. We can get the bits of array into big-int data type.

In Requests table ReqID is the primary key and rv value also needs to be checked for the changes. Here is the sample code for validating the changes.

Here is the completed project 

download

happy coding