DataGrid Paging using DataReader – C# Windows Forms
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
Public variables:
1: // Protected Connection.
2: protected SqlConnection mcnSample;
3:
4: // Page
5: private int mintTotalRecords = 0;
6: private int mintPageSize = 0;
7: private int mintPageCount = 0;
8: private int mintCurrentPage = 1;
9:
10: // Connection String
11: 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, i = 0;
5: SqlDataReader dr;
6: DataSet ds;
7: DataTable dt;
8:
9: intSkip = (this.mintCurrentPage * this.mintPageSize);
10:
11: strSql = "SELECT * FROM tblEmp";
12:
13: SqlCommand cmd = this.mcnSample.CreateCommand();
14: cmd.CommandText = strSql;
15:
16: dr = cmd.ExecuteReader();
17: ds = new DataSet();
18:
19: dt = ds.Tables.Add("tblEmp");
20:
21: // Add the table columns.
22: for (i = 0; i < dr.FieldCount; i++)
23: dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
24:
25: int intIdx = 0;
26: while (dr.Read())
27: {
28: if (intIdx >= intSkip)
29: {
30: DataRow r = dt.NewRow();
31:
32: // Assign DataReader values to DataRow.
33: for (i = 0; i < dr.FieldCount; i++)
34: r[i] = dr[i];
35:
36: dt.Rows.Add(r);
37: }
38:
39: if ((intIdx - intSkip) >= (this.mintPageSize - 1))
40: break;
41:
42: intIdx++;
43: }
44:
45: dr.Close();
46:
47: // Populate Data Grid
48: this.dgEmp.DataSource = ds.Tables["tblEmp"].DefaultView;
49:
50: // Show Status
51: this.lblStatus.Text = (this.mintCurrentPage + 1).ToString() + " / " + this.mintPageCount.ToString();
52:
53: cmd.Dispose();
54: ds.Dispose();
55: }
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.
