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
Public variables:
protected SqlConnection mcnSample;
private int mintTotalRecords = 0;
private int mintPageSize = 0;
private int mintPageCount = 0;
private int mintCurrentPage = 1;
protected const string CONNECTION_STRING =
"Server=localhost;UID=sa;PWD=;Database=Sample";
The fillGrid()
method:
private void fillGrid()
{
this.mintPageSize = int.Parse(this.tbPageSize.Text);
this.mintTotalRecords = getCount();
this.mintPageCount = this.mintTotalRecords / this.mintPageSize;
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()
{
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, i = 0;
SqlDataReader dr;
DataSet ds;
DataTable dt;
intSkip = (this.mintCurrentPage * this.mintPageSize);
strSql = "SELECT * FROM tblEmp";
SqlCommand cmd = this.mcnSample.CreateCommand();
cmd.CommandText = strSql;
dr = cmd.ExecuteReader();
ds = new DataSet();
dt = ds.Tables.Add("tblEmp");
for (i = 0; i < dr.FieldCount; i++)
dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
int intIdx = 0;
while (dr.Read())
{
if (intIdx >= intSkip)
{
DataRow r = dt.NewRow();
for (i = 0; i < dr.FieldCount; i++)
r[i] = dr[i];
dt.Rows.Add(r);
}
if ((intIdx - intSkip) >= (this.mintPageSize - 1))
break;
intIdx++;
}
dr.Close();
this.dgEmp.DataSource = ds.Tables["tblEmp"].DefaultView;
this.lblStatus.Text = (this.mintCurrentPage + 1).ToString() +
" / " + this.mintPageCount.ToString();
cmd.Dispose();
ds.Dispose();
}
Here is the code 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.