Jan 7, 2016

DataGrid Paging - C# Windows Forms

DataGrid paging using C# Windows Forms - Select only the required (page size) records from the table.
Is your email address OK? You are signed up for our newsletters but your email address is either unconfirmed, or has not been reconfirmed in a long time. Please click here to have a confirmation email sent so we can confirm your email address and start sending you newsletters again. Alternatively, you can update your subscriptions.
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 followingSelect 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();
}


0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib