Paging is perhaps one of the most required in data presentation, specially when it comes to huge amount of data, normal paging becomes nightmare. If you are developing your application using ASP.NET 2.0, then you can make use of ObjectDataSource in a very efficient manner to achieve paging.
The code which I am providing below will give you a complete understanding of how you can do this, this technique will fetch one the number of data which you set in PageSIze of GridView from the DataBase, instead of fetching entire data and on every PageIndexChanged event repeating the full cycle again,
First you need to either modify or write your procedure the input and output parameters which will fit into your .NET code to get the paging.
create procedure proc_EmployeeDetails
@empId int,
@empStatus varchar(10),
@pagestart int = null,
@pagesize int = null,
@numresults int output
as
create table #empresults
(
[rowid] [int] IDENTITY (1, 1) NOT NULL,
[empID] [nchar] (5) ,
[EmpName] [nvarchar] (30),
[Address] [varchar] (200),
[DOB] [datetime],
[Age] [int],
)
insert into #empresults (empid, empname, address, DOB, Age)
select empID, EmpName, Address, DOB, Age
where
empid = @empId AND empStatus = @empStatus
order by EmpName
set @numresults = @@rowcount
if @pagesize is null
set @pagesize = @numresults
if @pagestart is null
set @pagestart = 1
set rowcount @pagesize
select *
from #empresults
where rowid >= @pagestart
drop table #tempresults
Next I create a Employee class which will hold the results from the database.
public class EmployeeCollection : IList<Employee> { }
public class Employee
{
private int _empId;
private string _empName;
private DateTime? _DOB;
private int _age;
private string _address;
public Employee() { }
public int EmployeeId
{
get { return _empId; }
set { _empId = value; }
}
public string EmployeeName
{
get { return _empName; }
set { _empName = value; }
}
public DateTime? DOB
{
get { return _DOB; }
set { _DOB = value; }
}
public int Age
{
get { return _age; }
set { _age = value; }
}
public string Address
{
get { return _address; }
set { _address = value; }
}
}
Now as we have got the BusinessObjects, it time to create a class specifically designed to handle request from ObjectDataSource. This class you can keep in your app_code directory of web project and is used like facade layer between UI layer and Business Layer.
public class EmployeeDataSource
{
public EmployeeDataSource() { }
public int SelectCount(int empId, string employeeStatus, ObjectDataSourceSelectingEventArgs e)
{
return e.Arguments.TotalRowCount;
}
public EmployeeCollection Select(int empId, string employeeStatus, int maximumRows, int startRowIndex, ObjectDataSourceSelectingEventArgs e)
{
using (SqlConnection connection = new SqlConnection("Initial Catalog=Employee;Integrated Security=SSPI;Data Source=."))
using (SqlCommand command = new SqlCommand("proc_EmployeeDetails", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@empId", empId);
command.Parameters.AddWithValue("@empStatus", employeeStatus);
command.Parameters.AddWithValue("@pagestart", startRowIndex);
command.Parameters.AddWithValue("@pagesize", maximumRows);
command.Parameters.Add(new SqlParameter("@numresults", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Default, 0));
EmployeeCollection employees = new EmployeeCollection();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Employee emp = new Employee();
emp.EmployeeId = reader.GetInt32("empId");
emp.EmployeeName = reader.GetString("empname");
emp.DOB = reader.GetDateTime("DOB");
emp.Age = reader.GetInt32("Age");
emp.Address = reader.GetString("Address");
employees.Add(emp);
}
}
e.Arguments.TotalRowCount = (int)command.Parameters["@numresults"].Value;
return employees;
}
}
Now I am adding code to the code behind of the EmployeeDetails.aspx page.
protected void objectDataSourceOrders_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
if (!e.ExecutingSelectCount)
{
e.Arguments.MaximumRows = this.gridViewEmployees.PageSize;
e.InputParameters.Add("e", e);
}
}Here is the ASPX page code that goes along with the rest of this example
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="ddlEmpStatus" runat="server">
<asp:ListItem Text="Active" Value="Active" Selected="True"></asp:ListItem>
<asp:ListItem Text="Disabled" Value="Disabled"></asp:ListItem>
</asp:DropDownList>
</div>
<div>
<asp:GridView ID="gridViewEmployees" runat="server" AllowPaging="True" AutoGenerateColumns="False"
CellPadding="2" DataSourceID="objectDataSourceEmployee" ForeColor="Black" GridLines="None"
BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px">
<FooterStyle BackColor="Tan" />
<Columns>
<asp:BoundField DataField="EmployeeId" HeaderText="ProductId" SortExpression="ProductId" />
<asp:BoundField DataField="EmployeeName" HeaderText="ProductName" SortExpression="ProductName" />
<asp:BoundField DataField="DOB" HeaderText="UnitPrice" SortExpression="UnitPrice" />
<asp:BoundField DataField="Age" HeaderText="CustomerId"
SortExpression="CustomerId" />
<asp:BoundField DataField="Address" HeaderText="OrderId" SortExpression="OrderId" />
</Columns>
<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
</asp:GridView>
<asp:ObjectDataSource ID="objectDataSourceEmployee" runat="server" EnablePaging="True"
SelectMethod="Select" TypeName="EmployeeDataSource" OnSelecting="objectDataSourceOrders_Selecting" SelectCountMethod="SelectCount">
<SelectParameters >
<asp:QueryStringParameter QueryStringField="empid" DefaultValue="0" Name="empId" />
<asp:ControlParameter ControlID="ddlEmpStatus" DefaultValue="Active" Name="employeeStatus" PropertyName="Value" />
</SelectParameters>
</asp:ObjectDataSource></div>
</form>
</body>
In the aspx page I am passing the Query string and dropdown control value as a parameter, and Binding the result to the GridView.
The code above worked for me, I hope you too find this code useful.
Thanks
~Brij