• How to Bind Data to GridView using JQuery AJAX

    Posted on October 1, 2012 by in ASP.NET, C#, Dotnet

    In this article I am going to fetch data by asynchronously calling a webservice using JQUERY AJAX method and binding the output to a GridView.

    JQUERY Code:
    Since we are going to use JQUERY to satisfy our requirement, we need to add reference to the script file.

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

    My web service is going to run under a different port than my web page, so I need to enable cross-domain scripting. Failure to do so will result in “Transport Error”.

    //enable cross domain scipting
    $.support.cors = true;
    

    We are calling AJAX method of JQUERY to send (post) a request to web service. While posting a request to the web service we need to set some basic settings to the request

    type: The type of request to make (“POST” or “GET”), default is “GET”. In our case POST

    contentType: When sending data to the server, use this content type. Default is “application/x-www-form-urlencoded; charset=UTF-8”, which is fine for most cases. If you explicitly pass in a content-type to $.ajax(), then it’ll always be sent to the server (even if no data is sent).

    url: A string containing the URL to which the request is sent.,

    data: Data to be sent to the server.

    dataType: The type of data that you’re expecting back from the server. If it is set to “json”, it Evaluates the response as JSON and returns a JavaScript object.

    success(data, textStatus, jqXHR)
    A function to be called if the request succeeds. The function gets passed three arguments: The data returned from the server, formatted according to the dataType parameter; a string describing the status; and the jqXHR (in jQuery 1.4.x, XMLHttpRequest) object

    error(jqXHR, textStatus, errorThrown)
    A function to be called if the request fails. The function receives three arguments: The jqXHR (in jQuery 1.4.x, XMLHttpRequest) object, a string describing the type of error that occurred and an optional exception object, if one occurred. Possible values for the second argument (besides null) are “timeout”, “error”, “abort”, and “parsererror”. When an HTTP error occurs, errorThrown receives the textual portion of the HTTP status, such as “Not Found” or “Internal Server Error.”

    Complete Script Code:
    We don’t need to wait for the whole page is rendered before we make a call to the web service. The handler passed to .ready() is guaranteed to be executed after the DOM is ready, so this is usually the best place to attach all other event handlers and run other jQuery code.  I am going to call the webservice in .ready method itself. Once we get the response (products list), we iterate through all the products, create new row for each product and add them to the gridview.

    <script type="text/javascript">
    $(document).ready(function() {
      //enable cross domain messaging
      $.support.cors = true;        
     //make the async call
     $.ajax({
         type: "POST",
         contentType: "application/json; charset=utf-8",
         url: "http://localhost:24150/DataService.asmx/RetrieveProducts", //webserviceurl
         data: "{}", //empty parameter
         dataType: "json",
         success: function(products) {
                    //get the only row in the gridview.
                    //the idea is to copy the row from the gridview so that it will have all the formatting   
                    //and set product attributes to that row                          
                    var row = $("[id*=gvProducts] tr:last-child").clone(true);
                    //remove the dummy row we have created
                    $("[id*=gvProducts] tr").not($("[id*=gvProducts] tr:first-child")).remove();
                    for (var i = 0; i < products.d.length; i++) {
                        //Set product attributes to the row and add it to the gridview
                        $("td", row).eq(0).html(products.d[i].ProductID);
                        $("td", row).eq(1).html(products.d[i].ProductName);
                        $("td", row).eq(2).html(products.d[i].ProductNumber);
                        $("td", row).eq(3).html(products.d[i].ListPrice);
                        $("[id*=gvProducts]").append(row);
                        //get the copy of the last row again.
                        row = $("[id*=gvProducts] tr:last-child").clone(true);
                    }               
                },
                error: function(XMLHttpRequest, textStatus, errorThrown) {
                    alert(errorThrown);
                }
            });
        });
    </script>
    

    PageDesign:
    Nothing unusual in the page design. Added a gridview control to the page and added four bound columns to the gridview. (ProductID, Name, ProductNumber & ListPrice).

    <asp:GridView ID="gvProducts" CssClass="GridViewStyle"         
    AutoGenerateColumns="false" Width="60%" runat="server">         
    <RowStyle CssClass="RowStyle" />
    <AlternatingRowStyle CssClass="AltRowStyle" />
    <HeaderStyle CssClass="HeaderStyle" />
    <Columns>
        <asp:BoundField DataField="ProductID" HeaderText="Product ID"/>  
        <asp:BoundField DataField="ProductNumber" HeaderText="Product Number"/>  
        <asp:BoundField DataField="Name" HeaderText="Name"/>  
        <asp:BoundField DataField="ListPrice" HeaderText="Price"/>       
    </Columns>
    </asp:GridView>
    

    Source Code:
    We are getting products from the web service and adding them to the gridview as table rows.  When page is initially loaded, there will not be any rows in the gridview so that means the gridview content will not be rendered.  It will not even show the empty header. Our above JQUERY code will be executed after the page rendering is done, so it wont work. In order to get around it, we construct a dummy table out of the columns in gridview design, will create a dummy row and bind it to the gridview.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            FixGridHeader();
        }
    }
    
    private void FixGridHeader()
    {
        DataTable dtDummy = new DataTable();
        DataColumn column;
        //fetch column headers from the gridview and add them to the datatable columns
        foreach(DataControlField field in gvProducts.Columns)
        {
            column = new DataColumn();
            if (field is BoundField)
            {
                BoundField bfield = field as BoundField;
                column.ColumnName = (field as BoundField).DataField;
                dtDummy.Columns.Add(column);
            }                               
        }
        //add a dummy row
        //we need this dummy row so that we can copy formatting from this row while 
        //creating new rows from products list
        dtDummy.Rows.Add();
    
        //bind the datatable to the grid
        gvProducts.DataSource = dtDummy;
        gvProducts.DataBind();
    
    }
    

    Data WebService
    I have created a new web service application and added to the solution.  I am using AdventureWorks database for this example. Open web.config and add following entry to connection strings element

    <add name="Sql" connectionString="Data Source=<SERVERNAME>; Initial Catalog= TEST;User=testuser;Password=testuser;" providerName="System.Data.SqlClient"/> 
    

    I have created a new service class DataService.asmx. Script is enabled for the service so that it can be called by JQUERY AJAX API. I have created a new web method “RetrieveProducts” that would simply retrieve products from database and return them to the caller.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Services;
    using System.Data.SqlClient;
    
    namespace ASPNETDataService
    {
        /// <summary>
        /// Summary description for Service1
        /// </summary>
        [WebService(Namespace = "http://tempuri.org/")]
        [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
        [System.ComponentModel.ToolboxItem(false)]
        // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
        [System.Web.Script.Services.ScriptService]
        public class DataService : System.Web.Services.WebService
        {       
            [WebMethod]
            public List<Product> RetrieveProducts()
            {
                return GetProducts();            
            }
    
            public static List<Product> GetProducts()
            {
                DataSet dsProducts = RetrieveProductsFromDB();
                return PrepareProductsDS(dsProducts);
            }
    
            public static DataSet RetrieveProductsFromDB()
            {
                //fetch the connection string from web.config
                string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    
                //SQL statement to fetch entries from products            
                string sql = @"SELECT TOP 10 P.ProductID, P.Name, P.ProductNumber, P.ListPrice FROM Production.Product P";
    
                DataSet dsProducts = new DataSet();
                //Open SQL Connection
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    //Initialize command object
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                        //Fill the result set
                        adapter.Fill(dsProducts);
                    }
                }
                return dsProducts;
            }
    
            private static List<Product> PrepareProductsDS(DataSet dsProducts)
            {
                List<Product> productsList = new List<Product>();
                if (dsProducts != null && dsProducts.Tables.Count > 0 && dsProducts.Tables[0].Rows.Count > 0)
                {
                    Product product = null;
                    foreach (DataRow dr in dsProducts.Tables[0].Rows)
                    {
                        product = new Product();
                        product.ProductID = Convert.ToInt32(dr["ProductID"]);
                        product.ProductName = Convert.ToString(dr["Name"]);
                        product.ProductNumber = Convert.ToString(dr["ProductNumber"]);
                        product.ListPrice = Convert.ToDecimal(dr["ListPrice"]);
                        productsList.Add(product);
                    }
                }
                return productsList;
            }
        }
        
    }
    
    public class Product
    {
        public int ProductID { get; set; }
        public string ProductName { get; set; }
        public string ProductNumber { get; set; }
        public decimal ListPrice { get; set; }
    }
    

    Run the application and top 10 products returned from Webservice are added to the gridview as shown below:
    Bind Data to GridView using JQuery

    Be Sociable, Share!
      Post Tagged with , , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    3 Responsesso far.

    1. Louis Ho says:

      But my Pre-set Image button will not see after such binding, any solution

    2. Altaf Patel says:

      This is unique solution but I suppose is not applicable in case you have command column, like Delete. It seems to miss Datakey of current row.

    Leave a Reply