Updating Display Order In Database With Drag And Drop

Here, I have explained how to manage display order in the database with drag & drop features.
 
They are various ways to manage display order but I have explained the very simplest way to manage with the database.
 
I have explained the server-side process only.
 
I have created a product table in the database to store product details with display orders.
 
 
Insert some product details for example. 
 
 
Script
  1. CREATE TABLE [dbo].[TblProduct](  
  2.     [ProductID] [bigint] IDENTITY(1,1) NOT NULL,  
  3.     [ProductName] [varchar](50) NOT NULL,  
  4.     [Price] [intNOT NULL,  
  5.     [DisplayOrder] [intNULL,  
  6.  CONSTRAINT [PK_TblProduct] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [ProductID] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFFON [PRIMARY]  
  10. ON [PRIMARY]  
  11. GO  
We need to pass three parameters in the server-side to perform all operations.
  1. Current Position
  2. Target Position.
  3. Product Id
Step 1 - Identify the direction.
 
Normally drag & drop moves in an up or down direction. So first we determine the user is moving the product up or down.
 
If the target position is less than the current position it means a down direction. If it is greater then it means up direction. 
  1. direction = TargetPosition > CurrentPosition ? “down” : “up”;  
Step 2 - Update Dragged product display order 0
 
Set dragged product display order zero so we can update its display order later.
  1. UPDATE TblProduct   
  2. SET    DisplayOrder = 0   
  3. WHERE  DisplayOrder = @CurrentPosition   
  4.        AND ProductId = @ProductID   
Step 3 - If moving the product down.
 
If the product is moving down we need to decrease the display order by 1 between the current position & the target position.
  1. UPDATE TblProduct   
  2. SET    DisplayOrder = ( DisplayOrder - 1 )   
  3. WHERE  DisplayOrder > @CurrentPosition   
  4.        AND DisplayOrder <= @TargetPosition   
Step 4 - If moving the product up.
 
If the product is moving up  we need to increase the display order by 1 between the target position & the current position. 
  1. UPDATE TblProduct   
  2. SET    DisplayOrder = ( DisplayOrder + 1 )   
  3. WHERE  DisplayOrder >= @TargetPosition   
  4.        AND DisplayOrder < @CurrentPosition   
Step 5 - Update dragged product with the target position.
 
At last,  update dragged the product’s display order with the target position. 
  1. UPDATE TblProduct   
  2. SET    DisplayOrder = @TargetPosition   
  3. WHERE  DisplayOrder = 0   
  4.        AND ProductID = @ProductID   
With this logic, only three queries are required on the server side to manage display order with database, no matter how many products are in the list.