CRUD Operation In ASP.NET MVC Using AJAX And Bootstrap

This article shows how to perform CRUD Operation in ASP.NET MVC, using AJAX and Bootstrap. In previous ASP.NET MVC tutorials of this series, we saw,
What is AJAX and Bootstrap?

AJAX (Asynchronous JavaScript and XML) in the Web Application is used to update parts of the existing page and to retrieve the data from the Server asynchronously. AJAX improves the performance of the Web Application and makes the Application more interactive.

Bootstrap is one of the most popular HTML, CSS and JS frameworks for developing responsive, mobile first projects on the Web.
 
Let’s Begin

Create a new ASP.NET Web Application.

 

Select Empty ASP.NET MVC template and click OK.

 

Now, right-click on the project and click Manage NuGet Packages.

 

Search for Bootstrap and then click Install button.

 

After installing the package, you will see the Content and Scripts folder being added in your Solution Explorer.

 

Now, create a database and add a table (named Employee). The following is the schema for creating a table Employee:

 

After the table creation, create the stored procedures for Select, Insert, Update and Delete operations.
  1. --Select Employees  
  2. Create Procedure SelectEmployee    
  3. as     
  4. Begin    
  5. Select * from Employee;    
  6. End  
  7.   
  8. --Insert and Update Employee  
  9. Create Procedure InsertUpdateEmployee    
  10. (    
  11. @Id integer,    
  12. @Name nvarchar(50),    
  13. @Age integer,    
  14. @State nvarchar(50),    
  15. @Country nvarchar(50),    
  16. @Action varchar(10)    
  17. )    
  18. As    
  19. Begin    
  20. if @Action='Insert'    
  21. Begin    
  22.  Insert into Employee(Name,Age,[State],Country) values(@Name,@Age,@State,@Country);    
  23. End    
  24. if @Action='Update'    
  25. Begin    
  26.  Update Employee set Name=@Name,Age=@Age,[State]=@State,Country=@Country where EmployeeID=@Id;    
  27. End      
  28. End  
  29.   
  30. --Delete Employee  
  31. Create Procedure DeleteEmployee    
  32. (    
  33.  @Id integer    
  34. )    
  35. as     
  36. Begin    
  37.  Delete Employee where EmployeeID=@Id;    
  38. End  
Right click on Modal Folder and add Employee.cs class.

Employee.cs Code
  1. public class Employee  
  2. {  
  3.     public int EmployeeID { getset; }  
  4.   
  5.     public string Name { getset; }  
  6.           
  7.     public int Age { getset; }  
  8.           
  9.     public string State { getset; }  
  10.           
  11.     public string Country { getset; }  
  12. }  
Now, add another class in Modal Folder named as EmployeeDB.cs for the database related operations. In this example, I am going to use ADO.NET to access the data from the database.
  1. public class EmployeeDB  
  2.     {  
  3.         //declare connection string  
  4.         string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;  
  5.   
  6.         //Return list of all Employees  
  7.         public List<Employee> ListAll()  
  8.         {  
  9.             List<Employee> lst = new List<Employee>();  
  10.             using(SqlConnection con=new SqlConnection(cs))  
  11.             {  
  12.                 con.Open();  
  13.                 SqlCommand com = new SqlCommand("SelectEmployee",con);  
  14.                 com.CommandType = CommandType.StoredProcedure;  
  15.                 SqlDataReader rdr = com.ExecuteReader();  
  16.                 while(rdr.Read())  
  17.                 {  
  18.                     lst.Add(new Employee {   
  19.                         EmployeeID=Convert.ToInt32(rdr["EmployeeId"]),  
  20.                         Name=rdr["Name"].ToString(),  
  21.                         Age = Convert.ToInt32(rdr["Age"]),  
  22.                         State = rdr["State"].ToString(),  
  23.                         Country = rdr["Country"].ToString(),  
  24.                     });  
  25.                 }  
  26.                 return lst;  
  27.             }  
  28.         }  
  29.   
  30.         //Method for Adding an Employee  
  31.         public int Add(Employee emp)  
  32.         {  
  33.             int i;  
  34.             using(SqlConnection con=new SqlConnection(cs))  
  35.             {  
  36.                 con.Open();  
  37.                 SqlCommand com = new SqlCommand("InsertUpdateEmployee", con);  
  38.                 com.CommandType = CommandType.StoredProcedure;  
  39.                 com.Parameters.AddWithValue("@Id",emp.EmployeeID);  
  40.                 com.Parameters.AddWithValue("@Name", emp.Name);  
  41.                 com.Parameters.AddWithValue("@Age", emp.Age);  
  42.                 com.Parameters.AddWithValue("@State", emp.State);  
  43.                 com.Parameters.AddWithValue("@Country", emp.Country);  
  44.                 com.Parameters.AddWithValue("@Action""Insert");  
  45.                 i = com.ExecuteNonQuery();  
  46.             }  
  47.             return i;  
  48.         }  
  49.   
  50.         //Method for Updating Employee record  
  51.         public int Update(Employee emp)  
  52.         {  
  53.             int i;  
  54.             using (SqlConnection con = new SqlConnection(cs))  
  55.             {  
  56.                 con.Open();  
  57.                 SqlCommand com = new SqlCommand("InsertUpdateEmployee", con);  
  58.                 com.CommandType = CommandType.StoredProcedure;  
  59.                 com.Parameters.AddWithValue("@Id", emp.EmployeeID);  
  60.                 com.Parameters.AddWithValue("@Name", emp.Name);  
  61.                 com.Parameters.AddWithValue("@Age", emp.Age);  
  62.                 com.Parameters.AddWithValue("@State", emp.State);  
  63.                 com.Parameters.AddWithValue("@Country", emp.Country);  
  64.                 com.Parameters.AddWithValue("@Action""Update");  
  65.                 i = com.ExecuteNonQuery();  
  66.             }  
  67.             return i;  
  68.         }  
  69.   
  70.         //Method for Deleting an Employee  
  71.         public int Delete(int ID)  
  72.         {  
  73.             int i;  
  74.             using (SqlConnection con = new SqlConnection(cs))  
  75.             {  
  76.                 con.Open();  
  77.                 SqlCommand com = new SqlCommand("DeleteEmployee", con);  
  78.                 com.CommandType = CommandType.StoredProcedure;  
  79.                 com.Parameters.AddWithValue("@Id", ID);  
  80.                 i = com.ExecuteNonQuery();  
  81.             }  
  82.             return i;  
  83.         }  
  84.     }  
Right click on Controllers folder, add an Empty Controller and name it as HomeController.

 

Now, open HomeController and add the following action methods:
  1. public class HomeController : Controller  
  2.     {  
  3.         EmployeeDB empDB = new EmployeeDB();  
  4.         // GET: Home  
  5.         public ActionResult Index()  
  6.         {  
  7.             return View();  
  8.         }  
  9.         public JsonResult List()  
  10.         {  
  11.             return Json(empDB.ListAll(),JsonRequestBehavior.AllowGet);  
  12.         }  
  13.         public JsonResult Add(Employee emp)  
  14.         {  
  15.             return Json(empDB.Add(emp), JsonRequestBehavior.AllowGet);  
  16.         }  
  17.         public JsonResult GetbyID(int ID)  
  18.         {  
  19.             var Employee = empDB.ListAll().Find(x => x.EmployeeID.Equals(ID));  
  20.             return Json(Employee, JsonRequestBehavior.AllowGet);  
  21.         }  
  22.         public JsonResult Update(Employee emp)  
  23.         {  
  24.             return Json(empDB.Update(emp), JsonRequestBehavior.AllowGet);  
  25.         }  
  26.         public JsonResult Delete(int ID)  
  27.         {  
  28.             return Json(empDB.Delete(ID), JsonRequestBehavior.AllowGet);  
  29.         }  
  30.     }  
Right click on the Index action method of HomeController and click on Add View. As we are going to use Bootstrap and AJAX, we have to add their relative Scripts and CSS references in the head section of the view. I have also added employee.js, which will contain all AJAX code, that are required for CRUD operation.
  1. <script src="~/Scripts/jquery-1.9.1.js"></script>  
  2. <script src="~/Scripts/bootstrap.js"></script>  
  3. <link href="~/Content/bootstrap.css" rel="stylesheet" />  
  4. <script src="~/Scripts/employee.js"></script>  
Add the code, given below, in Index.cshtml view:
  1. <div class="container">  
  2.         <h2>Employees Record</h2>   
  3.         <button type="button" class="btn btn-primary" data-toggle="modal" data-target="#myModal" onclick="clearTextBox();">Add New Employee</button><br /><br />  
  4.         <table class="table table-bordered table-hover">  
  5.             <thead>  
  6.                 <tr>  
  7.                     <th>  
  8.                         ID  
  9.                     </th>  
  10.                     <th>  
  11.                         Name  
  12.                     </th>  
  13.                     <th>  
  14.                         Age  
  15.                     </th>  
  16.                     <th>  
  17.                         State  
  18.                     </th>  
  19.                     <th>  
  20.                         Country  
  21.                     </th>  
  22.                     <th>  
  23.                         Action  
  24.                     </th>  
  25.                 </tr>  
  26.             </thead>  
  27.             <tbody class="tbody">  
  28.   
  29.             </tbody>  
  30.         </table>  
  31.     </div>  
  32.     <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">  
  33.         <div class="modal-dialog">  
  34.             <div class="modal-content">  
  35.                 <div class="modal-header">  
  36.                     <button type="button" class="close" data-dismiss="modal">×</button>  
  37.                     <h4 class="modal-title" id="myModalLabel">Add Employee</h4>  
  38.                 </div>  
  39.                 <div class="modal-body">  
  40.                     <form>  
  41.                         <div class="form-group">  
  42.                             <label for="EmployeeId">ID</label>  
  43.                             <input type="text" class="form-control" id="EmployeeID" placeholder="Id" disabled="disabled"/>  
  44.                         </div>  
  45.                         <div class="form-group">  
  46.                             <label for="Name">Name</label>  
  47.                             <input type="text" class="form-control" id="Name" placeholder="Name"/>  
  48.                         </div>  
  49.                         <div class="form-group">  
  50.                             <label for="Age">Age</label>  
  51.                             <input type="text" class="form-control" id="Age" placeholder="Age" />  
  52.                         </div>  
  53.                         <div class="form-group">  
  54.                             <label for="State">State</label>  
  55.                             <input type="text" class="form-control" id="State" placeholder="State"/>  
  56.                         </div>  
  57.                         <div class="form-group">  
  58.                             <label for="Country">Country</label>  
  59.                             <input type="text" class="form-control" id="Country" placeholder="Country"/>  
  60.                         </div>  
  61.                     </form>  
  62.                 </div>  
  63.                 <div class="modal-footer">  
  64.                     <button type="button" class="btn btn-primary" id="btnAdd" onclick="return Add();">Add</button>  
  65.                     <button type="button" class="btn btn-primary" id="btnUpdate" style="display:none;" onclick="Update();">Update</button>  
  66.                     <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>  
  67.                 </div>  
  68.             </div>  
  69.         </div>          
  70.     </div>  
In the code, given above, we have added a button for adding New Employee. On clicking, It will open the modal dialog box of the bootstrap, which contains several fields of the employees for saving. We have also added a table, which will be populated with the use of AJAX.

Employee.js Code
  1. //Load Data in Table when documents is ready  
  2. $(document).ready(function () {  
  3.     loadData();  
  4. });  
  5.   
  6. //Load Data function  
  7. function loadData() {  
  8.     $.ajax({  
  9.         url: "/Home/List",  
  10.         type: "GET",  
  11.         contentType: "application/json;charset=utf-8",  
  12.         dataType: "json",  
  13.         success: function (result) {  
  14.             var html = '';  
  15.             $.each(result, function (key, item) {  
  16.                 html += '<tr>';  
  17.                 html += '<td>' + item.EmployeeID + '</td>';  
  18.                 html += '<td>' + item.Name + '</td>';  
  19.                 html += '<td>' + item.Age + '</td>';  
  20.                 html += '<td>' + item.State + '</td>';  
  21.                 html += '<td>' + item.Country + '</td>';  
  22.                 html += '<td><a href="#" onclick="return getbyID(' + item.EmployeeID + ')">Edit</a> | <a href="#" onclick="Delele(' + item.EmployeeID + ')">Delete</a></td>';  
  23.                 html += '</tr>';  
  24.             });  
  25.             $('.tbody').html(html);  
  26.         },  
  27.         error: function (errormessage) {  
  28.             alert(errormessage.responseText);  
  29.         }  
  30.     });  
  31. }  
  32.   
  33. //Add Data Function   
  34. function Add() {  
  35.     var res = validate();  
  36.     if (res == false) {  
  37.         return false;  
  38.     }  
  39.     var empObj = {  
  40.         EmployeeID: $('#EmployeeID').val(),  
  41.         Name: $('#Name').val(),  
  42.         Age: $('#Age').val(),  
  43.         State: $('#State').val(),  
  44.         Country: $('#Country').val()  
  45.     };  
  46.     $.ajax({  
  47.         url: "/Home/Add",  
  48.         data: JSON.stringify(empObj),  
  49.         type: "POST",  
  50.         contentType: "application/json;charset=utf-8",  
  51.         dataType: "json",  
  52.         success: function (result) {  
  53.             loadData();  
  54.             $('#myModal').modal('hide');  
  55.         },  
  56.         error: function (errormessage) {  
  57.             alert(errormessage.responseText);  
  58.         }  
  59.     });  
  60. }  
  61.   
  62. //Function for getting the Data Based upon Employee ID  
  63. function getbyID(EmpID) {  
  64.     $('#Name').css('border-color''lightgrey');  
  65.     $('#Age').css('border-color''lightgrey');  
  66.     $('#State').css('border-color''lightgrey');  
  67.     $('#Country').css('border-color''lightgrey');  
  68.     $.ajax({  
  69.         url: "/Home/getbyID/" + EmpID,  
  70.         typr: "GET",  
  71.         contentType: "application/json;charset=UTF-8",  
  72.         dataType: "json",  
  73.         success: function (result) {  
  74.             $('#EmployeeID').val(result.EmployeeID);  
  75.             $('#Name').val(result.Name);  
  76.             $('#Age').val(result.Age);  
  77.             $('#State').val(result.State);  
  78.             $('#Country').val(result.Country);  
  79.   
  80.             $('#myModal').modal('show');  
  81.             $('#btnUpdate').show();  
  82.             $('#btnAdd').hide();  
  83.         },  
  84.         error: function (errormessage) {  
  85.             alert(errormessage.responseText);  
  86.         }  
  87.     });  
  88.     return false;  
  89. }  
  90.   
  91. //function for updating employee's record  
  92. function Update() {  
  93.     var res = validate();  
  94.     if (res == false) {  
  95.         return false;  
  96.     }  
  97.     var empObj = {  
  98.         EmployeeID: $('#EmployeeID').val(),  
  99.         Name: $('#Name').val(),  
  100.         Age: $('#Age').val(),  
  101.         State: $('#State').val(),  
  102.         Country: $('#Country').val(),  
  103.     };  
  104.     $.ajax({  
  105.         url: "/Home/Update",  
  106.         data: JSON.stringify(empObj),  
  107.         type: "POST",  
  108.         contentType: "application/json;charset=utf-8",  
  109.         dataType: "json",  
  110.         success: function (result) {  
  111.             loadData();  
  112.             $('#myModal').modal('hide');  
  113.             $('#EmployeeID').val("");  
  114.             $('#Name').val("");  
  115.             $('#Age').val("");  
  116.             $('#State').val("");  
  117.             $('#Country').val("");  
  118.         },  
  119.         error: function (errormessage) {  
  120.             alert(errormessage.responseText);  
  121.         }  
  122.     });  
  123. }  
  124.   
  125. //function for deleting employee's record  
  126. function Delele(ID) {  
  127.     var ans = confirm("Are you sure you want to delete this Record?");  
  128.     if (ans) {  
  129.         $.ajax({  
  130.             url: "/Home/Delete/" + ID,  
  131.             type: "POST",  
  132.             contentType: "application/json;charset=UTF-8",  
  133.             dataType: "json",  
  134.             success: function (result) {  
  135.                 loadData();  
  136.             },  
  137.             error: function (errormessage) {  
  138.                 alert(errormessage.responseText);  
  139.             }  
  140.         });  
  141.     }  
  142. }  
  143.   
  144. //Function for clearing the textboxes  
  145. function clearTextBox() {  
  146.     $('#EmployeeID').val("");  
  147.     $('#Name').val("");  
  148.     $('#Age').val("");  
  149.     $('#State').val("");  
  150.     $('#Country').val("");  
  151.     $('#btnUpdate').hide();  
  152.     $('#btnAdd').show();  
  153.     $('#Name').css('border-color''lightgrey');  
  154.     $('#Age').css('border-color''lightgrey');  
  155.     $('#State').css('border-color''lightgrey');  
  156.     $('#Country').css('border-color''lightgrey');  
  157. }  
  158. //Valdidation using jquery  
  159. function validate() {  
  160.     var isValid = true;  
  161.     if ($('#Name').val().trim() == "") {  
  162.         $('#Name').css('border-color''Red');  
  163.         isValid = false;  
  164.     }  
  165.     else {  
  166.         $('#Name').css('border-color''lightgrey');  
  167.     }  
  168.     if ($('#Age').val().trim() == "") {  
  169.         $('#Age').css('border-color''Red');  
  170.         isValid = false;  
  171.     }  
  172.     else {  
  173.         $('#Age').css('border-color''lightgrey');  
  174.     }  
  175.     if ($('#State').val().trim() == "") {  
  176.         $('#State').css('border-color''Red');  
  177.         isValid = false;  
  178.     }  
  179.     else {  
  180.         $('#State').css('border-color''lightgrey');  
  181.     }  
  182.     if ($('#Country').val().trim() == "") {  
  183.         $('#Country').css('border-color''Red');  
  184.         isValid = false;  
  185.     }  
  186.     else {  
  187.         $('#Country').css('border-color''lightgrey');  
  188.     }  
  189.     return isValid;  
  190. }  
Build and run the Application.

Adding a Record (Preview)

 

Editing a Record (Preview)

 

Delete a Record(Preview)

 

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now