CRUD Operation Using Single Stored Procedure In ASP.NET MVC Of Real-Time Project

Introduction

CRUD means create update and delete.

With ASP.NET in MVC, we can also perform CRUD operations using stored procedure.

Description

Using a single stored procedure means selecting, updating, inserting and deleting all SQL queries; we can put in one stored procedure and perform this CRUD operation by calling only this single stored procedure.

Steps to build MVC Application

Step 1

Create MVC Application named “SatyaMvc4Crud”.

Step 2

Create a model class file called Customer.cs.

Code Ref 

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace MVC4crud.Models
{
   public class Customer
    {
        [Key]
        public int CustomerID { get; set; }

        [Required(ErrorMessage = "Enter Your Name")]
        [StringLength(4, ErrorMessage = "Name should be less than or equal to four characters.")]
        public string Name { get; set; }

        [Required(ErrorMessage = "Enter Your Address")]
        [StringLength(10, ErrorMessage = "Address should be less than or equal to ten characters.")]
        public string Address { get; set; }
        [Required(ErrorMessage = "Your must provide a PhoneNumber")]
        [Display(Name = "Home Phone")]
        [DataType(DataType.PhoneNumber)]
        [RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Not a valid Phone number")]
        public string Mobileno { get; set; }
        [DataType(DataType.Date)]
        [Required(ErrorMessage = "Enter Your DOB.")]
        [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}", ApplyFormatInEditMode = true)]
        [MVC4crud.Models.CustomValidationAttributeDemo.ValidBirthDate(ErrorMessage = "Birth Date can not be greater than current date")]
        public DateTime Birthdate { get; set; }
        [Required(ErrorMessage = "Enter Your EmailID")]
        [RegularExpression(@"^[\w-\._\+%]+@(?:[\w-]+\.)+[\w]{2,6}$", ErrorMessage = "Please enter a valid email address")]
        public string EmailID { get; set; }

        public List<Customer> ShowallCustomer { get; set; }
    }

}

Code description

Here, I have created some attributes to check validation of controls, which are based on control values. For textbox name and address validation, I have put [Required(ErrorMessage = "Your Message")].

This code will be executed, if your input is empty in controls. 

Now, if the user puts something but this does not satisfy the standard validation, then the code will be, as given below.

[StringLength(4, ErrorMessage = "Name should be less than or equal to four characters.")]

The user can put only four characters to control the input values.

Like this, you can check for other attributes, which are based on control validation values.

Here, I will declare 6 different entities to access the user and inputs. For every entity, I required an attribute to show the validation message failed for the end users.

e.g. [Required(ErrorMessage = "Enter Your Name")]

Like this required attribute, I used StringLength, Display, DisplayFormat, RegularExpression attributes.

We have used some attributes. For this, we have to add one namespace.

using System.ComponentModel.DataAnnotations;  

In name part, I can enter up to 4 characters.

[StringLength(4, ErrorMessage = "Name should be less than or equal to four characters.")]

In address part, I can enter up to 10 characters.

[StringLength(10, ErrorMessage = "Address should be less than or equal to ten characters.")]

In MobileNo. part, I can enter only 10 digit valid phone no. 

[DataType(DataType.PhoneNumber)]
[RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Not a valid Phone number")]

In an E-mail Id part, I can enter only a valid E-mail Id with @ symbol.

[RegularExpression(@"^[\w-\._\+%]+@(?:[\w-]+\.)+[\w]{2,6}$", ErrorMessage = "Please enter a valid email address")]

In Date Time part, I can enter only valid date, which should be less than current date. 

[DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}", ApplyFormatInEditMode = true)]
[val.Models.UserCustomValidation.ValidBirthDate(ErrorMessage = "Birth Date can not be greater than current date")]

For Custom validation of the entities, Code Ref is given below.

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

namespace MVC4crud.Models
{
    public class CustomValidationAttributeDemo
    {
        [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
        public sealed class ValidBirthDate : ValidationAttribute
        {
            protected override ValidationResult IsValid(object value, ValidationContext validationContext)
            {
                if (value != null)
                {
                    DateTime _birthJoin = Convert.ToDateTime(value);
                    if (_birthJoin > DateTime.Now)
                    {
                        return new ValidationResult("Birth date can not be greater than current date.");
                    }
                }
                return ValidationResult.Success;
            }
        }
    }
}

Code description

Here, I have used one Custom Validation class to customize your Date time validation. For this, I created one class file in Models folder named “CustomValidationAttributeDemo.cs” .

Here, I used one date time variable to access date time .

DateTime _birthJoin = Convert.ToDateTime(value);

Thus, I put some code to take the user’s birth date. Birth date should always be less than today’s date.

if (_birthJoin > DateTime.Now)
                    {
               return new ValidationResult("Birth date can not be greater than current date.");
                    }

Here, I used one class,  ValidBirthDate, that is inherited from ValidationAttribute class.

public sealed class ValidBirthDate : ValidationAttribute

What is ValidationAttribute class?

It serves as a base class for all the validation attributes.

Go to the definition of this ValidationAttribute class.

Here, System.ComponentModel.DataAnnotations.dll file references for this class files.

#region Assembly System.ComponentModel.DataAnnotations.dll, v4.0.0.0  // C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.ComponentModel.DataAnnotations.dll  #endregion  

The ValidationResult override method is used to represent a container for the result of the validation request. The ValidationContext class acts as a parameter inside ValidationResult override method. It is used to describe a context in which validation check is performed. 

protected override ValidationResult IsValid(object value, ValidationContext validationContext)
return new ValidationResult("Birth date can not be greater than current date.");
return ValidationResult.Success;

In custom validation class, I used Student.cs for DATE TIME entity. According to this, the user input date should be less than today’s date.

[val.Models.CustomValidationAttributeDemo.ValidBirthDate(ErrorMessage = "Birth Date can not be greater than current date")]

Here, CustomValidationAttributeDemo is the name of the Model Class and ValidBirthDate class is the child class of ValidateAttribute base class.

Step 3

Create a table to add stored procedure to perform CRUD operation in MVC.

Table SQL Ref 

CREATE TABLE [dbo].[Customer]
(
[CustomerID] [int] NOT NULL Primary key identity(1,1),
[Name] [varchar](100) NULL,
[Address] [varchar](300) NULL,
[Mobileno] [varchar](15) NULL,
[Birthdate] [datetime] NULL,
[EmailID] [varchar](300) NULL
)

Table SQL Description

Here, six columns are same as entities declared in models class  “Customer.cs”.

Here “[CustomerID]” is the primary key and auto increment feature is added in these columns.

Step 4

Now, create a  stored procedure to perform CRUD operation in MVC.

Stored procedure Script Ref 

USE [database_name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Usp_InsertUpdateDelete_Customer]
@CustomerID INT = NULL
,@Name NVARCHAR(100) = NULL
,@Mobileno NVARCHAR(15) = NULL
,@Address NVARCHAR(300) = 0
,@Birthdate DATETIME = NULL
,@EmailID NVARCHAR(15) = NULL
,@Query INT
AS
BEGIN
IF (@Query = 1)
BEGIN



INSERT INTO Customer(
NAME
,Address
,Mobileno
,Birthdate
,EmailID
)
VALUES (
@Name
,@Address
,@Mobileno
,@Birthdate
,@EmailID
)
IF (@@ROWCOUNT > 0)
BEGIN
SELECT 'Insert'
END
END
IF (@Query = 2)
BEGIN
UPDATE Customer
SET NAME = @Name
,Address = @Address
,Mobileno = @Mobileno
,Birthdate = @Birthdate
,EmailID = @EmailID
WHERE Customer.CustomerID = @CustomerID
SELECT 'Update'
END
IF (@Query = 3)
BEGIN
DELETE
FROM Customer
WHERE Customer.CustomerID = @CustomerID
SELECT 'Deleted'
END
IF (@Query = 4)
BEGIN
SELECT *
FROM Customer
END
END
IF (@Query = 5)
BEGIN
SELECT *
FROM Customer
WHERE Customer.CustomerID = @CustomerID
END

Stored procedure Script description

Here, five @Query parameters with different value for Insert/ Update/ Delete/ Select/Search statement.

Here (@Query = 1) means for insertion of the records. 

F (@Query = 1)
BEGIN
INSERT INTO Customer(
NAME
,Address
,Mobileno
,Birthdate
,EmailID
)
VALUES (
@Name
,@Address
,@Mobileno
,@Birthdate
,@EmailID
)

Like this, other (@Query = 2 to 5) is assigned for other operation performances.

By using single procedure by using this individual query parameter values; we can perform different operations.

Step 5

Create a class file called DataAccessLayer.cs inside manually created folder named DataAccess.

To add connection string, add name as well as stored procedure name to perform CRUD role.

Code Ref

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MVC4crud.Models;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace MVC4crud.DataAccess
{
    public class DataAccessLayer
    {
        public string InsertData(Customer objcust)
        {
            SqlConnection con = null;

            string result = "";
            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
                SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete_Customer", con);
                cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Parameters.AddWithValue("@CustomerID", 0);
                cmd.Parameters.AddWithValue("@Name", objcust.Name);
                cmd.Parameters.AddWithValue("@Address", objcust.Address);
                cmd.Parameters.AddWithValue("@Mobileno", objcust.Mobileno);
                cmd.Parameters.AddWithValue("@Birthdate", objcust.Birthdate);
                cmd.Parameters.AddWithValue("@EmailID", objcust.EmailID);
                cmd.Parameters.AddWithValue("@Query", 1);
                con.Open();
                result = cmd.ExecuteScalar().ToString();
                return result;
            }
            catch
            {
                return result = "";
            }
            finally
            {
                con.Close();
            }
        }
        public string UpdateData(Customer objcust)
        {
            SqlConnection con = null;
            string result = "";
            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
                SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete_Customer", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CustomerID", objcust.CustomerID);
                cmd.Parameters.AddWithValue("@Name", objcust.Name);
                cmd.Parameters.AddWithValue("@Address", objcust.Address);
                cmd.Parameters.AddWithValue("@Mobileno", objcust.Mobileno);
                cmd.Parameters.AddWithValue("@Birthdate", objcust.Birthdate);
                cmd.Parameters.AddWithValue("@EmailID", objcust.EmailID);
                cmd.Parameters.AddWithValue("@Query", 2);
                con.Open();
                result = cmd.ExecuteScalar().ToString();
                return result;
            }
            catch
            {
                return result = "";
            }
            finally
            {
                con.Close();
            }
        }
        public int DeleteData(String ID)
        {
            SqlConnection con = null;
            int result;
            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
                SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete_Customer", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CustomerID", ID);
                cmd.Parameters.AddWithValue("@Name", null);
                cmd.Parameters.AddWithValue("@Address", null);
                cmd.Parameters.AddWithValue("@Mobileno", null);
                cmd.Parameters.AddWithValue("@Birthdate", null);
                cmd.Parameters.AddWithValue("@EmailID", null);
                cmd.Parameters.AddWithValue("@Query", 3);
                con.Open();
                result = cmd.ExecuteNonQuery();
                return result;
            }
            catch
            {
                return result = 0;
            }
            finally
            {
                con.Close();
            }
        }
        public List<Customer> Selectalldata()
        {
            SqlConnection con = null;
            DataSet ds = null;
            List<Customer> custlist = null;
            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
                SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete_Customer", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CustomerID", null);
                cmd.Parameters.AddWithValue("@Name", null);
                cmd.Parameters.AddWithValue("@Address", null);
                cmd.Parameters.AddWithValue("@Mobileno", null);
                cmd.Parameters.AddWithValue("@Birthdate", null);
                cmd.Parameters.AddWithValue("@EmailID", null);
                cmd.Parameters.AddWithValue("@Query", 4);
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                ds = new DataSet();
                da.Fill(ds);
                custlist = new List<Customer>();
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Customer cobj = new Customer();
                    cobj.CustomerID = Convert.ToInt32(ds.Tables[0].Rows[i]["CustomerID"].ToString());
                    cobj.Name = ds.Tables[0].Rows[i]["Name"].ToString();
                    cobj.Address = ds.Tables[0].Rows[i]["Address"].ToString();
                    cobj.Mobileno = ds.Tables[0].Rows[i]["Mobileno"].ToString();
                    cobj.EmailID = ds.Tables[0].Rows[i]["EmailID"].ToString();
                    cobj.Birthdate = Convert.ToDateTime(ds.Tables[0].Rows[i]["Birthdate"].ToString());

                    custlist.Add(cobj);
                }
                return custlist;
            }
            catch
            {
                return custlist;
            }
            finally
            {
                con.Close();
            }
        }

        public Customer SelectDatabyID(string CustomerID)
        {
            SqlConnection con = null;
            DataSet ds = null;
            Customer cobj = null;
            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
                SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete_Customer", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
                cmd.Parameters.AddWithValue("@Name", null);
                cmd.Parameters.AddWithValue("@Address", null);
                cmd.Parameters.AddWithValue("@Mobileno", null);
                cmd.Parameters.AddWithValue("@Birthdate", null);
                cmd.Parameters.AddWithValue("@EmailID", null);
                cmd.Parameters.AddWithValue("@Query", 5);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                ds = new DataSet();
                da.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    cobj = new Customer();
                    cobj.CustomerID = Convert.ToInt32(ds.Tables[0].Rows[i]["CustomerID"].ToString());
                    cobj.Name = ds.Tables[0].Rows[i]["Name"].ToString();
                    cobj.Address = ds.Tables[0].Rows[i]["Address"].ToString();
                    cobj.Mobileno = ds.Tables[0].Rows[i]["Mobileno"].ToString();
                    cobj.EmailID = ds.Tables[0].Rows[i]["EmailID"].ToString();
                    cobj.Birthdate = Convert.ToDateTime(ds.Tables[0].Rows[i]["Birthdate"].ToString());

                }
                return cobj;
            }
            catch
            {
                return cobj;
            }
            finally
            {
                con.Close();
            }
        }
    }
}

Code description

Here, I will show how to implement query parameter value, which is 1 to implement in this class file to perform insert operation.

public string InsertData(Customer objcust)
        {
            SqlConnection con = null;

            string result = "";
            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
                SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete_Customer", con);
                cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Parameters.AddWithValue("@CustomerID", 0);
                cmd.Parameters.AddWithValue("@Name", objcust.Name);
                cmd.Parameters.AddWithValue("@Address", objcust.Address);
                cmd.Parameters.AddWithValue("@Mobileno", objcust.Mobileno);
                cmd.Parameters.AddWithValue("@Birthdate", objcust.Birthdate);
                cmd.Parameters.AddWithValue("@EmailID", objcust.EmailID);
                cmd.Parameters.AddWithValue("@Query", 1);
                con.Open();
                result = cmd.ExecuteScalar().ToString();
                return result;
            }
            catch
            {
                return result = "";
            }
            finally
            {
                con.Close();
            }
        }

In this InsertData() function, I used @Query = 1 value to perform insert operation. Here, I have added stored procedure name.

SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete_Customer", con);

To perform update operation, I have added the code in UpdateData function. 

cmd.Parameters.AddWithValue("@Query", 2);

To perform delete operation, I have added the code in DeleteData function. 

cmd.Parameters.AddWithValue("@Query", 3);

To perform select list of data, I have added the code in Selectalldata list function of customer model class. 

cmd.Parameters.AddWithValue("@Query", 4);

I have added one for loop to perform selection of all the data loop wise by using customer model class.

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    cobj = new Customer();
                    cobj.CustomerID = Convert.ToInt32(ds.Tables[0].Rows[i]["CustomerID"].ToString());
                    cobj.Name = ds.Tables[0].Rows[i]["Name"].ToString();
                    cobj.Address = ds.Tables[0].Rows[i]["Address"].ToString();
                    cobj.Mobileno = ds.Tables[0].Rows[i]["Mobileno"].ToString();
                    cobj.EmailID = ds.Tables[0].Rows[i]["EmailID"].ToString();
                    cobj.Birthdate = Convert.ToDateTime(ds.Tables[0].Rows[i]["Birthdate"].ToString());

                }

Now, I will filter the records by using customer Id values.

I have added the code given below.

cmd.Parameters.AddWithValue("@Query", 5);

Thus, I have added customer Id parameter in a function of customer model class.

public Customer SelectDatabyID(string CustomerID)

Now, I have closed the connection in every functions in this class by using catch and finally block. 

finally
            {
                con.Close();
            }

Step 6

Create a controller class file called CustomerController.cs.

Code Ref 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MVC4crud.Models;
using MVC4crud.DataAccess;

namespace MVC4crud.Controllers
{
    public class CustomerController : Controller
    {
        //
        // GET: /Customer/
        [HttpGet]
        public ActionResult InsertCustomer()
        {
            return View();
        }
        [HttpPost]
        public ActionResult InsertCustomer(Customer objCustomer)
        {

                objCustomer.Birthdate = Convert.ToDateTime(objCustomer.Birthdate);
                if (ModelState.IsValid) //checking model is valid or not
                {
                    DataAccessLayer objDB = new DataAccessLayer();
                    string result = objDB.InsertData(objCustomer);
                    //ViewData["result"] = result;
                    TempData["result1"] = result;
                    ModelState.Clear(); //clearing model
                    //return View();
                    return RedirectToAction("ShowAllCustomerDetails");
                }

                else
                {
                    ModelState.AddModelError("", "Error in saving data");
                    return View();
                }
        }

        [HttpGet]
        public ActionResult ShowAllCustomerDetails()
        {
            Customer objCustomer = new Customer();
            DataAccessLayer objDB = new DataAccessLayer(); //calling class DBdata
            objCustomer.ShowallCustomer = objDB.Selectalldata();
            return View(objCustomer);
        }
        [HttpGet]
        public ActionResult Details(string ID)
        {
            //Customer objCustomer = new Customer();
            //DataAccessLayer objDB = new DataAccessLayer(); //calling class DBdata
            //objCustomer.ShowallCustomer = objDB.Selectalldata();
            //return View(objCustomer);
            Customer objCustomer = new Customer();
            DataAccessLayer objDB = new DataAccessLayer(); //calling class DBdata
            return View(objDB.SelectDatabyID(ID));
        }
        [HttpGet]
        public ActionResult Edit(string ID)
        {
            Customer objCustomer = new Customer();
            DataAccessLayer objDB = new DataAccessLayer(); //calling class DBdata
            return View(objDB.SelectDatabyID(ID));
        }

        [HttpPost]
        public ActionResult Edit(Customer objCustomer)
        {
            objCustomer.Birthdate = Convert.ToDateTime(objCustomer.Birthdate);
            if (ModelState.IsValid) //checking model is valid or not
            {
                DataAccessLayer objDB = new DataAccessLayer(); //calling class DBdata
                string result = objDB.UpdateData(objCustomer);
                //ViewData["result"] = result;
                TempData["result2"] = result;
                ModelState.Clear(); //clearing model
                //return View();
                return RedirectToAction("ShowAllCustomerDetails");
            }
            else
            {
                ModelState.AddModelError("", "Error in saving data");
                return View();
            }
        }

        [HttpGet]
        public ActionResult Delete(String ID)
        {
                DataAccessLayer objDB = new DataAccessLayer();
                int result = objDB.DeleteData(ID);
               TempData["result3"] = result;
                ModelState.Clear(); //clearing model
                //return View();
                return RedirectToAction("ShowAllCustomerDetails");
         }
    }
}

Code description

In this controller class file, I have created one controller action method.

public ActionResult InsertCustomer(Customer objCustomer)
        {

                objCustomer.Birthdate = Convert.ToDateTime(objCustomer.Birthdate);
                if (ModelState.IsValid)
                {
                    DataAccessLayer objDB = new DataAccessLayer();
                    string result = objDB.InsertData(objCustomer);
                    //ViewData["result"] = result;
                    TempData["result1"] = result;
                    ModelState.Clear();                    //return View();
                    return RedirectToAction("ShowAllCustomerDetails");
                }

                else
                {
                    ModelState.AddModelError("", "Error in saving data");
                    return View();
                }
        }

Here, DataAccessLayer is added as a reference to use all its methods.

DataAccessLayer objDB = new DataAccessLayer();
string result = objDB.InsertData(objCustomer);

The code is given below for checking model is valid or not.

if (ModelState.IsValid)
for  “clearing model”

ModelState.Clear();

Here, if the condition satisfies, then model state will be valid, else the data will not save properly.

else
    {
      ModelState.AddModelError("", "Error in saving data");
      return View();
    }

Here, I added the show details view page name as soon as the data is inserted successfully, the page will show you the list of inserted data.

return RedirectToAction("ShowAllCustomerDetails");

In ShowAllCustomerDetails action result method, the Selectalldata of DataAccessLayer class is used.

[HttpGet]
public ActionResult ShowAllCustomerDetails()
        {
            Customer objCustomer = new Customer();
            DataAccessLayer objDB = new DataAccessLayer(); //calling class DBdata
            objCustomer.ShowallCustomer = objDB.Selectalldata();
            return View(objCustomer);
        }

In Details action result method, the SelectDatabyID of DataAccessLayer class is used.

The passed parameter value with the related data will be shown in the corresponding view page.

[HttpGet]
        public ActionResult Details(string ID)
        {
            Customer objCustomer = new Customer();
            DataAccessLayer objDB = new DataAccessLayer(); //calling class DBdata
            return View(objDB.SelectDatabyID(ID));
        }

The edit action result method has two attributes httpget and httppost.

For httpget attribute In “Edit” action result method, the “SelectDatabyID” of DataAccessLayer class is used to step update data by using this particular data.

The passed parameter value with the related data will be shown in the corresponding view page.      

[HttpGet]
public ActionResult Edit(string ID)
        {
            Customer objCustomer = new Customer();
            DataAccessLayer objDB = new DataAccessLayer(); //calling class DBdata
            return View(objDB.SelectDatabyID(ID));
        }

For httppost attribute, the edit controller action method takes customer model class object and UpdateData of DataAccessLayer class is  used.

[HttpPost]
        public ActionResult Edit(Customer objCustomer)
        {
            objCustomer.Birthdate = Convert.ToDateTime(objCustomer.Birthdate);
            if (ModelState.IsValid) //checking model is valid or not
            {
                DataAccessLayer objDB = new DataAccessLayer(); //calling class DBdata
                string result = objDB.UpdateData(objCustomer);
                //ViewData["result"] = result;
                TempData["result2"] = result;
                ModelState.Clear(); //clearing model
                //return View();
                return RedirectToAction("ShowAllCustomerDetails");
            }
            else
            {
                ModelState.AddModelError("", "Error in saving data");
                return View();
            }
        }

In Delete action result method, the DeleteData of DataAccessLayer class is used.

The passed parameter value with the related data will show to perform delete operation in the corresponding view page.

[HttpGet]
       public ActionResult Delete(String ID)
       {
               DataAccessLayer objDB = new DataAccessLayer();
               int result = objDB.DeleteData(ID);
              TempData["result3"] = result;
               ModelState.Clear(); //clearing model
               //return View();
               return RedirectToAction("ShowAllCustomerDetails");
        }

Step7

Now, create view cshtml file called ShowAllCustomerDetails.cshtml, InsertCustomer.cshtml.

,” Edit.cshtml”,” Details.cshtml”,” Delete.cshtml”.

Code ref. of InsertCustomer.cshtml

@model MVC4crud.Models.Customer
@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>InsertCustomer</title>
</head>
<body>
    <script src="~/Scripts/jquery-1.7.1.min.js"></script>
    <script src="~/Scripts/jquery.validate.min.js"></script>
    <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
    @using (Html.BeginForm())
    {

        @Html.ValidationSummary(true)
        <fieldset>
            <legend style="font-family:Arial Black;color:Green">Customer</legend>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Name)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Name)
                @Html.ValidationMessageFor(model => model.Name)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Address)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Address)
                @Html.ValidationMessageFor(model => model.Address)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Mobileno)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Mobileno)
                @Html.ValidationMessageFor(model => model.Mobileno)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Birthdate)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Birthdate)
                @Html.ValidationMessageFor(model => model.Birthdate)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.EmailID)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.EmailID)
                @Html.ValidationMessageFor(model => model.EmailID)
            </div>
            <p>
                <input type="submit" value="Insert" style="color:Navy;font-family:Arial; font-size:large" />
                <input type="reset" value="Reset" style="color:Navy;font-family:Arial; font-size:large" />
            </p>
        </fieldset>
    }
    <div style="color:Blue;font-family:Arial">
        @Html.ActionLink("Back to Customer Records", "ShowAllCustomerDetails")
    </div>
</body>
</html>

Code description

In this view page, I have added customer class reference or namespace.

@model MVC4crud.Models.Customer

To make validation summary active, use this code.

@Html.ValidationSummary(true)

Now, I have added some code to make textbox and label control according to customer model class entities. 

<div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Name)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Name)
                @Html.ValidationMessageFor(model => model.Name)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Address)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Address)
                @Html.ValidationMessageFor(model => model.Address)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Mobileno)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Mobileno)
                @Html.ValidationMessageFor(model => model.Mobileno)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Birthdate)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Birthdate)
                @Html.ValidationMessageFor(model => model.Birthdate)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.EmailID)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.EmailID)
                @Html.ValidationMessageFor(model => model.EmailID)
            </div>

To add label control, the code is given below.

@Html.LabelFor()

To add textbox control, the code is given below.

@Html.EditorFor()

To add validation messages, as defined in customer model class and customized model validation class, the code is given below.

@Html.ValidationMessageFor() 

Here, two types of buttons are used to save the data.

<input type="submit" value="Insert" style="color:Navy;font-family:Arial; font-size:large" />

Here, two types of buttons are used; where one is to reset the data.

<input type="reset" value="Reset" style="color:Navy;font-family:Arial; font-size:large" />

After saving data, the details view page will come with all the saved data.

@Html.ActionLink("Back to Customer Records", "ShowAllCustomerDetails")

Here, ShowAllCustomerDetails is the name of the controller action method as well as view name.

Here, I have added one hyperlink to redirect to other page. 

@Html.ActionLink()

Code ref. of Edit.cshtml 

@model MVC4crud.Models.Customer
@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />

    <title>Edit</title>
</head>
<body>
    <script src="~/Scripts/jquery-1.7.1.min.js"></script>
    <script src="~/Scripts/jquery.validate.min.js"></script>
    <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
    @using (Html.BeginForm())
    {
        @Html.ValidationSummary(true)
        <fieldset>
            <legend style="font-family:Arial Black;color:Green">Customer</legend>
            @Html.HiddenFor(model => model.CustomerID)
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Name)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Name)
                @Html.ValidationMessageFor(model => model.Name)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Address)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Address)
                @Html.ValidationMessageFor(model => model.Address)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Mobileno)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Mobileno)
                @Html.ValidationMessageFor(model => model.Mobileno)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.Birthdate)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.Birthdate)
                @Html.ValidationMessageFor(model => model.Birthdate)
            </div>
            <div class="editor-label" style="font-family:Arial Black">
                @Html.LabelFor(model => model.EmailID)
            </div>
            <div class="editor-field" style="color:Red;font-family:Arial">
                @Html.EditorFor(model => model.EmailID)
                @Html.ValidationMessageFor(model => model.EmailID)
            </div>
            <p>
                <input type="submit" value="Save" style="color:Navy;font-family:Arial; font-size:large" />
            </p>
        </fieldset>
    }
    <div style="color:Blue;font-family:Arial">
        @Html.ActionLink("Back to Customer Records", "ShowAllCustomerDetails")
    </div>
</body>
</html>

Code description

In this view pag,e the editor and label controls will bound existing data to update it. Hence, the data as in insert view page will load in corresponding HTML helper control and the user will update, as per requirement.

After update process completes, the view details page will come.

@Html.ActionLink("Back to Customer Records", "ShowAllCustomerDetails")

Here, I used multiple submit button for different actions. 

<input type="submit" value="Save" style="color:Navy;font-family:Arial; font-size:large"/>

Here, I have added hidden field control associate with customerid to perform an edit operation. 

@Html.HiddenFor(model => model.CustomerID)

Code ref. of  Details.cshtml 

@model MVC4crud.Models.Customer
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Details</title>
</head>
<body>
    <fieldset>
        <legend style="font-family:Arial Black;color:Green">Customer Report</legend>

        <div class="display-label" style="font-family:Arial Black">
            @Html.DisplayNameFor(model => model.Name)
        </div>
        <div class="display-field" style="color:Blue">
            @Html.DisplayFor(model => model.Name)
        </div>

        <div class="display-label" style="font-family:Arial Black">
            @Html.DisplayNameFor(model => model.Address)
        </div>
        <div class="display-field" style="color:Blue">
            @Html.DisplayFor(model => model.Address)
        </div>

        <div class="display-label" style="font-family:Arial Black">
            @Html.DisplayNameFor(model => model.Mobileno)
        </div>
        <div class="display-field" style="color:Blue">
            @Html.DisplayFor(model => model.Mobileno)
        </div>

        <div class="display-label" style="font-family:Arial Black">
            @Html.DisplayNameFor(model => model.Birthdate)
        </div>
        <div class="display-field" style="color:Blue">
            @Html.DisplayFor(model => model.Birthdate)
        </div>

        <div class="display-label" style="font-family:Arial Black">
            @Html.DisplayNameFor(model => model.EmailID)
        </div>
        <div class="display-field" style="color:Blue">
            @Html.DisplayFor(model => model.EmailID)
        </div>
    </fieldset>
    <p style="color:Blue;font-family:Arial">
        @Html.ActionLink("Back to Customer Records", "ShowAllCustomerDetails")
    </p>
</body>
</html>

Code description

In this view page, the data inserted and updated data will show for reporting purpose.

Here, no submit button is required to take action in page event.

Afterwards, the page will redirect to view details page.

@Html.ActionLink("Back to Customer Records", "ShowAllCustomerDetails")

Code ref. of  Delete.cshtml 

@model MVC4crud.Models.Customer
@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Delete</title>
</head>
<body>
    <h3 style="color:Red">Do You Want To Remove This Record?</h3>
    @using (Html.BeginForm())
    {
        <fieldset>
            <legend style="font-family:Arial Black;color:Green">Customer</legend>
            @Html.HiddenFor(model => model.CustomerID)
            <div class="display-label" style="font-family:Arial Black">
                @Html.DisplayNameFor(model => model.Name)
            </div>
            <div class="display-field" style="color:Blue">
                @Html.DisplayFor(model => model.Name)
            </div>
            <div class="display-label" style="font-family:Arial Black">
                @Html.DisplayNameFor(model => model.Address)
            </div>
            <div class="display-field" style="color:Blue">
                @Html.DisplayFor(model => model.Address)
            </div>
            <div class="display-label" style="font-family:Arial Black">
                @Html.DisplayNameFor(model => model.Mobileno)
            </div>
            <div class="display-field" style="color:Blue">
                @Html.DisplayFor(model => model.Mobileno)
            </div>
            <div class="display-label" style="font-family:Arial Black">
                @Html.DisplayNameFor(model => model.Birthdate)
            </div>
            <div class="display-field" style="color:Blue">
                @Html.DisplayFor(model => model.Birthdate)
            </div>

            <div class="display-label" style="font-family:Arial Black">
                @Html.DisplayNameFor(model => model.EmailID)
            </div>
            <div class="display-field" style="color:Blue">
                @Html.DisplayFor(model => model.EmailID)
            </div>
            <p>
                <input id="Submit1" onclick="return confirm('Are You Sure To Remove ?')"
                       type="submit" value="Remove" style="color:Navy;font-family:Arial; font-size:large" />
            </p>
        </fieldset>
    }
    <div style="color:Blue;font-family:Arial">
        @Html.ActionLink("Back to Customer Records", "ShowAllCustomerDetails")
    </div>
</body>
</html>

Code description 

In this view page, the delete confirmation text message is added in header.

<h3 style="color:Red">Do You Want To Remove This Record?</h3>

In button event, JavaScript message was added  and the user will decide whether it will be deleted or not.

<input id="Submit1" onclick="return confirm('Are You Sure To Remove ?')"type="submit" value="Remove" style="color:Navy;font-family:Arial; font-size:large" />

The data is loaded and is based on the selection of customer id and data associated with the entities defined in customer model class, which will be deleted.

Here, I have added hidden field control associate with customerid to perform delete operation.

@Html.HiddenFor(model => model.CustomerID)

After this the page will redirect to view details page.

@Html.ActionLink("Back to Customer Records", "ShowAllCustomerDetails")

Code ref. of  ShowAllCustomerDetails.cshtml 

@model MVC4crud.Models.Customer
@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ShowAllCustomerDetails</title>
</head>
<body>
    <script src="~/Scripts/jquery-1.7.1.min.js"></script>
    <script src="~/Scripts/jquery.validate.min.js"></script>
    <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
    <div style="font-family:Arial Black;">
            <h2 style="background-color: Yellow;color: Blue; text-align: center; font-style: oblique">SATYAPRAKASH's MVC Customer CRUD Operation</h2>
        @*<h2 style="text-align:center"></h2>*@
        <p> @*<p style="text-align:center">*@
    @Html.ActionLink(linkText: "New Customer", actionName: "InsertCustomer", controllerName: "Customer")
        </p>
        <br />
        <br />
        <table border="1" align="center">
            <tr>
                <th style="background-color:Yellow;color:blue">
                    @Html.DisplayNameFor(model => model.Name)

                </th>
                <th style="background-color: Yellow;color: blue">
                    @Html.DisplayNameFor(model => model.Address)
                </th>
                <th style="background-color: Yellow;color: blue">
                    @Html.DisplayNameFor(model => model.Mobileno)
                </th>
                <th style="background-color: Yellow;color: blue">
                    @Html.DisplayNameFor(model => model.Birthdate)
                </th>
                <th style="background-color: Yellow;color: blue">
                    @Html.DisplayNameFor(model => model.EmailID)
                </th>
                <th style="background-color: Yellow;color: blue">
                    Operation
                </th>
            </tr>

            @foreach (var item in Model.ShowallCustomer)
            {
                <tr>
                    <td>
                        @Html.DisplayFor(modelItem => item.Name)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.Address)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.Mobileno)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.Birthdate)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.EmailID)
                    </td>
                    <td>

                        @Html.ActionLink("Modify", "Edit", new { id = item.CustomerID }) |

                        @Html.ActionLink("Detail", "Details", new { id = item.CustomerID }) |
                        @*@Html.ActionLink("Remove", "Delete", new { id = item.CustomerID })*@
                        @Html.ActionLink("Remove", "Delete", new { id = item.CustomerID},
                        new { onclick = "return confirm('Are sure wants to delete?');" })
                    </td>
                </tr>
            }
        </table>
        @if (TempData["result1"] != null)
        {
            <script type="text/javascript">
                alert("Record Is Inserted Successfully");
            </script>
        }
        @if (TempData["result2"] != null)
        {
            <script type="text/javascript">
                alert("Record Is Updated Successfully");
            </script>
        }
        @if (TempData["result3"] != null)
        {
        <script type="text/javascript">
             alert("Record Is Deleted Successfully");
        </script>
        }
        <br/>
        <br/>
        <footer>
            <p style="background-color: Yellow;text-align:center ; color:blue">© @DateTime.Now.ToLocalTime()</p> @*Add Date Time*@
        </footer>
    </div>
</body> </html>

Code description

In this page, all the data will be visible along with EDIT/ DELETE/ DETAILS link to perform Crud operation. 

@Html.ActionLink("Modify", "Edit", new { id = item.CustomerID }) |

                        @Html.ActionLink("Detail", "Details", new { id = item.CustomerID }) |
                        @*@Html.ActionLink("Remove", "Delete", new { id = item.CustomerID })*@
                        @Html.ActionLink("Remove", "Delete", new { id = item.CustomerID},
                        new { onclick = "return confirm('Are sure wants to delete?');" })

The data will be shown, using “@Html.DisplayFor” html helper control in looping.          

@foreach (var item in Model.ShowallCustomer)

                   <td>
                       @Html.DisplayFor(modelItem => item.Name)
                   </td>
                   <td>
                       @Html.DisplayFor(modelItem => item.Address)
                   </td>
                   <td>
                       @Html.DisplayFor(modelItem => item.Mobileno)
                   </td>
                   <td>
                       @Html.DisplayFor(modelItem => item.Birthdate)
                   </td>
                   <td>
                       @Html.DisplayFor(modelItem => item.EmailID)
                   </td>


                 The header of data will be shown as mentioned in code.
               <th style="background-color: Yellow;color: blue">
                   @Html.DisplayNameFor(model => model.Name)

               </th>
               <th style="background-color: Yellow;color: blue">
                   @Html.DisplayNameFor(model => model.Address)
               </th>
               <th style="background-color: Yellow;color: blue">
                   @Html.DisplayNameFor(model => model.Mobileno)
               </th>
               <th style="background-color: Yellow;color: blue">
                   @Html.DisplayNameFor(model => model.Birthdate)
               </th>
               <th style="background-color: Yellow;color: blue">
                   @Html.DisplayNameFor(model => model.EmailID)
               </th>
               <th style="background-color: Yellow;color: blue">
                   Operation
               </th>

Here, I added the namespace of customer model class.

@model MVC4crud.Models.Customer

The title of the page will be written here.

<title>ShowAllCustomerDetails</title>

To go to new customer insertion view page, the code is given below.

@Html.ActionLink(linkText: "New Customer", actionName: "InsertCustomer", controllerName: "Customer")

Here ,

Link name : "New Customer",

The method defined in customer control class file as well as data access layer class file is InsertCustomer,

Controller Name: "Customer" 

Here, I am using tempdata method mechanism to transfer the data from one page to other.

To show insertion successful message, the code is given below. 

@if (TempData["result1"] != null)
        {
            <script type="text/javascript">
                alert("Record Is Inserted Successfully");
            </script>

To show update successful message, the code is given below. 

@if (TempData["result2"] != null)
{
    <script type="text/javascript">
        alert("Record Is Updated Successfully");
    </script>
}

To show delete successful message, the code is given below. 

@if (TempData["result3"] != null)
{
<script type="text/javascript">
     alert("Record Is Deleted Successfully");
</script>

To get current data time for better visualization to the client, the code is given below. 

<footer>
            <p style="background-color: Yellow;text-align:center ; color:blue">© @DateTime.Now.ToLocalTime()</p> @*Add Date Time*@
</footer>

Step 8

Add connection string in Web.config file.

Code Ref

<connectionStrings>
    <add name="mycon" providerName="System.Data.SqlClient" connectionString="Your Connection string put here" />
  </connectionStrings>

Code description

Here, “mycon” is the connection string name to be mentioned in Data Access Layer class file to make connection to the database as well as make CRUD operation.

add name="mycon"

 

Now, put your correct connection string .

connectionString = “”

 

Step 9

Set start page when MVC page loads first time.

Code Ref

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

Code description

Here, I have mentioned set start page.

defaults: new { controller = "Customer", action = "ShowAllCustomerDetails", id = UrlParameter.Optional }

Here, Controller name is Customer.

Now, the view name/ controller action method is ShowAllCustomerDetails. 

Step 10

You can customize the style of your own view design by using Site.css.

Here, you can add color, font size, font style and margin etc.

Step 11

Add and check reference Dll/ Assembly files to check version and other information.

In the References folder, you can check all DLL file information by right click and going to properties.

OUTPUT

The set start page URL is given below.

http://localhost:62159/Customer/ShowAllCustomerDetails 

Load data details with CRUD functionalities

http://localhost:62159/Customer/ShowAllCustomerDetails

Insert page

http://localhost:62159/Customer/InsertCustomer

Update  page

http://localhost:62159/Customer/Edit/93

Details page for report requirement

http://localhost:62159/Customer/Details/93

Delete page

For delete confirmation, it is, as shown below.

Total Operations In One Flow

Show all the data on the page is given below.

Insert some data

 

Update some data

 

 

 

Details of some data

Delete of some data

Now, the deleted data is not showing that it is empty now.

To insert new records, click the New Customer link.

Check date and time at footer of the show details view page.

Like above mentioned methods, you can implement CRU operation, using MVC in your real time scenario.

Summary

1. What is CRUD.
2. How to set code to implement CRUD in MVC.
3. Backend set up is same as real time scenario.
4. Output steps.