Implement Infinite Scroll in ASP.Net using jQuery AJAX and Linq

Last Reply 10 months ago By dharmendr

Posted 10 months ago

Implement Infinite Scroll (Endless Scroll) in ASP.Net using jQuery AJAX how to do this using Linq

public static DataSet GetCustomersData(int pageIndex)
{
    string query = "[GetCustomersPageWise]";
   SqlCommand cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    cmd.Parameters.AddWithValue("@PageSize", 10);
    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, "Customers");
                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;
            }
        }
    }
}
Posted 10 months ago
Hi @Pushpa,
Please try the following

Implement Infinite Scroll on Nested Repeater using jQuery AJAX in ASP.Net

It might help you.

Cheers Andrea.

Posted 10 months ago

Hi Pushpa,

For calling stored procedure using entity framework refer below article.

Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <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 = 1;
        var pageCount;
        $(window).scroll(function () {
            if ($(window).scrollTop() == $(document).height() - $(window).height()) {
                GetRecords();
            }
        });
        function GetRecords() {
            pageIndex++;
            if (pageIndex == 2 || pageIndex <= pageCount) {
                $("#loader").show();
                $.ajax({
                    type: "POST",
                    url: "CS.aspx/GetCustomers",
                    data: '{pageIndex: ' + pageIndex + '}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: OnSuccess,
                    failure: function (response) {
                        alert(response.d);
                    },
                    error: function (response) {
                        alert(response.d);
                    }
                });
            }
        }
        function OnSuccess(response) {
            var xmlDoc = $.parseXML(response.d);
            var xml = $(xmlDoc);
            pageCount = parseInt(xml.find("PageCount").eq(0).find("PageCount").text());
            var customers = xml.find("Customers");
            customers.each(function (index, item) {
                if (index > 0) {
                    var customer = $(this);
                    var table = $("#dvCustomers table").eq(0).clone(true);
                    $(".name", table).html(customer.find("ContactName").text());
                    $(".city", table).html(customer.find("City").text());
                    $(".postal", table).html(customer.find("PostalCode").text());
                    $(".country", table).html(customer.find("Country").text());
                    $(".phone", table).html(customer.find("Phone").text());
                    $(".fax", table).html(customer.find("Fax").text());
                    $("#dvCustomers").append(table).append("<br />");
                }
            });
            $("#loader").hide();
        }
    </script>
</head>
<body style="font-family: Arial; font-size: 10pt">
    <form id="form1" runat="server">
    <table>
        <tr>
            <td>
                <div id="dvCustomers">
                    <asp:Repeater ID="rptCustomers" runat="server">
                        <ItemTemplate>
                            <table cellpadding="2" cellspacing="0" border="1" style="width: 200px; height: 100px;
                                border: dashed 2px #04AFEF; background-color: #B0E2F5">
                                <tr>
                                    <td>
                                        <b><u><span class="name">
                                            <%# Eval("ContactName") %></span></u></b>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        <b>City: </b><span class="city">
                                            <%# Eval("City") %></span><br />
                                        <b>Postal Code: </b><span class="postal">
                                            <%# Eval("PostalCode") %></span><br />
                                        <b>Country: </b><span class="country">
                                            <%# Eval("Country")%></span><br />
                                        <b>Phone: </b><span class="phone">
                                            <%# Eval("Phone")%></span><br />
                                        <b>Fax: </b><span class="fax">
                                            <%# Eval("Fax")%></span><br />
                                    </td>
                                </tr>
                            </table>
                            <br />
                        </ItemTemplate>
                    </asp:Repeater>
                </div>
            </td>
            <td valign="bottom">
                <img id="loader" alt="" src="loading.gif" style="display: none" />
            </td>
        </tr>
    </table>
    </form>
</body>
</html>

Namespaces

using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Configuration;
using NorthwindModel;
using System.Data.Objects;

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        rptCustomers.DataSource = GetCustomersData(1);
        rptCustomers.DataBind();
    }
}
public static DataSet GetCustomersData(int pageIndex)
{
    NorthwindEntities entities = new NorthwindEntities();
    ObjectParameter pageCount = new ObjectParameter("PageCount", typeof(int));
    var result = entities.CustomerPager(pageIndex, 10, pageCount);
    DataTable dt = new DataTable("Customers");
    dt.Columns.Add("CustomerID", typeof(string));
    dt.Columns.Add("ContactName", typeof(string));
    dt.Columns.Add("CompanyName", typeof(string));
    dt.Columns.Add("City", typeof(string));
    dt.Columns.Add("Country", typeof(string));
    dt.Columns.Add("PostalCode", typeof(string));
    dt.Columns.Add("Phone", typeof(string));
    dt.Columns.Add("Fax", typeof(string));
    foreach (var item in result)
    {
        DataRow row = dt.NewRow();
        row["CustomerID"] = item.CustomerID;
        row["ContactName"] = item.ContactName;
        row["CompanyName"] = item.CompanyName;
        row["City"] = item.City;
        row["Country"] = item.Country;
        row["PostalCode"] = item.PostalCode;
        row["Phone"] = item.Phone;
        row["Fax"] = item.Fax;
        dt.Rows.Add(row);
    }

    using (DataSet ds = new DataSet("Customers"))
    {
        ds.Tables.Add(dt);
        DataTable dt1 = new DataTable("PageCount");
        dt1.Columns.Add("PageCount");
        dt1.Rows.Add();
        dt1.Rows[0][0] = Convert.ToInt32(pageCount.Value);
        ds.Tables.Add(dt1);
        return ds;
    }
}
[WebMethod]
public static string GetCustomers(int pageIndex)
{
    return GetCustomersData(pageIndex).GetXml();
}

Posted 10 months ago

Hello,   I am trying to solve my Problem using your solution and using Entity Framework I am not getting Columns for below stored procedure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE GetCustomersPageWise
      @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]
     INTO #Results
      FROM [Customers]
     
      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
GO

Posted 10 months ago
Pushpa says:
I am not getting Columns for below stored procedure.

 Will you please explain the above statement in more details.