Geeks With Blogs

News

Microsoft MVP


Moderator at CodeASP.NET


Quiz Master




free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

In this post we will take a  look at how to do pagination in GridView control. Paging is very helpful in presenting huge amount of data in the page because this helps speeding up the loading performance of the page and provides more friendly to end users in terms of data presentation.For this example, I’m going to highlight how to implement custom paging in GridView control using the power of LINQ and will show you some tips that would maximized the performance of the paged grid.

For those who are not familiar with LINQ then here’s a short overview: Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. For more details please read here.

To get started then let’s go ahead and fire up Visual Studio 2010 and then select new Web Application /WebSite project. Add a new page and then set up your page by adding a GridView and a Repeater control. The HTML mark up should look something like this:

    <h2>GridView Custom Paging with LINQ</h2>

    <asp:GridView ID="grdCustomer" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="Company" HeaderText="Company" />
            <asp:BoundField DataField="Name" HeaderText="Name" />
            <asp:BoundField DataField="Title" HeaderText="Title" />
            <asp:BoundField DataField="Address" HeaderText="Address" />
        </Columns>
    </asp:GridView>

    <asp:Repeater ID="rptPager" runat="server">
        <ItemTemplate>
            <asp:LinkButton ID="lnkPage" runat="server" 
                            Text='<%#Eval("Text") %>' 
                            CommandArgument='<%#Eval("Value") %>' 
                            Enabled='<%#Eval("Enabled") %>' 
                            OnClick="Page_Changed" 
                            ForeColor="#267CB2" 
                            Font-Bold="true" />
        </ItemTemplate>
    </asp:Repeater>

The HTML mark-up above consist of GridView and Repeater data representation controls. The GridView is where we display the list of customer information from the database. The Repeater will serve as our custom pager.

Note that in this example I used NorthWind.mdf as my database which you can download over here and used Entity Framework so that I can work on the conceptual model. I will not not elaborate more about the details on how to pull data from database using EF. If you are new to Entity Framework then you can take a look at my previous post that outlined the details about EF:

  1. Entity Framework – Inserting Data to Database
  2. Entity Framework – Fetching and Populating the data in the Form
  3. Entity Framework – Editing, Updating and Deleting data in the Form

Here’s the code behind for the whole stuff:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI.WebControls;

namespace WebFormsDemo
{

    public class Customer
    {
        public string Company { get; set; }
        public string Name { get; set; }
        public string Title { get; set; }
        public string Address { get; set; }
    }

    public partial class GridViewPagingWithLINQ : System.Web.UI.Page
    {
     
        private DB.NORTHWNDEntities northWindDB = new DB.NORTHWNDEntities();

        private List<Customer> GetCustomerEntity() {
            var customer = from c in northWindDB.Customers
                           select new Customer {
                               Company = c.CompanyName,
                               Name = c.ContactName,
                               Title = c.ContactTitle,
                               Address = c.Address
                           };

            return customer.ToList();
        }

        private void BindCustomerListGrid(int pageIndex) {

            int totalRecords = GetCustomerEntity().Count;
            int pageSize = 10;
            int startRow = pageIndex * pageSize;

            grdCustomer.DataSource = GetCustomerEntity().Skip(startRow).Take(pageSize);
            grdCustomer.DataBind();

            BindPager(totalRecords, pageIndex, pageSize);

        }

        private void BindPager(int totalRecordCount, int currentPageIndex, int pageSize) {
            double getPageCount = (double)((decimal)totalRecordCount / (decimal)pageSize);
            int pageCount = (int)Math.Ceiling(getPageCount);
            List<ListItem> pages = new List<ListItem>();
            if (pageCount > 1) {
                pages.Add(new ListItem("FIRST", "1", currentPageIndex > 1));
                for (int i = 1; i <= pageCount; i++) {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPageIndex + 1));
                }
                pages.Add(new ListItem("LAST", pageCount.ToString(), currentPageIndex < pageCount - 1));
            }

            rptPager.DataSource = pages;
            rptPager.DataBind();
        }

        protected void Page_Changed(object sender, EventArgs e) {
            int pageIndex = Convert.ToInt32(((sender as LinkButton).CommandArgument));
            BindCustomerListGrid(pageIndex - 1);
        }

        protected void Page_Load(object sender, EventArgs e) {
            if (!IsPostBack) {
                BindCustomerListGrid(0);
            }
        }
    }
}

The GetCustomerEntity() method is where we queried the data from the database using the LINQ syntax. The method returns a List of Customer. The BindCustomerListGrid() is the method in which we bind the data to the GridView. You noticed in that method that it uses the Take() and Skip() LINQ operators to chunk the data based on the page size that we passed in. The good thing about these operators is that it allows you to skip a certain number of rows, and only take a limited number of rows from that point. For example we set the page size to 10 which means that it will only select and display 10 records per page instead of selecting the entire results from the database which what the SqlDataSource is doing by default. The  BindPager() method is where we construct our pager based on the totalRecords and pageSize. The Page_Changed event handles the paging which basically sets what page the grid should display.

Running the code above will show something like below:

On initial load

paging1

 

After paging

paging2

Using firebug, you can see the number of milliseconds the page is rendered on initial page load and on actual paging. Now let’s improve the BindCustomerListGrid() method to speed up more our paging functionality using Application variable and Caching. Here’s the modified method below:

        private void BindCustomerListGrid(int pageIndex) {
            int totalRecords = 0;
            int pageSize = 10;
            int startRow = pageIndex * pageSize;

            if (Convert.ToInt32(Application["RowCount"]) == 0) {
                totalRecords = GetCustomerEntity().Count();
                Application["RowCount"] = totalRecords;
            }
            else {
                totalRecords = Convert.ToInt32(Application["RowCount"]);
            }

            List<Customer> customerList = new List<Customer>();

            if (Cache["CustomerList"] != null) {
                customerList = (List<Customer>)Cache["CustomerList"];
            }
            else {
                customerList = GetCustomerEntity();
                Cache.Insert("CustomerList", customerList, null, DateTime.Now.AddMinutes(3), TimeSpan.Zero);
            }

            grdCustomer.DataSource = customerList.Skip(startRow).Take(pageSize);
            grdCustomer.DataBind();

            BindPager(totalRecords, pageIndex, pageSize);
        }

As you can see we store the value of totalRecords on Application variable and use that on subsequent request. This will minimize database calls because we will not be calling GetCustomerEntity().Count() anymore and instead use the value stored in the Application variable. Another change we made is that we store the result set in a cache so that again we will not be hitting the database on each request. You set the value of the Application variable to 0 and set the Cache to null in the event where you do an update, insert or delete.

Here’s the output below when running the code:

On initial load

paging3

After paging

paging4 

As you notice there’s a big change on the performance of page loading time and on subsequent request. That’s it. I hope someone find this post useful!

Posted on Friday, August 10, 2012 12:22 AM | Back to top


Comments on this post: GridView Custom Paging with LINQ

# re: GridView Custom Paging with LINQ
Requesting Gravatar...
Hello
thank you for good explanation
Left by Kemal on Oct 16, 2012 8:29 PM

# re: GridView Custom Paging with LINQ
Requesting Gravatar...
Nice Blog Its very useful information. Dapfor provide a net grid which is very useful. you can read a tutorial from dapfor. com
Left by Net Grid on Nov 17, 2012 3:03 PM

# re: GridView Custom Paging with LINQ
Requesting Gravatar...
Great !
This is very helpfull to minimise the execution time when we deal with a gridview getting a lot of data rows
Left by kamel Gazzah on Dec 05, 2012 4:21 PM

# re: GridView Custom Paging with LINQ
Requesting Gravatar...
Your tutorial is excellent but i think you are missing something on asp.net point of view. I mean OnClick event not supported for linkbutton in repeater itemTemplate.Thats why you can fire your event this way :

<asp:Repeater ID="rptPager" runat="server" onitemcommand="rptPage_ItemCommand">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text")%>' CommandArgument='<%#Eval("Value")%>'
Enabled='<%#Eval("Enabled")%>' ForeColor="#267CB2" Font-Bold="true" CommandName="pagechange" />
</ItemTemplate>
</asp:Repeater>

and Code Behind Just add this :

protected void rptPage_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "pagechange")
{
int pageIndex = Convert.ToInt32((((LinkButton)e.CommandSource).CommandArgument));
BindCustomerListGrid(pageIndex - 1);

}
}
Left by Humayoun Kabir on Jan 10, 2013 6:14 PM

# re: GridView Custom Paging with LINQ
Requesting Gravatar...
@Humayoun Kabir,

Your solution will work too but just so you know LinkButton does OnClick server event: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.linkbutton.onclick.aspx
Left by Vinz on Jan 10, 2013 8:53 PM

# re: GridView Custom Paging with LINQ
Requesting Gravatar...
I think you misunderstood me.Onclick event works for linkbutton i know that but it won't work under a repeater control.Thats why i gave that solution.I tried your code for test purpose but it didn't work properly until i correct that with my given code.That's why i gave a solution.You can get many link on google by check "asp.net repeater linkbutton onclick event don't fire".....
Left by Humayoun Kabir on Jan 12, 2013 5:33 PM

# re: GridView Custom Paging with LINQ
Requesting Gravatar...
@humayoun

Im not sure if that would be the case. Click event does fire up even if its inside repeater or any data represetation control. All the codes and examples in my blog are tested first before i post it as public. Maybe there is something missing like wiring up the event handler or something that is why it wont work. Ive used that numerous times and i have no problem. Linkbuttons onclick event will fire inside repeater. If u see in the screenshots of the examples it does change the page and triggers the event. Anyway thank you for providing the another way.
Left by vinz on Jan 12, 2013 9:05 PM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net | Join free