Post
Ask Question

How To Insert And Retrieve Data Using jQuery AJAX In ASP.NET

Introduction

In this blog, we will learn how to insert and retrieve data using jQuery Ajax in asp.net. We will create a web service and consume that web service in our project with the help of jQuery Ajax.

Step 1

Create a database in the SQL server of your choice.

  1. CREATE TABLE [dbo].[tblEmployees](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Gender] [char](10) NULL,  
  5.     [Phone] [nvarchar](50) NULL,  
  6.     [Email] [nvarchar](50) NULL,  
  7.     [Age] [int] NULL,  
  8.     [Salary] [nvarchar](50) NULL,  
  9.  CONSTRAINT [PK_tblEmployees] PRIMARY KEY CLUSTERED   
  10. (  
  11.     [ID] ASC  
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  13. ) ON [PRIMARY]  
  14.   
  15. GO  
  16.   
  17. CREATE procedure [dbo].[spAddNewEmployee]  
  18. (  
  19. @Name nvarchar(50),  
  20. @Gender char(10),  
  21. @Phone nvarchar(50),  
  22. @Email nvarchar(50),  
  23. @Age int,  
  24. @Salary nvarchar(50)  
  25. )  
  26. as  
  27. begin  
  28. insert into tblEmployees(Name,Gender,Phone,Email,Age,Salary)  
  29. values(@Name,@Gender,@Phone,@Email,@Age,@Salary)  
  30. end  
  31.   
  32. CREATE procedure [dbo].[spGetEmployees]  
  33. as  
  34. begin  
  35. select ID,Name,Gender,Phone,Email,Age, Salary from tblEmployees  
  36. end  

Step 2

Add code in the webconfig file:

  1. <connectionStrings>  
  2.     <add name="DBCS" connectionString="data source=FARHAN\SQLEXPRESS; database=JQueryDB; integrated security=SSPI;" providerName="System.Data.SqlClient"/>  
  3.   </connectionStrings>  
  4. <system.web>  
  5.     <webServices>  
  6.       <protocols>  
  7.         <add name="HttpGet"/>  
  8.       </protocols>  
  9.     </webServices>  
  10.   </system.web>  

Step 3

Create class --  right click on new item, choose class, give the name employee.cs:

  1. public class Employee  
  2.     {  
  3.         public int ID { get; set; }  
  4.         public string Name { get; set; }  
  5.         public string Gender { get; set;}  
  6.         public string Phone { get; set; }  
  7.         public string Email { get; set; }  
  8.         public int Age { get; set; }  
  9.         public string Salary { get; set; }  
  10.     }  

Step 4

Create web service and give name as EmployeeService.asmx

Add namespace

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Script.Serialization;

To add employee

  1. [WebMethod]  
  2.         public void AddEmployee(Employee emp)  
  3.         {  
  4.             string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;  
  5.             using (SqlConnection con = new SqlConnection(CS))  
  6.             {  
  7.                 SqlCommand cmd = new SqlCommand("spAddNewEmployee", con);  
  8.                 cmd.CommandType = CommandType.StoredProcedure;  
  9.                 cmd.Parameters.Add(new SqlParameter()  
  10.                 {  
  11.                     ParameterName = "@Name",  
  12.                     Value = emp.Name  
  13.                 });  
  14.                 cmd.Parameters.Add(new SqlParameter()  
  15.                 {  
  16.                     ParameterName = "@Gender",  
  17.                     Value = emp.Gender  
  18.                 });  
  19.                 cmd.Parameters.Add(new SqlParameter()  
  20.                 {  
  21.                     ParameterName = "@Phone",  
  22.                     Value = emp.Phone  
  23.                 });  
  24.                 cmd.Parameters.Add(new SqlParameter()  
  25.                 {  
  26.                     ParameterName = "@Email",  
  27.                     Value = emp.Email  
  28.                 });  
  29.                 cmd.Parameters.Add(new SqlParameter()  
  30.                 {  
  31.                     ParameterName = "@Age",  
  32.                     Value = emp.Age  
  33.                 });  
  34.                 cmd.Parameters.Add(new SqlParameter()  
  35.                 {  
  36.                     ParameterName = "@Salary",  
  37.                     Value = emp.Salary  
  38.                 });  
  39.                 con.Open();  
  40.                 cmd.ExecuteNonQuery();  
  41.             }            
  42.         }  

To add retrieve employee

  1. [WebMethod]  
  2.         public void GetAllEmployees()  
  3.         {  
  4.            List<Employee> listEmployee = new List<Employee>();  
  5.            string CS=ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;   
  6.            using(SqlConnection con=new SqlConnection(CS))  
  7.            {  
  8.                SqlCommand cmd = new SqlCommand("spGetEmployees", con);  
  9.                cmd.CommandType = CommandType.StoredProcedure;  
  10.                con.Open();  
  11.                SqlDataReader rdr = cmd.ExecuteReader();  
  12.                while(rdr.Read())  
  13.                {  
  14.                    Employee employee = new Employee();  
  15.                    employee.ID = Convert.ToInt32(rdr["ID"]);  
  16.                    employee.Name = rdr["Name"].ToString();  
  17.                    employee.Gender = rdr["Gender"].ToString();  
  18.                    employee.Phone = rdr["Phone"].ToString();  
  19.                    employee.Email = rdr["Email"].ToString();  
  20.                    employee.Age = Convert.ToInt32(rdr["Age"]);  
  21.                    employee.Salary = rdr["Salary"].ToString();  
  22.   
  23.                    listEmployee.Add(employee);  
  24.                }  
  25.            }  
  26.            JavaScriptSerializer js = new JavaScriptSerializer();  
  27.            Context.Response.Write(js.Serialize(listEmployee));  
  28.         }  

Step 5

Add web, right click on project and add new item, choose web form, give it a name.

Add scripts and styles in the head section:

  1. <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">  
  2. <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">  
  3. <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  
  4. <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>  
  5. <script src="employee.js"></script>  

Step 6 - Create script file employee.js

Write script to insert and retrieve data into the database:

  1. $(document).ready(function () {  
  2.     var employee = {};  
  3.     employee.Name   = $('#txtName').val();  
  4.     employee.Gender = $('#ddlGender').val();  
  5.     employee.Phone  = $('#txtPhone').val();  
  6.     employee.Email  = $('#txtEmail').val();  
  7.     employee.Age    = $('#txtAge').val();  
  8.     employee.Salary = $('#txtSalary').val();  
  9.   
  10.     $.ajax({  
  11.         url: 'EmployeeService.asmx/AddEmployee',  
  12.         method: 'post',  
  13.         data:   '{emp: ' + JSON.stringify(employee) + '}',  
  14.         contentType: "application/json; charset=utf-8",  
  15.         dataType: "json",  
  16.         success: function () {  
  17.             getAllEmployees();  
  18.             console.log(data);  
  19.         },  
  20.         error: function (err) {  
  21.             console.log(err);  
  22.         }  
  23.     });  
  24.   
  25.  function getAllEmployees() {  
  26.         $.ajax({  
  27.             url: 'EmployeeService.asmx/GetAllEmployees',  
  28.             dataType: "json",  
  29.             method: 'GET',  
  30.             success: function (data) {  
  31.                 var employeeTable = $('#employee tbody');  
  32.                 employeeTable.empty();  
  33.   
  34.                 $(data).each(function (index, emp) {  
  35.                     employeeTable.append('<tr><td>' + emp.ID + '</td><td>'  
  36.                         + emp.Name + '</td><td>' + emp.Gender + '</td><td>'  
  37.                         + emp.Phone + '</td><td>' + emp.Email + '</td><td>'  
  38.                         + emp.Age + '</td><td>' + emp.Salary + '</td></tr>');  
  39.                 });  
  40.             },  
  41.             error: function (err) {  
  42.                 console.log(err);  
  43.             }  
  44.         });  
  45.     }  
  46. });  

Step 7 - Design HTML web form

  1. <body>  
  2.     <form id="form1" runat="server">  
  3.         <div class="container py-3">  
  4.             <h4 class="text-center text-uppercase">How to Insert and retrieve data using jquery ajax in asp.net </h4>  
  5.             <div class="card">  
  6.                 <div class="card-header bg-danger text-white">  
  7.                     <h5 class="card-title text-uppercase">Employee Information</h5>  
  8.                 </div>  
  9.                 <div class="card-body">  
  10.                     <div class="row">  
  11.                         <div class="col-md-6">  
  12.                             <div class="form-group">  
  13.                                 <label>Name</label>  
  14.                                 <div class="input-group">  
  15.                                     <div class="input-group-prepend">  
  16.                                         <span class="input-group-text"><i class="fa fa-user"></i></span>  
  17.                                     </div>  
  18.                                     <asp:TextBox ID="txtName" CssClass="form-control" runat="server"></asp:TextBox>  
  19.                                 </div>  
  20.                             </div>  
  21.                         </div>  
  22.                         <div class="col-md-6">  
  23.                             <div class="form-group">  
  24.                                 <label>Gender</label>  
  25.                                 <div class="input-group">  
  26.                                     <div class="input-group-prepend">  
  27.                                         <span class="input-group-text"><i class="fa fa-user"></i></span>  
  28.                                     </div>  
  29.                                     <asp:DropDownList ID="ddlGender" runat="server" CssClass="custom-select">  
  30.                                         <asp:ListItem Value="-1">Choose Gender</asp:ListItem>  
  31.                                         <asp:ListItem Text="Male" Value="Male"></asp:ListItem>  
  32.                                         <asp:ListItem Text="Female" Value="Female"></asp:ListItem>  
  33.                                     </asp:DropDownList>  
  34.                                 </div>  
  35.                             </div>  
  36.                         </div>  
  37.                     </div>  
  38.                     <div class="row">  
  39.                         <div class="col-md-6">  
  40.                             <div class="form-group">  
  41.                                 <label>Phone Number</label>  
  42.                                 <div class="input-group">  
  43.                                     <div class="input-group-prepend">  
  44.                                         <span class="input-group-text"><i class="fa fa-phone"></i></span>  
  45.                                     </div>  
  46.                                     <asp:TextBox ID="txtPhone" CssClass="form-control" runat="server"></asp:TextBox>  
  47.                                 </div>  
  48.                             </div>  
  49.                         </div>  
  50.                         <div class="col-md-6">  
  51.                             <div class="form-group">  
  52.                                 <label>Email</label>  
  53.                                 <div class="input-group">  
  54.                                     <div class="input-group-prepend">  
  55.                                         <span class="input-group-text"><i class="fa fa-envelope"></i></span>  
  56.                                     </div>  
  57.                                     <asp:TextBox ID="txtEmail" CssClass="form-control" runat="server"></asp:TextBox>  
  58.                                 </div>  
  59.                             </div>  
  60.                         </div>  
  61.                     </div>  
  62.                     <div class="row">  
  63.                         <div class="col-md-6">  
  64.                             <div class="form-group">  
  65.                                 <label>Age</label>  
  66.                                 <div class="input-group">  
  67.                                     <div class="input-group-prepend">  
  68.                                         <span class="input-group-text"><i class="fa fa-user"></i></span>  
  69.                                     </div>  
  70.                                     <asp:TextBox ID="txtAge" CssClass="form-control" runat="server"></asp:TextBox>  
  71.                                 </div>  
  72.                             </div>  
  73.                         </div>  
  74.                         <div class="col-md-6">  
  75.                             <div class="form-group">  
  76.                                 <label>Salary</label>  
  77.                                 <div class="input-group">  
  78.                                     <div class="input-group-prepend">  
  79.                                         <span class="input-group-text">₹</span>  
  80.                                     </div>  
  81.                                     <asp:TextBox ID="txtSalary" CssClass="form-control" runat="server"></asp:TextBox>  
  82.                                 </div>  
  83.                             </div>  
  84.                         </div>  
  85.                     </div>  
  86.                     <asp:Button ID="btnAddEmployee" CssClass="btn btn-danger rounded-0" runat="server" Text="Submit" />  
  87.                 </div>  
  88.             </div>  
  89.             <table id="employee" class="table table-bordered" style="margin-top:10px;">  
  90.                 <thead class="bg-danger text-center text-white">  
  91.                     <tr>  
  92.                         <th>ID</th>  
  93.                         <th>Name</th>  
  94.                         <th>Gender</th>  
  95.                         <th>Phone</th>  
  96.                         <th>Email</th>  
  97.                         <th>Age</th>  
  98.                         <th>Salary</th>  
  99.                     </tr>  
  100.                 </thead>  
  101.                 <tbody></tbody>  
  102.             </table>  
  103.         </div>  
  104.     </form>  
  105. </body>  

Step 8 - Run project ctr+F5

Final output

ASP.NET

ASP.NET