CRUD Operations In MVC Sagar Jaybhay
In series of this post you will able to understand CRUD Operations In MVC by Sagar Jaybhay.
CRUD Operations In MVC
In this, we have a scenario we need to display the top 25 employees and perform Edit, Display and Delete Operation on Employee.
To achieve above this we need to create a method in Business class to get the top 25 employees for that method is below.
public IEnumerable<Employee> GetEmployees()
List<Employee> employees = new List<Employee>();
string Query = "select top 25 * from Employee";
var data = this.dataAccess.GetTable(Query);
if(data!=null&&data.Rows.Count>0)
foreach(DataRow dataRow in data.Rows)
var emp = new Employee()
EmpID = Convert.ToInt32(dataRow["EmpID"]),
EmpCity = dataRow["EmpCity"].ToString(),
EmpEmail = dataRow["EmpEmail"].ToString(),
EmpGender = dataRow["EmpGender"].ToString(),
EmpName = dataRow["EmpName"].ToString(),
EmpSalary = Convert.ToDouble(dataRow["EmpSalary"].ToString()),
DepartmentID = Convert.ToInt32(dataRow["DepartmentID"].ToString())
;
employees.Add(emp);
return employees;
The above method will return the Employee enumerable list and this list we pass to our view. For that, we create an action method in our Employee Controller and the method is below.
public ActionResult DisplayCompleteEmployee()
var emplist = new BusinessLogic.Business().GetEmployees();
return View(emplist);
Now we need to create a Strongly-typed model that takes Ienumberable<Employee> list. Now our view looks like below.
@model IEnumerable<WebApplication1.Models.Employee>
@using WebApplication1.Models
@
ViewBag.Title = "DisplayCompleteEmployee";
<h2>Display Complete Employee</h2>
<br />
<div class="row" style="margin-left:0px !important">
@Html.ActionLink("Create Employee", "Create")
</div>
<br />
<hr />
<br />
<table class="table table-bordered table-responsive">
<thead>
<tr>
<td>Name</td>
<td>Gender</td>
<td>City</td>
<td>Email</td>
<td>Salary</td>
<td>Take Action</td>
</tr>
</thead>
<tbody>
@foreach (var emp in Model)
@Html.ActionLink("Delete", "EditEmpDetails", new EmpID = emp.EmpID )
</tbody>
</table>
In the above code, we add action link create employee but we don’t have a method for that in our controller so we need to create this method which response to create get request.
Below the method, we add in our controller and that method mark with HttpGet attribute means it should respond only get create method.
[HttpGet]
public ActionResult Create()
return View();
Now below image is that we add a view which uses scaffolding template for create
When we run our application and click on create employee action link we got view which is shown below
For the above view, we use the auto-generated code.
@model WebApplication1.Models.Employee
@
ViewBag.Title = "Create";
<h2>Create</h2>
@using (Html.BeginForm())
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Employee</h4>
<hr />
@Html.ValidationSummary(true, "", new @class = "text-danger" )
<div class="form-group">
@Html.LabelFor(model => model.EmpID, htmlAttributes: new @class = "control-label col-md-2" )
<div class="col-md-10">
@Html.EditorFor(model => model.EmpID, new htmlAttributes = new @class = "form-control" )
@Html.ValidationMessageFor(model => model.EmpID, "", new @class = "text-danger" )
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmpName, htmlAttributes: new @class = "control-label col-md-2" )
<div class="col-md-10">
@Html.EditorFor(model => model.EmpName, new htmlAttributes = new @class = "form-control" )
@Html.ValidationMessageFor(model => model.EmpName, "", new @class = "text-danger" )
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmpSalary, htmlAttributes: new @class = "control-label col-md-2" )
<div class="col-md-10">
@Html.EditorFor(model => model.EmpSalary, new htmlAttributes = new @class = "form-control" )
@Html.ValidationMessageFor(model => model.EmpSalary, "", new @class = "text-danger" )
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmpGender, htmlAttributes: new @class = "control-label col-md-2" )
<div class="col-md-10">
@Html.EditorFor(model => model.EmpGender, new htmlAttributes = new @class = "form-control" )
@Html.ValidationMessageFor(model => model.EmpGender, "", new @class = "text-danger" )
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmpCity, htmlAttributes: new @class = "control-label col-md-2" )
<div class="col-md-10">
@Html.EditorFor(model => model.EmpCity, new htmlAttributes = new @class = "form-control" )
@Html.ValidationMessageFor(model => model.EmpCity, "", new @class = "text-danger" )
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.EmpEmail, htmlAttributes: new @class = "control-label col-md-2" )
<div class="col-md-10">
@Html.EditorFor(model => model.EmpEmail, new htmlAttributes = new @class = "form-control" )
@Html.ValidationMessageFor(model => model.EmpEmail, "", new @class = "text-danger" )
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.DepartmentID, htmlAttributes: new @class = "control-label col-md-2" )
<div class="col-md-10">
@Html.EditorFor(model => model.DepartmentID, new htmlAttributes = new @class = "form-control" )
@Html.ValidationMessageFor(model => model.DepartmentID, "", 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>
<div>
@Html.ActionLink("Back to List", "Index")
</div>
Attributes in MVC
But if you see the image it will generate textbox for gender column and we don’t want this. We need a dropdown list for that and also we seen in the above create an image that contains textbox name empid, empname this good for a developer but for end-user we need to change this so we use DisplayName attribute to change the display name of an employee.
Now our model class becomes
public class Employee
[DisplayName("ID")]
public int EmpID get; set;
[DisplayName("Name")]
public string EmpName get; set;
[DisplayName("Salary")]
public double EmpSalary get; set;
[DisplayName("Gender")]
public string EmpGender get; set;
[DisplayName("City")]
public string EmpCity get; set;
[DisplayName("Email")]
public string EmpEmail get; set;
public int DepartmentID get; set;
To use DisplayName attribute we need to import using System.ComponentModel; This namespace in our class and our UI looks like this
To change gender textbox we need to write below code instead of textbox for empgender.
@Html.DropDownList("Gender",new List<SelectListItem> new SelectListItem Text="Male" ,Value="Male",
new SelectListItem Text="Female" ,Value="female","Select Gender")
@Html.ValidationMessageFor(model => model.EmpGender, "", new @class = "text-danger" )
After doing the create form we have to Create a button on that form and if you click on that you will get a 404 error why because there is no method to respond post request in our EmployeeController.
To create first we need to Hide EmpId from create form because it is unique and needs to be dynamic so we will hide this from. We set to display: none property in style and from UI empid is not displayed. Now our UI looks like below
To create employee we created a stored procedure for that and we call
this stored procedure from our business class. So first we need to create a
HttpPost enable create method in our controller, code for this is below.
Below is the method we created in business class and which in turn calls a method that is written in a database class.
public void CreateEmployee(Employee employee)
this.dataAccess.ExecuteProcedure(employee.EmpName, employee.EmpEmail, employee.EmpGender, employee.EmpSalary, employee.DepartmentID, employee.EmpCity);
Below is the method in the Database class.
public void ExecuteProcedure(string Name,string Email,string Gender,double salary,int deptid,string city)
using(var con=new SqlConnection(this._ConnectionString))
con.Open();
var cmd = new SqlCommand("spCreateEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("name", SqlDbType.NVarChar, 20));
cmd.Parameters.Add(new SqlParameter("salary", SqlDbType.Float, 50));
cmd.Parameters.Add(new SqlParameter("gender", SqlDbType.NVarChar, 50));
cmd.Parameters.Add(new SqlParameter("city", SqlDbType.NVarChar, 50));
cmd.Parameters.Add(new SqlParameter("email", SqlDbType.NVarChar, 50));
cmd.Parameters.Add(new SqlParameter("deptid", SqlDbType.Int, 50));
cmd.Parameters["name"].Value = Name;
cmd.Parameters["salary"].Value = salary;
cmd.Parameters["gender"].Value = Gender;
cmd.Parameters["city"].Value = city;
cmd.Parameters["email"].Value = Email;
cmd.Parameters["deptid"].Value = deptid;
cmd.ExecuteNonQuery();
For the above code, we have written a stored procedure in our SQL Server database which is below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[spCreateEmployee]
@name nvarchar(20),
@salary float,
@gender nvarchar(20),
@city nvarchar(20),
@email nvarchar(30),
@deptid int
as
begin
declare @empid int
select @empid=count(*) from Employee
set @empid=@empid+1
insert into Employee values(@empid,@name,@salary,@gender,@city,@email,@deptid)
end
GO
GitHub :- https://github.com/Sagar-Jaybhay/MVC5
Comments
Post a Comment