Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Get data from SQL Server and bind Grid View using jQuery, JSON and Ajax

Article:
Viewed:  1277 
Posted On:  3/6/2016 2:29:39 AM 
How to bind Grid View using jQuery, JSON and Ajax in ASP.NET? 

I am writing this article on demand as I got a request from one of my friend. He told me that he is looking this requirement so I created this. As per his requirement he needs to show data from SQL Server database table in ASP.NET Grid View using jQuery, JSON and AJAX call. 

Below is my Data Table from which I am showing data.

1.png

Image 1. 

Script of My Table is:

CREATE TABLE [dbo].[Customers](
          [CustomerID] [int] IDENTITY(1,1) NOT NULL,
          [Name] [varchar](50) NULL,
          [Mobile] [varchar](50) NULL,
          [City] [varchar](50) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
          [CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] 

GO

 Data in my Table:

2.png

 

Image 2.

 

Now right click on Project's Solution Explorer- > Manage Nu Get-> Type jQuery and Install it:


3.png

Image 3.

Below is my aspx code:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="ExpandFillNestedGridView.WebForm2" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Showing Data in ASP.NET Grid View Using jQuery, JSON & AJAX Call</title>

    <script src="Scripts/jquery-2.2.0.min.js"></script>

    <script type="text/javascript">

        $(document).ready(function () {
            $("#btnShowData").click(function () {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "WebForm2.aspx/BindCustomers",
                    data: "{}",
                    dataType: "json",
                    success: function (result) {
                        for (var i = 0; i < result.d.length; i++) {
                            $("#gvData").append("<tr><td>" + result.d[i].CustomerID + "</td><td>" +
                                            result.d[i].Name +
"</td><td>" + result.d[i].Mobile + "</td><td>" +
                                            result.d[i].City +
"</td></tr>");

                        }
                    },
                    error: function (result) {
                        alert("Error");
                    }
                });
            });
        });

    </script>

</head>
<body>
    <table style="background-color: yellow; border: solid 5px red; width: 100%" align="center">
        <tr>
            <td style="background-color: orangered; padding: 2px; text-align: center; color: white; font-weight: bold; font-size: 14pt;">
                           Showing Data Using jQuery, JSON & AJAX Call</td>
        </tr>
        <tr>
            <td>
                <button id="btnShowData" runat="server">Get Data</button>
                <br />
                <br />
                <form id="form1" runat="server" style="background-color:deepskyblue; padding:5px;">
                    <asp:GridView ID="gvData" runat="server" CellPadding="4" ShowHeaderWhenEmpty="true" ForeColor="White" Width="100%">
                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <RowStyle BackColor="#EFF3FB" />
                    </asp:GridView>
                </form>
            </td>
        </tr>
    </table>
</body>
</html>
 

Below is my aspx.cs code:

using System;
using System.Data;
using System.Linq;
using System.Web.Services;
using System.Data.SqlClient;
using System.Collections.Generic;
 
namespace ExpandFillNestedGridView
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindDummyGridrow();
            }
        }
 
        public void BindDummyGridrow()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Customer ID");
            dt.Columns.Add("Name");
            dt.Columns.Add("Mobile");
            dt.Columns.Add("City");
            gvData.DataSource = dt;
            gvData.DataBind();
        }

        [
WebMethod]
        public static Customer[] BindCustomers()
        {
            string connectionString = @"Data Source=.; database=CompanyDB;Integrated Security=true";
            DataTable dt = new DataTable();
            List<Customer> custList = new List<Customer>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand("select * from Customers", con))
                {
                    con.Open();
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    da.Fill(dt);
                    foreach (DataRow dtrow in dt.Rows)
                    {
                        Customer cust = new Customer();
                        cust.CustomerID = dtrow["CustomerID"].ToString();
                        cust.Name = dtrow["Name"].ToString();
                        cust.Mobile = dtrow["Mobile"].ToString();
                        cust.City = dtrow["City"].ToString();
                        custList.Add(cust);
                    }
                }
            }
            return custList.ToArray();
        }

        
public class Customer
        {
            public string CustomerID { get; set; }
            public string Name { get; set; }
            public string Mobile { get; set; }
            public string City { get; set; }
        }
    }
}

Now run your application:

 

4.png

Image 4.

 

5.png

 

Image 5.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us