Post
Ask Question

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 
  1. Id int Primary Key Not Null AutoIncremented Column  
  2. Name varchar(50) Null  
  3. LName varchar(50) Null  
  4. Gender varchar(50) Null  
  5. IsActive bit Null  
Write the following Stored Procedure, 
  1. Create PROCEDURE [dbo].[MVC_CRUD]  
  2. (  
  3. @Id int = 0,  
  4. @Para varchar(50)='',  
  5. @Name varchar(50)='',  
  6. @LName varchar(50)='',  
  7. @Gender varchar(50)='',  
  8. @IsActive bit = 1  
  9. )  
  10. AS  
  11. BEGIN  
  12. If @Para = 'ADD'  
  13. Begin  
  14. Insert into tblMVCCrud (Name,LName,Gender,IsActive) values (@Name,@LName,@Gender,1)  
  15. END  
  16. Else If @Para = 'UPDATE'  
  17. Begin  
  18. Update tblMVCCrud SET Name = @Name,LName = @LName,Gender = @Gender,IsActive = 1 where Id = @Id  
  19. END  
  20. Else If @Para = 'DELETE'  
  21. Begin  
  22. Update tblMVCCrud SET IsActive = 0 where Id = @Id  
  23. END  
  24. Else If @Para = 'Select_By_Id'  
  25. Begin  
  26. Select Id,Name,LName,Gender from tblMVCCrud where Id = @Id  
  27. END  
  28. Else If @Para = 'Select'  
  29. Begin  
  30. Select Id,Name,LName,Gender from tblMVCCrud where IsActive = 1  
  31. END  
  32. 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, 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Data.SqlClient;  
  6. using System.Data;  
  7. using System.Configuration;  
  8. using MvcApplication1.Models;  
  9. namespace MvcApplication1.DAL  
  10. {  
  11.     public class DataAccessLayer  
  12.     {  
  13.         int result = 0;  
  14.         SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["testCrud"].ToString());  
  15.         SqlCommand cmd = null;  
  16.         DataTable dt = null;  
  17.         SqlDataAdapter da = null;  
  18.         public int Registration(Class1 obj)  
  19.         {  
  20.             try  
  21.             {  
  22.                 using(cmd = new SqlCommand("MVC_CRUD", con))   
  23.                 {  
  24.                     cmd.CommandType = CommandType.StoredProcedure;  
  25.                     cmd.Parameters.AddWithValue("@Para", "ADD");  
  26.                     cmd.Parameters.AddWithValue("@Name", obj.Name);  
  27.                     cmd.Parameters.AddWithValue("@LName", obj.LName);  
  28.                     cmd.Parameters.AddWithValue("@Gender", obj.Gender);  
  29.                     if (con.State.Equals(ConnectionState.Closed))  
  30.                         con.Open();  
  31.                     result = cmd.ExecuteNonQuery();  
  32.                     con.Close();  
  33.                     return result;  
  34.                 }  
  35.             } catch (Exception ex)  
  36.             {  
  37.                 throw ex;  
  38.             }  
  39.         }  
  40.         public DataTable GetData()  
  41.         {  
  42.             try   
  43.             {  
  44.                 using(cmd = new SqlCommand("MVC_CRUD", con))  
  45.                 {  
  46.                     cmd.CommandType = CommandType.StoredProcedure;  
  47.                     cmd.Parameters.AddWithValue("@Para", "Select");  
  48.                     if (con.State.Equals(ConnectionState.Closed))  
  49.                         con.Open();  
  50.                     da = new SqlDataAdapter(cmd);  
  51.                     dt = new DataTable();  
  52.                     da.Fill(dt);  
  53.                     con.Close();  
  54.                     return dt;  
  55.                 }  
  56.             } catch (Exception ex)  
  57.             {  
  58.                 throw ex;  
  59.             }  
  60.         }  
  61.         public DataTable SelectById(string Id)  
  62.         {  
  63.             try  
  64.             {  
  65.                 using(cmd = new SqlCommand("MVC_CRUD", con)) {  
  66.                     cmd.CommandType = CommandType.StoredProcedure;  
  67.                     cmd.Parameters.AddWithValue("@Para", "Select_By_Id");  
  68.                     cmd.Parameters.AddWithValue("@Id", Convert.ToInt32(Id));  
  69.                     if (con.State.Equals(ConnectionState.Closed))  
  70.                         con.Open();  
  71.                     da = new SqlDataAdapter(cmd);  
  72.                     dt = new DataTable();  
  73.                     da.Fill(dt);  
  74.                     con.Close();  
  75.                     return dt;  
  76.                 }  
  77.             } catch (Exception ex)   
  78.             {  
  79.                 throw ex;  
  80.             }  
  81.         }  
  82.         public int UpdateRecords(Class1 obj)   
  83.         {  
  84.             try  
  85.             {  
  86.                 using(cmd = new SqlCommand("MVC_CRUD", con))  
  87.                 {  
  88.                     cmd.CommandType = CommandType.StoredProcedure;  
  89.                     cmd.Parameters.AddWithValue("@Para", "UPDATE");  
  90.                     cmd.Parameters.AddWithValue("@Id", obj.Id);  
  91.                     cmd.Parameters.AddWithValue("@Name", obj.Name);  
  92.                     cmd.Parameters.AddWithValue("@LName", obj.LName);  
  93.                     cmd.Parameters.AddWithValue("@Gender", obj.Gender);  
  94.                     if (con.State.Equals(ConnectionState.Closed))  
  95.                         con.Open();  
  96.                     result = cmd.ExecuteNonQuery();  
  97.                     con.Close();  
  98.                     return result;  
  99.                 }  
  100.             } catch (Exception ex)  
  101.             {  
  102.                 throw ex;  
  103.             }  
  104.         }  
  105.         public int Delete(Class1 obj)  
  106.         {  
  107.             try  
  108.             {  
  109.                 using(cmd = new SqlCommand("MVC_CRUD", con))  
  110.                 {  
  111.                     cmd.CommandType = CommandType.StoredProcedure;  
  112.                     cmd.Parameters.AddWithValue("@Para", "DELETE");  
  113.                     cmd.Parameters.AddWithValue("@Id", obj.Id);  
  114.                     if (con.State.Equals(ConnectionState.Closed))  
  115.                         con.Open();  
  116.                     result = cmd.ExecuteNonQuery();  
  117.                     con.Close();  
  118.                     return result;  
  119.                 }  
  120.             } catch (Exception ex)  
  121.             {  
  122.                 throw ex;  
  123.             }  
  124.         }  
  125.     }  
  126. }  
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, 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.ComponentModel.DataAnnotations;  
  5. using System.Data;  
  6. using System.Linq;  
  7. using System.Web;  
  8. namespace MvcApplication1.Models  
  9. {  
  10.     public class Class1  
  11.     {  
  12.         public int Id  
  13.         {  
  14.             get;  
  15.             set;  
  16.         }  
  17.         [Required(ErrorMessage = "Please Enter Name")]  
  18.         [DisplayName("Enter Name")]  
  19.         [StringLength(50)]  
  20.         public string Name   
  21.         {  
  22.             get;  
  23.             set;  
  24.         }  
  25.         [Required(ErrorMessage = "Please Enter Last Name")]  
  26.         [DisplayName("Enter Last Name")]  
  27.         [StringLength(50)]  
  28.         public string LName   
  29.         {  
  30.             get;  
  31.             set;  
  32.         }  
  33.         [Required(ErrorMessage = "Please Select Gender")]  
  34.         [DisplayName("Select Gender")]  
  35.         [StringLength(25)]  
  36.         public string Gender  
  37.         {  
  38.             get;  
  39.             set;  
  40.         }  
  41.         public DataTable getData   
  42.         {  
  43.             get;  
  44.             set;  
  45.         }  
  46.     }  
  47. }   
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, 
  1. using MvcApplication1.Models;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Data;  
  5. using System.Linq;  
  6. using System.Web;  
  7. using System.Web.Mvc;  
  8. namespace MvcApplication1.Controllers  
  9. {  
  10.     public class RegistrationController: Controller  
  11.     {  
  12.         //  
  13.         // GET: /Registration/  
  14.         DAL.DataAccessLayer objDAL = new DAL.DataAccessLayer();  
  15.         DataTable dt = null;  
  16.         int result = 0;  
  17.         public ActionResult Registration()  
  18.             {  
  19.                 return RedirectToAction("GetAllData");  
  20.             }  
  21.             [HttpPost]  
  22.         public ActionResult Registration(Class1 obj)  
  23.         {  
  24.             if (ModelState.IsValid)   
  25.             {  
  26.                 result = objDAL.Registration(obj);  
  27.                 TempData["result"] = result;  
  28.                 ModelState.Clear();  
  29.                 return RedirectToAction("GetAllData");  
  30.             } else {  
  31.                 ViewData["result"] = "Registration Not Successful";  
  32.                 return RedirectToAction("GetAllData");  
  33.             }  
  34.         }  
  35.         public ActionResult GetAllData()  
  36.         {  
  37.             Class1 objModel = new Class1();  
  38.             objModel.getData = objDAL.GetData();  
  39.             return View("Registration", objModel);  
  40.         }  
  41.         public ActionResult EditData(string Id)  
  42.         {  
  43.             Class1 objModel = new Class1();  
  44.             dt = new DataTable();  
  45.             dt = objDAL.SelectById(Id);  
  46.             if (dt.Rows.Count > 0)   
  47.             {  
  48.                 objModel.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());  
  49.                 objModel.Name = dt.Rows[0]["Name"].ToString();  
  50.                 objModel.LName = dt.Rows[0]["LName"].ToString();  
  51.                 objModel.Gender = dt.Rows[0]["Gender"].ToString();  
  52.             }  
  53.             return View(objModel);  
  54.         }  
  55.         public ActionResult DeleteData(string Id)   
  56.         {  
  57.                 Class1 objModel = new Class1();  
  58.                 dt = new DataTable();  
  59.                 dt = objDAL.SelectById(Id);  
  60.                 if (dt.Rows.Count > 0)  
  61.                 {  
  62.                     objModel.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());  
  63.                     objModel.Name = dt.Rows[0]["Name"].ToString();  
  64.                     objModel.LName = dt.Rows[0]["LName"].ToString();  
  65.                     objModel.Gender = dt.Rows[0]["Gender"].ToString();  
  66.                 }  
  67.                 return View(objModel);  
  68.             }  
  69.             [HttpPost]  
  70.         public ActionResult Update(Class1 obj)  
  71.         {  
  72.                 if (ModelState.IsValid)   
  73.                 {  
  74.                     result = objDAL.UpdateRecords(obj);  
  75.                     TempData["Update"] = result;  
  76.                     ModelState.Clear();  
  77.                     return RedirectToAction("GetAllData");  
  78.                 } else {  
  79.                     TempData["result"] = "Updation Not Successful";  
  80.                     return RedirectToAction("GetAllData");  
  81.                 }  
  82.             }  
  83.             [HttpPost]  
  84.         public ActionResult Delete(Class1 obj)   
  85.         {  
  86.             if (ModelState.IsValid)  
  87.             {  
  88.                 result = objDAL.Delete(obj);  
  89.                 TempData["Delete"] = result;  
  90.                 ModelState.Clear();  
  91.                 return RedirectToAction("GetAllData");  
  92.             } else  
  93.             {  
  94.                 TempData["Delete"] = "Updation Not Successful";  
  95.                 return RedirectToAction("GetAllData");  
  96.             }  
  97.         }  
  98.     }  
  99. }  
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 
  1. @model MvcApplication1.Models.Class1  
  2. @ {  
  3.     Layout = null;  
  4. }  
  5. @using(Html.BeginForm("Registration", "Registration")) { < table >  
  6.         < tr >  
  7.         < td >  
  8.         @Html.LabelFor(model => model.Name) < /td> < td >  
  9.         @Html.TextBoxFor(model => model.Name) < /td> < td >  
  10.         @Html.ValidationMessageFor(model => model.Name) < /td> < /tr> < tr >  
  11.         < td >  
  12.         @Html.LabelFor(model => model.LName) < /td> < td >  
  13.         @Html.TextBoxFor(model => model.LName) < /td> < td >  
  14.         @Html.ValidationMessageFor(model => model.LName) < /td> < /tr> < tr >  
  15.         < td >  
  16.         @Html.LabelFor(model => model.Gender) < /td> < td >  
  17.         @Html.RadioButtonFor(model => model.Gender, "Male", false) Male  
  18.     @Html.RadioButtonFor(model => model.Gender, "Female", false) Female < /td> < td >  
  19.         @Html.ValidationMessageFor(model => model.Gender) < /td> < /tr> < tr >  
  20.         < td >  
  21.         < input type = "submit"  
  22.     id = "submit1"  
  23.     value = "Submit" / >  
  24.         < /td> < /tr> < /table>  
  25. }  
  26. @ {  
  27.     if (Convert.ToInt32(ViewData["result"]) == 1) { < script type = "text/javascript"  
  28.         lang = "ja" >  
  29.             alert("Registration Successful!!!") < /script>  
  30.         ViewData["result"] = 0;  
  31.     } else { < script type = "text/javascript"  
  32.         lang = "ja" >  
  33.             alert(ViewData["result"]); < /script>  
  34.     }  
  35. }  
  36. @ {  
  37.     if (Convert.ToInt32(TempData["Update"]) == 1) { < script type = "text/javascript"  
  38.         lang = "ja" >  
  39.             alert("Updation Successful!!!") < /script>  
  40.         TempData["Update"] = 0;  
  41.     } else { < script type = "text/javascript"  
  42.         lang = "ja" >  
  43.             alert(TempData["Update"]); < /script>  
  44.     }  
  45. }  
  46. @ {  
  47.     if (Convert.ToInt32(TempData["Delete"]) == 1) { < script type = "text/javascript"  
  48.         lang = "ja" >  
  49.             alert("Deletion Successful!!!") < /script>  
  50.         TempData["Delete"] = 0;  
  51.     } else { < script type = "text/javascript"  
  52.         lang = "ja" >  
  53.             alert(TempData["Delete"]); < /script>  
  54.     }  
  55. }  
  56. @ {  
  57.     if (Model.getData.Rows.Count > 0) { < table >  
  58.             < th > Name < /th> < th > LastName < /th> < th > Gender < /th>  
  59.         @for(int i = 0; i < Model.getData.Rows.Count; i++) {  
  60.             var Name = Model.getData.Rows[i]["Name"].ToString();  
  61.             var LastName = Model.getData.Rows[i]["LName"].ToString();  
  62.             var Gender = Model.getData.Rows[i]["Gender"].ToString(); < tr >  
  63.                 < td >  
  64.                 @Name < /td> < td >  
  65.                 @LastName < /td> < td >  
  66.                 @Gender < /td> < td >  
  67.                 @Html.ActionLink("Edit", "EditData", new {  
  68.                     id = Model.getData.Rows[i]["Id"].ToString()  
  69.                 }) < /td> < td >  
  70.                 @Html.ActionLink("Delete", "DeleteData", new {  
  71.                     id = Model.getData.Rows[i]["Id"].ToString()  
  72.                 }) < /td> < /tr>  
  73.         } < /table>  
  74.     }  
  75. }   
Similarly, right click on EditData Method and add a View Name EditData. Add the following code in it, 
  1. @model MvcApplication1.Models.Class1  
  2. @ {  
  3.     Layout = null;  
  4. }  
  5. @using(Html.BeginForm("Update", "Registration")) { < table >  
  6.         < tr >  
  7.         < td >  
  8.         @Html.HiddenFor(model => model.Id) < /td> < /tr> < tr >  
  9.         < td >  
  10.         @Html.TextBoxFor(model => model.Name) < /td> < /tr> < tr >  
  11.         < td >  
  12.         @Html.TextBoxFor(model => model.LName) < /td> < /tr> < tr >  
  13.         < td >  
  14.         @Html.RadioButtonFor(model => model.Gender, "Male", false) Male  
  15.     @Html.RadioButtonFor(model => model.Gender, "Female", false) Female < /td> < /tr> < tr >  
  16.         < td >  
  17.         < input type = "submit"  
  18.     value = "Update"  
  19.     id = "update1" / >  
  20.         < /td> < /tr> < /table>  
  21. }   
Similarly, right click on DeleteData Method and add a View Name DeleteData. Add the following code in it, 
  1. @model MvcApplication1.Models.Class1  
  2. @ {  
  3.     Layout = null;  
  4. }  
  5. @using(Html.BeginForm("Delete", "Registration")) { < table >  
  6.         < tr >  
  7.         < td >  
  8.         @Html.HiddenFor(model => model.Id) < /td> < /tr> < tr >  
  9.         < td >  
  10.         @Html.TextBoxFor(model => model.Name) < /td> < /tr> < tr >  
  11.         < td >  
  12.         @Html.TextBoxFor(model => model.LName) < /td> < /tr> < tr >  
  13.         < td >  
  14.         @Html.RadioButtonFor(model => model.Gender, "Male", false) Male  
  15.     @Html.RadioButtonFor(model => model.Gender, "Female", false) Female < /td> < /tr> < tr >  
  16.         < td >  
  17.         < input type = "submit"  
  18.     value = "Delete"  
  19.     id = "update1" / >  
  20.         < /td> < /tr> < /table>  
  21. }   
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, 
  1. <connectionStrings>  
  2.     <add name="testCrud" connectionString="server=ANIKET\SQLEXPRESS_NEW;user id=sa;password=sa;database=EPIServerDB;Pooling=false;" />  
  3. </connectionStrings>   
This is just the basic crud operation using MVC 4 and I will come up with more details in the next blog.
Next Recommended Reading Insert Delete Update Read (Crud Operation) in MVC 4.0 using JQuery Json