Click here to Skip to main content
15,867,704 members
Articles / Web Development / ASP.NET

Display Large Amount of Data in GridView with Search Functionality

Rate me:
Please Sign up or sign in to vote.
4.79/5 (26 votes)
5 Sep 2010CPOL7 min read 207.1K   16.2K   81   27
This article explains how to display large amount of data and implement search functionality in GridView in ASP.NET 3.5

Part 1

GridView1.jpg

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:

  1. UI
  2. 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.

C#
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();

                //Cache records
                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.

C#
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)
                {
                    // remove customers data if customers count has changed 
                    // since first cache
                    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:

  1. Id = odsCustomers
  2. SelectMethod = GetCustomersSortedPage
  3. SelectCountMethod = GetCustomersCount
  4. TypeName = DemoGrid.DAL.AdvWorksDB (full DB class name)
  5. SortParameterName = sortExpression
  6. EnablePaging = true

GridView2.jpg

After setting these properties, your code will look like below:

ASP.NET
<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.NET
<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:

ASP.NET
<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:

GridviewSearch2.jpg

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.NET
<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.NET
<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:

GridviewSearch1.jpg

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
BugSearch by the City column doesn't work Pin
AntoniusBlock11-Sep-15 10:35
AntoniusBlock11-Sep-15 10:35 
QuestionHow can it work only with SQLClient and BLL Pin
bigwelly761-Nov-14 3:28
bigwelly761-Nov-14 3:28 
QuestionOther tables Pin
Member 961649315-Jul-14 7:17
Member 961649315-Jul-14 7:17 
Questionhow to update command use in this same code Pin
vikramraj patil1-Jun-14 20:00
professionalvikramraj patil1-Jun-14 20:00 
QuestionDatabase Pin
Member 1035126622-Jan-14 3:57
Member 1035126622-Jan-14 3:57 
Questionmy vote 5 out 0f 5 Pin
ketan italiya15-Oct-13 23:29
ketan italiya15-Oct-13 23:29 
GeneralMy vote of 5 Pin
Peaceful Salman17-Feb-13 4:43
Peaceful Salman17-Feb-13 4:43 
QuestionHow to update the Datatable when row data changed Pin
BobWhite20115-Sep-12 4:52
BobWhite20115-Sep-12 4:52 
AnswerRe: How to update the Datatable when row data changed Pin
Ravi Karia16-May-13 22:42
Ravi Karia16-May-13 22:42 
GeneralRe: How to update the Datatable when row data changed Pin
Emmanuel Constant MSSM11-Oct-13 10:48
professionalEmmanuel Constant MSSM11-Oct-13 10:48 
AnswerRe: How to update the Datatable when row data changed Pin
Emmanuel Constant MSSM11-Oct-13 10:50
professionalEmmanuel Constant MSSM11-Oct-13 10:50 
AnswerRe: How to update the Datatable when row data changed Pin
Emmanuel Constant MSSM11-Oct-13 10:51
professionalEmmanuel Constant MSSM11-Oct-13 10:51 
Questionhow to view Pin
pri77730-Aug-12 23:49
pri77730-Aug-12 23:49 
Generalhow to clear gridview datasource Pin
ush@2813-Jul-12 0:48
ush@2813-Jul-12 0:48 
GeneralRe: how to clear gridview datasource Pin
Ravi Karia13-Jul-12 2:52
Ravi Karia13-Jul-12 2:52 
GeneralHow to run this application in iis Pin
ush@2819-Jul-12 2:07
ush@2819-Jul-12 2:07 
GeneralRe: how to clear gridview datasource Pin
vimalrns4-Sep-12 20:59
professionalvimalrns4-Sep-12 20:59 
GeneralMy vote of 5 Pin
farouk gaber1-Jul-11 5:18
farouk gaber1-Jul-11 5:18 
GeneralWorking with EF ? Pin
Z@clarco22-May-11 22:03
Z@clarco22-May-11 22:03 
Generalnice one Pin
Pranay Rana29-Dec-10 19:12
professionalPranay Rana29-Dec-10 19:12 
GeneralMy vote of 4 Pin
Sumanth Pereji29-Dec-10 18:19
Sumanth Pereji29-Dec-10 18:19 
GeneralMy vote of 5 Pin
jkpieters14-Sep-10 22:19
jkpieters14-Sep-10 22:19 
GeneralMy vote of 3 Pin
Durga Sekaran13-Sep-10 0:24
Durga Sekaran13-Sep-10 0:24 
GeneralRe: My vote of 3 Pin
Ravi Karia13-Sep-10 8:18
Ravi Karia13-Sep-10 8:18 
GeneralMy vote of 3 Pin
Dave Kreskowiak2-Sep-10 1:58
mveDave Kreskowiak2-Sep-10 1:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.