Datagrid Custom Paging

The advantage of this approach to paging through records is that the only records retrieved from the database are the records displayed in the DataGrid. In other words, this approach works great with huge database tables.

Note: This approach works only with database tables that have a column that uniquely indexes each database row. For example, it works with a table that has an identity column when the identity column is not missing any values. If certain values are missing from the identity column, the DataGrid displays fewer records for some pages than others.

1) AllowCustomPaging Property to true.

2) AllowPaging Property to true.

3) PageSize to number of records display per page. Eg: 10.

4) Declare Static Variable

static int iStartIndex;

5) Write Code on Event.

private void Page_Load(object sender, System.EventArgs e)

{

if(!Page.IsPostBack)

{

//Setting DataGrid's Virtual Item count

String connStr,sql;

connStr = ConfigurationSettings.AppSettings

["connStr"];

OleDbConnection conn = new OleDbConnection(connStr);

sql = "select count(*) from emp";

OleDbCommand cmd = new OleDbCommand(sql,conn);

                  conn.open();

DGridEmp.VirtualItemCount =

((int)cmd.ExecuteScalar()/DGridEmp.PageSize);

conn.Close();

iStartIndex = 1;

BindGrid(fetchData(iStartIndex));

}

}

private DataTable fetchData(int iStart)

{

int iEnd = iStart + DGridEmp.PageSize;

String connStr,sql;

connStr = ConfigurationSettings.AppSettings

["connStr"];

OleDbConnection conn = new OleDbConnection(connStr);

sql = "select empid,empname,dob,salary from emp where

empid > " + iStart + " and empid <= " + iEnd + "

order by empid";

DataSet ds = new DataSet();

conn.Open();

OleDbDataAdapter da = new OleDbDataAdapter(sql,conn);

da.Fill(ds,"Emp");

return ds.Tables["Emp"];

}

private void DGridEmp_PageIndexChanged(object source,

System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

{

iStartIndex = (e.NewPageIndex * DGridEmp.PageSize);

DGridEmp.CurrentPageIndex = e.NewPageIndex;

BindGrid(fetchData(iStartIndex));

}