Implement custom paging in an asp.net gridview that uses objectdatasource - Part 52

Link for text version of this video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

In this video we will discuss about implementing custom paging in a gridview control that uses objectdatasource control.

Step 1: Create a stored procedure to retrieve only the required number of rows.
Create proc spGetEmployees
@StartIndex int,
@MaximumRows int
as
Begin
Set @StartIndex = @StartIndex + 1

Select EmployeeId, Name, Gender, City from
(Select ROW_NUMBER() over (order by EmployeeId) as RowNumber, EmployeeId, Name, Gender, City
from tblEmployee) Employees
--Replace GREATERTHAN word with greater than symbol and LESSTHAN word with less than symbol
End

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 startRowIndex, int maximumRows)
{
// 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", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter paramStartIndex = new SqlParameter();
paramStartIndex.Value = startRowIndex;
cmd.Parameters.Add(paramStartIndex);

SqlParameter paramMaximumRows = new SqlParameter();
paramMaximumRows.Value = maximumRows;
cmd.Parameters.Add(paramMaximumRows);

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);
}
}
return listEmployees;
}

public static int GetTotalCount()
{
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("Select Count(*) from tblEmployee", con);

con.Open();
return (int)cmd.ExecuteScalar();
}
}
}
}

Step 3: Build the solution.

Step 5: Configure ObjectDataSource1 control to retrieve data from Demo.EmployeeDataAccessLayer business object, using GetEmployees() method. On "Define Parameters" screen, click finish.

Step 6: Associate "ObjectDataSource1" with "GridView1" control, and select "Enable Paging" checkbox. Set PageSize property to 3.

What is the difference between default paging and custom paging?
With default paging all the rows are retrieved from the database every time we navigate to a different page, where as with custom paging we only retrieve the subset of rows that will be displayed in the gridview control. Custom paging is much better for performance over default paging. When compared with default paging, custom paging is relatively complex to implement.

[Csharp-video-tutorialsBlogspot]

Thank you very much for taking time to give feedback. In the description of this video, I have included the link for ASP .NET, C#, and SQL Server playlists. All the videos are arranged in logical sequence in these playlists, which could be useful to you. Please share the link with your friends who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video. For email alerts, when new videos are uploaded, you may subscribe to my channel.

[carlomolina]

Thank you very much for sharing your knowledge, just one question you have "DBCS" in connection-string parameter this is the name of your database ?

[tinytutor]

how can we write above proc if have more than 1 table

[dryoga_spesialiskulit]

you've helped me greatly.. is it possible to make paging gridview withoud using store procedure? i'm having problem using the store procedure using my own case

[aviyitbarach]

thank you it is very helpful
i have a question how to add filtering with in stored procedure
thank you for advance

[rajarshibanerjee]

Dear Venkat, kindly help me. I implemented as per the video shown here. But my GridView is completely empty. What may be the reason?? by debugging I found that data is coming correctly in the EmployeeDataAccessLayer, still the GridView is empty. Please help me.

[ashraflahamify]

i have something i cant understand: how the two parameters StartIndex and MaxRows get valuse ?? who send the values to thes parameters ??

[namaratashiva]

can u tell me, hw to filter mvc grid based on dropdown

[malikkhalilahmad]

I don't see any reason to use default paging then! can someone please give a scenario example of the situation where we can use default paging?