ASP.NET  

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

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

MVC Application

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; }
    }
}
C#

Code description

Here, I have created some attributes to check the 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 the 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.")]
C#

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")]
C#

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

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

using System.ComponentModel.DataAnnotations;
C#

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

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

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

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

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")]
C#

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

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

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

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

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;
            }
        }
    }
}
C#

Code description

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

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

DateTime _birthJoin = Convert.ToDateTime(value);
C#

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.");
}
C#

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

public sealed class ValidBirthDate : ValidationAttribute
C#

What is the 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
C#

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 the ValidationResult override method. It is used to describe a context in which a 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;
}
C#

In the custom validation class, I used Student. cs for the 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")]
C#

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

Demo

Step 3. Create a table to add stored procedures to perform CRUD operations in MVC.

Table SQL Ref

CREATE TABLE [dbo].[Customer]
(
    [CustomerID] [int] NOT NULL Primary key identity(1,1),
      NULL,
      NULL,
      NULL,
    [Birthdate] [datetime] NULL,
      NULL
)
SQL

Table SQL Description

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

Here “[CustomerID]” is the primary key and an 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

    IF (@Query = 5)
    BEGIN
        SELECT * FROM Customer
        WHERE Customer.CustomerID = @CustomerID;
    END
END;
SQL

Stored procedure Script description

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

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

CREATE PROCEDURE F
(
    @Query INT = 1
)
BEGIN
    INSERT INTO Customer
    (
        NAME,
        Address,
        Mobileno,
        Birthdate,
        EmailID
    )
    VALUES
    (
        @Name,
        @Address,
        @Mobileno,
        @Birthdate,
        @EmailID
    )
END
SQL

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.

Parameter

Step 5. Create a class file called DataAccessLayer.cs inside a 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("@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();
            }
        }
    }
}
C#

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;
        //ID", 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();
    }
}
C#

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

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

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

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

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

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

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

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

I have added one for loop to perform a selection of all the data loop-wise by using the 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());
}
C#

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

I have added the code given below.

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

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

public Customer SelectDatabyID(string CustomerID)
C#

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

finally
{
    con.Close();
}
C#

Connections

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);
                TempData["result1"] = result;
                ModelState.Clear(); // clearing model
                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
            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);
                TempData["result2"] = result;
                ModelState.Clear(); // clearing model
                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 RedirectToAction("ShowAllCustomerDetails");
        }
    }
}
C#

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();
    }
}
C#

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

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

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

if (ModelState.IsValid)
{
    // clearing model
    ModelState.Clear();
}
C#

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

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

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");
C#

In the ShowAllCustomerDetails action result method, the Selectalldata of the 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);
}
C#

In the Details action result method, the SelectDatabyID of the 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));
}
C#

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

For the HTTP get attribute In the “Edit” action result method, the “SelectDatabyID” of the 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));
}
C#

For the HTTP post attribute, the edit controller action method takes the customer model class object, and the UpdateData of the 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);
        TempData["result2"] = result;
        ModelState.Clear(); // clearing model
        return RedirectToAction("ShowAllCustomerDetails");
    }
    else
    {
        ModelState.AddModelError("", "Error in saving data");
        return View();
    }
}
C#

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

The passed parameter value with the related data will be shown to perform a 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");
}
C#

Controller

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

"Edit.cshtml",
"Details.cshtml",
"Delete.cshtml"
Markup

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>
Markup

Code description

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

@model MVC4crud.Models.Customer
C#

To make the validation summary active, use this code.

@Html.ValidationSummary(true)
Markup

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>
Markup

To add label control, the code is given below.

@Html.LabelFor()
Markup

To add textbox control, the code is given below.

@Html.EditorFor()
Markup

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

@Html.ValidationMessageFor()
Markup

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

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" />
Markup

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

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

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

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

@Html.ActionLink()
Markup

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>
Markup

Code description

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

After the update process is completed, the view details page will come.

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

Here, I used multiple submit buttons for different actions.

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

Here, I have added a hidden field control associated with customer id to perform an edit operation.

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

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>
Markup

Code description

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

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

Afterwards, the page will redirect to the view details page.

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

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>
Markup

Code description

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

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

In the button event, a 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" />
Markup

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

Here, I have added a hidden field control associated with the customer id to perform the delete operation.

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

After this, the page will redirect to the view details page.

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

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>
Markup

Code description On this page, all the data will be visible along with the EDIT/ DELETE/ DETAILS link to perform the 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 },
new { onclick = "return confirm('Are sure wants to delete?');" })
Markup

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

@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>
    </tr>
}

<!-- The header of data will be shown as mentioned in code. -->
<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>
Markup

Here, I added the namespace of the customer model class.

@model MVC4crud.Models.Customer
C#

The title of the page will be written here.

<title>ShowAllCustomerDetails</title>
Markup

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

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

Here,

Link name: "New Customer",

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

Controller Name: "Customer"

Here, I am using the temp data method mechanism to transfer the data from one page to another.

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

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

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

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

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

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

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>
Markup

Views

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>
XML

Code description

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

<add name="mycon">
Markup

Now, put your correct connection string.

connectionString = ""
Python

String

Step 9. Set the start page when the MVC page loads the first time.

Code Ref

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

Code description

Here, I have mentioned the set start page.

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

Here, the Controller's name is Customer.

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

ASP.NET

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.

Style

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

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

DLL file

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

Details

Insert page

http://localhost:62159/Customer/InsertCustomer

Insert page

Update page

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

Update  page

Details page for report requirement

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

Report requirement

Delete page

For delete confirmation, it is, as shown below.

Delete page

Total Operations in one flow

Show all the data on the page given below.

Total Operations

Insert some data

Data

OK

MVC

Update some data

Update some data

Localhost

Details of some data

Report

Delete some data

Cancel

Deleted

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

Empty

To insert new records, click the New Customer link.

Link

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

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. The backend setup is the same as real-time scenario.
  4. Output steps.


  • (37) Comments
View All Comments
User Image
 
Message
Membership not found