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

Last Reply 7 months ago By dharmendr

Posted 7 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 7 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 7 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 7 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 7 months ago
Pushpa says:
I am not getting Columns for below stored procedure.

 Will you please explain the above statement in more details.