Sorting is a common operation which is performed in most of the websites. This operation is usually executed by clicking on a table column which sorts the records in the respective cells underneath. In this article we are going to pimp up sorting by providing a drag and drop interface which is more interactive and looks more appealing to the users.

Database:

We will be using the Northwind database and in particular Categories table. We have added a custom field "SequenceNumber" to the categories table as shown below:  

 

The SequenceNumber field is used to indicate the current sorting order of the rows in the Categories table. The SequenceNumber of "0" indicates that the row will be displayed at the top. The screen shot below shows the Categories table populated with some custom data.



Populating the Repeater Control:

The first step is to populate the Repeater control with the data from the Categories table. Here is the implementation of the BindData method used to populate the Repeater control.



You can use any technique to populate the data. In the above sample we have shown the DataSet approach. The important point to notice is the order by clause which makes sure that records are fetched based on their SequenceNumber field.

The image below shows how the Repeater control will look like after being populated with the data.




We have used some styles to make our Repeater control look a little decent!

Let's move on to the ASPX code for the Repeater control.

ASPX Code for Repeater Control:

The Repeater ASPX code is only several lines long as shown below:



In the above code we are attaching some custom properties/attributes to the "li" element inside the Repeater control. A unique "id" is created by concatenating the "item_" with the current index of the Repeater control. The "originalId" attribute represents the "CategoryID" from the database. The "seqNumber" attribute represents the "SequenceNumber" from the database. Although the list is displayed successfully but it is not yet sortable. Let's see how to make a list sortable.

Making a List Sortable:

Adding the sorting functionality to the list sounds like a lot of work but JQuery awesomeness makes it a one liner approach. Here is the complete code you will need to make the list sortable:



Basically, we are saying that get the element with the ID "defaultList" and make it sortable. For this to work you must download and reference the JQuery and JQuery UI JavaScript files in your project as shown in the code below:



Although our list is now sortable but it serves no purpose. This is because once we sort the list and refresh the page our sort order is lost. We must save the sort order to some persistent storage so that next time when the user comes to the page he/she can see the new sort order.

Persisting Sort Order:

In order to persist the sort order we need to hook up the update function of the sortable() function. Take a look at the code below:



The update function is fired when ever the position of the element is changed. The sortable('toArray') function will return us an Array of all the sort elements ID. We will send the array to the updateSequenceNumber function which will perform further actions.



The updateSequenceNumber takes in the array of id's of the elements involved in sorting and create a colon and bar separated string which will later be fed to the ajax function for persistence.

The persistPositionUsingAjax function takes the long string and sends it to the server side method for the final persistence.



The server side UpdateSequenceNumber method looks something like the following:



Basically UpdateSequenceNumber takes apart the string and creates a long update T-SQL statement which is passed to the UpdateInDatabase method.



After the update is successfull you can visit your Categories table and witness the SequenceNumber being changed to the current sorting order. Now, you can close your browser and open the page again and you will notice that the items are sorted the same way you left the last time.

References:

1)
Using RegisterExpandoAttribute to Inject Custom Properties Client Side
2) Using JQuery Ajax API to Send Plain Text to Server Side Method

Conclusion:

In this article we learned how to use JQuery to create a sortable list which persists its sort state in the database. We also learned how to send plain text arguments to a web service method using JQuery Ajax API.

Live Sample Animation:



[Download Sample]