Gridview custom paging in asp.net 3.5 with SQL Server Sproc

Aravind Kumar    ASP.NET, C#.NET     custom paging, gridview paging

GridView — Displays a set of data items in an HTML table. ASP.NET GridView control enables you to display, sort, page, select, and edit data.

Default gridview paging works best when you deal with limited pages. If there are more pages then, the performance suffers. In this case direct jump to desire page is a good alternative.

I have deal with a problem on GridView while working on user control (.ascx). I have a user control (.ascx) with GridView in it. I have used Stored Procedure to retrieve data. Bounding data to gridview display all the records and Default paging setting not work for me. Here Custom Paging helps me and only those database records that need to be displayed get retrieved. Using GridView Custom Paging solve the Issue for me.

Here, I am going to demonstrate a sample on GridView Custom Paging. I want to display records from two different tables depending on search pattern, let’s consider, I have student table contain sName, sAddress, ClassId and class table contain classId and ClassName. Now I want to display Name, Address and ClassName based on string pattern. Here I will get Ramdom Records based on search pattern. In this example we are going to use ASP.Net DropDownList along with ASP.Net GridView for Pagination to provide Jump To Page Number facility to the user.

First of all take a look at our table structure. Create New table based on below structure and add some dummy data in it…

Create New table based on above structure and add some dummy data in it.

GridView Markup

First you’ll have to drag an ASP.Net GridView control and a DropDownList control to the Asp.Net web page. Following piece of code will do it for you.

Source code

<div>
<asp:TextBox ID="txtSearch" runat="server" Text=""></asp:TextBox> &nbsp;<asp:Button ID="btnSearch" runat="server" Text="Go" />
<asp:GridView ID="grdUserDtl" runat="server" AutoGenerateColumns = "false"
AllowPaging = "true" PageSize = "10" PagerSettings-Visible = "false">
<Columns>
<asp:BoundField DataField="sName" HeaderText="Student Name" />
<asp:BoundField DataField="sAddress" HeaderText="Address" />
<asp:BoundField DataField="ClassName" HeaderText="Class" />
</Columns>
<EmptyDataTemplate>
No company found for your search
</EmptyDataTemplate>
</asp:GridView>
Jump To:
<asp:DropDownList ID="ddlJumpToPage" runat="server"
OnSelectedIndexChanged = "PageNumberChanged" AutoPostBack = "true">
</asp:DropDownList>
</div>

Stored Procedure for Custom Paging

We are ready with database, gridview and DropDownList. Now I am going to design a stored procedure to get the Customer records from the tables tblStudent and tblClass.

Sql Code

CREATE procedure sp_StudentDtl
(
@strSearchText varchar(100),
@intPageSize int,
@intCurrentPage int,
@intTotalRecords int output
)
AS
BEGIN

– Author: <Chetankumar Akarte>
– Description: <Return Records depending ON @intPageSize AND @intCurrentPage.>

DECLARE @tblStudentDtl TABLE(
SrNo int IDENTITY NOT NULL,
sName varchar(50) ,
sAddress varchar(100),
ClassName varchar(100)
)
DECLARE @start int
SET @start=@intPageSize*(@intCurrentPage - 1)

INSERT INTO @tblStudentDtl (sName,sAddress,ClassName)
SELECT sName, sAddress,ClassName FROM tblStudent A JOIN tblClass B ON A.ClassId=B.ClassId WHERE sName LIKE @strSearchText +‘%’

SELECT @intTotalRecords=Count(*) FROM tblStudent A JOIN tblClass B ON A.ClassId=B.ClassId WHERE sName LIKE @strSearchText +‘%’

SET ROWCOUNT @intPageSize
SELECT sName, sAddress,ClassName FROM  @tblStudentDtl WHERE SrNo > @start
SET ROWCOUNT 0

END

In this Stored Procedure we are three input parameter: @strSearchText varchar(100) for search pattern, @intPageSize integer type to define rows display per page, @intCurrentPage integer type to define page number which help us to select desire data.

Binding data from Stored Procedure with the GridView

In web.config we are going to add a key ‘gridPageSize’ at appSettings which will help us to customize pagging efficiently. We also going to set a connectionStrings ‘conString’ to connect to our database.

Source code

<appSettings>
<add key="gridPageSize" value="15"/>
</appSettings>
<connectionStrings>
<add name="conString" connectionString="Data Source=SerVerPath\sqlexpress;Initial Catalog=dbSchool;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

Use The System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server, comes with collection of classes used to access a SQL Server database in the managed space.

Source code

using System.Data.SqlClient;

private int BindResult(int currentPage)
{
int TotalRows = 0;
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("sp_StudentDtl");
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@intTotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@strSearchText", txtSearch.Text.Trim());
cmd.Parameters.AddWithValue("@intPageSize", Convert.ToInt16(ConfigurationManager.AppSettings["gridPageSize"]));
cmd.Parameters.AddWithValue("@intCurrentPage", currentPage);

cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
TotalRows = (int)cmd.Parameters["@intTotalRecords"].Value;
grdUserDtl.PageIndex = currentPage - 1;
grdUserDtl.DataSource = dt;
grdUserDtl.DataBind();
return TotalRows;
}

private void populateList(int TotalRows)
{
int PageCount = Convert.ToInt32(Math.Floor(Convert.ToDouble((TotalRows / Convert.ToInt32(ConfigurationManager.AppSettings["gridPageSize"]))))); ;
for (int i = 1; i <= PageCount; i++)
{
ddlJumpToPage.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
}

On page load we need to call above functions. We want the functionality of filter records based on search pattern so we need to call these functions on click event of search button btnSearch.

Source code

protected void Page_Load(object sender, EventArgs e)
{
ddlJumpToPage.Items.Clear();
int TotalRows = this.BindResult(1);
this.populateList(TotalRows);
}

protected void btnSearch_Click(object sender, EventArgs e)
{
ddlJumpToPage.Items.Clear();
int TotalRows = this.BindResult(1);
this.populateList(TotalRows);
}

Handling the Page Change Event

ASP.Net DropDownList control ddlJumpToPage which is going to take care of page change event through OnSelectedIndexChanged method, we just need to define a PageChanged function for it.

Source code

protected void PageChanged(object sender, EventArgs e)
{
int Page = Convert.ToInt32(ddlJumpToPage.SelectedItem.Value);
this.BindResult(Page);
}

If you enjoyed this article please consider sharing it!