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

CREATE TABLE [dbo].[TblProduct](
    [ProductID] [bigint] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](50) NOT NULL,
    [Price] [int] NOT NULL,
    [DisplayOrder] [int] NULL,
 CONSTRAINT [PK_TblProduct] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
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. 

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.

UPDATE TblProduct
SET    DisplayOrder = 0
WHERE  DisplayOrder = @CurrentPosition
       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.

UPDATE TblProduct
SET    DisplayOrder = ( DisplayOrder - 1 )
WHERE  DisplayOrder > @CurrentPosition
       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. 

UPDATE TblProduct
SET    DisplayOrder = ( DisplayOrder + 1 )
WHERE  DisplayOrder >= @TargetPosition
       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. 

UPDATE TblProduct
SET    DisplayOrder = @TargetPosition
WHERE  DisplayOrder = 0
       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.