Problem with automatic Paging:
When data is displayed in the DataGrid control, datasource is loaded
every time the DataGrid control moves to a different page. This can consume a lot of resources when the data source is very large resulting in degraded performance.
Custom paging allows to load just the segment of data needed to display a single page. By using custom paging, data is retrieved in page-size "chunks" i.e.
retrieve just a page data which needs to be displayed.
To do that, you turn off the automatic paging feature of the grid so that it doesn't assume that it is working with the entire dataset. We then get the correct number of rows to fill the grid.
AllowCustomPaging property of datagrid is used for enabling custom paging in a datagrid.
What is VirtualItemCount property:
This property of datagrid is used to specify the virtual number of items in the DataGrid control when custom paging is used.
How to retrieve only requird records:
Suppose you have specified pagesize as n.
Records for 1st page: 1 to x
Records for 2nd page: x+1 to xn
Records for 3rd page: 2x+1 to xn
…
…
Records for x page: (n-1 )*x+1 to x*n
Sql for retrieving nth page records:
If we are able to arrange records by id in sequence no. (1,2,3,4,5,….), the sql for retrieving nth page should look like:
Select field1, field2 … from table where id> x*n and
id <= (n+1)* x
Where n=pagesize
X=page no.
How to create unique id for records:
For creating unique id for records, we can create a temporary table as below:
CREATE TABLE #Temp
(
RecID int IDENTITY(1,1),
FieldId int
)
We need to populate the temporary from main table as:
INSERT INTO #Temp (FieldId)
SELECT FieldId
FROM Table
ORDER BY Field
Once temporary table is populated, we can write the select query to retrieve records for nth page as below:
SELECT table.*
FROM #Temp a, table b
Where
a.FieldId = b.fieldId
WHERE RecId > @PageSize * @CurrentPage
AND RecId <= @PageSize * (@CurrentPage+1)
We can also use the query like:
SELECT top @pagesize table.*
FROM #Temp a, table b
Where
a.FieldId = b.fieldId
WHERE RecId > @PageSize * @CurrentPage
Example:
<%@ Page Language="C#" AutoEventWireup="True" %>
<%@ Import Namespace="System.Data" %>
<html>
<script runat="server">
int startIndex = 0;
ICollection CreateDataSource()
{
// Create sample data for the DataGrid control.
DataTable dt = new DataTable();
DataRow dr;
// Define the columns of the table.
dt.Columns.Add(new DataColumn("IntegerValue", typeof(Int32)));
dt.Columns.Add(new DataColumn("StringValue", typeof(string)));
dt.Columns.Add(new DataColumn("DateTimeValue", typeof(string)));
dt.Columns.Add(new DataColumn("BoolValue", typeof(bool)));
// Populate the table with sample values. When using custom paging,
// a query should only return enough data to fill a single page,
// beginning at the start index.
for (int i = startIndex; i < (startIndex + MyDataGrid.PageSize); i++)
{
dr = dt.NewRow();
dr[0] = i;
dr[1] = "Item " + i.ToString();
dr[2] = DateTime.Now.AddDays(i).ToShortDateString();
dr[3] = (i % 2 != 0) ? true : false;
dt.Rows.Add(dr);
}
DataView dv = new DataView(dt);
return dv;
}
void Page_Load(Object sender, EventArgs e)
{
// Load sample data only once, when the page is first loaded.
if (!IsPostBack)
{
// Set the virtual item count, which specifies the total number
// items displayed in the DataGrid control when custom paging
// is used.
MyDataGrid.VirtualItemCount = 200;
// Retrieve the segment of data to display on the page from the
// data source and bind it to the DataGrid control.
BindGrid();
}
}
void MyDataGrid_Page(Object sender, DataGridPageChangedEventArgs e)
{
MyDataGrid.CurrentPageIndex = e.NewPageIndex;
startIndex = MyDataGrid.CurrentPageIndex * MyDataGrid.PageSize;
BindGrid();
}
void BindGrid()
{
MyDataGrid.DataSource = CreateDataSource();
MyDataGrid.DataBind();
}
</script>
<body>
<form runat="server" ID="Form1">
<h3> DataGrid Custom Paging Example </h3>
<asp:DataGrid id="MyDataGrid"
AllowCustomPaging="True"
AllowPaging="True"
PageSize="10"
OnPageIndexChanged="MyDataGrid_Page"
runat="server">
<HeaderStyle BackColor="Navy"
ForeColor="White"
Font-Bold="True" />
<PagerStyle Mode="NumericPages"
HorizontalAlign="Right" />
</asp:DataGrid>
</form>
</body>
</html>
Hope it helps in improving the performance of the pages where large data is displayed using datagrid.
Your comments are welcome.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|