How to work on Jquery sortable to communicate DB
#1.
The following code you can see or you can paste the code in head tag:
<script language="javascript" type="text/javascript">
$(document).ready(function() {
$.ajax({
type: "POST",
url: "AjaxServices.asmx/GetDataForSortable",
data: "{}",
contentType: "application/json; Charset=utf-8",
dataType: "json",
success: function(result){
//alert(result.d);
var results = eval('(' + result.d + ')');
var dd = '';
dd += '<ul id="defaultList" class="list">';
for(var i=0; i<results.length; i++)
{
dd += '<li id="item_'+ results[i].Id +'" originalId="'+ results[i].Id +'" class="itemStyle" seqnumber="'+ results[i].Sequence +'"><span>'+ results[i].Name +'</span></li>';
}
dd += '</ul>';
$('#SortableList').empty()
.html(dd)
.slideDown('slow',function(){
window.scrollTo(0, $('#SortableList').height())
});
$("#defaultList").sortable(
{
update: function(ev, ui)
{
var result = $('#defaultList').sortable('toArray');
updateSequenceNumber(result);
}
});
}
});
});
function updateSequenceNumber(items) {
var originalIdAndSequenceNumber = '';
var index = 0;
for (i = 0; i <= items.length - 1; i++) {
if (items[i].length == 0)
continue;
var item = $('#' + items[i])[0];
originalIdAndSequenceNumber += item.attributes["originalId"].nodeValue + ":" + index.toString();
originalIdAndSequenceNumber += "|";
index = index + 1;
}
persistPositionUsingAjax(originalIdAndSequenceNumber);
}
function persistPositionUsingAjax(originalIdAndSequenceNumber) {
$.ajax({
type: "POST",
dataType: "text",
url: "AjaxServices.asmx/UpdateSequenceNumber",
data: "s=" + originalIdAndSequenceNumber,
success: function() {
}
});
}
</script>
#2. Second thing apply styleseet code on the table. Copy the following code and then paste it in head tag:
<style type="text/css">
.list
{
background: #ccc;
border: solid 1px #98b8d8;
background: #ecf1f5;
padding: 2px;
}
li
{
list-style-type: none;
}
.sortableClass
{
}
.itemStyle
{
background: #fff url(images/drag.png) no-repeat 5px 7px;
border: solid 2px #c4d8ec;
margin: 10px;
height: 33px;
cursor: move;
text-align: left;
}
.itemStyle:hover
{
border: solid 2px #aaaaaa;
}
.itemStyle span
{
float: left;
margin: 7px 0px 0px 40px;
}
.itemStyle .icon
{
float: right;
margin: 3px 0px 25px 0px;
}
.arrow{ cursor:pointer;}
</style>
#3. Third thing is that create the html form in the page and create a div tag like this with id SortableList.
<body>
<form id="form1" runat="server">
<div id="SortableList">
</div>
</form>
</body>
</html>
#4. Fourth one is that create the webservice.asmx file and write the code to communicate with database. The following code is helpful for you.
using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Web.Script.Serialization;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Text;
/// <summary>
/// Summary description for AjaxServices
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
[ScriptService()]
public class AjaxServices : System.Web.Services.WebService {
public AjaxServices () {
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public void UpdateSequenceNumber(string s)
{
s = s.TrimEnd('|');
string updateQuery = @"update MenuMaster set Sequence = {0} where MenuId = {1}";
StringBuilder sb = new StringBuilder();
string[] originalIdAndSeqNumberArray = s.Split('|');
foreach (string originalIdAndSeqNumberCombined in originalIdAndSeqNumberArray)
{
string[] tempArray = originalIdAndSeqNumberCombined.Split(':');
int originalId = Convert.ToInt32(tempArray[0]);
int sequenceNumber = Convert.ToInt32(tempArray[1]);
sb.Append(String.Format(updateQuery, sequenceNumber, originalId));
sb.Append(System.Environment.NewLine);
}
SqlCommand cmd = new SqlCommand(sb.ToString());
DbHelper.Save(cmd, false);
}
[WebMethod]
public string GetDataForSortable()
{
List<MenuMaster> mm = new List<MenuMaster>();
SqlCommand cmd = new SqlCommand("Select * from MenuMaster order by Sequence");
DataTable dt = DbHelper.Get(cmd, false) as DataTable;
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
MenuMaster m = new MenuMaster() {
Id = Convert.ToInt32(row["MenuId"].ToString()),
Name = row["MenuName"].ToString(),
Sequence = Convert.ToInt32(row["Sequence"].ToString())
};
mm.Add(m);
}
return new JavaScriptSerializer().Serialize(mm);
}
return new JavaScriptSerializer().Serialize("Hello");
}
}
interface IMenu
{
Nullable<int> Id { get; set; }
string Name { get; set; }
int Sequence { get; set; }
}
public sealed class MenuMaster : IMenu
{
public Nullable<int> Id { get; set; }
public string Name { get; set; }
public int Sequence { get; set; }
}
public class DbHelper
{
public static object Get(SqlCommand cmd, bool isStoredProcedure)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
{
cmd.CommandTimeout = 30;
cmd.Connection = con;
cmd.CommandType = (isStoredProcedure == true) ? CommandType.StoredProcedure : CommandType.Text;
cmd.Connection.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
cmd.Connection.Close();
return dt;
}
}
public static object Save(SqlCommand cmd, bool isStoredProcedure)
{
int recordEffected = 0;
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
{
cmd.CommandTimeout = 30;
cmd.Connection = con;
cmd.CommandType = (isStoredProcedure == true) ? CommandType.StoredProcedure : CommandType.Text;
cmd.Connection.Open();
recordEffected = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return recordEffected;
}
}
}
#5. First of all configure your web.config file for 2.0 frame work. If you work on 3.5 frame work then no problem with this code. And it will be execute successfully.
If you download the full code then click the following link:
No comments:
Post a Comment