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>

<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.