Click here to Skip to main content
11,502,503 members (39,111 online)
Click here to Skip to main content

DataGrid Paging - C# Windows Forms

, 8 Nov 2006 CPOL 134.8K 9.4K 77
Rate this:
Please Sign up or sign in to vote.
DataGrid paging using C# Windows Forms - Select only the required (page size) records from the table.

Sample Image - DataGridPaging.gif

Introduction

This is an example of Windows Forms DataGrid paging using C#. I have used a Microsoft SQL Server 2000 database in the demo.

Using the code

Create a table:

CREATE TABLE tblEmp (E_ID int PRIMARY KEY, E_Name varchar(60), 
                     E_Salary money, E_DOJ datetime)
GO

In this example, the loadPage() method fetches only the required page data from the table using the following Select statement.

// Select only the n records.
strSql = "SELECT TOP " + this.mintPageSize + 
    " * FROM tblEmp WHERE E_Id NOT IN " + 
    "(SELECT TOP " + intSkip + " E_Id FROM tblEmp)";

Public variables:

// Protected Connection.
protected SqlConnection mcnSample;

// Page
private int mintTotalRecords = 0;
private int mintPageSize = 0;
private int mintPageCount = 0;
private int mintCurrentPage = 1;

// Connection String
protected const string CONNECTION_STRING = 
  "Server=localhost;UID=sa;PWD=;Database=Sample";

The fillGrid() method:

private void fillGrid()
{
    // For Page view.
    this.mintPageSize = int.Parse(this.tbPageSize.Text);
    this.mintTotalRecords = getCount();
    this.mintPageCount = this.mintTotalRecords / this.mintPageSize;
    
    // Adjust page count if the last page contains partial page.
    if (this.mintTotalRecords % this.mintPageSize > 0)
        this.mintPageCount++;

    this.mintCurrentPage = 0;

    loadPage();
}

The getCount() method: this method gets the record count much faster than the SELECT COUNT(*) statement does.

private int getCount()
{
    // This select statement is very fast compare to SELECT COUNT(*)
    string strSql = "SELECT Rows FROM SYSINDEXES " + 
                    "WHERE Id = OBJECT_ID('tblEmp') AND IndId < 2";
    int intCount = 0;

    SqlCommand cmd = this.mcnSample.CreateCommand();
    cmd.CommandText = strSql;

    intCount = (int) cmd.ExecuteScalar();
    cmd.Dispose();

    return intCount;
}

The loadPage() method:

private void loadPage()
{
    string strSql = "";
    int intSkip = 0;

    intSkip = (this.mintCurrentPage * this.mintPageSize);

    // Select only the n records.
    strSql = "SELECT TOP " + this.mintPageSize + 
        " * FROM tblEmp WHERE E_Id NOT IN " + 
        "(SELECT TOP " + intSkip + " E_Id FROM tblEmp)";

    SqlCommand cmd = this.mcnSample.CreateCommand();
    cmd.CommandText = strSql;

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    
    DataSet ds = new DataSet();
    da.Fill(ds, "tblEmp");

    // Populate Data Grid
    this.dgEmp.DataSource = ds.Tables["tblEmp"].DefaultView;

    // Show Status
    this.lblStatus.Text = (this.mintCurrentPage + 1).ToString() + 
      " / " + this.mintPageCount.ToString();

    cmd.Dispose();
    da.Dispose();
    ds.Dispose();
}

Here is how the page navigation is implemented:

private void goFirst()
{
    this.mintCurrentPage = 0;

    loadPage();
}

private void goPrevious()
{
    if (this.mintCurrentPage == this.mintPageCount)
        this.mintCurrentPage = this.mintPageCount - 1;

    this.mintCurrentPage--;

    if (this.mintCurrentPage < 1) 
        this.mintCurrentPage = 0;

    loadPage();
}

private void goNext()
{
    this.mintCurrentPage++;

    if (this.mintCurrentPage > (this.mintPageCount - 1))
        this.mintCurrentPage = this.mintPageCount - 1;

    loadPage();
}

private void goLast()
{
    this.mintCurrentPage = this.mintPageCount - 1;

    loadPage();
}

History

  • Released on November 9th 2006.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manoh
Team Leader
India India
No Biography provided

Comments and Discussions

 
QuestionSQL Injection? Pin
Member 99818936-Jul-13 1:10
memberMember 99818936-Jul-13 1:10 
QuestionHi everybody. Still interested in paging a DataGridView, please try this one, thanks! Pin
Alejandro Miralles28-Dec-12 8:40
memberAlejandro Miralles28-Dec-12 8:40 
Generalmy vote of 2 Pin
ARIA 54-Oct-11 22:05
memberARIA 54-Oct-11 22:05 
useful but It's very easy.
GeneralMy vote of 5 Pin
karthickjce11-Jul-11 1:31
memberkarthickjce11-Jul-11 1:31 
GeneralMy vote of 2 Pin
behzad200011-Aug-10 19:55
memberbehzad200011-Aug-10 19:55 
Questionhow to insert data into table after crating table Pin
dmrsantosh26-Apr-10 23:20
memberdmrsantosh26-Apr-10 23:20 
GeneralQuestion Pin
zerepperez11-Oct-09 8:34
memberzerepperez11-Oct-09 8:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 9 Nov 2006
Article Copyright 2006 by Manoh
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid