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

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

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, and RegularExpression attributes.

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

using System.ComponentModel.DataAnnotations;

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

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

In the 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 the @ symbol.

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

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

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 the Models folder named “CustomValidationAttributeDemo.cs”.

Here, I used one date time variable to access the 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 the ValidationAttribute class.

public sealed class ValidBirthDate : ValidationAttribute

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

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

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

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

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;

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

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

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 the insert operation. Here, I have added the stored procedure name.

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

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

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

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

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

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

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

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 a customer Id parameter in a function of the customer model class.

public Customer SelectDatabyID(string CustomerID)  

Now, I have closed the connection in every function 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);
                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");
        }                   
    }
}

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)
{
    // clearing model
    ModelState.Clear();
}

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

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 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);
}

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));
}