jQuery – MSSQL Pagination and Data Templates

Here is a tutorial to show you how to paginate JSON data in the client using jQuery, jQuery Templates plugin, ASP.NET and SQL Server.

For this tutorial, you will need:

From the Chinook archive, copy

to your MSSQL Data folder.

i.e., C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Run the CreateSqlServer.bat

Open up Visual Studio | Server Explorer (Ctrl+Alt+S)

Right Click on “Data Connections” and select “Add Connection…”

For the Data source, select “Microsoft SQL Server”

Select the SQL Server Name. i.e., [COMPUTER NAME]\SQLEXPRESS

Connect to a database, Select the Chinook database

Create the sp_ArtistsByPage.sql stored procedure:

In Visual Studio, Create a new project (Ctrl+Shift+N)

Select ASP.NET Empty Web Application (This template is available when .NET Framework4 is selected)

Add a database Connection String in the web.config similar to this one. Note: VGN-FW290 is the name of my computer. Replace this with yours obviously. Tip: in Server Explorer, Expand the Data Connections node and right-click on the Chinook connection. Select properties(F4) and copy the Connection String.

	<add name="ChinookConnection" connectionString="Data Source=VGN-FW290\SQLEXPRESS;Initial Catalog=Chinook;Integrated Security=True" providerName="System.Data.SqlClient"/>

Copy the jQuery .js file to the Scripts folder of the application. Then right click on the Scripts folder and select add existing item (Alt+Shift+A). Browse to the folder you just copied jQuery to and select it so it is part of the project.

Repeat the process for the jQuery Pager and Templates plugins, (jquery.pager.js and jquery.tmpl.min.js). Use either the uncompressed or minified (*.min.js) versions.

Drag the jQuery library Template plugin files from the Solution Explorer into the Default.aspx source to the line just before the closing </form> tag.

Add the following css to \Styles\screen.css, add it to the project and drag into the source before the closing </head> tag.

	font-family: Arial, Sans-Serif;
a {color:#369;}
a:hover {
a.disabled {color:#ADADAD;}
a.disabled:hover {
h1, h2, h3 {
	margin:.8em 0 .2em 0;
p {
	margin:.4em 0 .8em 0;
img {
#artists {position: relative; width:700px;}
#artistTable {border-collapse:collapse; margin-top:10px; clear:both; }
#artistList tr.highlight {background-color: #FDF5CE;} 
#artistList td {padding: 2px 5px 2px 5px;} 
#loading {
	width: 24px;
	background-image: url('../Styles/ajax-loader.gif');
#pager ul.pages
	display: block;
	border: none;
	text-transform: uppercase;
	font-size: 10px;
	padding: 0;
#pager ul.pages li
	list-style: none;
	float: left;
	border: 1px solid #888;
	text-decoration: none;
	margin: 0 5px 0 0;
	padding: 5px;
	background-color: #FFF;
#pager ul.pages li:hover
	border: 1px solid #00376F;
#pager ul.pages li.pgEmpty
	border: 1px solid #CCC;
	color: #AAA;
	cursor: default;
#pager ul.pages li.pgCurrent
	border: 1px solid #003F7E;
	color: #042CCA;
	font-weight: 700;
	background-color: #dadada;
    position: absolute;
    top: -10px;
    right: 0;
#pageSize label 
	padding: 0 2px 0 0;
#pageSize select {width:50px;}

Default.aspx should now look something like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="PaginationSQL._Default" %>

<!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">
	<link href="Styles/screen.css" rel="stylesheet" type="text/css" />
	<form id="form1" runat="server">
	<div> </div>
	<script src="Scripts/jquery-1.4.4.min.js" type="text/javascript"></script>
	<script src="Scripts/jquery.pager.js" type="text/javascript"></script>
	<script src="Scripts/jquery.tmpl.js" type="text/javascript"></script>
Solution Explorer

Add a Generic Handler to the project and name it Json.ashx. Go to www.preloaders.net and download a preloader animation. Copy it to the Styles folder and add it to the project. Your Solution Explorer tree should look something like this one shown at left.

C# code for the handler

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;
using System.Web.Configuration;
using System.Web.Script.Serialization;

namespace PaginationSQL
    /// Summary description for Json
    public class Json : IHttpHandler
        private string ChinookConnection = WebConfigurationManager.ConnectionStrings["ChinookConnection"].ConnectionString;
        private string data = "";
        private int page_number = 1;
        private int page_size = 25;
        private int total_count = 0;

        public void ProcessRequest(HttpContext context)
            page_size = Convert.ToInt32(context.Request.QueryString["page_size"]);
            page_number = Convert.ToInt32(context.Request.QueryString["page_number"]);
            StringBuilder json = new StringBuilder();

            string artistId = "";
            string name = "";

            DataSet artist = GetArtistsByPage(page_size, page_number);
            foreach (DataRow dr in artist.Tables[0].Rows)
                artistId = dr["ArtistId"].ToString();
                name = JavaScriptStringEncode(dr["Name"].ToString());

                json.Append("\"artistId\":\"" + artistId + "\",");
                json.Append("\"artistName\":\"" + name + "\"");

                total_count = Convert.ToInt32(dr["TotalCount"]);
            data = "{\"items\":[";
            if (json.Length != 0)
                //remove trailing comma since we can't determine if last record during the append
                //data += "[" + json.ToString(0, json.Length - 1) + "]";
                data += json.ToString(0, json.Length - 1);
            data += "],\"page_number\":" + page_number + ",\"page_size\":" + page_size + ",\"total_count\":" + total_count + "}";

            context.Response.ContentType = "application/json;charset=utf-8";

        public bool IsReusable
                return false;

        //if your app has more data access methods, move this into a
        //data access layer class, such as Dal.cs
        public DataSet GetArtistsByPage(int pageSize, int pageNumber)
            SqlConnection con = new SqlConnection(ChinookConnection);
            SqlCommand cmd = new SqlCommand("ch_ArtistsByPage", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@pageSize", pageSize);
            cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "Artists");
            return ds;

        //if your app works with json elsewhere, move this into
        //another class - perhaps Utils.cs
        public string JavaScriptStringEncode(string message)
            if (String.IsNullOrEmpty(message))
                return message;
            StringBuilder builder = new StringBuilder();
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            serializer.Serialize(message, builder);
            return builder.ToString(1, builder.Length - 2); // remove first + last quote


Finally, add this template and jQuery page specific javascript at the bottom of the page above the closing </form> tag. Press F5 to test the web application.

<script id="artistTmpl" type="text/x-jquery-tmpl">
		<td align="right" valign="top">${artistId}</td>
<script type="text/javascript">
	$(document).ready(function () {


		var pageIndex = 1, pageSize = 10, pageCount = 0;
		$("#pageSize select").change(function () {
			pageIndex = 1
			pageSize = $(this).val();

		function getArtists(index) {
			var query = "Json.ashx?page_number=" + index + "&page_size=" + pageSize;
			pageIndex = index;
			.fadeOut("medium", function () {
					dataType: "json",
					url: query,
					jsonp: "$callback",
					success: showArtists

		function showArtists(data) {
			pageCount = Math.ceil(data.total_count / pageSize),
			artists = data.items;
			$("#pager").pager({ pagenumber: pageIndex, pagecount: pageCount, buttonClickCallback: getArtists });
			$("#artistList tr").hover(
				function () {
				function () {


This project is available for browsing and download at GitHub:
[PaginationSQL @GitHub]

Published by

Jim Frenette

Web Developer - views here are my own except those taken from people more clever than me.