In this article I will explain how to display large amount of data in GridView with Search functionality in ASP.Net by making use of Paging using Stored Procedures and jQuery AJAX.
By combining the power of SQL Server and jQuery we can efficiently do paging through large amounts of data.
Database
For this article I have used Microsoft’s Northwind database. You can download it using the link provided below.
HTML Markup
Below is the HTML Markup. It has an ASP.Net GridView, a TextBox for entering search term and an HTML DIV control where the Pager will be populated for pagination.
Search:
<asp:TextBox ID="txtSearch" runat="server" />
<hr />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name"
ItemStyle-CssClass="ContactName" />
<asp:BoundField HeaderStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />
<asp:BoundField HeaderStyle-Width="150px" DataField="City" HeaderText="City" />
</Columns>
</asp:GridView>
<br />
<div class="Pager">
</div>
Namespaces
You will need to import the following namespaces
C#
using System.Data;
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Web.Services
Imports System.Configuration
Imports System.Data.SqlClient
Populating GridView Dummy with Dummy Data
In Page Load event of the page I am populating the GridView with dummy data so that we can use its HTML Table structure to populate data using jQuery.
I have created a variable PageSize which will decide the number of records to be displayed per page in GridView
Note: The dummy DataTable being populated has the same columns which will be returned from the SQL Query
C#
private static int PageSize = 5;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDummyRow();
}
}
private void BindDummyRow()
{
DataTable dummy = new DataTable();
dummy.Columns.Add("CustomerID");
dummy.Columns.Add("ContactName");
dummy.Columns.Add("City");
dummy.Rows.Add();
gvCustomers.DataSource = dummy;
gvCustomers.DataBind();
}
VB.Net
Private Shared PageSize As Integer = 5
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindDummyRow()
End If
End Sub
Private Sub BindDummyRow()
Dim dummy As New DataTable()
dummy.Columns.Add("CustomerID")
dummy.Columns.Add("ContactName")
dummy.Columns.Add("City")
dummy.Rows.Add()
gvCustomers.DataSource = dummy
gvCustomers.DataBind()
End Sub
Implement Search and Pagination using SQL Server Stored Procedure
The following stored procedure which will perform search and pagination on the table records and return per page data.
CREATE PROCEDURE [dbo].[GetCustomers_Pager]
@SearchTerm VARCHAR(100) = ''
,@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [CustomerID] ASC
)AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
,[City]
INTO #Results
FROM [Customers]
WHERE [ContactName] LIKE @SearchTerm + '%' OR @SearchTerm = ''
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
WebMethod to handle jQuery AJAX calls
Below is the Web Method which is being called by the jQuery AJAX function which populates the GridView with data and also does Pagination.
The SearchTerm and PageIndex are passed as parameter from Client side, while the PageSize we get from the static variable we have declared. Based on these parameters we can fetch the records Page wise.
The records fetched along with the Total Record Count are sent to Client Side as XML string.
Note: Total Record Count is necessary to populate pager.
C#
[WebMethod]
public static string GetCustomers(string searchTerm, int pageIndex)
{
string query = "[GetCustomers_Pager]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
return GetData(cmd, pageIndex).GetXml();
}
private static DataSet GetData(SqlCommand cmd, int pageIndex)
{
string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds, "Customers");
DataTable dt = new DataTable("Pager");
dt.Columns.Add("PageIndex");
dt.Columns.Add("PageSize");
dt.Columns.Add("RecordCount");
dt.Rows.Add();
dt.Rows[0]["PageIndex"] = pageIndex;
dt.Rows[0]["PageSize"] = PageSize;
dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
ds.Tables.Add(dt);
return ds;
}
}
}
}
VB.Net
<WebMethod()> _
Public Shared Function GetCustomers(searchTerm As String, pageIndex As Integer) As String
Dim query As String = "[GetCustomers_Pager]"
Dim cmd As New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SearchTerm", searchTerm)
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", PageSize)
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
Return GetData(cmd, pageIndex).GetXml()
End Function
Private Shared Function GetData(cmd As SqlCommand, pageIndex As Integer) As DataSet
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Using con As New SqlConnection(strConnString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As New DataSet()
sda.Fill(ds, "Customers")
Dim dt As New DataTable("Pager")
dt.Columns.Add("PageIndex")
dt.Columns.Add("PageSize")
dt.Columns.Add("RecordCount")
dt.Rows.Add()
dt.Rows(0)("PageIndex") = pageIndex
dt.Rows(0)("PageSize") = PageSize
dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
ds.Tables.Add(dt)
Return ds
End Using
End Using
End Using
End Function
Client Side Implementation
Below is the complete Client Side Implementation, here on load event of jQuery the GridView is populated with PageIndex 1 using the method GetCustomers which populates the ASP.Net GridView which was initially populated with dummy data.
Using the Record Count, the pager is populated inside the HTML DIV using the ASPSnippets_Pager jQuery Plugin.
A jQuery Keyup event handler has been attached to the Search TextBox which will trigger an AJAX call and will filter the GridView with the matched records.
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
GetCustomers(1);
});
$("[id*=txtSearch]").live("keyup", function () {
GetCustomers(parseInt(1));
});
$(".Pager .page").live("click", function () {
GetCustomers(parseInt($(this).attr('page')));
});
function SearchTerm() {
return jQuery.trim($("[id*=txtSearch]").val());
};
function GetCustomers(pageIndex) {
$.ajax({
type: "POST",
url: "Default.aspx/GetCustomers",
data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.d);
}
});
}
var row;
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Customers");
if (row == null) {
row = $("[id*=gvCustomers] tr:last-child").clone(true);
}
$("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();
if (customers.length > 0) {
$.each(customers, function () {
var customer = $(this);
$("td", row).eq(0).html($(this).find("ContactName").text());
$("td", row).eq(1).html($(this).find("CustomerID").text());
$("td", row).eq(2).html($(this).find("City").text());
$("[id*=gvCustomers]").append(row);
row = $("[id*=gvCustomers] tr:last-child").clone(true);
});
var pager = xml.find("Pager");
$(".Pager").ASPSnippets_Pager({
ActiveCssClass: "current",
PagerCssClass: "pager",
PageIndex: parseInt(pager.find("PageIndex").text()),
PageSize: parseInt(pager.find("PageSize").text()),
RecordCount: parseInt(pager.find("RecordCount").text())
});
$(".ContactName").each(function () {
var searchPattern = new RegExp('(' + SearchTerm() + ')', 'ig');
$(this).html($(this).text().replace(searchPattern, "<span class = 'highlight'>" + SearchTerm() + "</span>"));
});
} else {
var empty_row = row.clone(true);
$("td:first-child", empty_row).attr("colspan", $("td", row).length);
$("td:first-child", empty_row).attr("align", "center");
$("td:first-child", empty_row).html("No records found for the search criteria.");
$("td", empty_row).not($("td:first-child", empty_row)).remove();
$("[id*=gvCustomers]").append(empty_row);
}
};
</script>
CSS
Below is the necessary CSS styles which you need to put on your page.
<style type="text/css">
body
{
font-family: Arial;
font-size: 10pt;
}
table
{
border: 1px solid #ccc;
}
table th
{
background-color: #F7F7F7;
color: #333;
font-weight: bold;
}
table th, table td
{
padding: 5px;
border-color: #ccc;
}
.Pager span
{
color: #333;
background-color: #F7F7F7;
font-weight: bold;
text-align: center;
display: inline-block;
width: 20px;
margin-right: 3px;
line-height: 150%;
border: 1px solid #ccc;
}
.Pager a
{
text-align: center;
display: inline-block;
width: 20px;
border: 1px solid #ccc;
color: #fff;
color: #333;
margin-right: 3px;
line-height: 150%;
text-decoration: none;
}
.highlight
{
background-color: #FFFFAF;
}
</style>
Screenshots
GridView displaying matched records.
GridView when no records are found.
Demo
Downloads
