Loading ...

Reorder list using jQuery and ASP.NET | CodeAsp.Net

Reorder list using jQuery and ASP.NET

(40695)
4.64
/5
Avg: 4.64/5: (1 votes)
Published: 6/13/2013 by Raghav Khunger

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.

 

Comments (15)

zia
zia said:

great. will try this

2/7/2011
 · 
 
by
monika nafdey
monika nafdey said:
Raghav, great logic.. I used this jquery and code for my site.. its so cool.Tried on my locally.. will let you know for online..
12/1/2011
 · 
 
by
Ali Kazak
Ali Kazak said:
Thanks so much for you. This is exactly what I need.
12/5/2011
 · 
 
by
Stefan H
Stefan H said:
Thank you for your work, i used this with a submit button, works perfect!
12/25/2011
 · 
 
by
Stefan H
Stefan H said:
Hi, do you know a solution to use the reorder with a table (asp gridview)
1/10/2012
 · 
 
by
Kiley
Kiley said:
Hi, this is exactly what I'm looking for. I'm trying to convert this to vb, has one done that and, if so, would you be willing to share? Thanks!
1/12/2012
 · 
 
by
Hsu
Hsu said:
Really thanks. That's what I am looking for ... :)
4/20/2012
 · 
 
by
Gus
Gus said:
Hi thanks for you code. I am trying to implement it but I get this error: Type or namespace name 'Item' could not be found (are you missing a using directive or an assembly reference?) Is there some ref I need other than those in the project? I get this for both the web service and the .aspx page. thanks Gus
5/16/2012
 · 
 
by
gus
gus said:
never mind I forgot to add the business class..
5/16/2012
 · 
 
by
Chad Richardson
Thank you so much for this! This is exactly what I was looking for. I am trying it out now, but I had a question about the webservice code in UpdateItemsOrder. You create: Collection<Item> items = new Collection<Item>();, but it doesn't look like you ever use the items variable after that. Am I missing something?
5/16/2012
 · 
 
by
raghav_khunger
Demo is working again.
9/1/2012
 · 
 
by
Vikram Sharma
Vikram Sharma said:
This is really cool, this is what i'm looking for. Keep on posting these kind of things.
Thanks for the post.
10/6/2012
 · 
 
by
Svein Erik
Svein Erik said:
Thank you very much for sharing this! I've successfully implemented it on a project I'm working on. Thank you!
12/11/2012
 · 
 
by
raghav_khunger
Cool!
12/14/2012
 · 
 
by
Gaviin
Gaviin said:
Good day. Is there a way to extract the text of the sorted Item example " Item 6" on the Client side?

Kind Regards
3/7/2013
 · 
 
by
Sagar Patade
Sagar Patade said:
Great Help Raghu... it did wonders for me.... i implemented on Grid view and it worked. Thanks!!
5/7/2013
 · 
 
by
sagar
sagar said:
how it can be done on gridview ?
var order = $('#sortable').sortable('toArray').join(',').replace(/id_/gi, '')
order is empty you cant get id in order variable
and i have that issue, so anyone know how to solve it post on comment..
12/19/2013
 · 
 
by
Burak Hasdikici
Burak Hasdikici said:
I had this error " Invalid object name 'dbo.SPLIT' " how can i fix it?
12/25/2013
 · 
 
by
  • Name:*
  • Email:*
  • Website:
Type the characters you see in the image *

Top articles

Related articles

  • Calling web service using jQuery in ASP.NET

    In this article I will show how to call ASP.NET webservice using jQuery. Webservice call using jQuery will let you to prevent the re-rendering the entire content of the page. The request is made partially and you can replace the content of the page without postback. Thus increasing usability and leads to less data transfer for the same cause
    6/13/2013 by Raghav Khunger

Quick Vote

What kind of email newsletter would you prefer to receive from CodeAsp.Net?