Paging & Sorting a GridView with EntityFramework

I've seen many different ways of paging and sorting data in a GridView or ListView, most of them seemed either cumbersome or over complicated. I've used standard SQL data access methods and stored procedures in the past, but I wanted to use the Entity Framework as an example. I came across one obstacle with the OrderBy clause, which I solved by using the LINQ Dynamic Query Library (System.Linq.Dyanmic). Here's a link to Scott Gu's blog that contains the examples and the download links.

I've used the AdventureWorks sample database, you get get a copy from here.  You'll need to add an ADO.NET Data Entity Model to your Web Application, hook it upto the AdventureWorks database and include the Views.

Here's my sample aspx page that contains a GridView that is data bound to an ObjectDataSource to handle the sorting and paging.  The ObjectDataSource connects to a method that returns a list of Customers.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="PagedSortableGridView._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>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ObjectDataSource runat="server" ID="odsCustomers" SelectMethod="GetCustomers" 
        TypeName="PagedSortableGridView._Default" EnablePaging="true" SortParameterName="sortParameter"/>
        <asp:GridView runat="server" DataSourceID="odsCustomers"
        AutoGenerateColumns="false"
        AllowPaging="true" 
        AllowSorting="true"
        GridLines="None"
        EnableSortingAndPagingCallbacks="true"> 
            <PagerSettings
             Visible="true"
             Position="TopAndBottom"
             PreviousPageText="Previous"
             NextPageText="Next"
             Mode="NextPreviousFirstLast" />
            <Columns>
                <asp:BoundField HeaderText="First Name" DataField="FirstName" SortExpression="FirstName" />
                <asp:BoundField HeaderText="Last Name" DataField="LastName" SortExpression="LastName" />
                <asp:BoundField HeaderText="Phone" DataField="Phone" SortExpression="Phone" />
                <asp:BoundField HeaderText="Email Address" DataField="EmailAddress" SortExpression="EmailAddress" />
                <asp:BoundField HeaderText="Address" DataField="AddressLine1" SortExpression="AddressLine1" />
                <asp:BoundField HeaderText="City" DataField="City" SortExpression="City" />
                <asp:BoundField HeaderText="State" DataField="StateProvinceName" SortExpression="StateProvinceName" />
                <asp:BoundField HeaderText="Postcode" DataField="PostalCode" SortExpression="PostalCode" />
                <asp:BoundField HeaderText="Country" DataField="CountryRegionName" SortExpression="CountryRegionName" />
            </Columns>
        </asp:GridView>      
    </div>
    </form>
</body>
</html>
Here's the code behind that contains the Select method used by the ObjectDataSource.


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

namespace PagedSortableGridView
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        public List GetCustomers(int startRowIndex, int maximumRows, string sortParameter)
        {
            maximumRows = 20;
            if (sortParameter == string.Empty) sortParameter = "LastName";
            startRowIndex = startRowIndex == 0 ? 1 : startRowIndex;
            using (AdventureWorksEntities db = new AdventureWorksEntities())
            {
                return (from c in db.vIndividualCustomer
                        select c)
                        .OrderBy(sortParameter)
                        .Skip(startRowIndex)
                        .Take(maximumRows).ToList();
            }
        }
    }
}

1 comments:

Anonymous said...

I cant see the implementation of sorting