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]
  15. GO
  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
  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();
  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();
  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. });
  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();
  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