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).

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

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

Create an Instead Trigger for an update. 

CREATE TRIGGER [Update_view]
    ON [dbo].[stud_detaile]
    instead of update
    AS
    BEGIN
        if(UPDATE(Id))
        begin
        Raiserror(' it is wrong updation',1,16)
        return
        end
        if(UPDATE(collegename))
        BeGIN
        declare @collegeid int

        select @collegeid=collegeid  from
        college join inserted on
        inserted.collegename=college.collegename

        if(@collegeid is null)
        begin
        Raiserror('invalid college Name',16,1)
        return
        end
        update student set collegeId=@collegeid from
        inserted join student on student.Id=inserted.Id

        end
        if(UPDATE(Name))
        Begin
        update student set Name=inserted.Name from inserted join student on inserted.Id=student.Id
        end
        if(UPDATE(Rollno))
        Begin
        update student set Rollno=inserted.Rollno from inserted join student on inserted.Id=student.Id
        end
        if(UPDATE(classs))
        Begin
        update student set classs=inserted.classs from inserted join student on inserted.Id=student.Id
        end
        if(UPDATE(subjects))
        Begin
        update student set subjects=inserted.subjects from inserted join student on inserted.Id=student.Id
        end
        if(UPDATE(fees))
        Begin
        update student set fees=inserted.fees from inserted join student on inserted.Id=student.Id
        end
    END
GO

Now, create an Instead Trigger for the row deletion. 

CREATE TRIGGER [Delete_View]
    ON [dbo].[stud_detaile]
    instead of delete
    AS
    BEGIN
        delete student from deleted join student on student.Id=deleted.Id
    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.

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

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.

In this way, we can create our model.

Now, we can double click on Model1.edmx.

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. 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Webtest1.Models;
namespace Webtest1.Controllers
{
    public class HomeController : Controller
    {
        demo2Entities1 ds = new demo2Entities1();

        public ActionResult Index()
        {
            return View();
        }
}
}

Now, we will create show_detaile() method. 

[HttpGet]
      public ActionResult show_detail1()
      {

          var data = ds.stud_detaile.ToList();

          return View(data);


      }

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

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

public ActionResult SaveContact(stud_detaile sd)
        {
ds.View_update(sd.Name, sd.classs, sd.fees,sd.collegename,            sd.Rollno,sd.subjects, sd.Id);

            return View("show_detail1");

                }

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. 

public ActionResult DeleteContact(stud_detaile sd)
        {
            ds.View_Delete(sd.Id);
            return View("show_detail1");
        }

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. 

@model ICollection<Webtest1.Models.stud_detaile>

<div id="ajaxgrid">
    @{
WebGrid grid = new WebGrid(Model, canPage: true, rowsPerPage: 50, selectionFieldName: "selectedRow", ajaxUpdateContainerId: "ajaxgrid");
        grid.Pager(WebGridPagerModes.NextPrevious);

}
</div>
<h2 class="alert-success">show_detail</h2>

<div id="ajaxid">
    @grid.GetHtml(tableStyle:
    "table table-responsive table-bordered",
    headerStyle: "",
   alternatingRowStyle: "webgrid-alternating-row",
    selectedRowStyle: "webgrid-row-style",
    mode: WebGridPagerModes.FirstLast,
    columns: grid.Columns
    (grid.Column("Id", "ID"),
  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>),
  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>),
  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>),
  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>),
  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>),
  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>),
  grid.Column("Action", style: "col2", format: @<text>
        <div>
            <div style="padding-left:20px;">
                <button class="btn btn-default display-mode edit-item" id="@item.ID">Edit</button>
                <button class="btn btn-default display-mode delete-item" id="@item.ID">Delete</button>
                <button class="btn btn-default save-item edit-mode" id="@item.ID">Save</button>
                <button class="btn btn-default cancel-item edit-mode" id="@item.ID">Cancel</button>
            </div>

        </div>

</text>))

            )
</div>

Now, we will add some jQuery code. 

<script src="~/Scripts/jquery-3.1.1.min.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.min.js"></script>
<script type="text/javascript">
    $(function () {
        $('.edit-mode').hide();
        $('.edit-item').on('click', function () {
            $('.edit-mode').hide();
            $('.delete-mode').hide();
            $('.display-mode').show();
            var tr = $(this).parents('tr:first');
            tr.find('.edit-mode, .display-mode').toggle();
        });
        $('.cancel-item').on('click', function () {
            var tr = $(this).parents('tr:first');
            tr.find('.display-mode,.edit-mode').toggle();
        });
        $('.delete-item').on('click', function () {
            if (confirm("Are you sure to delete this contact?")) {
                var tr = $(this).parents('tr:first');
                var ID = $(this).prop('id');
                //Deletes the record with ID sent below
                $.post(
                    '/Home/DeleteContact/',
                    { ID: ID },
                    function (item) {
                        tr.remove();
                    }, "json");
             location.reload();
            }
        });
        $('.save-item').on('click', function () {
            $('#pro').show();
            var tr = $(this).parents('tr:first');
            var ID = $(this).prop('id');
            var classs1 = tr.find('[name=classs-Edit]').val();
            var collegename1 = tr.find('[name=collegename-Edit]').val();
            var fees1 = tr.find('[name=fees-Edit]').val();
            var Name1 = tr.find('[name=Name-Edit]').val();
            var Rollno1 = tr.find('[name=Rollno-Edit]').val();
            var subjects1 = tr.find('[name=subjects-Edit]').val();
            $.ajax({
                type: "POST",
                url: "/Home/SaveContact/",
                data: { ID: ID, classs: classs1, collegename: collegename1, fees: fees1, Name: Name1, Rollno: Rollno1, subjects: subjects1 },
                success: function (item) {
                    tr.find('#classs').text(item.classs1);
                    tr.find('#collegename').text(item.collegename1);
                    tr.find('#fees').text(item.fees1);
                    tr.find('#Name').text(item.Name1);
                    tr.find('#Rollno').text(item.Rollno1);
                    tr.find('#subjects').text(item.subjects1);

                    if (item != null) {
                        $('#pro').hide();
                        location.reload();
                    }
                    else
                        alert(item);

                },
                error: function (result) {
                    alert('Error!');
                }

            });
            tr.find('.edit-mode, .display-mode').toggle();

        });
    })

</script>

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

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

Afterwards, click Save button.


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

Here, a row is successfully deleted.