Hi makumbi,
Please refer below sample.
HTML
Search:
<asp:TextBox ID="txtSearch" runat="server" />
<hr />
<asp:GridView ID="gvCustomers" runat="server" OnSelectedIndexChanged="OnSelectedIndexChanged" AutoGenerateColumns="False" Height="16px" Width="720px">
<Columns>
<asp:BoundField HeaderStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />
<asp:BoundField HeaderStyle-Width="150px" DataField="ContactName" HeaderText="ContactName" />
<asp:BoundField HeaderStyle-Width="150px" DataField="ContactTitle" HeaderText="ContactTitle" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="PostalCode" HeaderText="PostalCode" />
<asp:BoundField DataField="Country" HeaderText="Country" />
<asp:CommandField ShowSelectButton="true" ButtonType="Link" />
</Columns>
</asp:GridView>
<br />
<div class="Pager">
</div>
<asp:HiddenField ID="hfId" runat="server"></asp:HiddenField>
<asp:HiddenField ID="hfName" runat="server"></asp:HiddenField>
<asp:HiddenField ID="hfTitle" runat="server"></asp:HiddenField>
<asp:HiddenField ID="hfCity" runat="server"></asp:HiddenField>
<asp:HiddenField ID="hfPostalCode" runat="server"></asp:HiddenField>
<asp:HiddenField ID="hfCountry" runat="server"></asp:HiddenField>
Name:
<asp:Label ID="lblContactName" runat="server"></asp:Label>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="ASPSnippets_Pager.min.js"></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: "CS.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("CustomerID").text());
$("td", row).eq(1).html($(this).find("ContactName").text());
$("td", row).eq(2).html($(this).find("ContactTitle").text());
$("td", row).eq(3).html($(this).find("City").text());
$("td", row).eq(4).html($(this).find("PostalCode").text());
$("td", row).eq(5).html($(this).find("Country").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);
}
};
$(function () {
$("[id*=gvCustomers]").find("a").click(function () {
var row = $(this).closest("tr");
var customerID = row.find("td").eq(0).html();
var contactName = row.find("td").eq(1).html();
var contactTitle = row.find("td").eq(2).html();
var city = row.find("td").eq(3).html();
var postalCode = row.find("td").eq(4).html();
var country = row.find("td").eq(5).html();
document.getElementById("hfId").value = customerID;
document.getElementById("hfName").value = contactName;
document.getElementById("hfTitle").value = contactTitle;
document.getElementById("hfCity").value = city;
document.getElementById("hfPostalCode").value = postalCode;
document.getElementById("hfCountry").value = country;
//return false;
});
});
</script>
Namespaces
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Services;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Services
Code
C#
private static int PageSize = 5;
private string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
protected void Page_Load(object sender, System.EventArgs e)
{
if (!this.IsPostBack)
{
BindDummyRow();
}
}
private DataTable ExecuteQuery(SqlCommand cmd, string action)
{
// Dim conString As String = conString
using (SqlConnection con = new SqlConnection(conString))
{
cmd.Connection = con;
switch (action)
{
case "SELECT":
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
break;
}
case "UPDATE":
{
con.Open();
cmd.ExecuteReader();
con.Close();
break;
}
}
return null; /* TODO Change to default(_) if this is not a reference type */
}
}
private void BindDummyRow()
{
DataTable dummy = new DataTable();
dummy.Columns.Add("CustomerID");
dummy.Columns.Add("ContactName");
dummy.Columns.Add("ContactTitle");
dummy.Columns.Add("City");
dummy.Columns.Add("PostalCode");
dummy.Columns.Add("Country");
dummy.Rows.Add();
gvCustomers.DataSource = dummy;
gvCustomers.DataBind();
}
protected void OnSelectedIndexChanged(object sender, EventArgs e)
{
string customerID = hfId.Value;
string contactName = hfName.Value;
string contactTitle = hfTitle.Value;
string city = hfCity.Value;
string postalCode = hfPostalCode.Value;
string country = hfCountry.Value;
lblContactName.Text = contactName;
// Response.Redirect(String.Format("Libraryorders.aspx?account={0}&names={1}&class={2}&stream={3}&types={4}&program={5}&Regno={6}&booklimit={7}&house={8}&username={9}", account, names, classs, streams, types, program, regno, bklimit, house, username))
//Response.Redirect(string.Format("Home.aspx?account={0}&names={1}&class={2}&stream={3}&types={4}&program={5}&Regno={6}&booklimit={7}&house={8}&username={9}", account, names, classs, streams, types, program, regno, bklimit, house, username));
}
[WebMethod]
public static string GetCustomers(string searchTerm, int pageIndex)
{
string query = "[GetCustomerPagination]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@City", 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 conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds, "Customers");
return ds;
}
}
}
}
VB.Net
Private Shared PageSize As Integer = 5
Private conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
BindDummyRow()
End If
End Sub
Private Function ExecuteQuery(ByVal cmd As SqlCommand, ByVal action As String) As DataTable
Using con As SqlConnection = New SqlConnection(conString)
cmd.Connection = con
Select Case action
Case "SELECT"
Using sda As SqlDataAdapter = New SqlDataAdapter()
sda.SelectCommand = cmd
Using dt As DataTable = New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
Exit Select
Case "UPDATE"
con.Open()
cmd.ExecuteReader()
con.Close()
Exit Select
End Select
Return Nothing
End Using
End Function
Private Sub BindDummyRow()
Dim dummy As DataTable = New DataTable()
dummy.Columns.Add("CustomerID")
dummy.Columns.Add("ContactName")
dummy.Columns.Add("ContactTitle")
dummy.Columns.Add("City")
dummy.Columns.Add("PostalCode")
dummy.Columns.Add("Country")
dummy.Rows.Add()
gvCustomers.DataSource = dummy
gvCustomers.DataBind()
End Sub
Protected Sub OnSelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim customerID As String = hfId.Value
Dim contactName As String = hfName.Value
Dim contactTitle As String = hfTitle.Value
Dim city As String = hfCity.Value
Dim postalCode As String = hfPostalCode.Value
Dim country As String = hfCountry.Value
lblContactName.Text = contactName
End Sub
<WebMethod>
Public Shared Function GetCustomers(ByVal searchTerm As String, ByVal pageIndex As Integer) As String
Dim query As String = "[GetCustomerPagination]"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@City", 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(ByVal cmd As SqlCommand, ByVal pageIndex As Integer) As DataSet
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As DataSet = New DataSet()
sda.Fill(ds, "Customers")
Return ds
End Using
End Using
End Using
End Function
Screenshot
