Load on demand data in GridView with records from multiple Tables in ASP.Net

Here i have created sample that shows the data of two table(inner join).

HTML

<div>
    <table class="Grid" cellspacing="0" rules="all" border="1" id="Table1" style="width: 500px;
        border-collapse: collapse;">
        <tr>
            <th scope="col" style="width: 200px;">
                Customer Name
            </th>
            <th scope="col" style="width: 100px;">
                City
            </th>
            <th scope="col" style="width: 100px;">
                Country
            </th>
            <th scope="col" style="width: 100px;">
                Order Ship City
            </th>
        </tr>
    </table>
    <div id="dvGrid" style="height: 250px; overflow: auto; width: 517px">
        <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" CssClass="Grid"
            Width="500">
            <Columns>
                <asp:BoundField DataField="ContactName" HeaderText="Customer Name" ItemStyle-CssClass="name"
                    ItemStyle-Width="200" HeaderStyle-Width="200" />
                <asp:BoundField DataField="City" HeaderText="City" ItemStyle-CssClass="city" ItemStyle-Width="100"
                    HeaderStyle-Width="100" />
                <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-CssClass="country"
                    ItemStyle-Width="100" HeaderStyle-Width="100" />
                <asp:BoundField DataField="ShipCity" HeaderText="Ship City" ItemStyle-CssClass="shipCity"
                    ItemStyle-Width="100" HeaderStyle-Width="100" />
            </Columns>
        </asp:GridView>
    </div>
    <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;
        $(function () {
            $("[id$=gvCustomers] tr").eq(0).remove();
        });

        $("#dvGrid").on("scroll", function (e) {
            var $o = $(e.currentTarget);
            if ($o[0].scrollHeight - $o.scrollTop() <= $o.outerHeight()) {
                GetRecords();
            }
        });

        function GetRecords() {
            pageIndex++;
            if (pageIndex == 2 || pageIndex <= pageCount) {


                if ($("[id$=gvCustomers] .loader").length == 0) {
                    var row = $("[id$=gvCustomers] tr").eq(0).clone(true);
                    row.addClass("loader");
                    row.children().remove();
                    row.append('<td colspan = "999" style = "background-color:white"><img id="loader" alt="" src="103.gif"  /></td>');
                    $("[id$=gvCustomers]").append(row);
                }
                $.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");
            $("[id$=gvCustomers] .loader").remove();
            customers.each(function () {
                var customer = $(this);
                var row = $("[id$=gvCustomers] tr").eq(0).clone(true);
                $(".name", row).html(customer.find("ContactName").text());
                $(".city", row).html(customer.find("City").text());
                $(".country", row).html(customer.find("Country").text());
                $(".shipCity", row).html(customer.find("ShipCity").text());
                $("[id$=gvCustomers]").append(row);
            });

            $("#loader").hide();
        }
    </script>
</div>

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        gvCustomers.DataSource = GetCustomersPageWise(1, 10);
        gvCustomers.DataBind();
    }
}

public static DataSet GetCustomersPageWise(int pageIndex, int pageSize)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("[Customer_GetCustomerWithPaging]"))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", pageSize);
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds, "Customers");
                    DataTable dt = new DataTable("RecordCount");
                    dt.Columns.Add("RecordCount");
                    dt.Rows.Add();
                    dt.Rows[0][0] = cmd.Parameters["@RecordCount"].Value;
                    ds.Tables.Add(dt);
                    return ds;
                }
            }
        }
    }
}

[WebMethod]
public static string GetCustomers(int pageIndex)
{
    //Added to similate delay so that we see the loader working
    //Must be removed when moving to production
    System.Threading.Thread.Sleep(2000);

    return GetCustomersPageWise(pageIndex, 10).GetXml();
}

SQL

--EXEC Customer_GetCustomerWithPaging 3,10,NULL
CREATE PROCEDURE [Customer_GetCustomerWithPaging]
	  @PageIndex int,
	  @PageSize int,
	  @RecordCount int=NULL OUTPUT
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT ROW_NUMBER() OVER
			(
				ORDER BY [Customers].[CustomerID]
			) AS RowNumber
			,[Customers].[CustomerID]
			,[ContactName]		  
			,[City]
			,[Country]
			,[Orders].ShipCity			
	INTO #Results
	FROM [Customers]
	INNER JOIN Orders
	ON [Customers].CustomerId = Orders.[CustomerID]
	GROUP BY [Customers].[CustomerID],[ContactName],[City],[Country],[Orders].ShipCity	

	SELECT @RecordCount = COUNT(*) FROM #Results

	SELECT * from #Results 
	WHERE RowNumber BETWEEN ((@PageIndex-1)*@PageSize)+1 AND ((@PageIndex-1)*@PageSize)+@PageSize OR @PageIndex = -1

	DROP TABLE #Results
END

Screenshot