Click here to Skip to main content
Click here to Skip to main content

DataGrid Paging - C# Windows Forms

, 8 Nov 2006 CPOL
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? PinmemberMember 99818936-Jul-13 2:10 
QuestionHi everybody. Still interested in paging a DataGridView, please try this one, thanks! PinmemberAlejandro Miralles28-Dec-12 9:40 
Generalmy vote of 2 PinmemberARIA 54-Oct-11 23:05 
useful but It's very easy.
GeneralMy vote of 5 Pinmemberkarthickjce11-Jul-11 2:31 
GeneralMy vote of 2 Pinmemberbehzad200011-Aug-10 20:55 
Questionhow to insert data into table after crating table Pinmemberdmrsantosh27-Apr-10 0:20 
GeneralQuestion Pinmemberzerepperez11-Oct-09 9: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
Web04 | 2.8.141223.1 | Last Updated 9 Nov 2006
Article Copyright 2006 by Manoh
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid