Hi arehman,
Check this example. Now please take its reference and correct your code.
Using the below article i have created the example.
DataBase
The following screenshot displays database table with images stored in it. The backup file of the database is present in the below article sample.
SQL
CREATE PROCEDURE [dbo].[Files_GetFiles_Pager]
@SearchTerm VARCHAR(100) = ''
,@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [Id] ASC
)AS RowNumber
,[Name]
,[Id]
,[Data]
INTO #Results
FROM tblFiles1
WHERE [Name] 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
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<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>
<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/GetFiles",
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 files = xml.find("Files");
if (row == null) {
row = $("[id*=gvFiles] tr:last-child").clone(true);
}
$("[id*=gvFiles] tr").not($("[id*=gvFiles] tr:first-child")).remove();
if (files.length > 0) {
$.each(files, function () {
var employee = $(this);
$("td", row).eq(0).html($(this).find("Id").text());
$("td", row).eq(1).html($(this).find("Name").text());
$("td", row).eq(2).html("<img src='" + $(this).find("Image").text() + "' alt='" + $(this).find("Name").text() + "' height='100px' width='100px' />");
$("[id*=gvFiles]").append(row);
row = $("[id*=gvFiles] 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())
});
$(".Name").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*=gvFiles]").append(empty_row);
}
};
</script>
</head>
<body>
<form id="form1" runat="server">
Search:
<asp:TextBox ID="txtSearch" runat="server" />
<hr />
<asp:GridView ID="gvFiles" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" />
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-CssClass="Name" />
<asp:ImageField DataImageUrlField="Data" HeaderText="Image" />
</Columns>
</asp:GridView>
<br />
<div class="Pager">
</div>
</form>
</body>
</html>
Namespaces
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
VB.Net
Imports System.Data
Imports System.Web.Services
Imports System.Data.SqlClient
Code
C#
private static int PageSize = 2;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDummyRow();
}
}
private void BindDummyRow()
{
DataTable dummy = new DataTable();
dummy.Columns.Add("Id");
dummy.Columns.Add("Name");
dummy.Columns.Add("Data");
dummy.Rows.Add();
gvFiles.DataSource = dummy;
gvFiles.DataBind();
}
[WebMethod]
public static string GetFiles(string searchTerm, int pageIndex)
{
string query = "[Files_GetFiles_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["constr"].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, "Files");
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["Files"].Columns.Add("Image");
for (int i = 0; i < ds.Tables["Files"].Rows.Count; i++)
{
byte[] imgBytes = (byte[])ds.Tables["Files"].Rows[i]["Data"];
string base64String = Convert.ToBase64String(imgBytes, 0, imgBytes.Length);
ds.Tables["Files"].Rows[i]["Image"] = "data:image/png;base64," + base64String;
}
ds.Tables.Add(dt);
return ds;
}
}
}
}
VB.Net
Private Shared PageSize As Integer = 2
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindDummyRow()
End If
End Sub
Private Sub BindDummyRow()
Dim dummy As DataTable = New DataTable()
dummy.Columns.Add("Id")
dummy.Columns.Add("Name")
dummy.Columns.Add("Data")
dummy.Rows.Add()
gvFiles.DataSource = dummy
gvFiles.DataBind()
End Sub
<WebMethod()>
Public Shared Function GetFiles(ByVal searchTerm As String, ByVal pageIndex As Integer) As String
Dim query As String = "[Files_GetFiles_Pager]"
Dim cmd As SqlCommand = 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(ByVal cmd As SqlCommand, ByVal pageIndex As Integer) As DataSet
Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(strConnString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As DataSet = New DataSet()
sda.Fill(ds, "Files")
Dim dt As DataTable = 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("Files").Columns.Add("Image")
For i As Integer = 0 To ds.Tables("Files").Rows.Count - 1
Dim imgBytes As Byte() = CType(ds.Tables("Files").Rows(i)("Data"), Byte())
Dim base64String As String = Convert.ToBase64String(imgBytes, 0, imgBytes.Length)
ds.Tables("Files").Rows(i)("Image") = "data:image/png;base64," & base64String
Next
ds.Tables.Add(dt)
Return ds
End Using
End Using
End Using
End Function
Screenshot