HTML JSON PAGINATION JQUERY


/ Published in: HTML
Save to your folder(s)

HTML PAGED LIST , USING AJAX, JSON AND DATABASE


Copy this code and paste it in your HTML
  1. PAGE.HTML
  2. =========
  3. <!DOCTYPE HTML />
  4. <link href="/css/screen.css" rel="stylesheet" type="text/css" />
  5. </head>
  6. <form id="form1" >
  7. <h2>LIST</h2>
  8. <div id="artists">
  9. <div id="pageSize">
  10. <label>Page size</label><br />
  11. <option>10</option>
  12. <option>25</option>
  13. <option>50</option>
  14. <option selected="selected">100</option>
  15. </select>
  16. </div>
  17. <div id="pager">
  18. <ul class="pages"><li class="pgEmpty">first</li><li class="pgEmpty">prev</li></ul>
  19. </div>
  20. <table id="artistTable">
  21. <tr>
  22. <th>ID</th>
  23. <th width="100">First Name</th>
  24. <th width="100">Last Name</th>
  25. <th width="100">Email</th>
  26. </tr>
  27. </thead>
  28. <tbody id="artistList"></tbody>
  29. </table>
  30. <div id="loading"></div>
  31. </div>
  32. <script src="/media/js/jquery.js" type="text/javascript"></script>
  33. <script src="/media/js/jquery.pager.js" type="text/javascript"></script>
  34. <script src="/media/js/jquery.tmpl.min.js" type="text/javascript"></script>
  35. <script id="artistTmpl" type="text/x-jquery-tmpl">
  36. <tr>
  37. <td>${userid}</td>
  38. <td>${firstname}</td>
  39. <td>${lastname}</td>
  40. <td>${email}</td>
  41. </tr>
  42. </script>
  43. <script type="text/javascript">
  44. $(document).ready(function () {
  45. $("#loading").hide();
  46. var pageIndex = 1, pageSize = 100, pageCount = 0;
  47. getArtists(pageIndex);
  48.  
  49. $("#pageSize select").change(function () {
  50. pageIndex = 1
  51. pageSize = $(this).val();
  52. getArtists(pageIndex);
  53. });
  54.  
  55.  
  56. function getArtists(index) {
  57. var query = "getJson.aspx?page=" + index + "&page_size=" + pageSize;
  58. pageIndex = index;
  59. $("#artistList")
  60. .fadeOut("medium", function () {
  61. $("#loading").show()
  62. $.ajax({
  63. dataType: "json",
  64. url: query,
  65. jsonp: "$callback",
  66. success: showArtists
  67. });
  68. });
  69. }
  70.  
  71. function showArtists(data) {
  72. artists = data.results;
  73. varpageData = data.pagesummary;
  74. pageCount = Math.ceil(varpageData[0].total_count / varpageData[0].page_size);
  75. $("#pager").pager({ pagenumber: pageIndex, pagecount: pageCount, buttonClickCallback: getArtists });
  76. $("#artistList").empty()
  77. $("#artistTmpl").tmpl(artists).appendTo("#artistList")
  78. $("#loading").hide().find("div").fadeIn(4000).end()
  79. $("#artistList").fadeIn("medium")
  80. $("#artistList tr").hover(
  81. function () {
  82. $(this).addClass("highlight");
  83. },
  84. function () {
  85. $(this).removeClass("highlight");
  86. });
  87. }
  88.  
  89. });
  90. </script>
  91. </form>
  92. </body>
  93. </html>
  94.  
  95. JSON.ASPX.CS
  96. ===========
  97.  
  98. using System;
  99. using System.Collections.Generic;
  100. using System.Web;
  101. using System.Web.UI;
  102. using System.Web.UI.WebControls;
  103. using System.Data;
  104. using System.Data.SqlClient;
  105. using System.Configuration;
  106. using System.Web.Security;
  107. using System.Web.Script.Serialization;
  108. using System.Text;
  109.  
  110. public partial class streetsmartenergy_GetJSON : System.Web.UI.Page
  111. {
  112. protected void Page_Load(object sender, EventArgs e)
  113. {
  114. ViewState["SortExpression"] = "Userid";
  115. ViewState["SortDirection"] = "ASC";
  116. int varcurpage = (string.IsNullOrEmpty(Request.QueryString["page"])) ? 1: Int32.Parse(Request.QueryString["page"].ToString()) ;
  117. int varpageSize = (string.IsNullOrEmpty(Request.QueryString["page_size"])) ? 10 : Int32.Parse(Request.QueryString["page_size"].ToString());
  118.  
  119. GetCustomersPageWise(varcurpage, varpageSize);
  120. }
  121.  
  122.  
  123. private void GetCustomersPageWise(int pageIndex,int pagesize)
  124. {
  125. using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ToString()))
  126. {
  127. using (SqlCommand cmd = new SqlCommand("GetUsersPagedResults", con))
  128. {
  129. cmd.CommandType = CommandType.StoredProcedure;
  130. cmd.Parameters.AddWithValue("@startRowIndex", pageIndex);
  131. cmd.Parameters.AddWithValue("@maximumRows", pagesize);
  132. cmd.Parameters.AddWithValue("@SortExpression", ViewState["SortExpression"].ToString());
  133. cmd.Parameters.AddWithValue("@SortDir", ViewState["SortDirection"].ToString());
  134. cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
  135. cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
  136. con.Open();
  137.  
  138. SqlDataAdapter ad = new SqlDataAdapter(cmd);
  139. DataSet ds = new DataSet();
  140. ad.Fill(ds);
  141. DataView dvPerson = ds.Tables[0].DefaultView;
  142. int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
  143.  
  144. DataTable dd = ds.Tables[0];
  145. List<FlexBoxItem> _flexList = new List<FlexBoxItem>();
  146. foreach (DataRow dr in dd.Rows)
  147. {
  148. _flexList.Add(new FlexBoxItem(dr["userid"].ToString(), dr["firstname"].ToString(), dr["lastname"].ToString(), dr["email"].ToString()));
  149. }
  150. FlexBoxResult _flexBoxResult = new FlexBoxResult(_flexList);
  151.  
  152. JavaScriptSerializer _jss = new JavaScriptSerializer();
  153. StringBuilder _jsonResult = new StringBuilder();
  154. _jss.Serialize(_flexBoxResult, _jsonResult);
  155. string data = String.Format("\"pagesummary\":[{{\"page_number\":\"{0}\",\"total_pages\":\"{3}\",\"page_size\":\"{1}\",\"total_count\":\"{2}\"}}]}}", pageIndex, pagesize, recordCount, (recordCount / pagesize) + 1);
  156.  
  157. _jsonResult.Replace("}]}", "}],");
  158. _jsonResult.Append(data);
  159. con.Close();
  160.  
  161. Response.ContentType = "application/json";
  162. Response.Write(_jsonResult.ToString());
  163. Response.End();
  164.  
  165. }
  166. }
  167. }
  168.  
  169. [Serializable()]
  170. public class FlexBoxItem
  171. {
  172. string _userid = string.Empty;
  173. string _firstname = string.Empty;
  174. string _lastname = string.Empty;
  175. string _email = string.Empty;
  176.  
  177. public string userid { get { return _userid; } set { _userid = value; } }
  178. public string firstname { get { return _firstname; } set { _firstname = value; } }
  179. public string lastname { get { return _lastname; } set { _lastname = value; } }
  180. public string email { get { return _email; } set { _email = value; } }
  181.  
  182. public FlexBoxItem(string flexbox_userid, string flexbox_firstname,string flexbox_lastname, string flexbox_email)
  183. {
  184. _userid = flexbox_userid;
  185. _firstname = flexbox_firstname;
  186. _lastname = flexbox_lastname;
  187. _email = flexbox_email;
  188. }
  189.  
  190. }
  191.  
  192. [Serializable()]
  193. public class FlexBoxResult
  194. {
  195. List<FlexBoxItem> _results = null;
  196.  
  197. /// <summary>
  198. /// get/set flexbox item list
  199. /// </summary>
  200. public List<FlexBoxItem> results
  201. {
  202. get { return _results; }
  203. set { _results = value; }
  204. }
  205.  
  206. /// <summary>
  207. /// constructor with flexbox item list
  208. /// </summary>
  209. /// <param name="_list"></param>
  210. public FlexBoxResult(List<FlexBoxItem> _list)
  211. {
  212. _results = _list;
  213. }
  214. }
  215.  
  216. }
  217.  
  218. STORED PROC
  219. ==========
  220. CREATE PROCEDURE [glob1001].[GetUsersPagedResults]
  221. (
  222. @startRowIndex int,
  223. @maximumRows int,
  224. @RecordCount INT OUTPUT,
  225. @SortExpression varchar(50) = 'userid',
  226. @SortDir varchar(4) = 'ASC'
  227. )
  228. AS
  229. DECLARE @StartRow INT
  230. DECLARE @EndRow INT
  231. --set @StartRow = (@startRowIndex * @maximumRows) - (@maximumRows - 1)
  232. --if @startRowIndex = 1 begin set @StartRow = 1 end
  233. --set @EndRow = (@StartRow + @maximumRows ) - 1
  234.  
  235. SELECT @StartRow = (@startRowIndex - 1) * @maximumRows + 1,
  236. @EndRow = (@startRowIndex - 1) * @maximumRows + @maximumRows ;
  237.  
  238.  
  239. DECLARE @TempItems TABLE
  240. (
  241. ID int IDENTITY,
  242. userID int
  243. )
  244.  
  245. INSERT INTO @TempItems (userID)
  246. SELECT userID
  247. FROM tblUser
  248. Order by
  249. Case When lower(@SortExpression) = 'userid' and @SortDir = 'ASC' Then UserID End ASC,
  250. Case When lower(@SortExpression) = 'userid' and @SortDir = 'DESC' Then UserID End DESC,
  251. Case When lower(@SortExpression) = 'lastname' and @SortDir = 'ASC' Then LastName End ASC,
  252. Case When lower(@SortExpression) = 'lastname' and @SortDir = 'DESC' Then LastName End DESC,
  253. Case When lower(@SortExpression) = 'firstname' and @SortDir = 'ASC' Then FirstName End ASC,
  254. Case When lower(@SortExpression) = 'firstname' and @SortDir = 'DESC' Then FirstName End DESC,
  255. Case When lower(@SortExpression) = 'email' and @SortDir = 'ASC' Then Email End ASC,
  256. Case When lower(@SortExpression) = 'email' and @SortDir = 'DESC' Then Email End DESC
  257.  
  258. SELECT e.*
  259. FROM @TempItems t
  260. INNER JOIN tbluser e ON
  261. t.userID = e.userID
  262. WHERE ID BETWEEN @StartRow AND @EndRow
  263. --(@startRowIndex * @maximumRows) AND (@startRowIndex + @maximumRows) - 1
  264. order by
  265. Case When lower(@SortExpression) = 'userid' and @SortDir = 'ASC' Then e.UserID End ASC,
  266. Case When lower(@SortExpression) = 'userid' and @SortDir = 'DESC' Then e.UserID End DESC,
  267. Case When lower(@SortExpression) = 'lastname' and @SortDir = 'ASC' Then LastName End ASC,
  268. Case When lower(@SortExpression) = 'lastname' and @SortDir = 'DESC' Then LastName End DESC,
  269. Case When lower(@SortExpression) = 'firstname' and @SortDir = 'ASC' Then FirstName End ASC,
  270. Case When lower(@SortExpression) = 'firstname' and @SortDir = 'DESC' Then FirstName End DESC,
  271. Case When lower(@SortExpression) = 'email' and @SortDir = 'ASC' Then Email End ASC,
  272. Case When lower(@SortExpression) = 'email' and @SortDir = 'DESC' Then Email End DESC
  273.  
  274. SELECT @RecordCount = COUNT(*) FROM tblUser

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.