Revision: 51791
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at October 4, 2011 15:05 by ayaz
Initial Code
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
Initial URL
Initial Description
HTML PAGED LIST , USING AJAX, JSON AND DATABASE
Initial Title
HTML JSON PAGINATION JQUERY
Initial Tags
html, jquery, json
Initial Language
HTML