Reorder list using jQuery and ASP.NET

In this article I will explain how to reorder list using jQuery and ASP.NET and the saving the item's updated order in the database. The advantage of reordering list with jQuery is that it is light weight, saving of bandwidth and good from user prespective. I have used jQuery UI Sortable List to sort the items. The changes are persisted to database via ajax request. This way of reordering is very interactive as the items are reordered via Drag and Drop and changes are preserved without PostBack which gives a very smooth cross browser experience.

Below is the screenshot how the sequence of the items is sent in the form of comma separated string to the server via ajax request.



DB part:

Let's create a table and name it Items with the three columns ItemID, ItemName, ItemOrder. ItemOrder will contain the sequence number of the item i.e. item's order number. I have inserted few sample records to have some data in our table. Below is the script for it:

Table:

GO
CREATE TABLE [Items]
    (
      [ItemID] INT IDENTITY ,
      [ItemName] VARCHAR(50) ,
      [ItemOrder] INT
    )
GO
INSERT  INTO [Items]
        SELECT  'Item1' ,
                1
INSERT  INTO [Items]
        SELECT  'Item2' ,
                2
INSERT  INTO [Items]
        SELECT  'Item3' ,
                3
INSERT  INTO [Items]
        SELECT  'Item4' ,
                4
INSERT  INTO [Items]
        SELECT  'Item5' ,
                5
INSERT  INTO [Items]
        SELECT  'Item6' ,
                6
INSERT  INTO [Items]
        SELECT  'Item7' ,
                7
INSERT  INTO [Items]
        SELECT  'Item8' ,
                8
INSERT  INTO [Items]
        SELECT  'Item9' ,
                9
INSERT  INTO [Items]
        SELECT  'Item10' ,
                10  
GO

Now to show the items in front end we have to make a select SP. This SP will give the records present inside the Items table. The records will be represented in ascending order based on ItemOrder column. Below is the script for it:

Select SP:

GO
CREATE PROCEDURE [SelectItems]
AS 
    BEGIN
        SET NOCOUNT ON 
        
        SELECT  [ItemID] ,
                [ItemName]
        FROM    [Items]
        ORDER BY [ItemOrder] ASC
            
        SET NOCOUNT OFF             
    END
GO 

We have to make a Update SP too which will come in action when the user will reorder the list from front end. This SP will accept one argument ItemOrder. This argument contains items order in a comma separated form (1,4,5,6,7,8..). Below is the script: 

Update SP

GO
CREATE PROCEDURE [UpdateItemsOrder]
    @ItemOrder VARCHAR(255)
AS 
    BEGIN
   
   
        SET NOCOUNT ON      
        DECLARE @Temp TABLE
            (
              [ItemOrder] INT IDENTITY ,
              [ItemID] INT
            )
        INSERT  INTO @Temp
                SELECT  [Value]
                FROM    dbo.SPLIT(@ItemOrder, ',')
        SET NOCOUNT OFF
        UPDATE  [Items]
        SET     [Items].[ItemOrder] = [Temp].[ItemOrder]
        FROM    [Items]
                INNER JOIN @Temp [Temp] ON [Items].[ItemID] = [Temp].[ItemID]

    END
GO 

Above I have used a split function (dbo.SPLIT) to split the comma separated string passed from outside into a table. Below is the script for it:

Split function:

GO
CREATE FUNCTION [Split]
    (
      @text VARCHAR(MAX) ,
      @delimiter VARCHAR(20) = ' '
    )
RETURNS @Strings TABLE
    (
      [position] INT IDENTITY
                     PRIMARY KEY ,
      [value] VARCHAR(100)
    )
AS 
    BEGIN
        DECLARE @index INT
        SET @index = -1
        WHILE ( LEN(@text) > 0 ) 
            BEGIN -- Find the first delimiter
                SET @index = CHARINDEX(@delimiter, @text)

                IF ( @index = 0 )
                    AND ( LEN(@text) > 0 ) 
                    BEGIN
                        INSERT  INTO @Strings
                        VALUES  ( CAST(@text AS VARCHAR(100)) )
                        BREAK
                    END

                IF ( @index > 1 ) 
                    BEGIN
                        INSERT  INTO @Strings
                        VALUES  ( CAST(LEFT(@text, @index - 1) AS VARCHAR(100)) )
                        SET @text = RIGHT(@text, ( LEN(@text) - @index ))
                    END --Delimiter is 1st position = no @text to insert
                ELSE 
                    SET @text = CAST(RIGHT(@text, ( LEN(@text) - @index )) AS VARCHAR(100))
            END
        RETURN
    END

GO

Front End Part:

Business class:

Let's make a Business class for Items and name it Item. This class contains the following property ItemID, ItemName and ItemOrder. Below is the code for it:

[Serializable]
public class Item
{
    public int ItemID { get; set; }
    public string ItemName { get; set; }
    public int ItemOrder { get; set; }
}

ASPX code:

Let's come to ASPX part. I have used ListView to show the items in the list. The ListView will be binded with the data fetched from database via SelectItems SP. User will drag the items according to his needs and accordingly changes will be preserved in the database via ajax request made to Sortable.asmx whose code we will discuss later in this article. Below is the code of our ASPX page.

Sample.aspx:

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Collections.ObjectModel" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.8/themes/redmond/jquery-ui.css"
        type="text/css" media="all" />

    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js" type="text/javascript"></script>

    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.8/jquery-ui.min.js"
        type="text/javascript"></script>

    <script type="text/javascript">

        $(function() {
            $('#sortable').sortable({
                placeholder: 'ui-state-highlight',
                update: OnSortableUpdate
            });
            $('#sortable').disableSelection();

            var progressMessage = 'Saving changes... <img src="loading.gif"/>';
            var successMessage = 'Saved successfully!';
            var errorMessage = 'There was some error in processing your request';
            var messageContainer = $('#message').find('p');

            function OnSortableUpdate(event, ui) {
                var order = $('#sortable').sortable('toArray').join(',').replace(/id_/gi, '')
                //console.info(order);
                
                messageContainer.html(progressMessage);
                
                $.ajax({
                    type: 'POST',
                    url: 'Sortable.asmx/UpdateItemsOrder',
                    data: '{itemOrder: \'' + order + '\'}',
                    contentType: 'application/json; charset=utf-8',
                    dataType: 'json',
                    success: OnSortableUpdateSuccess,
                    error: OnSortableUpdateError
                });
            }

            function OnSortableUpdateSuccess(response) {
                if (response != null && response.d != null) {
                    var data = response.d;
                    if (data == true) {
                        messageContainer.html(successMessage);
                    }
                    else {
                        messageContainer.html(errorMessage);
                    }
                    //console.info(data);
                }
            }

            function OnSortableUpdateError(xhr, ajaxOptions, thrownError) {
                messageContainer.html(errorMessage);
            }

        });

    </script>

    <style type="text/css">
        #sortable
        {
            list-style-type: none;
            margin: 0;
            padding: 0;
            width: 400px;
        }
        #sortable li
        {
            margin: 0 5px 5px 5px;
            padding: 5px;
            font-size: 1.2em;
            height: 1.5em;
            cursor: move;
        }
        html > body #sortable li
        {
            height: 1.5em;
            line-height: 1.2em;
        }
        
    </style>
</head>
<body>
    <form id="form1" runat="server">
    
    <div class="ui-widget" id="message">
        <div class="ui-state-highlight ui-corner-all" style="margin-top: 20px; padding: 0 .7em;">
            <p>
                Reorder Items
            </p>
        </div>
    </div>
    
    <br />
    <ul id="sortable">
        <asp:ListView ID="ItemsListView" runat="server" ItemPlaceholderID="myItemPlaceHolder">
            <LayoutTemplate>
            </LayoutTemplate>
            <LayoutTemplate>
                <asp:PlaceHolder ID="myItemPlaceHolder" runat="server"></asp:PlaceHolder>
            </LayoutTemplate>
            <ItemTemplate>
                <li class="ui-state-default" id='id_<%# Eval("ItemID") %>'>
                    <%# Eval("ItemName") %></li>
            </ItemTemplate>
        </asp:ListView>
    </ul>
    </form>
</body>
</html>

<script runat="server">

    protected void Page_Load(object sender, EventArgs e)
    {
        ItemsListView.DataSource = FindItems();
        ItemsListView.DataBind();

    }

    public static IEnumerable<Item> FindItems()
    {
        Collection<Item> items = new Collection<Item>();
        string connectionString =
        "Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = "SelectItems";
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    Item item;
                    while (dataReader.Read())
                    {
                        item = new Item();
                        item.ItemID = (int)dataReader["ItemID"];
                        item.ItemName = Convert.ToString(dataReader["ItemName"]);
                        items.Add(item);
                    }
                }
            }
        }
        return items;
    }
</script>

WebService Code:

Let's come to webservice code which will be hit by the client side to update the item's updated order in the database. In this webservice the UpdateItemsOrder method is written which will accept one argument which is the updated sequence from the client's item's list. This method will hit the the DB and call the UpdateItemsOrder SP and accordingly response will be returned whether the rows are updated or not. Below is the code for it:

Sortable.asmx:

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;


/// <summary>
/// Summary description for Sortable
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class Sortable : System.Web.Services.WebService
{


    [WebMethod]
    public bool UpdateItemsOrder(string itemOrder)
    {
        Collection<Item> items = new Collection<Item>();
        string connectionString =
                "Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = "UpdateItemsOrder";
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter paramUserName = new SqlParameter("@ItemOrder", SqlDbType.VarChar, 255);
                paramUserName.Value = itemOrder;
                command.Parameters.Add(paramUserName);


                connection.Open();
                return (command.ExecuteNonQuery() > 0);

            }
        }

    }
}

Below are the screenshots of the FireBug of the requests made to the server.

Screenshot of the Post Data sent to server: 




ScreenShot of the Response Data received from server:

That's it we are done with reordering list using jQuery and ASP.NET. Above you saw how we can drag and drop the items of the list and can change their positions according to our needs. The changes are preserved via updated sequence sent to the server. 

Do let me know your feedback, comments.