DataGrid Paging - C# Windows Forms

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();
}