Update And Delete Row From Multi-Table Base Select View With MVC 5.0

Introduction

In my previous article, we have already learned how to use select view with MVC 5.0. In this article, we will learn to update and delete row from multi-table base select view with MVC 5.0. By this approach, we also manipulate the data in our original table.

Step 1

Here, we are creating our database demo2 and two tables (student, college).

ASP.NET

Afterwards, we need to create select view, which shows the data of both the tables.

ASP.NET

Afterwards, we will create Instead Trigger on select view (stud_detaile) for update and delete.

Create an Instead Trigger for an update. 

  1. CREATE TRIGGER [Update_view]  
  2.     ON [dbo].[stud_detaile]  
  3.     instead of update  
  4.     AS  
  5.     BEGIN  
  6.         if(UPDATE(Id))  
  7.         begin  
  8.         Raiserror(' it is wrong updation',1,16)  
  9.         return  
  10.         end  
  11.         if(UPDATE(collegename))  
  12.         BeGIN  
  13.         declare @collegeid int  
  14.   
  15.         select @collegeid=collegeid  from   
  16.         college join inserted on  
  17.         inserted.collegename=college.collegename  
  18.       
  19.         if(@collegeid is null)  
  20.         begin  
  21.         Raiserror('invalid college Name',16,1)  
  22.         return   
  23.         end  
  24.         update student set collegeId=@collegeid from   
  25.         inserted join student on student.Id=inserted.Id  
  26.               
  27.         end  
  28.         if(UPDATE(Name))  
  29.         Begin  
  30.         update student set Name=inserted.Name from inserted join student on inserted.Id=student.Id  
  31.         end  
  32.         if(UPDATE(Rollno))  
  33.         Begin  
  34.         update student set Rollno=inserted.Rollno from inserted join student on inserted.Id=student.Id  
  35.         end  
  36.         if(UPDATE(classs))  
  37.         Begin  
  38.         update student set classs=inserted.classs from inserted join student on inserted.Id=student.Id  
  39.         end  
  40.         if(UPDATE(subjects))  
  41.         Begin  
  42.         update student set subjects=inserted.subjects from inserted join student on inserted.Id=student.Id  
  43.         end  
  44.         if(UPDATE(fees))  
  45.         Begin  
  46.         update student set fees=inserted.fees from inserted join student on inserted.Id=student.Id  
  47.         end  
  48.     END  
  49. GO   

Now, create an Instead Trigger for the row deletion. 

  1. CREATE TRIGGER [Delete_View]  
  2.     ON [dbo].[stud_detaile]  
  3.     instead of delete  
  4.     AS  
  5.     BEGIN  
  6.         delete student from deleted join student on student.Id=deleted.Id  
  7.     END   

Step 2

Now, in second step, we will open Solution Explorer, select Model and right click on Model. Select Add option and afterwards, select New item.

ASP.NET

Open new item panel, select ADO.NET Entity Data Model and click Add button.

ASP.NET

Now, an Entity Data Model Wizard opens and select Generate from the database option. Click Next option and we are creating a New connection. Click Next option and choose an Entity Framework 5.0. Click Next and select your Views, followed by stud_detaile and click Finish button.

ASP.NET

In this way, we can create our model.

ASP.NET

Now, we can double click on Model1.edmx.

ASP.NET

Now, there are three classes, which are automatically created. First two classes are responsible for table third class stud_detail and represents our database. Select View. 

Step 3

Create Home controller and open Home controller. Give the reference of our Model and create the object of our connection class. 

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using Webtest1.Models;  
  7. namespace Webtest1.Controllers  
  8. {  
  9.     public class HomeController : Controller  
  10.     {  
  11.         demo2Entities1 ds = new demo2Entities1();  
  12.           
  13.         public ActionResult Index()  
  14.         {  
  15.             return View();  
  16.         }   
  17. }  
  18. }   

Now, we will create show_detaile() method. 

  1. [HttpGet] 
  2.       public ActionResult show_detail1()  
  3.       {  
  4.            
  5.           var data = ds.stud_detaile.ToList();  
  6.            
  7.           return View(data);         
  8.            
  9.            
  10.       }   

The new custom view and the name of view is the show_detail1.cshtml.

ASP.NET

Afterwards, we are creating a method, whose name is SaveContact(). We are using this method to update the database. Let’s do this code. 

  1. public ActionResult SaveContact(stud_detaile sd)  
  2.         {  
  3. ds.View_update(sd.Name, sd.classs, sd.fees,sd.collegename,            sd.Rollno,sd.subjects, sd.Id);  
  4.             
  5.             return View("show_detail1");  
  6.               
  7.                 }   

After that we are create the another method that name is DeleteContact().we are the use this method for deletion of the row in database. Let’s do this code. 

  1. public ActionResult DeleteContact(stud_detaile sd)  
  2.         {  
  3.             ds.View_Delete(sd.Id);  
  4.             return View("show_detail1");  
  5.         }   

Step 3

Now, we are going on our custom view show_detail1.cshtml. Here, we are adding Web-grid to show the data and we are also adding edit and delete button on the Web- grid. 

  1. @model ICollection<Webtest1.Models.stud_detaile>  
  2.   
  3. <div id="ajaxgrid">  
  4.     @{  
  5. WebGrid grid = new WebGrid(Model, canPage: true, rowsPerPage: 50, selectionFieldName: "selectedRow", ajaxUpdateContainerId: "ajaxgrid");  
  6.         grid.Pager(WebGridPagerModes.NextPrevious);  
  7.   
  8. }  
  9. </div>  
  10. <h2 class="alert-success">show_detail</h2>  
  11.   
  12. <div id="ajaxid">  
  13.     @grid.GetHtml(tableStyle:  
  14.     "table table-responsive table-bordered",  
  15.     headerStyle: "",  
  16.    alternatingRowStyle: "webgrid-alternating-row",  
  17.     selectedRowStyle: "webgrid-row-style",  
  18.     mode: WebGridPagerModes.FirstLast,  
  19.     columns: grid.Columns  
  20.     (grid.Column("Id""ID"),  
  21.   grid.Column("Class", style: "col2", format: @<text><span id="classs" class="display-mode">@item.classs</span>@Html.TextBox("classs-Edit", (string)item.classs, new { @class = "edit-mode form-control", id = "@item.ID" })</text>),  
  22.   grid.Column("College Name", style: "col2", format: @<text><span id="collegename" class="display-mode">@item.collegename</span>@Html.TextBox("collegename-Edit", (string)item.collegename, new { @class = "edit-mode form-control", id = "@item.ID" })</text>),  
  23.   grid.Column("FEES",style: "col2", format: @<text><span id="fees" class="display-mode">@item.fees</span>@Html.TextBox("fees-Edit", (string)item.fees, new { @class = "edit-mode form-control", id = "@item.ID" })</text>),  
  24.   grid.Column("NAME",style: "col2", format: @<text><span id="Name" class="display-mode">@item.Name</span>@Html.TextBox("Name-Edit", (string)item.Name, new { @class = "edit-mode form-control", id = "@item.ID" })</text>),  
  25.   grid.Column("Roll No",style: "col2", format: @<text><span id="Rollno" class="display-mode">@item.Rollno</span>@Html.TextBox("Rollno-Edit", (string)item.Rollno, new { @class = "edit-mode form-control", id = "@item.ID" })</text>),  
  26.   grid.Column("subjects",style: "col2", format: @<text><span id="subjects" class="display-mode">@item.subjects</span>@Html.TextBox("subjects-Edit", (string)item.subjects, new { @class = "edit-mode form-control", id = "@item.ID" })</text>),  
  27.   grid.Column("Action", style: "col2", format: @<text>  
  28.         <div>  
  29.             <div style="padding-left:20px;">  
  30.                 <button class="btn btn-default display-mode edit-item" id="@item.ID">Edit</button>  
  31.                 <button class="btn btn-default display-mode delete-item" id="@item.ID">Delete</button>  
  32.                 <button class="btn btn-default save-item edit-mode" id="@item.ID">Save</button>  
  33.                 <button class="btn btn-default cancel-item edit-mode" id="@item.ID">Cancel</button>  
  34.             </div>  
  35.   
  36.         </div>  
  37.   
  38. </text>))  
  39.   
  40.             )  
  41. </div>   

Now, we will add some jQuery code. 

  1. <script src="~/Scripts/jquery-3.1.1.min.js"></script>  
  2. <script src="~/Scripts/jquery.unobtrusive-ajax.min.js"></script>  
  3. <script type="text/javascript">  
  4.     $(function () {  
  5.         $('.edit-mode').hide();  
  6.         $('.edit-item').on('click'function () {  
  7.             $('.edit-mode').hide();  
  8.             $('.delete-mode').hide();  
  9.             $('.display-mode').show();  
  10.             var tr = $(this).parents('tr:first');  
  11.             tr.find('.edit-mode, .display-mode').toggle();  
  12.         });  
  13.         $('.cancel-item').on('click'function () {  
  14.             var tr = $(this).parents('tr:first');  
  15.             tr.find('.display-mode,.edit-mode').toggle();  
  16.         });  
  17.         $('.delete-item').on('click'function () {  
  18.             if (confirm("Are you sure to delete this contact?")) {  
  19.                 var tr = $(this).parents('tr:first');  
  20.                 var ID = $(this).prop('id');  
  21.                 //Deletes the record with ID sent below  
  22.                 $.post(  
  23.                     '/Home/DeleteContact/',  
  24.                     { ID: ID },  
  25.                     function (item) {  
  26.                         tr.remove();  
  27.                     }, "json");  
  28.              location.reload();  
  29.             }  
  30.         });  
  31.         $('.save-item').on('click'function () {  
  32.             $('#pro').show();  
  33.             var tr = $(this).parents('tr:first');  
  34.             var ID = $(this).prop('id');  
  35.             var classs1 = tr.find('[name=classs-Edit]').val();  
  36.             var collegename1 = tr.find('[name=collegename-Edit]').val();  
  37.             var fees1 = tr.find('[name=fees-Edit]').val();  
  38.             var Name1 = tr.find('[name=Name-Edit]').val();  
  39.             var Rollno1 = tr.find('[name=Rollno-Edit]').val();  
  40.             var subjects1 = tr.find('[name=subjects-Edit]').val();  
  41.             $.ajax({  
  42.                 type: "POST",  
  43.                 url: "/Home/SaveContact/",  
  44.                 data: { ID: ID, classs: classs1, collegename: collegename1, fees: fees1, Name: Name1, Rollno: Rollno1, subjects: subjects1 },  
  45.                 success: function (item) {  
  46.                     tr.find('#classs').text(item.classs1);  
  47.                     tr.find('#collegename').text(item.collegename1);  
  48.                     tr.find('#fees').text(item.fees1);  
  49.                     tr.find('#Name').text(item.Name1);  
  50.                     tr.find('#Rollno').text(item.Rollno1);  
  51.                     tr.find('#subjects').text(item.subjects1);  
  52.                      
  53.                     if (item != null) {  
  54.                         $('#pro').hide();  
  55.                         location.reload();  
  56.                     }  
  57.                     else  
  58.                         alert(item);  
  59.                       
  60.                 },  
  61.                 error: function (result) {  
  62.                     alert('Error!');  
  63.                 }  
  64.                  
  65.             });  
  66.             tr.find('.edit-mode, .display-mode').toggle();  
  67.              
  68.         });  
  69.     })  
  70.   
  71. </script>   

Now, build the solution and run. Show your output.

ASP.NET

Afterwards, click Edit button, where ID=2 and change Roll No.

ASP.NET

Afterwards, click Save button.

ASP.NET
Here, our table is successfully updated. Now, we will click Delete, where ID =11;

ASP.NET
Here, a row is successfully deleted.