Pages

Load partial data in Gridview using JQuery Ajax

Data controls in asp.net, by default, display all the data that you provide them. However some (in fact many) time, you may want to display only first few records initially and remaining on subsequent user interactions. There are number of ways to achieve this. For example, enabling pagination support on the control (if it supports), implementing server side pagination in your stored procedures or writing custom logic in application code to fetch only required sets of data etc.

Here I am going to show another way where you can load data from database in sets and then bind the gridview, using JQuery and Ajax. Note that other data controls can also be bound using this approach. To better visualize what I am going to do, imagine your facebook wall that loads updates as you scroll down in the browser and not all in one go.

You can find the source code here.

For this sample, I will be using pubs database (you can download pubs sample database from here) to display all the records from 'authors' table. To start with, add a gridview and html button control to aspx page (say, AuthorList.aspx).
<asp:GridView runat="server" ID="gvAuthors" />
<input type="button" id="btnLoad" value="load more" />
Then we need a method that we can call from JQuery script to fetch data from pubs database. So I have a webmethod, GetAuthors(), in AuthorList.aspx code behind.
[WebMethod]
public static IList<Author> GetAuthors(int startRecord, int maxRecords)
{
    BusinessLogic bl = new BusinessLogic();
    IList<Author> authors = bl.GetAuthors(startRecord, maxRecords);

    return authors;
}
BusinessLogic class has a method called GetAuthors which returns the list of authors from pubs database. Start index of result set and records to be fetched are specified by startRecord and maxRecords parameters respectively. Author class is nothing but a business object that represents each row of the 'pubs.dbo.authors' table.
public IList<Author> GetAuthors(int startRecord, int maxRecords)
{
    var data = new DataAccess(connectionString);

    // Avoid sending queries through application code. Use stored procedures.
    DataSet ds = data.GetDataSet("select au_id, au_fname, au_lname, city, state from authors", startRecord, maxRecords, "authors");

    IList<Author> authors = new List<Author>();

    foreach (DataRow row in ds.Tables["authors"].Rows)
    {
        authors.Add(new Author
        {
            ID = (string)row["au_id"],
            FirstName = (string)row["au_fname"],
            LastName = (string)row["au_lname"],
            City = (string)row["city"],
            State = (string)row["state"]
        });
    }

    return authors;
}
Logic is to load initial set of data in traditional way i.e. by calling BindGrid() method in load event of AuthorList.aspx page, and then loading additional sets using client script and ajax.
private const int MAX_RECORDS = 5;

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
        BindGrid();
}

private void BindGrid()
{
    // This is first call to db
    gvAuthors.DataSource = GetAuthors(0, MAX_RECORDS);
    gvAuthors.DataBind();
}
Now that we are done with the server side code, lets write client script (JQuery) that will make Ajax request to load additional data from database.
function loadData() {
    // Since first set was fetched by server code.
    requestCount = requestCount + 1;

    var startRecord = requestCount * maxRecords;

    $.ajax({
        type: "POST",
        url: "AuthorList.aspx/GetAuthors",
        data: "{ \"startRecord\":" + startRecord + ", \"maxRecords\":" + maxRecords + " }",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: bindGrid
    });
}
This loadData() function is attached to click event of the 'load more' html button when the page is loaded.
$(document).ready(function () {
    $('#btnLoad').click(loadData);
});
On success of ajax request we receive response data in bindGrid() function and bind this data to the gridview.
function bindGrid(data) {
    // Get list of authors from response.
    var dataRows = data.d;

    if (dataRows.length == 0) {
        alert('no more data to load');
        return;
    }

    for (var i = 0; i < dataRows.length; i++) {
        $('#gvAuthors')
            .append('<tr>' + '<td>' + dataRows[i].ID + '</td>' +
                    '<td>' + dataRows[i].FirstName + '</td>' +
                    '<td>' + dataRows[i].LastName + '</td>' +
                    '<td>' + dataRows[i].City + '</td>' +
                    '<td>' + dataRows[i].State + '</td>' +
                '</tr>');
    }
}
I think this will suffice to give idea about what's done here, moreover, I have provided link to the complete source code above.

---

10 comments:

  1. Hola,

    me ha servido tu artículo, gracias.

    Pero no me pintaba los datos, la solución ha sido poner en el grid:
    ClientIDMode="static"

    ReplyDelete
  2. Hola de nuevo,

    otra solución diferente, es añadir dentro del for:
    $("#<%=GridView1.ClientID%>")

    ReplyDelete
  3. Thanks for the post but I am getting the below error:

    No such interface supported error coming from $.ajax method

    append: function() {

    return this.domManip(arguments, true, false, function(elem){

    if (this.nodeType == 1)

    this.appendChild( elem ); // No such interface supported error

    });

    ReplyDelete
  4. hello nice Post ..Thanks..and there is no error

    ReplyDelete
  5. This Code is not working...
    it returns data in array from *.aspx.cs page after insertion but grid is not displaying that new record...

    ReplyDelete
    Replies
    1. The code above was tested and even worked for others. Please make sure if you are not missing something. Debug or log to console to see what is going wrong.

      Delete
  6. gridrow event will be lost if do this way

    ReplyDelete
  7. grid row events will be lost if to do by this way

    ReplyDelete
    Replies
    1. There are times when you don't need them.

      Delete