Part 1
Introduction
This article explains how to display thousands or millions of data in GridView in ASP.NET 3.5.
In Part 1, our main goal is to achieve super fast performance of gridview when it displays a large amount of data. When users navigate through data, they should not have a feeling that they are navigating to millions of data.
In Part 2, we will implement search functionality over the GridView.
To achieve this goal, we will leverage custom paging, caching and AJAX. We will also implement sorting in this part. I have used AdventureWorks sample database for SQL Server 2008.
Background
Basic knowledge of ASP.NET 3.5, C#, Custom Paging, GridView, Caching, AJAX, SQL Server 2008 and VS 2008 is required.
Other than these, I have used DataPager with GridView for custom paging. Originally, you cannot use DataPager with GridView directly because DataPager can be used with control which implements IPageableItemContainer interface. So we have to create a new custom control which derives from GridView and also implements IPageableItemContainer.
For more information, visit here.
Overview
Let’s first understand the whole process behind it. When GridView will be displayed for the first time, we will check whether the data is already being cached or not. If not, then we will fetch all the records from the database and store it in cache.
We will display limited records depending on page index and page size on each request. We will calculate StartingRowIndex and MaximumRecords for custom paging as below:
StartingRowIndex = (PageIndex -1)*PageSize;
MaximumRecords = PageSize.
If page index is 1 and page size is 10, then startingRowIndex will be 0 and MaximumRecords will be 10, so we will return 10 rows from datatable from 0 to 9. Likewise if page index is 2 and page size is 10, then startingRowIndex will be 10 and MaximumRecords will be 10, so we will return 10 rows from datatable starting from 10 to 19.
So every time we will bind only 10 records to GridView, but we will display page number based on total records in the database. This is where custom paging and DataPager comes into the picture.
Using the Code
I have two separate layers in this project:
- UI
- Data access layer
I have created DAL in a different folder of the same project but if you wish, you can create a different project for DAL. Even if you want to make it a 3-tier application, then you can have a separate project for business layer linked to UI and DAL. But for the simplicity, I have created DAL in the same project.
So let’s start our journey step by step.
Step 1: First create the Web Application Project in VS 2008. Keep all settings as they are. We are going to use GridView with DataPager. So, we have to create server control which derives from GridView class and also implement IPageableItemContainer. Please visit here for better understanding because this article is not about using datapager with GridView.
Step 2: After creating GridView server control, let’s create data access layer. I have used AdvantureWorks sample database in SQL Server 2008. So you can download this sample database to run sample code.
Add a new folder ‘DAL’ for data access layer. Add new class called ‘AdvWorksDB’. We will fetch customer data from DimCustomer table in AdvantureWorks database. This table has over 18000 records.
AdvWorksDB class will have two functions:
GetCustomerSortedPage GetCustomersCount
Remember one thing, we are dealing with large amount of data. So for performance purposes, we can’t fetch all the records and bind it to gridview at one shot. If you do this, your page will crash. So we will fetch the data of a particular page size of gridview e.g. 10, 25, 50. So at any point of time, we will fetch only limited records and bind it to grid, say 10 records if pagesize is 10.
private const string CUSTOMER_CACHE_KEY = "CUSTOMER_DATA";
private const string CUSTOMERCOUNT_CACHE_KEY = "CUSTOMER_COUNT";
public static DataTable GetCustomersSortedPage
(int maximumRows, int startRowIndex,
string sortExpression, string searchCriteria)
{
if (string.IsNullOrEmpty(sortExpression))
sortExpression = "customerkey";
try
{
if (AdvWorksDBCache.isRecordsCached(CUSTOMER_CACHE_KEY))
return AdvWorksDBCache.GetData
(CUSTOMER_CACHE_KEY, startRowIndex + 1,
maximumRows, sortExpression, searchCriteria);
SqlConnection dbConnection = new SqlConnection
(ConfigurationManager.ConnectionStrings["SQLDBConString"].ToString());
string sql = "select Cust.*,G.City from
DimCustomer Cust inner join DimGeography G on
G.GeographyKey = Cust.GeographyKey ";
SqlCommand custCommand = new SqlCommand(sql, dbConnection);
custCommand.CommandType = CommandType.Text;
SqlDataAdapter ad = new SqlDataAdapter(custCommand);
DataTable dtCustomers = new DataTable();
ad.Fill(dtCustomers);
dbConnection.Close();
AdvWorksDBCache.Add(CUSTOMER_CACHE_KEY,dtCustomers);
}
catch (Exception e)
{
throw;
}
return AdvWorksDBCache.GetData(CUSTOMER_CACHE_KEY, startRowIndex + 1,
maximumRows, sortExpression, null);
}
In this function, we first check whether the data is already being cached? If data is not cached, then we fetch all the records and add them in cache and then return a chunk of records from cache. AdvWorksDBCache is a class for caching. We will see it soon.
The second function is GetCustomersCount, which returns count of records in DimCustomer table.
public static int GetCustomersCount(string searchCriteria)
{
int custCount = 0;
try
{
SqlConnection dbConnection = new SqlConnection
(ConfigurationManager.ConnectionStrings["SQLDBConString"].ToString());
string sql = "select count(*) from DimCustomer ";
if (!string.IsNullOrEmpty(searchCriteria))
sql = sql + " where " + searchCriteria;
SqlCommand sqlCommand = new SqlCommand(sql, dbConnection);
sqlCommand.Connection = dbConnection;
dbConnection.Open();
sqlCommand.CommandType = CommandType.Text;
custCount = Convert.ToInt32(sqlCommand.ExecuteScalar());
dbConnection.Close();
if (AdvWorksDBCache.Get(CUSTOMERCOUNT_CACHE_KEY) != null)
{
if (Convert.ToInt32(AdvWorksDBCache.Get(CUSTOMERCOUNT_CACHE_KEY)) !=
custCount && string.IsNullOrEmpty(searchCriteria))
{
AdvWorksDBCache.Remove(CUSTOMER_CACHE_KEY);
}
}
if (string.IsNullOrEmpty(searchCriteria))
AdvWorksDBCache.Add(CUSTOMERCOUNT_CACHE_KEY , custCount);
}
catch (Exception e)
{
throw;
}
return custCount ;
}
We use caching here just to reduce unnecessary calls to database. So add a new class called ‘AdvWorksDBCache’. This class will have standard functions like Add, Get, Remove for caching. It also has GetData function just to return data page from cached data. Please see source code for better understanding.
Step 3: Now we will create UI for this. You can find your GridView server control in toolbar. Drag and drop to the default.aspx. Keep it as it is for now. We will configure it later.
Now drag and drop ObjectDataSource from the tool bar to the Default.aspx. We have to set the following properties for the ObjectDataSource:
Id = odsCustomers SelectMethod = GetCustomersSortedPage SelectCountMethod = GetCustomersCount TypeName = DemoGrid.DAL.AdvWorksDB (full DB class name) SortParameterName = sortExpression EnablePaging = true
After setting these properties, your code will look like below:
<asp:ObjectDataSource ID="odsCustomers" runat="server"
SelectMethod="GetCustomersSortedPage"
TypeName="DemoGrid.DAL.AdvWorksDB"
EnablePaging="True" SelectCountMethod="GetCustomersCount"
SortParameterName="sortExpression">
</asp:ObjectDataSource>
Now drag and drop DataPager to Default.aspx and set the properties as per your paging format. I have set it like below:
<asp:DataPager ID="pager" runat="server" PagedControlID="gvCustomers">
<Fields>
<asp:NextPreviousPagerField FirstPageText="<<" LastPageText=">>"
NextPageText=">" PreviousPageText="<"
ShowFirstPageButton="True"
ShowNextPageButton="False" ButtonCssClass="datapager" />
<asp:NumericPagerField ButtonCount="10"
NumericButtonCssClass="datapager"
CurrentPageLabelCssClass="datapager" />
<asp:NextPreviousPagerField LastPageText=">>" NextPageText=">"
ShowLastPageButton="True"
ShowPreviousPageButton="False" ButtonCssClass="datapager" />
</Fields>
</asp:DataPager>
Now it’s time to configure gridview. We have to set DataSourceId = odsCustomers, which is our ObjectDataSource, AutoGenerateColumns = false, AllowSorting = true and OnDataBound event. Here, we also need pagesize dropdownbox and label at the bottom of the GridView, so that user can change page size. I have configured it as below:
<cc1:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="False"
AllowSorting="True"
CssClass="tablestyle" OnDataBound="gvCustomers_DataBound"
DataSourceID="odsCustomers" AllowPaging="True" >
<AlternatingRowStyle CssClass="altrowstyle" />
<HeaderStyle CssClass="headerstyle" />
<RowStyle CssClass="rowstyle" Wrap="false" />
<EmptyDataRowStyle BackColor="#edf5ff" Height="300px"
VerticalAlign="Middle" HorizontalAlign="Center" />
<EmptyDataTemplate >
No Records Found
</EmptyDataTemplate>
<Columns>
<asp:BoundField SortExpression="customerkey"
DataField="customerkey" HeaderText="Customer Key" />
<asp:BoundField SortExpression="City"
DataField="City" HeaderText="City" />
<asp:BoundField DataField="CustomerAlternateKey"
HeaderText="Customer Alternate Key"
SortExpression="CustomerAlternateKey" />
<asp:BoundField DataField="Title" HeaderText="Title"
SortExpression="Title"/>
<asp:BoundField DataField="FirstName"
HeaderText="First Name" SortExpression="FirstName"/>
<asp:BoundField DataField="LastName"
HeaderText="Last Name" SortExpression="LastName"/>
<asp:BoundField DataField="BirthDate"
HeaderText="Birth Date" SortExpression="BirthDate"/>
</Columns>
<PagerTemplate >
<table width="100%" >
<tr>
<td style="text-align: left">
Page Size:
<asp:DropDownList ID="ddPageSize"
runat="server" EnableViewState="true"
OnSelectedIndexChanged=
"ddPageSize_SelectedIndexChanged"
AutoPostBack="true">
<asp:ListItem Text="10" ></asp:ListItem>
<asp:ListItem Text="25" ></asp:ListItem>
<asp:ListItem Text="50" ></asp:ListItem>
</asp:DropDownList>
</td>
<td style="text-align: right">
<asp:Label ID="lblPageCount" runat="server">
</asp:Label>
</td>
</tr>
</table>
</PagerTemplate>
</cc1:GridView>
So now you are ready to go. Run it and see the performance. You can put datapager and gridview in AJAX updatepanel to remove flickering effect.
In Part 2, we will add search functionality over the GridView.
Notes
- I have written a cache class just to give demo of caching ability. You can modify the cache class as per your need and add dependency. You may leverage caching properties of
ObjectDataSource.
- You can insert
checkbox column in the same way as you normally do it with GridView. But here, you have to maintain a list of selected rows whenever user checks/unchecks checkboxes and navigates to data.
- You can also use LINQ to SQL. You just need to modify the methods in generated class to use in
ObjectDataSource.
Part 2
We discussed how we can display a large amount of data in GridView without compromising performance. Now in part 2, we will continue with the same example and build search functionality over the GridView:
Overview
We will search on chached data and bind only 10 records (depends on page size) to GridView. This way we can reduce the database call and maintain the performance.
Using the Code
First of all, we have to add search header above the GridView. We will add dropdownbox for columns, another dropdown for search criteria and one textbox for search value. We will also add ‘Search’ and ‘Reload’ button. I have added this code in panel above the GridView as below:
<asp:Panel ID="pnlSearch" runat="server" >
<table style="background-color:#C7CFF7; border-width:3px;
border-style:solid; margin: 0px 20px 0px 0px;">
<tr>
<td colspan="5">
<asp:Label ID="lblSearchError" runat="server"
ForeColor="Red" ></asp:Label>
</td>
</tr>
<tr>
<td align="left">Search Column</td>
<td align="left">Search Criteria</td>
<td align="left">Search Value</td>
<td></td>
<td></td>
</tr>
<tr>
<td><asp:HiddenField ID="hfSearchCriteria" runat="server" />
<asp:DropDownList ID="ddSearchField" Width="150px" runat="server">
<asp:ListItem Text="Customer Key"
Value="customerkey"></asp:ListItem>
<asp:ListItem Text="City" Value="City"></asp:ListItem>
<asp:ListItem Text="Customer Alternate Key"
Value="CustomerAlternateKey"></asp:ListItem>
<asp:ListItem Text="Title" Value="Title"></asp:ListItem>
<asp:ListItem Text="First Name" Value="FirstName">
</asp:ListItem>
<asp:ListItem Text="Last Name" Value="LastName">
</asp:ListItem>
<asp:ListItem Text="Birth Date" Value="BirthDate">
</asp:ListItem>
</asp:DropDownList>
</td>
<td>
<asp:DropDownList ID="ddSearchCriteria" runat="server">
<asp:ListItem Text= "Contains" ></asp:ListItem>
<asp:ListItem Text= "DoesNotContain" ></asp:ListItem>
<asp:ListItem Text="EqualTo" ></asp:ListItem>
<asp:ListItem Text= "NotEqualTo" ></asp:ListItem>
<asp:ListItem Text= "GreaterThan"></asp:ListItem>
<asp:ListItem Text= "LessThan" ></asp:ListItem>
<asp:ListItem Text= "GreaterThanOrEqualTo" ></asp:ListItem>
<asp:ListItem Text= "LessThanOrEqualTo"></asp:ListItem>
</asp:DropDownList>
</td>
<td>
<asp:TextBox ID="txtSearchValue" runat="server"></asp:TextBox>
</td>
<td>
<asp:Button ID="btnSearch" CssClass="searchbtn"
runat="server" OnClick="btnSearch_Click" Text="Search" />
</td>
<td>
<asp:Button ID="btnReload" CssClass="reloadbtn"
runat="server" OnClick="btnReload_Click" Text="Reload" />
</td>
</tr>
</table>
</asp:Panel>
If you have noticed, I have taken 1 hidden field ‘hfSearchCriteria’ which will contain full search criteria string, e.g., “CustomerKey=11000”. We will pass it to ObjectDataSourse as SelectParameters as following:
<asp:ObjectDataSource ID="odsCustomers" runat="server"
SelectMethod="GetCustomersSortedPage" TypeName="DemoGrid.DAL.AdvWorksDB"
EnablePaging="True" SelectCountMethod="GetCustomersCount"
SortParameterName="sortExpression">
<SelectParameters>
<asp:ControlParameter ControlID="hfSearchCriteria"
Name="searchCriteria" Direction="Input" />
</SelectParameters>
</asp:ObjectDataSource>
You can set this from the property window of the ObjectDataSource by adding SelectParameters collection as below:
Whenever we will bind GridView, ObjectDataSource will pass value of hidden field to the select method. In our case, it’s ‘GetCustomerSortedPage”. Actual searching is happening in cache class AdvWorksDBCache. If you see GetData method of AdvWorksDBCache, we are filtering datatable with search criteria and return records based on startingRowIndex and MaximumRecords. On search button click, we just build search string and assign it to hidden field and then rebind the GridView. Please see the attached source code for more understanding. So this way, we can implement search functionality over the GridView. Hope you would like it.
