Paging a GridView with ObjectDataSource

by Jagadish Pulakhandam on 9/14/2011 8:23:38 AM
Rated 0 from 0 votes
Brief: Demonstrates on binding a GridView to ObjectDataSource and adding paging support to it.
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:
  • Creating a business method which accepts paging parameters and return only that chunk of data database (can be further optimized with stored procedures, but making it simple here).
  • Configuring ObjectDataSource to paging methods (business logic)
  • Binding a GridView to ObjectDataSource
  • Enabling paging in GridView
  • All of the above, without writing any code-behind

 

Source Code:

01.using System;
02.using System.Collections.Generic;
03.using System.Linq;
04.using System.Text;
05.  
06.namespace NorthwindDataAccess.Models
07.{
08.    public class Order
09.    {
10.        public int OrderID { get; set; }
11.        public string CustomerID { get; set; }
12.        public int EmployeeID { get; set; }
13.        public DateTime OrderDate { get; set; }
14.        public double Freight { get; set; }
15.        public string ShipCity { get; set; }
16.        public string ShipCountry { get; set; }
17.    }
18.}
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> GetPagedList(int StartIndex, int PageSize)
14.        {
15.            //It is best to include the following logic in a stored procedure (for great performance)
16.            //This logic is tweaked to have "StartIndex" starting with 0 as the first row index (PageIndex of GridView start at 0)
17.            StringBuilder sb = new StringBuilder();
18.            sb.AppendLine("DECLARE @startRowIndex INT, @maximumRows INT");
19.            sb.AppendLine("SELECT @startRowIndex = " + StartIndex.ToString());
20.            sb.AppendLine("SELECT @maximumRows = " + PageSize.ToString());
21.            sb.AppendLine("IF @startRowIndex = 0");
22.            sb.AppendLine("     SELECT @startRowIndex = 1");
23.            sb.AppendLine("ELSE");
24.            sb.AppendLine("     SELECT @startRowIndex = @startRowIndex + 1");
25.            sb.AppendLine("DECLARE @first_id INT, @startRow INT");
26.            sb.AppendLine("SET ROWCOUNT @startRowIndex");
27.            sb.AppendLine("SELECT @first_id = OrderID FROM Orders ORDER BY OrderID");
28.            sb.AppendLine("SET ROWCOUNT @maximumRows");
29.            sb.AppendLine("SELECT OrderID, CustomerID, EmployeeID, OrderDate, Freight, ShipCity, ShipCountry FROM Orders ");
30.            sb.AppendLine("WHERE OrderID >= @first_id ORDER BY OrderID");
31.            sb.AppendLine("SET ROWCOUNT 0");
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.        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
13.            DataSourceID="ObjectDataSource1" PageSize="20">
14.        </asp:GridView>
15.        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
16.            SelectMethod="GetPagedList" 
17.            TypeName="NorthwindDataAccess.Factories.OrderFactory" EnablePaging="True" 
18.            MaximumRowsParameterName="PageSize" SelectCountMethod="GetOrdersCount" 
19.            StartRowIndexParameterName="StartIndex">
20.        </asp:ObjectDataSource>
21.    </div>
22.    </form>
23.</body>
24.</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