An Efficient Generic Pager Control using jQuery/jTemplate

• Download source - 26.48 KB

Introduction

I developed this Pager control with the following features:

1. It can work with any ASP.NET databound controls like Repeater, GridView, etc.
2. It's very scalable, efficient and DB friendly.
3. It's very easy to implement & understand.

Here's the Main Idea

First the DB part of this control. All the trick lies in here. I used a Repeater control as the databound control on which my pager will work. But it will work on any databound control. As an example, the query output which binds this repeater control appears as below:

The repeater control displays 5 records per page. Currently this "number-of-records" is hardcoded in the logic. The AddressID field is numeric and is the primary key. Basically, I store just the first AddressId for each page, on the client-side.

Hence, for the above query, I would save the following beginning AddressIDs at the client end.

AddressID = 859 (Page# 1)
AddressID = 15460 (Page# 2)
AddressID = 20017 (Page# 3)
AddressID = 25816 (Page# 4)
AddressID = 27652 (Page# 5)

Now, when the page index is changed by either Next/Previous button click OR by the dropdownlist selection change -- a jQuery-AJAX fires a query on the DB, passing the starting AddressID. It retrieves 5 records starting with the supplied AddressID. Here's an example:

[WebMethod]
public static List<RepeaterData> getRepeaterData(int nStartAddressID)
{
    List<RepeaterData> _RepeaterData = new List<RepeaterData>();
    SqlDataReader reader = null;
    DataTable dt = new DataTable();
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = ConfigurationManager.ConnectionStrings
				["ConnStr2"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand())
        {
            //Instead of Dynamic SQL , please use StoredProc for better design.
            //The SqlParameter will protect against any SQL injection 
            //as well as the strong int data-type for the variable nStartAddressID.
            cmd.CommandText = "SELECT TOP (5) AddressID, AddressLine1, 
				City, PostalCode " +
                              "FROM [AdventureWorks].[Person].[Address] " +
                              "WHERE AddressLine1 like '105%' " +
                              "and AddressID >= @AddressID " +
                              "ORDER BY AddressID";
            cmd.Parameters.Add("@AddressID", SqlDbType.Int).Value = nStartAddressID;
            cmd.Connection = conn;
            StringBuilder sb = new StringBuilder();
            conn.Open();
            reader = cmd.ExecuteReader();
            dt.Load(reader);
            conn.Close();
        }
    }
    foreach (DataRow dr in dt.Rows)
    {
        RepeaterData lp = new RepeaterData();
        lp.AddressID = int.Parse(dr["AddressID"].ToString());
        lp.AddressLine1 = dr["AddressLine1"].ToString();
        lp.City = dr["City"].ToString();
        lp.PostalCode = dr["PostalCode"].ToString();
        _RepeaterData.Add(lp);
    }
    return _RepeaterData;
}

In this example, I have used Dynamic SQL, but please try to use StoredProc instead, for better design. In this case, the SqlParameter will protect against any SQL injection. Please note that the variable nStartAddressID is an int which again protects from various injection attacks.

One thing to notice here is -- on the first PageLoad, I bind the Repeater, Dropdownlist, set the JavaScript variables from codebehind. Once the page is loaded, all actions are through jQuery AJAX using jTemplate. No postbacks.
Since the Repeater does not emit any HTML of its own, the jTemplate easily replaces the Repeater's HTML, after the AJAX call is done.

On the initial first pageLoad, I used a little different query to get the first 5 rows.

SELECT ROW_NUMBER () OVER (ORDER BY AddressID) AS RowNumber, _
AddressID, AddressLine1, City, PostalCode 
FROM [AdventureWorks].[Person].[Address] where AddressLine1 _
like '105%' ORDER BY AddressID;   

You may please use StoredProc with SqlParameter to make it safe against Injection attacks. Here I am just trying to demonstrate the concept.

As you can see, I got the rownumber in my DataView & used that to grab the first top 5 records. However on any subsequent paging action, I used the starting addressID to get relevant 5 records. This is what I mean.

At the client end -- this is how the pagenumber & corresponding starting AddressIDs are stored on initial pageLoad.

var js1=[{"PagerNums":1,"AddressID":859},
{"PagerNums":2,"AddressID":15460},{"PagerNums":3,"AddressID":20017},
{"PagerNums":4,"AddressID":25816},{"PagerNums":5,"AddressID":27652}] 

As you can see, I just need to keep track on which pagenumber, the page is currently on. So when either the Prev/Next button is clicked OR the dropdown selection is changed, I figure out the starting AddressID from the current page number & supply that AddressID as a parameter to the AJAX DB call.

function UpdatePrevNextClick() {
    $.ajax({
        type: "POST",
        url: "PagerRepeater4.aspx/getRepeaterData",
        data: "{ 'nStartAddressID': '" + CurAddrID + "' }",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (data) {
            $('#RptPager').setTemplateURL('JTemplates/PagerTemplate.htm');
            $('#RptPager').processTemplate(data.d);
            HidePrevNextButton();
            UpdateDDL();
        }
    });
}

The paging action primarily depends on what is the current page number, the databound control is on. There are 3 triggers for paging -- namely -- Next button click, Previous button click OR selecting the page number from the dropdownlist.

Let's look into details when user selects any page number from the dropdownlist.

The dropdownlist in the pager contains all unique page numbers.
So when the user selects any desired page number, I figure out the corresponding start-AddressID from the JS array stored in the client.

Here's the code for that:

<script type="text/javascript">
        var Totalpages = 0;
        $(document).ready(function () {
            var dropDownList1 = $('select[id$=ddl1]');
            Totalpages = dropDownList1[0].length;
            $('.b1').html(dropDownList1[0].length);
            HidePrevNextButton();

            dropDownList1.change(function (e) {
                CurPageNum = this.value;
                CurAddrID = js1[CurPageNum-1].AddressID;
                UpdatePrevNextClick();
            });
        });
    </script>

Once I obtain the start Address ID, I fire the Query asynchronously through jQuery AJAX, as stated above. When the relevant section of the page is refreshed, I always verify if it is necessary to show/hide Prev/Next button. Prev button gets hidden when the first page is loaded and similarly Next button gets hidden when the last page gets hidden.

That's all about it. Thanks for reading.

History

• 2011-05-05: Version 1