SQL Server

Get Data From SQL Server And Bind GridView Using jQuery, JSON And Ajax

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

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

table

Script of my Table

  1. CREATE TABLE [dbo].[Customers](  
  2. [CustomerID] [int] IDENTITY(1,1)NOTNULL,  
  3. [Name] [varchar](50)NULL,  
  4. [Mobile] [varchar](50)NULL,  
  5. [City] [varchar](50)NULL,  
  6. CONSTRAINT [PK_Customer] PRIMARYKEYCLUSTERED  
  7. (  
  8. [CustomerID] ASC  
  9. )WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,  
  10. IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,  
  11. ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]  
  12. )ON [PRIMARY]  
  13.   
  14. GO  
Data in my Table

table

Now right click on Project’s Solution Explorer, then click Manage Nuget

Type jQuery and Install

install

Below is my aspx code
  1. <%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="WebForm2.aspx.cs"Inherits="ExpandFillNestedGridView.WebForm2"%>  
  2.     <!DOCTYPE html>  
  3.     <html xmlns="http://www.w3.org/1999/xhtml">  
  4.         <head runat="server">  
  5.             <title>Showing Data in ASP.NET Grid View Using jQuery, JSON & AJAX Call</title>  
  6.             <script src="Scripts/jquery-2.2.0.min.js">  
  7.                 </script>  
  8.                 <script type="text/javascript">  
  9.                     $(document).ready(function ()  
  10.                      {  
  11.                         $("#btnShowData").click(function ()   
  12.                         {  
  13.                           $.ajax  
  14.                           ({   
  15.                             type: "POST", contentType: "application/json; charset=utf-8", url: "WebForm2.aspx/BindCustomers", data: "{}",  
  16.                             dataType: "json", success: function (result) { for (vari = 0; i<result.d.length; i++)  
  17.                             {  
  18.                               $( "#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>");  
  19.                             }   
  20.                           }, error: function (result)   
  21.                             {   
  22.                               alert( "Error");   
  23.                             }   
  24.                           });   
  25.                         });   
  26.                  });   
  27.                 </script>  
  28.                         </head>  
  29.   
  30.                         <body>  
  31.                             <tablestyle="background-color: yellow; border: solid 5px red; width: 100%" align="center">  
  32.                                 <tr>  
  33.                                     <tdstyle="background-color: orangered; padding: 2px; text-align: center; color: white; font-weight: bold; font-size: 14pt;">Showing Data Using jQuery, JSON & AJAX Call</td>  
  34.                                 </tr>  
  35.                                 <tr>  
  36.                                     <td>  
  37.                                         <buttonid="btnShowData" runat="server">Get Data</button>  
  38.                                             <br/>  
  39.                                             <br/>  
  40.                                             <formid="form1" runat="server" style="background-color:deepskyblue; padding:5px;">  
  41.                                                 <asp:GridViewID="gvData" runat="server" CellPadding="4" ShowHeaderWhenEmpty="true" ForeColor="White" Width="100%">  
  42.                                                     <HeaderStyleBackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
  43.                                                     <RowStyleBackColor="#EFF3FB" />  
  44.                                                 </asp:GridView>  
  45.                                            </form>  
  46.                                     </td>  
  47.                                 </tr>  
  48.                                 </table>  
  49.                         </body>  
  50.   
  51.                         </html>  
Below is my aspx.cs code
  1. using System;  
  2. using System.Data;  
  3. using System.Linq;  
  4. using System.Web.Services;  
  5. using System.Data.SqlClient;  
  6. using System.Collections.Generic;  
  7.   
  8. namespace ExpandFillNestedGridView  
  9. {  
  10.     public partial class WebForm2: System.Web.UI.Page   
  11.     {  
  12.         protected void Page_Load(object sender, EventArgs e)  
  13.         {  
  14.             if (!IsPostBack)   
  15.             {  
  16.                 BindDummyGridrow();  
  17.             }  
  18.         }  
  19.   
  20.         public void BindDummyGridrow()  
  21.         {  
  22.                 DataTable dt = new DataTable();  
  23.                 dt.Columns.Add("Customer ID");  
  24.                 dt.Columns.Add("Name");  
  25.                 dt.Columns.Add("Mobile");  
  26.                 dt.Columns.Add("City");  
  27.                 gvData.DataSource = dt;  
  28.                 gvData.DataBind();  
  29.             }  
  30.             [WebMethod]  
  31.         public static Customer[] BindCustomers()  
  32.         {  
  33.             string connectionString = @ "Data Source=.; database=CompanyDB;Integrated Security=true";  
  34.             DataTabledt = newDataTable();  
  35.             List < Customer > custList = newList < Customer > ();  
  36.             using(SqlConnection con = newSqlConnection(connectionString))  
  37.             {  
  38.                 using(SqlCommand command = newSqlCommand("select * from Customers", con))   
  39.                 {  
  40.                     con.Open();  
  41.                     SqlDataAdapter da = newSqlDataAdapter(command);  
  42.                     da.Fill(dt);  
  43.                     foreach(DataRowdtrowindt.Rows)  
  44.                     {  
  45.                         Customercust = newCustomer();  
  46.                         cust.CustomerID = dtrow["CustomerID"].ToString();  
  47.                         cust.Name = dtrow["Name"].ToString();  
  48.                         cust.Mobile = dtrow["Mobile"].ToString();  
  49.                         cust.City = dtrow["City"].ToString();  
  50.                         custList.Add(cust);  
  51.                     }  
  52.                 }  
  53.             }  
  54.             return custList.ToArray();  
  55.         }  
  56.         public class Customer  
  57.         {  
  58.             public string CustomerID   
  59.             {  
  60.                 get;  
  61.                 set;  
  62.             }  
  63.             public string Name  
  64.             {  
  65.                 get;  
  66.                 set;  
  67.             }  
  68.             public string Mobile  
  69.             {  
  70.                 get;  
  71.                 set;  
  72.             }  
  73.             public string City   
  74.             {  
  75.                 get;  
  76.                 set;  
  77.             }  
  78.         }  
  79.     }  
  80. }  
Now run your application,

run

run

 
Ebook Download
View all
Learn
View all
Get Started

You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

Become a sharper developer and jumpstart your career.

Basic

$0

$

. 00

monthly

For Basic members:

  • Standard Profile
  • Access learning courses
  • Access free e-books
  • 1x point
Premium

$20

$

. 00

monthly

For Premium members:

  • Earn 2x reward points
  • Premium profile with Verified check
  • Premium Avatar with status update
  • Learning courses
  • Unlimited Certifications and badges on profile
  • Unlimited Book downloads
  • Access to Premium content
  • Online Training
  • Unlimited PDF conversions and downloads
  • Professional Resume Writing
  • Interview Preparations Guide
  • 25 messages per month
  • 1 "Kodzu" avatar, basic
Elite

$45

$

. 00

monthly

For Elite members:

  • Bundle E-books
  • Access to all Learn Series
  • Unlimited Apply Jobs