ASP.NET GridView Tutorial

Implement custom paging in an asp.net gridview without using datasource controls – Part 54

Link for csharp, asp.net, ado.net, dotnet basics and sql server video tutorial playlists
http://www.youtube.com/user/kudvenkat/playlists

Link for text version of this video
http://csharp-video-tutorials.blogspot.com/2013/04/implement-custom-paging-in-aspnet_11.html

Step 1: Create a stored procedure to retrieve employees by page index and page size. This procedure also has an output parameter to return the total

number of rows in the database table.
Create proc spGetEmployees_by_PageIndex_and_PageSize
@PageIndex int,
@PageSize int,
@TotalRows int output
as
Begin
Declare @StartRowIndex int
Declare @EndRowIndex int

Set @StartRowIndex = (@PageIndex * @PageSize) + 1;
Set @EndRowIndex = (@PageIndex + 1) * @PageSize;

Select EmployeeId, Name, Gender, City from
(Select ROW_NUMBER() over (order by EmployeeId) as RowNumber, EmployeeId, Name, Gender, City
from Sample.dbo.tblEmployee) Employees
–Replace ] GREATERTHAN symbol and “[” with LESSTHAN symbol
Where RowNumber ]= @StartRowIndex and RowNumber [= @EndRowIndex

Select @TotalRows = COUNT(*) from tblEmployee
End

Step 2: Add a class file with name = “EmployeeDataAccessLayer.cs”.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
public class Employee
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public string City { get; set; }
}

public class EmployeeDataAccessLayer
{
// Replace square brackets with angular brackets
public static List[Employee] GetEmployees(int pageIndex, int pageSize, out int totalRows)
{
// Replace square brackets with angular brackets
List[Employee] listEmployees = new List[Employee]();

string CS = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand(“spGetEmployees_by_PageIndex_and_PageSize”, con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter paramStartIndex = new SqlParameter();
paramStartIndex.ParameterName = “@PageIndex”;
paramStartIndex.Value = pageIndex;
cmd.Parameters.Add(paramStartIndex);

SqlParameter paramMaximumRows = new SqlParameter();
paramMaximumRows.ParameterName = “@PageSize”;
paramMaximumRows.Value = pageSize;
cmd.Parameters.Add(paramMaximumRows);

SqlParameter paramOutputTotalRows = new SqlParameter();
paramOutputTotalRows.ParameterName = “@TotalRows”;
paramOutputTotalRows.Direction = ParameterDirection.Output;
paramOutputTotalRows.SqlDbType = SqlDbType.Int;

cmd.Parameters.Add(paramOutputTotalRows);

con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.EmployeeId = Convert.ToInt32(rdr[“EmployeeId”]);
employee.Name = rdr[“Name”].ToString();
employee.Gender = rdr[“Gender”].ToString();
employee.City = rdr[“City”].ToString();

listEmployees.Add(employee);
}

rdr.Close();
totalRows = (int)cmd.Parameters[“@TotalRows”].Value;

}
return listEmployees;
}
}
}

Step 3: Drag and drop gridview and a repeater control and set the required properties accordingly.

Step 4: Copy and paste the following code in webform1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
int totalRows = 0;
GridView1.DataSource = EmployeeDataAccessLayer.
GetEmployees(0, GridView1.PageSize, out totalRows);
GridView1.DataBind();

DatabindRepeater(0, GridView1.PageSize, totalRows);
}
}
protected void linkButton_Click(object sender, EventArgs e)
{
int totalRows = 0;
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
pageIndex -= 1;
GridView1.PageIndex = pageIndex;
GridView1.DataSource = EmployeeDataAccessLayer.
GetEmployees(pageIndex, GridView1.PageSize, out totalRows);
GridView1.DataBind();
DatabindRepeater(pageIndex, GridView1.PageSize, totalRows);
}

Please refer to my blog for the complete code
http://csharp-video-tutorials.blogspot.com/2013/04/implement-custom-paging-in-aspnet_11.html