Posted By

ayaz on 10/04/11
Tagged

Versions (?)

Last Edited at 10/04/11 03:05pm
Statistics

Viewed 871 times
Favorited by 0 user(s)
Related snippets

HTML JSON PAGINATION JQUERY

/ Published in: HTML
HTML PAGED LIST , USING AJAX, JSON AND DATABASE
Expand | Embed | Plain Text
Copy this code and paste it in your HTML
PAGE.HTML
=========
<!DOCTYPE HTML />
<html>
<head>
	<title></title>
	<link href="/css/screen.css" rel="stylesheet" type="text/css" />
</head>
<body>
	<form id="form1" >
	<h2>LIST</h2>
	<div id="artists">
		<div id="pageSize">
			<label>Page size</label><br />
			<select>
				<option>10</option>
				<option>25</option>
				<option>50</option>
				<option selected="selected">100</option>
			</select>
		</div>
		<div id="pager">
			<ul class="pages"><li class="pgEmpty">first</li><li class="pgEmpty">prev</li></ul>
		</div>
		<table id="artistTable">
			<thead>
				<tr>
					<th>ID</th>
					<th width="100">First Name</th>
                    <th width="100">Last Name</th>
                    <th width="100">Email</th>
				</tr>
			</thead>
			<tbody id="artistList"></tbody>
		</table>
		<div id="loading"></div>
	</div>
	<script src="/media/js/jquery.js" type="text/javascript"></script>
	<script src="/media/js/jquery.pager.js" type="text/javascript"></script>
	<script src="/media/js/jquery.tmpl.min.js" type="text/javascript"></script>
	<script id="artistTmpl" type="text/x-jquery-tmpl">
		<tr>
			<td>${userid}</td>
			<td>${firstname}</td>
            <td>${lastname}</td>
            <td>${email}</td>
		</tr>
	</script>
	<script type="text/javascript">
	    $(document).ready(function () {
	        $("#loading").hide();
	        var pageIndex = 1, pageSize = 100, pageCount = 0;
	        getArtists(pageIndex);    
 
            $("#pageSize select").change(function () {
	            pageIndex = 1
	            pageSize = $(this).val();
	            getArtists(pageIndex);
	        });       
 
 
	        function getArtists(index) {
	            var query = "getJson.aspx?page=" + index + "&page_size=" + pageSize;
	            pageIndex = index;
	            $("#artistList")
				.fadeOut("medium", function () {
				    $("#loading").show()
				    $.ajax({
				        dataType: "json",
				        url: query,
				        jsonp: "$callback",
				        success: showArtists
				    });
				});
	        }
 
	        function showArtists(data) {
	            artists = data.results;
	            varpageData = data.pagesummary;
                pageCount = Math.ceil(varpageData[0].total_count / varpageData[0].page_size);	            
	            $("#pager").pager({ pagenumber: pageIndex, pagecount: pageCount, buttonClickCallback: getArtists });
	            $("#artistList").empty()
	            $("#artistTmpl").tmpl(artists).appendTo("#artistList")
	            $("#loading").hide().find("div").fadeIn(4000).end()
	            $("#artistList").fadeIn("medium")
	            $("#artistList tr").hover(
					function () {
					    $(this).addClass("highlight");
					},
					function () {
					    $(this).removeClass("highlight");
					});
	        }
 
	    });
	</script>
	</form>
</body>
</html>
 
JSON.ASPX.CS
===========
 
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Security;
using System.Web.Script.Serialization;
using System.Text;
 
public partial class streetsmartenergy_GetJSON : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        ViewState["SortExpression"] = "Userid";
        ViewState["SortDirection"] = "ASC";
        int varcurpage = (string.IsNullOrEmpty(Request.QueryString["page"])) ? 1: Int32.Parse(Request.QueryString["page"].ToString()) ;
        int varpageSize = (string.IsNullOrEmpty(Request.QueryString["page_size"])) ? 10 : Int32.Parse(Request.QueryString["page_size"].ToString());
 
        GetCustomersPageWise(varcurpage, varpageSize);
    }
 
 
    private void GetCustomersPageWise(int pageIndex,int pagesize)
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ToString()))
        {
            using (SqlCommand cmd = new SqlCommand("GetUsersPagedResults", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@startRowIndex", pageIndex);
                cmd.Parameters.AddWithValue("@maximumRows", pagesize); 
                cmd.Parameters.AddWithValue("@SortExpression", ViewState["SortExpression"].ToString());
                cmd.Parameters.AddWithValue("@SortDir", ViewState["SortDirection"].ToString());
                cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                con.Open();
 
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                DataView dvPerson = ds.Tables[0].DefaultView;
                int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
 
                DataTable dd = ds.Tables[0];
                List<FlexBoxItem> _flexList = new List<FlexBoxItem>();
                foreach (DataRow dr in dd.Rows)
                {
                    _flexList.Add(new FlexBoxItem(dr["userid"].ToString(), dr["firstname"].ToString(), dr["lastname"].ToString(), dr["email"].ToString()));
                }
                FlexBoxResult _flexBoxResult = new FlexBoxResult(_flexList);
 
                JavaScriptSerializer _jss = new JavaScriptSerializer();
                StringBuilder _jsonResult = new StringBuilder();
                _jss.Serialize(_flexBoxResult, _jsonResult);
                string data = String.Format("\"pagesummary\":[{{\"page_number\":\"{0}\",\"total_pages\":\"{3}\",\"page_size\":\"{1}\",\"total_count\":\"{2}\"}}]}}", pageIndex, pagesize, recordCount, (recordCount / pagesize) + 1);
 
                _jsonResult.Replace("}]}", "}],");
                _jsonResult.Append(data);
                con.Close();
 
                Response.ContentType = "application/json";
                Response.Write(_jsonResult.ToString());
                Response.End();
 
            }
        }
    }
 
    [Serializable()]
    public class FlexBoxItem
    {
        string _userid = string.Empty;
        string _firstname = string.Empty;
        string _lastname = string.Empty;
        string _email = string.Empty;
 
        public string userid  { get { return _userid; } set { _userid = value; } }
        public string firstname { get { return _firstname; } set { _firstname = value; } }
        public string lastname { get { return _lastname; } set { _lastname = value; } }
        public string email { get { return _email; } set { _email = value; } }
 
        public FlexBoxItem(string flexbox_userid, string flexbox_firstname,string flexbox_lastname, string flexbox_email)
        {
            _userid = flexbox_userid;
            _firstname = flexbox_firstname;
            _lastname = flexbox_lastname;
            _email = flexbox_email;
        }
 
    }
 
    [Serializable()]
    public class FlexBoxResult
    {
        List<FlexBoxItem> _results = null;
 
        /// <summary>
        /// get/set flexbox item list
        /// </summary>
        public List<FlexBoxItem> results
        {
            get { return _results; }
            set { _results = value; }
        }
 
        /// <summary>
        /// constructor with flexbox item list
        /// </summary>
        /// <param name="_list"></param>
        public FlexBoxResult(List<FlexBoxItem> _list)
        {
            _results = _list;
        }
    }
 
}
 
STORED PROC
==========
CREATE PROCEDURE [glob1001].[GetUsersPagedResults]  
(  
   @startRowIndex    int,  
   @maximumRows      int,     
   @RecordCount INT OUTPUT,  
   @SortExpression   varchar(50) = 'userid',  
   @SortDir varchar(4) = 'ASC'     
)  
AS  
DECLARE  @StartRow INT  
DECLARE  @EndRow INT  
--set @StartRow = (@startRowIndex * @maximumRows) - (@maximumRows - 1)  
--if @startRowIndex = 1 begin  set @StartRow = 1  end  
--set @EndRow = (@StartRow + @maximumRows ) - 1  
 
SELECT @StartRow = (@startRowIndex - 1) * @maximumRows + 1,  
     @EndRow = (@startRowIndex - 1) * @maximumRows + @maximumRows ;  
 
 
DECLARE @TempItems TABLE  
(  
   ID int IDENTITY,  
   userID int  
)  
 
INSERT INTO @TempItems (userID)  
SELECT userID  
FROM tblUser  
Order by  
Case When lower(@SortExpression) = 'userid' and @SortDir = 'ASC' Then UserID  End ASC,   
Case When lower(@SortExpression) = 'userid'  and @SortDir = 'DESC' Then UserID  End DESC,  
Case When lower(@SortExpression) = 'lastname' and @SortDir = 'ASC' Then LastName End ASC,   
Case When lower(@SortExpression) = 'lastname' and @SortDir = 'DESC' Then LastName End DESC,   
Case When lower(@SortExpression) = 'firstname' and @SortDir = 'ASC' Then FirstName End ASC,  
Case When lower(@SortExpression) = 'firstname' and @SortDir = 'DESC' Then FirstName End DESC,  
Case When lower(@SortExpression) = 'email' and @SortDir = 'ASC' Then Email End ASC,  
Case When lower(@SortExpression) = 'email' and @SortDir = 'DESC' Then Email End DESC  
 
SELECT e.*  
FROM @TempItems t  
   INNER JOIN tbluser e ON  
      t.userID = e.userID  
WHERE ID BETWEEN @StartRow AND @EndRow  
--(@startRowIndex * @maximumRows) AND (@startRowIndex + @maximumRows) - 1  
order by  
Case When lower(@SortExpression) = 'userid' and @SortDir = 'ASC' Then e.UserID  End ASC,   
Case When lower(@SortExpression) = 'userid'  and @SortDir = 'DESC' Then e.UserID  End DESC,  
Case When lower(@SortExpression) = 'lastname' and @SortDir = 'ASC' Then LastName End ASC,   
Case When lower(@SortExpression) = 'lastname' and @SortDir = 'DESC' Then LastName End DESC,   
Case When lower(@SortExpression) = 'firstname' and @SortDir = 'ASC' Then FirstName End ASC,  
Case When lower(@SortExpression) = 'firstname' and @SortDir = 'DESC' Then FirstName End DESC,  
Case When lower(@SortExpression) = 'email' and @SortDir = 'ASC' Then Email End ASC,  
Case When lower(@SortExpression) = 'email' and @SortDir = 'DESC' Then Email End DESC  
 
SELECT @RecordCount = COUNT(*) FROM tblUser
Report this snippet Tweet
Comments

Subscribe to comments
Comment:
You need to login to post a comment.