DataGridPaging

Introduction

This is an example of Windows Forms DataGrid paging using c#. I have used Microsoft SQL Server 2000 database.

Using the code

Create a table: 

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

 

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

   1: // Select only the n records.
   2: strSql = "SELECT TOP " + this.mintPageSize + 
   3:     " * FROM tblEmp WHERE E_Id NOT IN " + 
   4:     "(SELECT TOP " + intSkip + " E_Id FROM tblEmp)";
   5: Public variables: 
   6:  Collapse
   7:  
   8: // Protected Connection.
   9: protected SqlConnection mcnSample;
  10:  
  11: // Page
  12: private int mintTotalRecords = 0;
  13: private int mintPageSize = 0;
  14: private int mintPageCount = 0;
  15: private int mintCurrentPage = 1;
  16:  
  17: // Connection String
  18: protected const string CONNECTION_STRING = Server=localhost;UID=sa;PWD=;Database=Sample";

 

fillGrid() method: 

   1: private void fillGrid()
   2: {
   3:     // For Page view.
   4:     this.mintPageSize = int.Parse(this.tbPageSize.Text);
   5:     this.mintTotalRecords = getCount();
   6:     this.mintPageCount = this.mintTotalRecords / this.mintPageSize;
   7:     
   8:     // Adjust page count if the last page contains partial page.
   9:     if (this.mintTotalRecords % this.mintPageSize > 0)
  10:         this.mintPageCount++;
  11:  
  12:     this.mintCurrentPage = 0;
  13:  
  14:     loadPage();
  15: }

 

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

   1: private int getCount()
   2: {
   3:     // This select statement is very fast compare to SELECT COUNT(*)
   4:     string strSql = "SELECT Rows FROM SYSINDEXES WHERE Id = OBJECT_ID('tblEmp') AND IndId < 2";
   5:     int intCount = 0;
   6:  
   7:     SqlCommand cmd = this.mcnSample.CreateCommand();
   8:     cmd.CommandText = strSql;
   9:  
  10:     intCount = (int) cmd.ExecuteScalar();
  11:     cmd.Dispose();
  12:  
  13:     return intCount;
  14: }

 

loadPage() method: 

   1: private void loadPage()
   2: {
   3:     string strSql = "";
   4:     int intSkip = 0;
   5:  
   6:     intSkip = (this.mintCurrentPage * this.mintPageSize);
   7:  
   8:     // Select only the n records.
   9:     strSql = "SELECT TOP " + this.mintPageSize + 
  10:         " * FROM tblEmp WHERE E_Id NOT IN " + 
  11:         "(SELECT TOP " + intSkip + " E_Id FROM tblEmp)";
  12:  
  13:     SqlCommand cmd = this.mcnSample.CreateCommand();
  14:     cmd.CommandText = strSql;
  15:  
  16:     SqlDataAdapter da = new SqlDataAdapter(cmd);
  17:     
  18:     DataSet ds = new DataSet();
  19:     da.Fill(ds, "tblEmp");
  20:  
  21:     // Populate Data Grid
  22:     this.dgEmp.DataSource = ds.Tables["tblEmp"].DefaultView;
  23:  
  24:     // Show Status
  25:     this.lblStatus.Text = (this.mintCurrentPage + 1).ToString() + " / " + this.mintPageCount.ToString();
  26:  
  27:     cmd.Dispose();
  28:     da.Dispose();
  29:     ds.Dispose();
  30: }

 

For page navigation: 

   1: private void goFirst()
   2: {
   3:     this.mintCurrentPage = 0;
   4:  
   5:     loadPage();
   6: }
   7:  
   8: private void goPrevious()
   9: {
  10:     if (this.mintCurrentPage == this.mintPageCount)
  11:         this.mintCurrentPage = this.mintPageCount - 1;
  12:  
  13:     this.mintCurrentPage--;
  14:  
  15:     if (this.mintCurrentPage < 1) 
  16:         this.mintCurrentPage = 0;
  17:  
  18:     loadPage();
  19: }
  20:  
  21: private void goNext()
  22: {
  23:     this.mintCurrentPage++;
  24:  
  25:     if (this.mintCurrentPage > (this.mintPageCount - 1))
  26:         this.mintCurrentPage = this.mintPageCount - 1;
  27:  
  28:     loadPage();
  29: }
  30:  
  31: private void goLast()
  32: {
  33:     this.mintCurrentPage = this.mintPageCount - 1;
  34:  
  35:     loadPage();
  36: }

 

History

Released on November 9th 2006.