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:

e.g., 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. e.g., [COMPUTER NAME]\SQLEXPRESS

Connect to a database, Select the Chinook database

Create the sp_ArtistsByPage.sql stored procedure:

CREATE PROCEDURE [dbo].[ch_ArtistsByPage]
(
	@pageSize INT,
	@pageNumber INT
)
AS

DECLARE
	@firstRow INT,
	@lastRow INT

SELECT	@firstRow = (@pageNumber - 1) * @pageSize + 1,
	@lastRow = (@pageNumber - 1) * @pageSize + @pageSize ;

WITH Artist  AS
(
	SELECT ROW_NUMBER() OVER (ORDER BY Name ASC) AS RowNumber,
	COUNT(*) OVER () AS TotalCount, * FROM dbo.Artist
)
SELECT	* FROM	Artist
	WHERE RowNumber BETWEEN @firstRow AND @lastRow

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.

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

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.

body
{
    margin:5px;
    background:#FFF;
    font-family: Arial, Sans-Serif;
    font-size:90%;
}
a {color:#369;}
a.active
{
    color:#000;
    text-decoration:none;
}
a:hover {
    color:#FFF;
    background:#369;
    text-decoration:none;
}
a.disabled {color:#ADADAD;}
a.disabled:hover {
    color:#ADADAD;
    background:none;
}
h1, h2, h3 {
    margin:.8em 0 .2em 0;
    padding:0;
}
p {
    margin:.4em 0 .8em 0;
    padding:0;
}
img {
    margin:0;
    border:0;
}
#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;
    height: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;
}
#pageSize
{
    position: absolute;
    top: -10px;
    right: 0;
    width:80px;
    text-align:right;
}
#pageSize label
{
    font-size:10px;
    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">
    <title></title>
    <link href="Styles/screen.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <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>
    </form>
</body>
</html>

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 above.

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("{");
                json.Append("\"artistId\":\"" + artistId + "\",");
                json.Append("\"artistName\":\"" + name + "\"");
                json.Append("},");

                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.Clear();
            context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            context.Response.ContentType = "application/json;charset=utf-8";
            context.Response.Flush();
            context.Response.Write(data);
        }

        public bool IsReusable
        {
            get
            {
                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">
    <tr>
        <td align="right" valign="top">${artistId}</td>
        <td>${artistName}</td>
    </tr>
</script>
<script type="text/javascript">
    $(document).ready(function () {

        $("#loading").hide();

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

        function getArtists(index) {
            var query = "Json.ashx?page_number=" + index + "&page_size=" + pageSize;
            pageIndex = index;
            $("#artistList")
            .fadeOut("medium", function () {
                $("#loading").show()
                $.ajax({
                    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").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>

This project is available for browsing and download at GitHub:

Source Code
comments powered by Disqus