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: 


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. 


		// Protected connection variable.
		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";

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

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


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

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

For page navigation: 


		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.