Click here to Skip to main content
Click here to Skip to main content

Display Large Amount of Data in GridView with Search Functionality

, 5 Sep 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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.

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

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

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)

Share

About the Author

Ravi Karia
Architect
India India
No Biography provided

Comments and Discussions

 
QuestionHow can it work only with SQLClient and BLL Pinmemberbigwelly761-Nov-14 4:28 
QuestionOther tables PinmemberMember 961649315-Jul-14 8:17 
Questionhow to update command use in this same code Pinprofessionalvikramraj patil1-Jun-14 21:00 
QuestionDatabase PinmemberMember 1035126622-Jan-14 4:57 
Questionmy vote 5 out 0f 5 Pinprofessionalketan italiya16-Oct-13 0:29 
GeneralMy vote of 5 Pinmembersam2222217-Feb-13 5:43 
QuestionHow to update the Datatable when row data changed PinmemberBobWhite20115-Sep-12 5:52 
AnswerRe: How to update the Datatable when row data changed PinmemberRavi Karia16-May-13 23:42 
GeneralRe: How to update the Datatable when row data changed PinmemberEmmanuel Constant MSSM11-Oct-13 11:48 
AnswerRe: How to update the Datatable when row data changed PinmemberEmmanuel Constant MSSM11-Oct-13 11:50 
AnswerRe: How to update the Datatable when row data changed PinmemberEmmanuel Constant MSSM11-Oct-13 11:51 
Questionhow to view Pinmemberpri77731-Aug-12 0:49 
Generalhow to clear gridview datasource Pinmemberush@2813-Jul-12 1:48 
GeneralRe: how to clear gridview datasource PinmemberRavi Karia13-Jul-12 3:52 
GeneralHow to run this application in iis Pinmemberush@2819-Jul-12 3:07 
GeneralRe: how to clear gridview datasource Pinmembervimalrns4-Sep-12 21:59 
GeneralMy vote of 5 Pinmemberfarouk gaber1-Jul-11 6:18 
GeneralWorking with EF ? PinmemberZ@clarco22-May-11 23:03 
Generalnice one PinmemberPranay Rana29-Dec-10 20:12 
GeneralMy vote of 4 PinmemberSumanth Pereji29-Dec-10 19:19 
GeneralMy vote of 5 Pinmemberjkpieters14-Sep-10 23:19 
GeneralMy vote of 3 Pinmemberdurgausha13-Sep-10 1:24 
GeneralRe: My vote of 3 PinmemberRavi Karia13-Sep-10 9:18 
GeneralMy vote of 3 PinmvpDave Kreskowiak2-Sep-10 2:58 
GeneralRe: My vote of 3 PinmemberRavi Karia15-Sep-10 5:17 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 6 Sep 2010
Article Copyright 2010 by Ravi Karia
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid