Enable Sorting and Paging features of GridVew (bound to ObjectDataSource)

by Jagadish Pulakhandam on 9/14/2011 9:26:38 AM
Rated 0 from 0 votes
Brief: Demonstrates on enabling sorting/paging features of a GridVew, when it is bound to ObjectDataSource
Posted to: Data Binding using ObjectDataSource in ASP.NET
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Demonstrates the following:
  • Adding a business method which retrieves data in a sorted/paged manner (it is best to have this logic shifted to database as stored procedure)
  • Configuring ObjectDataSource with Paging and Sorting features
  • Configuring GridVew with Paging and Sorting features and binding it to ObjectDataSource
  • No code-behind for any of the above.

Screen shot:



Source Code:

01.using System;
02.using System.Collections.Generic;
03.using System.Linq;
04.using System.Text;
05.  
06.using System.Data;
07.using NorthwindDataAccess.Models;
08.namespace NorthwindDataAccess.Factories
09.{
10.    public class OrderFactory
11.    {
12.          
13.        public static List<Order> GetSortedPagedList(int StartIndex, int PageSize, string SortExpression)
14.        {
15.            if (string.IsNullOrEmpty(SortExpression)) SortExpression = "OrderID"; //defaulting sort to OrderID
16.  
17.            //It is best to include the following logic in a stored procedure (for great performance)
18.            //This logic is tweaked to have "StartIndex" starting with 0 as the first row index (PageIndex of GridView start at 0)
19.            StartIndex += 1;
20.            StringBuilder sb = new StringBuilder();
21.            sb.AppendLine("DECLARE @sql nvarchar(4000) ");
22.            sb.AppendLine("SET @sql = 'SELECT OrderID, CustomerID, EmployeeID, OrderDate, Freight, ShipCity, ShipCountry ");
23.            sb.AppendLine("            FROM ");
24.            sb.AppendLine("              (SELECT OrderID, CustomerID, EmployeeID, OrderDate, Freight, ShipCity, ShipCountry, ");
25.            sb.AppendLine("               ROW_NUMBER() OVER(ORDER BY " + SortExpression + ") as RowNum ");
26.            sb.AppendLine("               FROM Orders e ");
27.            sb.AppendLine("              ) as OrdersInfo ");
28.            sb.AppendLine("            WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), " + StartIndex + ") + ");
29.            sb.AppendLine("            ' AND (' + CONVERT(nvarchar(10), " + StartIndex + ") + ' + ' ");
30.            sb.AppendLine("            + CONVERT(nvarchar(10), " + PageSize +") + ') - 1'");
31.            sb.AppendLine("EXEC sp_executesql @sql");
32.  
33.            DataTable dtResult = DbHelper.GetResultSet(sb.ToString());
34.            return dtResult
35.                .AsEnumerable()
36.                .Select(dr => new Models.Order()
37.                {
38.                    OrderID = Convert.ToInt16(dr["OrderID"]),
39.                    CustomerID = dr["CustomerID"].ToString(),
40.                    EmployeeID = Convert.ToInt16(dr["EmployeeID"]),
41.                    OrderDate = Convert.ToDateTime(dr["OrderDate"]),
42.                    Freight = Convert.ToDouble(dr["Freight"]),
43.                    ShipCity = dr["ShipCity"].ToString(),
44.                    ShipCountry = dr["ShipCountry"].ToString()
45.                })
46.                .ToList();
47.        }
48.  
49.        public static int GetOrdersCount()
50.        {
51.            return Convert.ToInt16(DbHelper.GetValue("SELECT COUNT(*) FROM Orders"));
52.        }
53.    }
54.}
01.<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="NorthwindWebSite.WebForm1" %>
02.  
03.<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
04.  
05.<html xmlns="http://www.w3.org/1999/xhtml">
06.<head runat="server">
07.    <title></title>
08.</head>
09.<body>
10.    <form id="form1" runat="server">
11.    <div>
12.      
13.        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
14.            AllowSorting="True" DataSourceID="ObjectDataSource1" PageSize="20">
15.        </asp:GridView>
16.        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
17.            SelectMethod="GetSortedPagedList" 
18.            TypeName="NorthwindDataAccess.Factories.OrderFactory" EnablePaging="True" 
19.            MaximumRowsParameterName="PageSize" SelectCountMethod="GetOrdersCount" 
20.            SortParameterName="SortExpression" StartRowIndexParameterName="StartIndex">
21.        </asp:ObjectDataSource>
22.      
23.    </div>
24.    </form>
25.</body>
26.</html>

Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following

Video/Screen Recording (may not have audio narration/annotations)
You need to Login or Join for FREE to download the following