MVC .NET View, Insert, Update and Delete using Database

Question

The admin of a golf club has a requirement for an application which will help him manage the members of the club.

Create the tables in the following DB Create the following tables . Server -myserver User ID – yourid Password – yourpassword Database – yourdatabase

Table 1: tbl_Club<empid> 

Insert the following data into the above table using only STORED PROCEDURE.

Table 2: tbl_Member<empID>

Create an MVC application with EF for the below operations :

Crud Operation 1 : Register Member

The Admin should be able to register new members in the club. Create a web page which is used to register the member and on click of submit button need to show a pop up message with success message and memberid. The available club id should be displayed to the admin in a dropdown list for registration.

Crud Operation 2 : View and Remove Member

The user should be able to view the memberid, membername and clublocation in a webgrid or table. These columns should be joined from two tables using SQL joins. There should be a button named 'Remove' which will delete the member from the database after getting confirmation from the user.

Solution

SQL SERVER OPERATIONS

Database used

USE DB02TEST01;

Table creation and insert data

CREATE TABLE aa_tbl_Club_1637935(
ClubID INT PRIMARY KEY IDENTITY(1001,1),
ClubLocation VARCHAR(50)
)
 

SELECT * FROM aa_tbl_Club_1637935

INSERT INTO aa_tbl_Club_1637935 VALUES
('Trivandrum'),
('Kochi'),
('Calicut'),
('Paala')

Table creation and insert data

CREATE TABLE aa_bl_Member_16379(
MemberID INT PRIMARY KEY IDENTITY(1,1),
MemberName VARCHAR(50),
Gender VARCHAR(50),
MemberShipType VARCHAR(50),
ClubID INT CONSTRAINT FK_club_Id FOREIGN KEY (ClubID)  REFERENCES tbl_Club_1637935(ClubID)
)

SELECT * FROM aa_bl_Member_16379

INSERT INTO aa_bl_Member_16379 VALUES
('Rumman Ansari','Male','Gold',1001)

Create Procedures for select data using joining

// without joining

ALTER PROCEDURE sp_select_bl_Member_16379
AS
BEGIN
SELECT * FROM aa_bl_Member_16379
END 


// with joining

ALTER PROCEDURE aa_sp_select_bl_Member_16379
AS
BEGIN
SELECT bl.MemberID, bl.MemberName,  cl.ClubLocation
from aa_bl_Member_16379 as bl, aa_tbl_Club_1637935 as cl where
bl.ClubID = cl.ClubID group by bl.MemberID, bl.MemberName,  cl.ClubLocation
END


// procedure for insertion 

ALTER PROCEDURE aa_sp_insert_bl_Member_16379
@MemberName VARCHAR(50),
@Gender VARCHAR(50),
@MemberShipType VARCHAR(50),
@ClubID INT,
@MemberID INT OUT
AS
BEGIN
INSERT INTO aa_bl_Member_16379 VALUES
(@MemberName, @Gender, @MemberShipType, @ClubID)
SET @MemberID = @@IDENTITY
END

// code for checking the insertion
DECLARE @MemberID INT
EXEC aa_sp_insert_bl_Member_16379 'A1 Ansari','Male','Gold',1002, @MemberID OUT
PRINT @MemberID



// procedure for delete database record

ALTER PROCEDURE aa_delete_bl_Member_16379
@hello INT 
AS
BEGIN
DELETE FROM aa_bl_Member_16379 WHERE MemberID = @hello 
END

EXEC aa_delete_bl_Member_16379 6


// procedure for update the database


CREATE PROCEDURE aa_update_sp_aa_bl_Member_16379
@MemberName VARCHAR(50),
@Gender VARCHAR(50),
@MemberShipType VARCHAR(50),
@ClubID INT,
@MemberID INT
AS
BEGIN
UPDATE aa_bl_Member_16379 SET MemberName = @MemberName,
Gender = @Gender,
MemberShipType = @MemberShipType ,
ClubID = @ClubID WHERE
MemberID = @MemberID 
END

.NET CODE

RouteConfig

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;

namespace GolfWebApplication
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
            );
        }
    }
}

DB02TEST01Entities

This is database connection part

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace GolfWebApplication.Models
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.Core.Objects;
    using System.Linq;
    
    public partial class DB02TEST01Entities : DbContext
    {
        public DB02TEST01Entities()
            : base("name=DB02TEST01Entities")
        {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    
        public virtual DbSet<aa_bl_Member_16379> aa_bl_Member_16379 { get; set; }
        public virtual DbSet<aa_tbl_Club_1637935> aa_tbl_Club_1637935 { get; set; }
    
        public virtual int aa_sp_insert_bl_Member_16379(string memberName, string gender, string memberShipType, Nullable<int> clubID, ObjectParameter memberID)
        {
            var memberNameParameter = memberName != null ?
                new ObjectParameter("MemberName", memberName) :
                new ObjectParameter("MemberName", typeof(string));
    
            var genderParameter = gender != null ?
                new ObjectParameter("Gender", gender) :
                new ObjectParameter("Gender", typeof(string));
    
            var memberShipTypeParameter = memberShipType != null ?
                new ObjectParameter("MemberShipType", memberShipType) :
                new ObjectParameter("MemberShipType", typeof(string));
    
            var clubIDParameter = clubID.HasValue ?
                new ObjectParameter("ClubID", clubID) :
                new ObjectParameter("ClubID", typeof(int));
    
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("aa_sp_insert_bl_Member_16379", memberNameParameter, genderParameter, memberShipTypeParameter, clubIDParameter, memberID);
        }
    
        public virtual ObjectResult<aa_sp_select_bl_Member_16379_Result> aa_sp_select_bl_Member_16379()
        {
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<aa_sp_select_bl_Member_16379_Result>("aa_sp_select_bl_Member_16379");
        }
    
        public virtual int aa_delete_bl_Member_16379(Nullable<int> hello)
        {
            var helloParameter = hello.HasValue ?
                new ObjectParameter("hello", hello) :
                new ObjectParameter("hello", typeof(int));
    
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("aa_delete_bl_Member_16379", helloParameter);
        }
    
        public virtual int aa_update_sp_aa_bl_Member_16379(string memberName, string gender, string memberShipType, Nullable<int> clubID, Nullable<int> memberID)
        {
            var memberNameParameter = memberName != null ?
                new ObjectParameter("MemberName", memberName) :
                new ObjectParameter("MemberName", typeof(string));
    
            var genderParameter = gender != null ?
                new ObjectParameter("Gender", gender) :
                new ObjectParameter("Gender", typeof(string));
    
            var memberShipTypeParameter = memberShipType != null ?
                new ObjectParameter("MemberShipType", memberShipType) :
                new ObjectParameter("MemberShipType", typeof(string));
    
            var clubIDParameter = clubID.HasValue ?
                new ObjectParameter("ClubID", clubID) :
                new ObjectParameter("ClubID", typeof(int));
    
            var memberIDParameter = memberID.HasValue ?
                new ObjectParameter("MemberID", memberID) :
                new ObjectParameter("MemberID", typeof(int));
    
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("aa_update_sp_aa_bl_Member_16379", memberNameParameter, genderParameter, memberShipTypeParameter, clubIDParameter, memberIDParameter);
        }
    }
}

Controler part

using GolfWebApplication.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity.Core.Objects;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;

namespace GolfWebApplication.Controllers
{
    public class HomeController : Controller
    {
        DB02TEST01Entities modelEntityObject = new DB02TEST01Entities();

        // GET: Home
       
        public ActionResult Index()
        {
            List<aa_sp_select_bl_Member_16379_Result> listObj = new List<aa_sp_select_bl_Member_16379_Result>();
            listObj = modelEntityObject.aa_sp_select_bl_Member_16379().ToList();
            return View(listObj);
        }

        // this code is much more important to get the data
        public ActionResult AddMember()
        {
            return View();
        }


        // this code is used to add member in the database
        [HttpPost]
        public ActionResult AddMember(aa_bl_Member_16379 objMemberClass)
        {
            ObjectParameter memberID = new ObjectParameter("memberID", typeof(int));
            int rows = modelEntityObject.aa_sp_insert_bl_Member_16379(objMemberClass.MemberName, objMemberClass.Gender, objMemberClass.MemberShipType, objMemberClass.ClubID, memberID);

            if (rows > 0)
            {
                TempData["Message"] = "Inserted with id =" + memberID.Value.ToString();
                return RedirectToAction("Index");
            }

            else
            {
                ViewBag.Message = " Sorry !! Insert Failed";
                return View();
            }
        }

        // this code is used to delete the information from the database
        public ActionResult Delete(int id) {

            int rowsEffected;
            Response.Write("<script> confirm('Are you sure?')</script>");
            rowsEffected = modelEntityObject.aa_delete_bl_Member_16379(id);
            if (rowsEffected > 0) { 
               Response.Write("<script> alert('Deleted Successfully')</script>");
               
            }

            return RedirectToAction("Index");

        }


        // this code is used to edit the information and this is a get method to get the data 
        // from database and it will render the data in the edit form

        [HttpGet]
        public ActionResult Edit(int id) {

            aa_bl_Member_16379 member = new aa_bl_Member_16379();
            member = modelEntityObject.aa_bl_Member_16379.Where(x => x.MemberID == id).FirstOrDefault();
            return View(member); 
        }


      // remember the below method is used to edit the data taken from the edit page
      // This is a post method as this is sending the updated data in the database
 
        [HttpPost]
        public ActionResult Edit(aa_bl_Member_16379 objMemberClass)
        {

           modelEntityObject.aa_update_sp_aa_bl_Member_16379(objMemberClass.MemberName, objMemberClass.Gender, objMemberClass.MemberShipType, objMemberClass.ClubID, objMemberClass.MemberID);

           return RedirectToAction("Index");
        }


    }
}

Model Part

aa_bl_Member_16379

This code is autogenerated don't try to change this page. If you need to change then you have to do it by importing the data table into your project and refresh your project and rebuild it again.

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace GolfWebApplication.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class aa_bl_Member_16379
    {
        public int MemberID { get; set; }
        public string MemberName { get; set; }
        public string Gender { get; set; }
        public string MemberShipType { get; set; }
        public Nullable<int> ClubID { get; set; }
    }
}

aa_sp_select_bl_Member_16379_Result

This code is autogenerated don't try to change this page. If you need to change then you have to do it by importing the data table into your project and refresh your project and rebuild it again.

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace GolfWebApplication.Models
{
    using System;
    
    public partial class aa_sp_select_bl_Member_16379_Result
    {
        public int MemberID { get; set; }
        public string MemberName { get; set; }
        public string ClubLocation { get; set; }
    }
}

aa_tbl_Club_1637935

This code is autogenerated don't try to change this page. If you need to change then you have to do it by importing the data table into your project and refresh your project and rebuild it again.

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace GolfWebApplication.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class aa_tbl_Club_1637935
    {
        public int ClubID { get; set; }
        public string ClubLocation { get; set; }
    }
}

ViewJoin Class (Created by myself)

This is page is written by myself. This is a properties which you need to view the joining the two table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace GolfWebApplication.Models
{
    public class ViewJoin
    {

        public int MemberID { get; set; }
        public int MemberName { get; set; }
        public int ClubLocation { get; set; } 
    }

  
}

View Part

Index.cshtml

This is a autogenerated page. You can write it by your own choice according to your requirement.

@model IEnumerable<GolfWebApplication.Models.aa_sp_select_bl_Member_16379_Result>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "AddMember")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.MemberID)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.MemberName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.ClubLocation)
        </th>
        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.MemberID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.MemberName)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.ClubLocation)
            </td>
            <td>
                @Html.ActionLink("Edit", "Edit", new {   id=item.MemberID  }) |
                @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
                @Html.ActionLink("Delete", "Delete", new { id = item.MemberID  })
            </td>
        </tr>

    }
</table>

<div style="background-color:orange">
    @ViewBag.Message
</div>

AddMember.cshtml

This is a autogenerated page. You can write it by your own choice according to your requirement.

 @model GolfWebApplication.Models.aa_bl_Member_16379

@{
    ViewBag.Title = "AddMember";
}

<h2>AddMember</h2>

 @{ 
     List<SelectListItem> list = new List<SelectListItem>();
     list.Add(new SelectListItem
     {
         Text ="Trivandrum ",
         Value ="1001",
     });
     list.Add(new SelectListItem
     {
         Text = "Kochi",
         Value = "1002",
     });
     list.Add(new SelectListItem
     {
         Text = "Calicut",
         Value = "1003",
     });
     list.Add(new SelectListItem
     {
         Text = "Paala",
         Value = "1004",
     });

     })
 }

@using (Html.BeginForm())
{

    <table class="table table-bordered">
        <tr>
            <td> @Html.LabelFor(model => model.MemberName)</td>
            <td> @Html.EditorFor(model => model.MemberName)</td>
        </tr>
        <tr>
            <td> @Html.LabelFor(model => model.Gender)</td> 
            <td>
                @Html.RadioButtonFor(model => model.Gender, "Male")Male
                @Html.RadioButtonFor(model => model.Gender, "Female")Female
            </td>
            @*<td> @Html.DropDownListFor(model => model.Gender, new SelectList(),
                    "Select Gender",
                    new { @class = "form-control" })</td>*@
        </tr>
        <tr>
            <td>@Html.LabelFor(model => model.MemberShipType) </td>
            <td>@Html.EditorFor(model => model.MemberShipType)</td>
        </tr>
        <tr>
            <td> @Html.LabelFor(model => model.ClubID)  </td>
            <td> @Html.DropDownListFor(model=>model.ClubID, list,"--Select ID--") </td>
        </tr>
        <tr>
            <td colspan="2">
                <input id="Submit1" type="submit" value="submit" />
            </td>
        </tr>
    </table>
    <div style="background-color:orange">
        @ViewBag.Message
    </div>
}

Edit.cshtml

This is a autogenerated page. You can write it by your own choice according to your requirement.

@model GolfWebApplication.Models.aa_bl_Member_16379

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>aa_bl_Member_16379</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.MemberID, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.MemberID, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.MemberID, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.MemberName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.MemberName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.MemberName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Gender, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Gender, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Gender, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.MemberShipType, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.MemberShipType, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.MemberShipType, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.ClubID, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ClubID, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.ClubID, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                @Html.ActionLink("Back to List", "Edit")
                <input type="submit" value="Save" class="btn btn-default" formaction="EditMember"/>
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>