MVC CRUD Operation

This blog is about basic crud operations using MVC 4 in ASP.NET.

 

Step 1:  Firstly, go to Database and create a table with the following fields:

 

tblMVCCrud is Table Name Fields 

Id int Primary Key Not Null AutoIncremented Column
Name varchar(50) Null
LName varchar(50) Null
Gender varchar(50) Null
IsActive bit Null

Write the following Stored Procedure, 

Create PROCEDURE [dbo].[MVC_CRUD]
(
@Id int = 0,
@Para varchar(50)='',
@Name varchar(50)='',
@LName varchar(50)='',
@Gender varchar(50)='',
@IsActive bit = 1
)
AS
BEGIN
If @Para = 'ADD'
Begin
Insert into tblMVCCrud (Name,LName,Gender,IsActive) values (@Name,@LName,@Gender,1)
END
Else If @Para = 'UPDATE'
Begin
Update tblMVCCrud SET Name = @Name,LName = @LName,Gender = @Gender,IsActive = 1 where Id = @Id
END
Else If @Para = 'DELETE'
Begin
Update tblMVCCrud SET IsActive = 0 where Id = @Id
END
Else If @Para = 'Select_By_Id'
Begin
Select Id,Name,LName,Gender from tblMVCCrud where Id = @Id
END
Else If @Para = 'Select'
Begin
Select Id,Name,LName,Gender from tblMVCCrud where IsActive = 1
END
END

Now, come to the coding part,

MVC is an Architectural Pattern, that separates the application into three main components that is Model, View and Controller.

 

Advantage of MVC Over ASP.NET is Separation of Concern, it is Loosely Coupled. View-View Component is used for UI Logic of the application, it will include all the UI components like textboxes, Dropdown, RadioButton, etc.

 

Model-Model Component contains all database related logic. It will retrieve information from the database and update it back to the Database.

 

Controller

Controllers act as an interface between Model and View components to process all the business logic and incoming requests, manipulate data using the Model component and interact with the Views to render the final output.

 

Ways to Pass Data from Controller to View

1. ViewData
2. ViewBag
3. TempData 

Open Visual Studio 2013, select File, New Project, then ASP.NET MVC 4 Web Application.

 

Select Internet Application, Right Click on the Application, Add, create a new folder and name it DAL,

 

Right click on DAL Folder, Add, New Item, then add a class and name it DataAccessLayer and write the following code in it, 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using MvcApplication1.Models;
namespace MvcApplication1.DAL
{
    public class DataAccessLayer
    {
        int result = 0;
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["testCrud"].ToString());
        SqlCommand cmd = null;
        DataTable dt = null;
        SqlDataAdapter da = null;
        public int Registration(Class1 obj)
        {
            try
            {
                using(cmd = new SqlCommand("MVC_CRUD", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "ADD");
                    cmd.Parameters.AddWithValue("@Name", obj.Name);
                    cmd.Parameters.AddWithValue("@LName", obj.LName);
                    cmd.Parameters.AddWithValue("@Gender", obj.Gender);
                    if (con.State.Equals(ConnectionState.Closed))
                        con.Open();
                    result = cmd.ExecuteNonQuery();
                    con.Close();
                    return result;
                }
            } catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable GetData()
        {
            try
            {
                using(cmd = new SqlCommand("MVC_CRUD", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "Select");
                    if (con.State.Equals(ConnectionState.Closed))
                        con.Open();
                    da = new SqlDataAdapter(cmd);
                    dt = new DataTable();
                    da.Fill(dt);
                    con.Close();
                    return dt;
                }
            } catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable SelectById(string Id)
        {
            try
            {
                using(cmd = new SqlCommand("MVC_CRUD", con)) {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "Select_By_Id");
                    cmd.Parameters.AddWithValue("@Id", Convert.ToInt32(Id));
                    if (con.State.Equals(ConnectionState.Closed))
                        con.Open();
                    da = new SqlDataAdapter(cmd);
                    dt = new DataTable();
                    da.Fill(dt);
                    con.Close();
                    return dt;
                }
            } catch (Exception ex)
            {
                throw ex;
            }
        }
        public int UpdateRecords(Class1 obj)
        {
            try
            {
                using(cmd = new SqlCommand("MVC_CRUD", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "UPDATE");
                    cmd.Parameters.AddWithValue("@Id", obj.Id);
                    cmd.Parameters.AddWithValue("@Name", obj.Name);
                    cmd.Parameters.AddWithValue("@LName", obj.LName);
                    cmd.Parameters.AddWithValue("@Gender", obj.Gender);
                    if (con.State.Equals(ConnectionState.Closed))
                        con.Open();
                    result = cmd.ExecuteNonQuery();
                    con.Close();
                    return result;
                }
            } catch (Exception ex)
            {
                throw ex;
            }
        }
        public int Delete(Class1 obj)
        {
            try
            {
                using(cmd = new SqlCommand("MVC_CRUD", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Para", "DELETE");
                    cmd.Parameters.AddWithValue("@Id", obj.Id);
                    if (con.State.Equals(ConnectionState.Closed))
                        con.Open();
                    result = cmd.ExecuteNonQuery();
                    con.Close();
                    return result;
                }
            } catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

Build the Class File

 

Right click on Models Folder and Add New item that is class and name it Class1.

 

Add the following code in it, 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Linq;
using System.Web;
namespace MvcApplication1.Models
{
    public class Class1
    {
        public int Id
        {
            get;
            set;
        }
        [Required(ErrorMessage = "Please Enter Name")]
        [DisplayName("Enter Name")]
        [StringLength(50)]
        public string Name
        {
            get;
            set;
        }
        [Required(ErrorMessage = "Please Enter Last Name")]
        [DisplayName("Enter Last Name")]
        [StringLength(50)]
        public string LName
        {
            get;
            set;
        }
        [Required(ErrorMessage = "Please Select Gender")]
        [DisplayName("Select Gender")]
        [StringLength(25)]
        public string Gender
        {
            get;
            set;
        }
        public DataTable getData
        {
            get;
            set;
        }
    }
}

The getData has return type DataTable and is use to store all Data Values fetch from Database.

 

Right click on Controller and Add a Controller and name it Registration, just attach suffix before Controller. So it will be RegistrationController and add the following code in it, 

using MvcApplication1.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace MvcApplication1.Controllers
{
    public class RegistrationController: Controller
    {
        //
        // GET: /Registration/
        DAL.DataAccessLayer objDAL = new DAL.DataAccessLayer();
        DataTable dt = null;
        int result = 0;
        public ActionResult Registration()
            {
                return RedirectToAction("GetAllData");
            }
            [HttpPost]
        public ActionResult Registration(Class1 obj)
        {
            if (ModelState.IsValid)
            {
                result = objDAL.Registration(obj);
                TempData["result"] = result;
                ModelState.Clear();
                return RedirectToAction("GetAllData");
            } else {
                ViewData["result"] = "Registration Not Successful";
                return RedirectToAction("GetAllData");
            }
        }
        public ActionResult GetAllData()
        {
            Class1 objModel = new Class1();
            objModel.getData = objDAL.GetData();
            return View("Registration", objModel);
        }
        public ActionResult EditData(string Id)
        {
            Class1 objModel = new Class1();
            dt = new DataTable();
            dt = objDAL.SelectById(Id);
            if (dt.Rows.Count > 0)
            {
                objModel.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
                objModel.Name = dt.Rows[0]["Name"].ToString();
                objModel.LName = dt.Rows[0]["LName"].ToString();
                objModel.Gender = dt.Rows[0]["Gender"].ToString();
            }
            return View(objModel);
        }
        public ActionResult DeleteData(string Id)
        {
                Class1 objModel = new Class1();
                dt = new DataTable();
                dt = objDAL.SelectById(Id);
                if (dt.Rows.Count > 0)
                {
                    objModel.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
                    objModel.Name = dt.Rows[0]["Name"].ToString();
                    objModel.LName = dt.Rows[0]["LName"].ToString();
                    objModel.Gender = dt.Rows[0]["Gender"].ToString();
                }
                return View(objModel);
            }
            [HttpPost]
        public ActionResult Update(Class1 obj)
        {
                if (ModelState.IsValid)
                {
                    result = objDAL.UpdateRecords(obj);
                    TempData["Update"] = result;
                    ModelState.Clear();
                    return RedirectToAction("GetAllData");
                } else {
                    TempData["result"] = "Updation Not Successful";
                    return RedirectToAction("GetAllData");
                }
            }
            [HttpPost]
        public ActionResult Delete(Class1 obj)
        {
            if (ModelState.IsValid)
            {
                result = objDAL.Delete(obj);
                TempData["Delete"] = result;
                ModelState.Clear();
                return RedirectToAction("GetAllData");
            } else
            {
                TempData["Delete"] = "Updation Not Successful";
                return RedirectToAction("GetAllData");
            }
        }
    }
}

Now right click on Action Registration and add View, Name it Registration. It should be strongly typed view, Model class should be the name of Model class which has been created above. Uncheck the CheckBox, use a layout Master Page while adding View.

 

Add the following code on View 

@model MvcApplication1.Models.Class1
@ {
    Layout = null;
}
@using(Html.BeginForm("Registration", "Registration")) { < table >
        < tr >
        < td >
        @Html.LabelFor(model => model.Name) < /td> < td >
        @Html.TextBoxFor(model => model.Name) < /td> < td >
        @Html.ValidationMessageFor(model => model.Name) < /td> < /tr> < tr >
        < td >
        @Html.LabelFor(model => model.LName) < /td> < td >
        @Html.TextBoxFor(model => model.LName) < /td> < td >
        @Html.ValidationMessageFor(model => model.LName) < /td> < /tr> < tr >
        < td >
        @Html.LabelFor(model => model.Gender) < /td> < td >
        @Html.RadioButtonFor(model => model.Gender, "Male", false) Male
    @Html.RadioButtonFor(model => model.Gender, "Female", false) Female < /td> < td >
        @Html.ValidationMessageFor(model => model.Gender) < /td> < /tr> < tr >
        < td >
        < input type = "submit"
    id = "submit1"
    value = "Submit" / >
        < /td> < /tr> < /table>
}
@ {
    if (Convert.ToInt32(ViewData["result"]) == 1) { < script type = "text/javascript"
        lang = "ja" >
            alert("Registration Successful!!!") < /script>
        ViewData["result"] = 0;
    } else { < script type = "text/javascript"
        lang = "ja" >
            alert(ViewData["result"]); < /script>
    }
}
@ {
    if (Convert.ToInt32(TempData["Update"]) == 1) { < script type = "text/javascript"
        lang = "ja" >
            alert("Updation Successful!!!") < /script>
        TempData["Update"] = 0;
    } else { < script type = "text/javascript"
        lang = "ja" >
            alert(TempData["Update"]); < /script>
    }
}
@ {
    if (Convert.ToInt32(TempData["Delete"]) == 1) { < script type = "text/javascript"
        lang = "ja" >
            alert("Deletion Successful!!!") < /script>
        TempData["Delete"] = 0;
    } else { < script type = "text/javascript"
        lang = "ja" >
            alert(TempData["Delete"]); < /script>
    }
}
@ {
    if (Model.getData.Rows.Count > 0) { < table >
            < th > Name < /th> < th > LastName < /th> < th > Gender < /th>
        @for(int i = 0; i < Model.getData.Rows.Count; i++) {
            var Name = Model.getData.Rows[i]["Name"].ToString();
            var LastName = Model.getData.Rows[i]["LName"].ToString();
            var Gender = Model.getData.Rows[i]["Gender"].ToString(); < tr >
                < td >
                @Name < /td> < td >
                @LastName < /td> < td >
                @Gender < /td> < td >
                @Html.ActionLink("Edit", "EditData", new {
                    id = Model.getData.Rows[i]["Id"].ToString()
                }) < /td> < td >
                @Html.ActionLink("Delete", "DeleteData", new {
                    id = Model.getData.Rows[i]["Id"].ToString()
                }) < /td> < /tr>
        } < /table>
    }
}

Similarly, right click on EditData Method and add a View Name EditData. Add the following code in it, 

@model MvcApplication1.Models.Class1
@ {
    Layout = null;
}
@using(Html.BeginForm("Update", "Registration")) { < table >
        < tr >
        < td >
        @Html.HiddenFor(model => model.Id) < /td> < /tr> < tr >
        < td >
        @Html.TextBoxFor(model => model.Name) < /td> < /tr> < tr >
        < td >
        @Html.TextBoxFor(model => model.LName) < /td> < /tr> < tr >
        < td >
        @Html.RadioButtonFor(model => model.Gender, "Male", false) Male
    @Html.RadioButtonFor(model => model.Gender, "Female", false) Female < /td> < /tr> < tr >
        < td >
        < input type = "submit"
    value = "Update"
    id = "update1" / >
        < /td> < /tr> < /table>
}

Similarly, right click on DeleteData Method and add a View Name DeleteData. Add the following code in it, 

@model MvcApplication1.Models.Class1
@ {
    Layout = null;
}
@using(Html.BeginForm("Delete", "Registration")) { < table >
        < tr >
        < td >
        @Html.HiddenFor(model => model.Id) < /td> < /tr> < tr >
        < td >
        @Html.TextBoxFor(model => model.Name) < /td> < /tr> < tr >
        < td >
        @Html.TextBoxFor(model => model.LName) < /td> < /tr> < tr >
        < td >
        @Html.RadioButtonFor(model => model.Gender, "Male", false) Male
    @Html.RadioButtonFor(model => model.Gender, "Female", false) Female < /td> < /tr> < tr >
        < td >
        < input type = "submit"
    value = "Delete"
    id = "update1" / >
        < /td> < /tr> < /table>
}

Now come to the Controller part in ActionResult Registration, I have used ReturnRedirectToAction and Action Name is GetAllData, in GetAllData it fetches data from the database and binds it to the Table on RegistrationView. In GetAllData I have passed the ViewName and Model object in return View,

 

Now I have used TempData to store the result, can check it in Delete, Registration and Update Action.

The reason is TempData and the Value does not becomes Null when redirection occurs, whereas in ViewData the Value becomes Null when redirection occurs.

 

Do not forget to add ConnectionString in web.config, 

<connectionStrings>
    <add name="testCrud" connectionString="server=ANIKET\SQLEXPRESS_NEW;user id=sa;password=sa;database=EPIServerDB;Pooling=false;" />
</connectionStrings>

This is just the basic crud operation using MVC 4 and I will come up with more details in the next blog.