Load on demand data in Gridview using Jquery

Load on demand data in Gridview using Jquery

Description:

In this article I'm going to explain how to load data on demand using Jquery. In some cases to improve the performance of application we are loading data on demand basis, rather than load all the records at a time, in this article I'm going to explain how to achieve this.

HTML Markup:

Design div tag and place a gridview control inside that and set the div style property of overflow to auto.

<div id="dvGrid" style="height: 200px; overflow: auto; width: 417px">
   <asp:GridView ID="gvStudentInfo" runat="server" AutoGenerateColumns="false" CssClass="grid" Width="400">
       <Columns>
          <asp:BoundField DataField="Student_Name" HeaderText="Student Name" ItemStyle-Width="200" HeaderStyle-Width="200" />
          <asp:BoundField DataField="Addres" HeaderText="Address" ItemStyle-Width="200" HeaderStyle-Width="200" />
       </Columns>
   </asp:GridView>
</div>

Stored Procedure to fetch records on demand:

 

Since we are loading data on demand I prepare stored procedure with paging option, because we are loaded records based on index and page count.

 

 

 

 

CREATE PROCEDURE GetStudentsPageWise

@PageIndex INT, @PageSize INT, @PageCount INT OUTPUT

AS

/*

    pagging in procedure side

*/

BEGIN

    SET NOCOUNT ON;

    SELECT ROW_NUMBER() OVER ( ORDER BY [StudentID] ASC) AS RowNumber,

           [StudentID],

           [Student_Name],

           [Addres]

    INTO   #Results

    FROM   StudentInfo;

    DECLARE @RecordCount AS INT;

    SELECT @RecordCount = COUNT(*)

    FROM   #Results;

    SET @PageCount = CEILING(CAST (@RecordCount AS DECIMAL (10, 2)) / CAST (@PageSize AS DECIMAL (10, 2)));

    SELECT *

    FROM   #Results

    WHERE  RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 AND (((@PageIndex - 1) * @PageSize + 1) + @PageSize) - 1;

    DROP TABLE #Results;

END  

Binding Gridview:

 

Use appropriate namespaces for the same. 

 

 

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            gvStudentInfo.DataSource = GetStudentsPageWise(1, 10);
            gvStudentInfo.DataBind();
        }
    }
    public static DataSet GetStudentsPageWise(int pageIndex, int pageSize)
    {
        string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("[GetStudentsPageWise]"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", pageSize);
                cmd.Parameters.Add("@PageCount", 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, "Students");
                        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;
                    }
                }
            }
        }
    }

 

In the above code based on PageIndex and PageSize as requested it's loading the records, but as per design we are fixing the height of the div tag is 200px; when the records height meets that range then scroll bar is enabled, whenever we scroll the header content will move to top, we can't able to see the header if we scroll down the records. To overcome this situation we can rearrange markup for fixed header like below

Fixed Grid Headers:

 

Keep the below design above the div tag.

<table class="Grid" cellspacing="0" rules="all" border="1" id="Table1" style="width: 400px; border-collapse: collapse;">
    <tr>
       <th scope="col" style="width: 200px;">Student Name</th>
       <th scope="col" style="width: 200px;">Address</th>
    </tr>
</table>

 

With the above steps the Gridview contains 2 headers, one is default header and another one is as we design in above step for fixed header. In Later steps we can remove the default header to give the unique style for grid control.

 

 

$(function () {
   //Remove the original GridView header
   $("[id$=gvStudentInfo] tr").eq(0).remove();
});

Load records on demand using Jquery:

 

If we want to load database records (server side )using Jquery we have only one option i.e. using AJAX call, for that we require service method. 

 

For the above scenario I just create a service method in my server side like below. 

 

[WebMethod]
public static string GetStudents(int pageIndex)
{
    System.Threading.Thread.Sleep(2000);
    return GetStudentsPageWise(pageIndex, 10).GetXml();
}

 

 

Now, the next step is call the above webmethod using AJAX and append the records to gridview control. Use below code snippet for that.

 

<script type="text/javascript" src="../1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    var pageIndex = 1;
    var pageCount;
    $(function () {
        //Remove the original GridView header
        $("[id$=gvStudentInfo] tr").eq(0).remove();
    });
           
    //Load GridView Rows when DIV is scrolled
    $("#dvGrid").on("scroll", function (e) {
        var $o = $(e.currentTarget);
        if ($o[0].scrollHeight - $o.scrollTop() <= $o.outerHeight()) {
            LoadRecords();
        }
    });

    //Function to make AJAX call to the Web Method
    function LoadRecords() {
       pageIndex++;
       if (pageIndex == 2 || pageIndex <= pageCount) {

            //Show Loader
            if ($("[id$=gvStudentInfo] .loader").length == 0) {
                var row = $("[id$=gvStudentInfo] 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="loaderimage.gif"  /></td>');
                $("[id$=gvStudentInfo]").append(row);
            }
                    
            $.ajax({
                 type: "POST",
                 url: "CS.aspx/GetStudents",
                 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 to recieve XML response append rows to GridView
    function OnSuccess(response) {
       var xmlDoc = $.parseXML(response.d);
       var xml = $(xmlDoc);
       pageCount = parseInt(xml.find("PageCount").eq(0).find("PageCount").text());
       var students = xml.find("Students");
       $("[id$=gvStudentInfo] .loader").remove();
       students.each(function (index) {
       var student = $(this);
                 
       AppendNewRow ('[id$=gvStudentInfo]', '<tr><td>' + student.find("Student_Name").text() + '</td><td>' + student.find("Addres").text() + '</td></tr>');
       });

       //Hide Loader
       $("#loader").hide();
   }

   function AppendNewRow(table, rowcontent) {
       if ($(table).length > 0) {
          if ($(table + ' > tbody').length == 0) 
              $(table).append('<tbody />');
          ($(table + ' > tr').length > 0) ? $(table).children('tbody:last').children('tr:last').append(rowcontent) : $(table).children('tbody:last').append(rowcontent);
       }
   }
</script>

Using above client side script, I just call the webmethod service data and append that to my gridview control. After follow above all steps the output looks like below.

Output:

 

 

 

Referenced Links

 

I refer below link to understand better for implement the above requirement. Load on demand for Gridview

 

Conclusion:

Hope this article will helpful those who are looking for the same. If you have any help regarding this please post your comments below.