In this article I will explain how to dynamically load images from database along with other information when is page scrolling down using DataList control in ASP.Net.
Database
Below is the design of the table that will store the URL of the image files along with some other information.
HTML Markup
The HTML Markup consists of an ASP.Net DataList control which will be used as a Template to recreate items when page is scrolled.
<asp:DataList ID="dlImages" runat="server" RepeatLayout="Table" RepeatColumns="3"
CellPadding="2" CellSpacing="20">
<ItemTemplate>
<table class="item" cellpadding="0" cellspacing="0" border="0">
<tr>
<td align="center" class="header">
<span class="name">
<%# Eval("Name") %></span>
</td>
</tr>
<tr>
<td align="center" class="body">
<img class="image" src='<%# Eval("Url") %>' alt="" />
</td>
</tr>
<tr>
<td align="center">
Photo by <a href="http://www.flickr.com/photos/pearlshelf/">Pearl Photo</a>
</td>
</tr>
<tr>
<td class="footer" align="center">
<a href='<%# Eval("Url") %>' target="_blank" class="button">View</a>
<input type="hidden" class="is_used" value="0" />
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
Implement Pagination in SQL Server Stored Procedure
Since we need to get data on demand i.e. in parts we need to have pagination capability in our stored procedure so that we can fetch records based on page index or page number. This stored procedure returns Total Page Count as OUPUT parameter; this count will help notify jQuery that it should stop making AJAX calls as the all the data has been fetched.
CREATE PROCEDURE [dbo].[GetImagesPageWise]
@PageIndex INT = 1
,@PageSize INT = 3
,@PageCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [Id] ASC
)AS RowNumber
,Id
,Name
,Url
INTO #Results
FROM [Images]
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM #Results
SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
PRINT @PageCount
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
Binding a dummy record to the DataList
In the Page Load event, I am binding a dummy record to the DataList control. This is very necessary as jQuery needs some HTML content which it can replicate the data fetched via jQuery AJAX.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindDummyItem();
}
}
private void BindDummyItem()
{
DataTable dummy = new DataTable();
dummy.Columns.Add("Id");
dummy.Columns.Add("Name");
dummy.Columns.Add("Url");
int count = dlImages.RepeatColumns == 0 ? 1 : dlImages.RepeatColumns;
for (int i = 0; i < count; i++)
{
dummy.Rows.Add();
}
dlImages.DataSource = dummy;
dlImages.DataBind();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.BindDummyItem()
End If
End Sub
Private Sub BindDummyItem()
Dim dummy As New DataTable()
dummy.Columns.Add("Id")
dummy.Columns.Add("Name")
dummy.Columns.Add("Url")
Dim count As Integer = If(dlImages.RepeatColumns = 0, 1, dlImages.RepeatColumns)
For i As Integer = 0 To count - 1
dummy.Rows.Add()
Next
dlImages.DataSource = dummy
dlImages.DataBind()
End Sub
WebMethod to handle AJAX calls from jQuery and fetch
The following web method handles the jQuery AJAX calls when page is scrolled down. It simply fetches the records from the database and the returns the XML to the client.
Along with the data, the count of the total pages is also sent so that the client side function is informed whether the last page has been fetched.
C#
[WebMethod]
public static string GetImages(int pageIndex)
{
return GetImagesData(pageIndex).GetXml();
}
public static DataSet GetImagesData(int pageIndex)
{
int pageSize = 9;
string query = "[GetImagesPageWise]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
return GetData(cmd);
}
private static DataSet GetData(SqlCommand cmd)
{
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, "Images");
DataTable dt = new DataTable("PageCount");
dt.Columns.Add("PageCount");
dt.Rows.Add();
dt.Rows[0][0] = cmd.Parameters["@PageCount"].Value;
ds.Tables.Add(dt);
return ds;
}
}
}
}
VB.Net
<WebMethod()> _
Public Shared Function GetImages(pageIndex As Integer) As String
Return GetImagesData(pageIndex).GetXml()
End Function
Public Shared Function GetImagesData(pageIndex As Integer) As DataSet
Dim pageSize As Integer = 9
Dim query As String = "[GetImagesPageWise]"
Dim cmd As New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", pageSize)
cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
Return GetData(cmd)
End Function
Private Shared Function GetData(cmd As SqlCommand) As DataSet
Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").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, "Images")
Dim dt As New DataTable("PageCount")
dt.Columns.Add("PageCount")
dt.Rows.Add()
dt.Rows(0)(0) = cmd.Parameters("@PageCount").Value
ds.Tables.Add(dt)
Return ds
End Using
End Using
End Using
End Function
jQuery AJAX and Client Side implementation
Below is the client side implementation where the actual job is done, here I have attached a jQuery scroll event handler to the page. When the page is scrolled a jQuery AJAX call is sent to the server for fetching the records.
Inside the Success event handler, the response XML is parsed and the records along with the maximum page count is retrieved.
Then based on the DataList RepeatColumns property value, the DataList Items are cloned and appended to the page with the values fetched from the server.
C#
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
var pageIndex = 0;
var pageCount;
$(window).scroll(function () {
if ($(window).scrollTop() == $(document).height() - $(window).height()) {
GetRecords();
}
});
$(function () {
GetRecords();
});
function GetRecords() {
pageIndex++;
if (pageIndex == 1 || pageIndex <= pageCount) {
$("#loader").show();
$.ajax({
type: "POST",
url: "CS.aspx/GetImages",
data: '{pageIndex: ' + pageIndex + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.responseText);
},
error: function (response) {
alert(response.responseText);
}
});
}
}
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
pageCount = parseInt(xml.find("PageCount").eq(0).find("PageCount").text());
var images = xml.find("Images");
var repeatColumns = parseInt("<%=dlImages.RepeatColumns == 0 ? 1 : dlImages.RepeatColumns %>");
var rowCount = Math.ceil(images.length / repeatColumns);
var j = 0;
images.each(function () {
var image = $(this);
var row = $("[id*=dlImages] .item:last").closest("tr");
if ($(".is_used[value='1']", row).length == repeatColumns) {
row = $("[id*=dlImages] tr").eq(0).clone();
$(".is_used", row).val("0");
$(".image", row).attr("src", "");
$(".button", row).attr("href", "");
$(".loader", row).remove();
$("[id*=dlImages]").append(row);
j = 0;
} else {
row = $("[id*=dlImages] .item:last").closest("tr");
}
var cell = $(".item", row).eq(j);
$(".name", cell).html(image.find("Name").text());
$(".button", cell).attr("href", image.find("Url").text());
$(".is_used", cell).attr("value", "1");
var img = $(".image", cell);
var loader = $("<img class = 'loader' src = 'loader.gif' />");
img.after(loader);
img.hide();
img.attr("src", image.find("Url").text());
img.load(function () {
$(this).parent().find(".loader").remove();
$(this).fadeIn();
});
j++;
});
$("[id*=dlImages] .is_used[value='0']").closest(".item").remove();
}
</script>
VB.Net
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
var pageIndex = 0;
var pageCount;
$(window).scroll(function () {
if ($(window).scrollTop() == $(document).height() - $(window).height()) {
GetRecords();
}
});
$(function () {
GetRecords();
});
function GetRecords() {
pageIndex++;
if (pageIndex == 1 || pageIndex <= pageCount) {
$("#loader").show();
$.ajax({
type: "POST",
url: "VB.aspx/GetImages",
data: '{pageIndex: ' + pageIndex + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.responseText);
},
error: function (response) {
alert(response.responseText);
}
});
}
}
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
pageCount = parseInt(xml.find("PageCount").eq(0).find("PageCount").text());
var images = xml.find("Images");
var repeatColumns = parseInt("<%=IIf(dlImages.RepeatColumns = 0, 1, dlImages.RepeatColumns) %>");
var rowCount = Math.ceil(images.length / repeatColumns);
var j = 0;
images.each(function () {
var image = $(this);
var row = $("[id*=dlImages] .item:last").closest("tr");
if ($(".is_used[value='1']", row).length == repeatColumns) {
row = $("[id*=dlImages] tr").eq(0).clone();
$(".is_used", row).val("0");
$(".image", row).attr("src", "");
$(".button", row).attr("href", "");
$(".loader", row).remove();
$("[id*=dlImages]").append(row);
j = 0;
} else {
row = $("[id*=dlImages] .item:last").closest("tr");
}
var cell = $(".item", row).eq(j);
$(".name", cell).html(image.find("Name").text());
$(".button", cell).attr("href", image.find("Url").text());
$(".is_used", cell).attr("value", "1");
var img = $(".image", cell);
var loader = $("<img class = 'loader' src = 'loader.gif' />");
img.after(loader);
img.hide();
img.attr("src", image.find("Url").text());
img.load(function () {
$(this).parent().find(".loader").remove();
$(this).fadeIn();
});
j++;
});
$("[id*=dlImages] .is_used[value='0']").closest(".item").remove();
}
</script>
Demo
Downloads
source : http://www.aspsnippets.com/Articles/Load-images-while-scrolling-page-down-with-jQuery-AJAX-in-ASPNet.aspx
No comments:
Post a Comment