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

• Report rights infringement
• published: 11 Apr 2013
• views: 14762

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