Custom Paging improves the performance of default paging by retrieving only those records from the database that need to be displayed for the particular page of data requested by the user.
Let see how to create custom paging in gridview..
Step 1
First create a database and table.
1: CREATE DATABASE A
2:
3: USE A
4:
5: CREATE TABLE StudentA
6: (
7: StudentID int identity(1,1) primary key,
8: FirstName nvarchar(50),
9: LastName nvarchar(50),
10: )
Step 2
we can implement the stored procedure that fetches the records only for that page by accepting startRowIndex and maximumRows as parameters.
1: CREATE Procedure dbo.Sp_Paging
2: (
3: @StartRowIndex NUMERIC(18,0),
4: @MaximumRows NUMERIC(18,0)
5: )
6: AS
7: BEGIN
8:
9: DECLARE @StartRow NUMERIC(18,0)
10: DECLARE @EndRow NUMERIC(18,0)
11:
12: SET @StartRow=(@StartRowIndex+1)
13: SET @EndRow=@StartRow+@MaximumRows;
14:
15: -- Create a temp result set with row number
16: WITH TempStudent AS
17: (SELECT *,ROW_NUMBER() OVER(ORDER BY StudentID) AS 'RowNumber' FROM StudentA)
18:
19: -- fetch data from temp result set
20: SELECT StudentID,FirstName,LastName FROM TempStudent
21: WHERE RowNumber BETWEEN @StartRow AND @EndRow
22:
23:
24:
25: END
Step 3
First we create a class in app_code folder for fetching data from database.
The following code snippet will have two methods called GetStudentData() and GetRowsCount().The GetStudentData() method will take two parameters called startRowIndex and maximumRows to fetch the data from database and GetRowsCount() will used for getting number of rows count.
1: public class Student
2: {
3: #region Declaration
4:
5: private SqlConnection SqlCon = null;
6: private SqlCommand SqlComm = null;
7: private SqlDataAdapter SqlDa = null;
8: private DataTable Table = null;
9:
10: #endregion
11:
12: #region Constructor
13:
14: public Student()
15: { }
16:
17: #endregion
18:
19: #region Methods
20:
21: public DataTable GetStudentData(long StartRowIndex, long MaximumRows)
22: {
23: try
24: {
25: SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.ToString().Trim());
26: SqlCon.Open();
27:
28: SqlComm = new SqlCommand();
29: SqlComm.Connection = SqlCon;
30: SqlComm.Parameters.Add("@StartRowIndex", SqlDbType.BigInt).Value = StartRowIndex;
31: SqlComm.Parameters.Add("@MaximumRows", SqlDbType.BigInt).Value = MaximumRows;
32:
33: SqlComm.CommandType = CommandType.StoredProcedure;
34: SqlComm.CommandText = "Sp_Paging";
35:
36: Table=new DataTable();
37: SqlDa = new SqlDataAdapter(SqlComm);
38: SqlDa.Fill(Table);
39:
40: }
41: catch (Exception ex)
42: {
43: throw new Exception(ex.Message);
44: }
45:
46: return Table;
47: }
48:
49: public int ? GetRowsCount()
50: {
51: int ? RowsCount=null;
52: try
53: {
54: SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.ToString().Trim());
55: SqlCon.Open();
56:
57: SqlComm = new SqlCommand();
58: SqlComm.Connection = SqlCon;
59:
60: SqlComm.CommandType = CommandType.Text;
61: SqlComm.CommandText = "SELECT COUNT(*) as 'RowCount' FROM StudentA";
62: try
63: {
64: RowsCount = (int?)SqlComm.ExecuteScalar();
65: }
66: catch (Exception)
67: {
68: RowsCount = 0;
69: }
70:
71: }
72: catch (Exception ex)
73: {
74: throw new Exception(ex.Message);
75: }
76:
77: return RowsCount;
78: }
79:
80: #endregion
81: }
Step 4
now,we can configure the ObjectDataSource control with these methods.it is look like this
1: <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="Student" SelectCountMethod="GetRowsCount" SelectMethod="GetStudentData"
2: EnablePaging="true" StartRowIndexParameterName="StartRowIndex" MaximumRowsParameterName="MaximumRows">
3: </asp:ObjectDataSource>
Configuring ObjectDataSource Control for Custom Paging
1.Type Name - This property needs to be configured with the class that has the implementation of SelectCountMethod and SelectMethod.
2.EnablePaging - This property accepts a Boolean to enable paging with ObjectDataSource control.
3.SelectCountMethod - We need to specify the name of the method that can fetch the total number of records available in the database.
4.SelectMethod - This property will accept the name of the method that fetches actual database record.
5.StartRowIndexParameterName - This property will accept the start row index of the record to fetch from database.
6.MaximumRowsParameterName - This property will accept the maximum number of rows that can be fetched at one time. This will be equivalent to page size.
Step 5
Finally, we will configure the GridView control, it is look like this
1: <asp:GridView ID="gvStudent" runat="server" AllowPaging="True"
2: AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333"
3: GridLines="None" PageSize="3" DataSourceID="ObjectDataSource1">
4: <RowStyle BackColor="#EFF3FB" />
5: <Columns>
6: <asp:BoundField DataField="StudentID" HeaderText="StudentID" />
7: <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
8: <asp:BoundField DataField="LastName" HeaderText="LastName" />
9: </Columns>
10: <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
11: <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
12: <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
13: <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
14: <EditRowStyle BackColor="#2461BF" />
15: <AlternatingRowStyle BackColor="White" />
16: </asp:GridView>
* In pageSize property set the number of rows for display in gridview.
* In DataSourceID property bind the ObjectDatasource ObjectName.
Full .aspx page code
1: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
2:
3: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4:
5: <html xmlns="http://www.w3.org/1999/xhtml">
6: <head runat="server">
7: <title>Untitled Page</title>
8: </head>
9: <body>
10: <form id="form1" runat="server">
11: <div>
12:
13: <asp:ScriptManager ID="ScriptManager1" runat="server">
14: </asp:ScriptManager>
15:
16: <asp:UpdatePanel ID="UpdatePanel" runat="server">
17: <ContentTemplate>
18: <asp:GridView ID="gvStudent" runat="server" AllowPaging="True"
19: AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333"
20: GridLines="None" PageSize="3" DataSourceID="ObjectDataSource1">
21: <RowStyle BackColor="#EFF3FB" />
22: <Columns>
23: <asp:BoundField DataField="StudentID" HeaderText="StudentID" />
24: <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
25: <asp:BoundField DataField="LastName" HeaderText="LastName" />
26: </Columns>
27: <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
28: <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
29: <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
30: <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
31: <EditRowStyle BackColor="#2461BF" />
32: <AlternatingRowStyle BackColor="White" />
33: </asp:GridView>
34:
35: <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="Student" SelectCountMethod="GetRowsCount" SelectMethod="GetStudentData"
36: EnablePaging="true" StartRowIndexParameterName="StartRowIndex" MaximumRowsParameterName="MaximumRows">
37: </asp:ObjectDataSource>
38: </ContentTemplate>
39: </asp:UpdatePanel>
40:
41: </div>
42: </form>
43: </body>
44: </html>
Run the Project.
Download
Download Source Code
No comments:
Post a Comment