C# – What’s the best way to manage list item sort order with Drag & Drop UI

csorting

I have a list of Students that I should display to user on a web page in tabular format.
The items are stored in DB along with SortOrder information.

On the web page, user can rearrange the list order by dragging and dropping the items to their desired sort order, similar to this post.

Below is a screenshot of my test page.
enter image description here

In the above example, each row has sort order info attached to it. When I drop John Doe (Student Id 10) above the Student Id 1 row, the list order should now be: 2, 10, 1, 8, 11.

What's the optimistic (less resource hungry) way to store and update Sort Order information?

My only idea for now is, for every change in the list's sort order, every object's SortOrder value should be updated, which in my opinion is very resource hungry.

Just FYI: I might have at most 25 rows in my table.

Best Answer

I have think of something, which can reduce your queries. Here in my example, I have added a new column for sorting named pos. So, initially without any dragging your table will be like -

Initial State

Now, Lets consider that you dragged the Item 4 between Item 1 & Item 2. Now, new pos value for Item 4 will be (20 + 10) / 2, which is 15. So, You will only need to update a single row in database. And, you will get -

After Drag

Here is a flowchart with the edge cases. i is the new array index of your row after dragged -

Flow Chart

This flowchart doesn't handle ArrayOutOfBound checks. And, for edge cases you will need more than one query.

Since you have only 25 rows, you can take a very big value (e.g. 10,000) for the pos difference (I took 10 for this example). The bigger the value is, the less it will collide.

Related Topic