Bind Dataset to ASP.Net GridView using jQuery AJAX

In this article I will explain how to bind Dataset to ASP.Net GridView control using jQuery AJAX and Web Methods.

Database and Connection string

For this sample to work you will need to download the Microsoft Northwind database using the following link

Download Northwind Database

Below is the connection string from the Web.Config file

<connectionStrings>

<add name="conString" connectionString="Data Source=.\SQLExpress;

database=Northwind;Integrated Security=true"/>

</connectionStrings>

 

GridView Markup

The GridView markup is very simple with three bound fields.

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" Font-Names="Arial"

    Font-Size="10pt" RowStyle-BackColor="#A1DCF2" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor = "White">

    <Columns>

        <asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />

        <asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="CustomerID" />

        <asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />

    </Columns>

</asp:GridView>

 

Namespaces

You will have to inherit the following namespaces in order to use this sample

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Web.Services;

 

Binding the Dummy row

You will need to bind the one dummy row to the ASP.Net GridView so that the ASP.Net GridView is rendered. Also the dummy row is required to retain the CSS styles and formatting of the row.

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

    {

        this.BindDummyRow();

    }

}

 

private void BindDummyRow()

{

    DataTable dummy = new DataTable();

    dummy.Columns.Add("CustomerID");

    dummy.Columns.Add("ContactName");

    dummy.Columns.Add("City");

    dummy.Rows.Add();

    gvCustomers.DataSource = dummy;

    gvCustomers.DataBind();

}

Above you will see I have created a dummy DataTable with three columns that I need to bind to the ASP.Net GridView.

 

WebMethod to handler jQuery AJAX

Now we will create a Web method that will actually handle the AJAX requests from the jQuery AJAX handler and return the records fetched from the Customers table of the Northwind database as a string of XML.

[WebMethod]

public static string GetCustomers()

{

    string query = "SELECT top 10 CustomerID, ContactName, City FROM Customers";

    SqlCommand cmd = new SqlCommand(query);

    return GetData(cmd).GetXml();

}

private static DataSet GetData(SqlCommand cmd)

{

    string strConnString = ConfigurationManager.ConnectionStrings["conString"].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);

                return ds;

 

            }

        }

    }

}

Above you can see I am executing a simple select query on the Customers table of the Northwind database using the GetData function DataSet object. This dataset object is later on converted to XML using the GetXml() method in the web method.

 

Client side functionality

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>

<script type="text/javascript">

    $(function () {

        $.ajax({

            type: "POST",

            url: "Default.aspx/GetCustomers",

            data: '{}',

            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);

        var customers = xml.find("Table");

        var row = $("[id*=gvCustomers] tr:last-child").clone(true);

        $("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();

        $.each(customers, function () {

            var customer = $(this);

            $("td", row).eq(0).html($(this).find("CustomerID").text());

            $("td", row).eq(1).html($(this).find("ContactName").text());

            $("td", row).eq(2).html($(this).find("City").text());

            $("[id*=gvCustomers]").append(row);

            row = $("[id*=gvCustomers] tr:last-child").clone(true);

        });

    }

</script>

In the above code snippet a jQuery AJAX call is made to the Web method GetCustomers in the document ready event of the page. The web method then returns an XML string which is parsed using the jQuery parseXML method. Once the XML document is created rows are created and appended to the ASP.Net GridView control.

 

Screenshots

The below screenshot displays the ASP.Net GridView control which has been populated using jQuery AJAX and ASP.Net Web methods.

Downloads

You can download the complete source code for this article using the download link provided below

BindGridViewjQuery.zip