Tips n Tracks

  • Increase font size
  • Default font size
  • Decrease font size
  • default color
  • black color

Reference

Sample image

Microsoft .NET Framework Get Details.

Sample image

Microsoft .NET Framework Get Details.

Reference

Sample image Microsoft .NET Framework Get Details.
Sample image

Microsoft .NET Framework Get Details.


GridView Custom Paging in ASP.NET 3.5 with SQL Server Stored Procedure


GridView Custom Paging in ASP.NET 3.5 with SQL Server Stored Procedure

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…

Table 1 – tblStudent
SrNo sName sAddress ClassId
1 Lucy Redmond 1
2 Sam SmallVilla 1
3 Mitchell Mumbai 1
4 Scott SmallVilla 2
5 Kathryn Redmond 3
6 Frank Mumbai 1
  ….
2000 Jay Mumbai 3

Table 2 – tblClass
ClassId ClassName
1 Electronics Engineering
2 Computer Engineering
3 Mechanical Engineering

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.

<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.

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.

<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>

Now we need to bind custom result to our gridview, I have define a private function BindResult which will take currentPage number as a input and papualte result accordingly and return Total record count. Another function populateList use that Total record count to generate page numbers for DropDownList ddlJumpToPage.

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.
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.

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.

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

Download

GridView Custom Paging in ASP.NET 3.5 with SQL Server Stored Procedure

Download

 

Chetan love blogging. He regularly blogs at http://www.tipsntracks.com. You can connect with Chetan on Twitter, Facebook and Google Plus...

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments

Your article, Chetankumar Akarte, is a great learning tool for me. But I believe I found a typo:

protected void PageChanged(object sender, EventArgs e)

should be

protected void PageNumberChanged(object sender, EventArgs e)

I am also having a problem with the grid binding to the data, I am always getting just the first (page) records no matter which page number is selected in the ddlJumpToPage control.

Any ideas?

UDT
:)

Comment by UpsideDownTire on May 12, 2010 @ 9:24 pm

In regards to the “PageNumberChanged” vs. “PageChanged” I mentioned before; I see now that your download code is more correct. However, the code presented in the article above is not. Just thought you should know.

Still debugging the GridView *always* showing the first page of the record set.

:)

Comment by UpsideDownTire on May 12, 2010 @ 9:54 pm

Like I said, this article has been a good learning tool. Here’s a change in your code that seems to work for me:

I added a check for ” if (!IsPostBack) ”

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

[/code]

Comment by UpsideDownTire on May 12, 2010 @ 10:04 pm

hi,

following is script for creating table for this article.

CREATE TABLE tblStudent
(
SrNo int identity(1001,1) primary key,
sName varchar(50),
sAddress varchar(500),
ClassId int
)

insert into tblStudent(sName,SAddress,ClassId)
values
(‘Manish’,'noida’,2)

select * from tblStudent

CREATE TABLE tblClass
(
ClassId int identity(1,1) primary key,
ClassName varchar(100)
)

insert into tblClass(ClassName)
values
(‘Information Technology’)

select * from tblStudent

Thanks
Manish Agrahari

Comment by Manish Agrahari on February 16, 2011 @ 3:17 am

Do u have any idea how to use numeric paging that we have with grid view.
If we have too much pages than there is draw back with DropDownList.

Comment by Anjum Rizwi on May 26, 2011 @ 3:27 am

Hi, Thanks for the article, But I am facing a small problem. Suppose I have 22 records and my records per page is 5 then it is showing till 4 pages that is last 2 recored are nt showing. At that time it should show 5 pages and in the last page it should show 2 records. I guess some modification needed in populateList() method..

Waiting 4 ur reply

Comment by Bijay on October 2, 2011 @ 2:25 am

Hi, This article realy good for beginner. Who do i use this code if my table does not have srNo column which is used in logic to get page records?

Comment by vinod jain on February 6, 2012 @ 10:32 am

Hi,
I am beginner so please provide all the grid view events with classification so it would help me a lot how the grid view used

Comment by Dhaval on March 8, 2012 @ 11:26 am
Comment by Vivek on May 11, 2013 @ 11:49 am

Hey good article when there is Serial No. column in table and when there is not much data in table (pagination issue).

After a lot of search i have found another great article that full fill these requirements .

Check :-
Custom paging in asp.net using stored procedure in gridview with nice pagination

Comment by Rohit on May 16, 2013 @ 6:35 am

Leave a comment

(required)

(required)

*
To prove that you're not a bot, enter this code
Anti-Spam Image