Q.CRUD Operations using ASP.NET MVC with Repository Patern

Step 1. Create Table Name "Employee" In Sql Server

CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](200) NULL,
[City] [varchar](50) NULL,
[Address] [varchar](250) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 2. Create store procedure

Create procedure [dbo].[AddNewEmpDetails] 
( 
   @Name varchar (50), 
   @City varchar (50), 
   @Address varchar (50) 
) 
as 
begin 
   Insert into Employee values(@Name,@City,@Address) 
End



Create Procedure [dbo].[GetEmployees] 
as 
begin 
   select *from Employee 
End


Create procedure [dbo].[UpdateEmpDetails] 
( 
   @EmpId int, 
   @Name varchar (50), 
   @City varchar (50), 
   @Address varchar (50) 
) 
as 
begin 
   Update Employee 
   set Name=@Name, 
   City=@City, 
   Address=@Address 
   where Id=@EmpId 
End

Create procedure [dbo].[DeleteEmpById] 
( 
   @EmpId int 
) 
as 
begin 
   Delete from Employee where Id=@EmpId 
End

Step 3. Open Visual Studio 2015

Go To "File", then "New" and click "Project..." then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click on OK. After clicking, the following window will appear:

Step 4. As shown in the preceding screenshot, click on Empty template and check MVC option, then click OK. This will create an empty MVC web application whose Solution Explorer will look like the following

Step 5. Now let us create the model class named EmpModel.cs by right clicking on model folder and Write the following code

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

namespace CRUDUsingMVCWithAdoDotNet2.Models
{
    public class EmpModel
    {
        [Display(Name = "Id")]
        public int Empid { get; set; }

        [Required(ErrorMessage = "First name is required.")]
        public string Name { get; set; }

        [Required(ErrorMessage = "City is required.")]
        public string City { get; set; }

        [Required(ErrorMessage = "Address is required.")]
        public string Address { get; set; }
    }

}

Step 6.Now let us add the MVC 5 controller as in the following screenshot:

Step 7 Create Repository class.

Now create Repository folder and Add EmpRepository.cs class for database related operations, after adding the solution explorer will look like the following screenshot:

Step 8. Write the following Code

using CRUDUsingMVCWithAdoDotNet2.Models;

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

namespace CRUDUsingMVCWithAdoDotNet2.Repository

{

    public class EmpRepository

    {

        private SqlConnection con;

        //To Handle connection related activities    

        private void connection()

        {

            string constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();

            con = new SqlConnection(constr);

        }

        //To Add Employee details    

        public bool AddEmployee(EmpModel obj)

        {

            connection();

            SqlCommand com = new SqlCommand("AddNewEmpDetails", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Name", obj.Name);

            com.Parameters.AddWithValue("@City", obj.City);

            com.Parameters.AddWithValue("@Address", obj.Address);

            con.Open();

            int i = com.ExecuteNonQuery();

            con.Close();

            if (i >= 1)

            {

                return true;

            }

            else

            {

                return false;

            }            

        }

        //To view employee details with generic list     

        public List<EmpModel> GetAllEmployees()

        {

            connection();

            List<EmpModel> EmpList = new List<EmpModel>();

            SqlCommand com = new SqlCommand("GetEmployees", con);

            com.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataTable dt = new DataTable();

            con.Open();

            da.Fill(dt);

            con.Close();

            //Bind EmpModel generic list using dataRow     

            foreach (DataRow dr in dt.Rows)

            {

                EmpList.Add(

                    new EmpModel

                    {

                        Empid = Convert.ToInt32(dr["Id"]),

                        Name = Convert.ToString(dr["Name"]),

                        City = Convert.ToString(dr["City"]),

                        Address = Convert.ToString(dr["Address"])

                    }                    

                    );            

            }

            return EmpList;

        }

        //To Update Employee details    

        public bool UpdateEmployee(EmpModel obj)

        {

            connection();

            SqlCommand com = new SqlCommand("UpdateEmpDetails", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@EmpId", obj.Empid);

            com.Parameters.AddWithValue("@Name", obj.Name);

            com.Parameters.AddWithValue("@City", obj.City);

            com.Parameters.AddWithValue("@Address", obj.Address);

            con.Open();

            int i = com.ExecuteNonQuery();

            con.Close();

            if (i >= 1)

            {

                return true;

            }

            else

            {

                return false;

            }

        }

        //To delete Employee details    

        public bool DeleteEmployee(int Id)

        {

            connection();

            SqlCommand com = new SqlCommand("DeleteEmpById", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@EmpId", Id);

            con.Open();

            int i = com.ExecuteNonQuery();

            con.Close();

            if (i >= 1)

            {

                return true;

            }

            else

            {

                return false;

            }

        }

    }

}

Step 9.Create Methods into the EmployeeController.cs file.

Now open the EmployeeController.cs and create the following action methods:

using CRUDUsingMVCWithAdoDotNet2.Models;

using CRUDUsingMVCWithAdoDotNet2.Repository;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace CRUDUsingMVCWithAdoDotNet2.Controllers

{

    public class EmployeeController : Controller

    {

        // GET: Employee/GetAllEmpDetails    

        public ActionResult GetAllEmpDetails()

        {

            EmpRepository EmpRepo = new EmpRepository();

            ModelState.Clear();

            return View(EmpRepo.GetAllEmployees());

        }

        // GET: Employee/AddEmployee    

        public ActionResult AddEmployee()

        {

            return View();

        }

        // POST: Employee/AddEmployee    

        [HttpPost]

        public ActionResult AddEmployee(EmpModel Emp)

        {

            try

            {

                if (ModelState.IsValid)

                {

                    EmpRepository EmpRepo = new EmpRepository();

                    if (EmpRepo.AddEmployee(Emp))

                    {

                        ViewBag.Message = "Employee details added successfully";

                    }

                }

                return View();

            }

            catch

            {

                return View();

            }

        }

        // GET: Employee/EditEmpDetails/5    

        public ActionResult EditEmpDetails(int id)

        {

            EmpRepository EmpRepo = new EmpRepository();

            return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Empid == id));

        }

        // POST: Employee/EditEmpDetails/5    

        [HttpPost]

        public ActionResult EditEmpDetails(int id, EmpModel obj)

        {

            try

            {

                EmpRepository EmpRepo = new EmpRepository();

                EmpRepo.UpdateEmployee(obj);

                return RedirectToAction("GetAllEmpDetails");

            }

            catch

            {

                return View();

            }

        }

        // GET: Employee/DeleteEmp/5    

        public ActionResult DeleteEmp(int id)

        {

            try

            {

                EmpRepository EmpRepo = new EmpRepository();

                if (EmpRepo.DeleteEmployee(id))

                {

                    ViewBag.AlertMsg = "Employee details deleted successfully";

                }

                return RedirectToAction("GetAllEmpDetails");

            }

            catch

            {

                return View();

            }

        }

    }

}

Step 10. Create Views.

Create the Partial view to Add the employees

@model CRUDUsingMVCWithAdoDotNet2.Models.EmpModel

@using (Html.BeginForm()) 

{

    @Html.AntiForgeryToken()

    

    <div class="form-horizontal">

        <h4>Add New Employee</h4>

        <hr />

        <div>

            @Html.ActionLink("Back to Employee List", "GetAllEmpDetails")

        </div> 

      

        @Html.ValidationSummary(true, "", new { @class = "text-danger" })        

        <div class="form-group">

            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })

            <div class="col-md-10">

                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })

                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })

            </div>

        </div>

        <div class="form-group">

            @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })

            <div class="col-md-10">

                @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })

                @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })

            </div>

        </div>

        <div class="form-group">

            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })

            <div class="col-md-10">

                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })

                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })

            </div>

        </div>

        <div class="form-group">

            <div class="col-md-offset-2 col-md-10">

                <input type="submit" value="Create" class="btn btn-default" />

            </div>

        </div>

        <div class="form-group">

            <div class="col-md-offset-2 col-md-10" style="color:green">

                @ViewBag.Message

            </div>

        </div>  

    </div>

}

<script src="~/Scripts/jquery-1.10.2.min.js"></script>

<script src="~/Scripts/jquery.validate.min.js"></script>

<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Step 11.To View Added Employees

To view the employee details let us create the partial view named GetAllEmpDetails:

@model IEnumerable<CRUDUsingMVCWithAdoDotNet2.Models.EmpModel>

<p>

    @Html.ActionLink("Add New Employee", "AddEmployee")

</p> 

<table class="table">

    <tr>

        <th>

            @Html.DisplayNameFor(model => model.Empid)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.Name)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.City)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.Address)

        </th>

        <th></th>

    </tr>

@foreach (var item in Model) {

    <tr>

        <td>

            @Html.DisplayFor(modelItem => item.Empid)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.Name)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.City)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.Address)

        </td>

        <td>

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

            @Html.ActionLink("Delete", "DeleteEmp", new { id = item.Empid }, new { onclick = "return confirm('Are sure wants to delete?');" })

        </td>

    </tr>

}

</table>

Step12,To Update Added Employees

Follow the same procedure and create EditEmpDetails view to edit the employees. After creating the view the code will be like the following:

EditEmpDetails.cshtml 

@model CRUDUsingMVCWithAdoDotNet2.Models.EmpModel

@using (Html.BeginForm())

{

    @Html.AntiForgeryToken()

    

    <div class="form-horizontal">

        <h4>Update Employee Details</h4>

        <hr />

        <div>

            @Html.ActionLink("Back to Details", "GetAllEmployees")

        </div>  

        @Html.ValidationSummary(true, "", new { @class = "text-danger" })

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

              

        <div class="form-group">

            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })

            <div class="col-md-10">

                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })

                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })

            </div>

        </div>

        <div class="form-group">

            @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })

            <div class="col-md-10">

                @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })

                @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })

            </div>

        </div>

        <div class="form-group">

            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })

            <div class="col-md-10">

                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })

                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })

            </div>

        </div>

        <div class="form-group">

            <div class="col-md-offset-2 col-md-10">

                <input type="submit" value="Save" class="btn btn-default" />

            </div>

        </div>

    </div>

}

<script src="~/Scripts/jquery-1.10.2.min.js"></script>

<script src="~/Scripts/jquery.validate.min.js"></script>

<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Step 13. Configure RouteConfig.cs to set default action as in the following code snippet:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Web.Routing;

namespace CRUDUsingMVCWithAdoDotNet2

{

    public class RouteConfig

    {

        public static void RegisterRoutes(RouteCollection routes)

        {

            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(

                name: "Default",

                url: "{controller}/{action}/{id}",

                defaults: new { controller = "Employee", action = "AddEmployee", id = UrlParameter.Optional }

            );

        }

    }

}

Now follpwing Output will Come