CRUD Operation With Repository And Database Initialization In Code First Approach In MVC

In the previous article, I explained about Code-First Migrations and how to enable those. In this article, I will explain how to perform CRUD and how to perform searching functionality using the code-first approach and Repository. And also in this article, I will give a brief introduction of Database Initialization. Let’s see step by step.

See the previous articles for basic details.

Step 1

Open Visual Studio and go to File >> Project >> Web application and click OK. Then, it will open a window. In that window, select MVC or Empty.

MVC

Step2

First, we will right-click on the Solution Explorer in our project >> Add >> New item.

Step 3

Now, we will add a domain class so here, I am taking an employee class. Right click on the Models folder and add a class.

[Table("TblEmployee")]
public class Employee
{
[Key]
public int EmpId { get; set; }
[Required()]
[StringLength(100, MinimumLength = 4)]
public string Name { get; set; }
[Required()]
[StringLength(200, MinimumLength = 10)]
public string Address { get; set; }
[Required()]
[StringLength(200, MinimumLength = 5)]
public string Email { get; set; }
[Required()]
[StringLength(10, MinimumLength = 10)]
public string MobileNo { get; set; }
}

Now, we will create a DbContext class inside the Models folder. For that, right click on the Models folder and add a class.

And write the below code.

public class EmpDataContext : DbContext
{
public EmpDataContext()
: base("name=MySqlConnection")
{
}
public DbSet<Employee> employees { get; set; }
}

After that, go in web.config and set the connection string.

<connectionStrings>
<add name="MySqlConnection" connectionString="Data Source=MANNU;database=MyDemoDB;User Id=sa;Password=123;"
providerName="System.Data.SqlClient" />
</connectionStrings>

Step 4

After that, build the project and then it will automatically generate the database or table in SQL so, we can check also in the SQL Server.

MVC

Step 5

Now, we will add a repository class inside the Models folder. Right click on the Models folder and add Repositories folder and add a class inside the Repositories folder.

public class Repository<T> where T : class
{
EmpDataContext objDataContext = null;
protected DbSet<T> DbSet
{
get;set;
}
public Repository()
{
objDataContext = new EmpDataContext();
DbSet = objDataContext.Set<T>();
}
public Repository(EmpDataContext objDataContext)
{
this.objDataContext = objDataContext;
}
public List<T> GetAll()
{
return DbSet.ToList();
}
public T GetById(int id)
{
return DbSet.Find(id);
}
public void Add(T entity)
{
DbSet.Add(entity);
}
public void Delete(T entity)
{
DbSet.Remove(entity);
}
public void SaveChanged()
{
objDataContext.SaveChanges();
}
}

Step 6

Now, we will go in the Controller class and create an object of our repository class.

Now, we will complete our CRUD operation using the Code First approach with repository class. First, we will write the code for the Create operation. For this, we have to create the action method for getting the request and posting the records named as Create method.

public ActionResult create()
{
return View();
}
[HttpPost]
public ActionResult create(Employee objEmp)
{
objRepository.Add(objEmp);
objRepository.SaveChanged();
return View();
}

And, after that, we will write HTML code like below.

@model Example3.Models.Employee
@using (Html.BeginForm())
{
<div class="form-horizontal">
<h4>Employee</h4>
<hr />
@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.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">
@Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Email, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.MobileNo, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.MobileNo, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.MobileNo, "", 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", "EmpDetails")
</div>

Now, we will write the code for posting the details.

After that, click on the Create button. Then, it will save the records in the database.

Now, we will write the code for retrieving the data.

public ActionResult EmpDetails()
{
return View(objRepository.GetAll());
}

Here, right click on the Action method “EmpDetails” and press Add View option. And write the below HTML code.

@model IEnumerable<Example3.Models.Employee>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr class="btn-primary">
<th>
@Html.DisplayNameFor(model => model.EmpId)
</th>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th>
@Html.DisplayNameFor(model => model.Email)
</th>
<th>
@Html.DisplayNameFor(model => model.MobileNo)
</th>
<th>Action</th>
</tr>
@foreach (var item in Model) {
<tr class="btn-info">
<td>
@Html.DisplayFor(modelItem => item.EmpId)
</td>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.DisplayFor(modelItem => item.Email)
</td>
<td>
@Html.DisplayFor(modelItem => item.MobileNo)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.EmpId }) |
@Html.ActionLink("Details", "Details", new { id = item.EmpId }) |
@Html.ActionLink("Delete", "Delete", new { id = item.EmpId })
</td>
</tr>
}
</table>

Now, we will see the output.

Now, we will write code for seeing the details of a particular employee.

public ActionResult Details(string id)
{
int empId = Convert.ToInt32(id);
var emp = objRepository.GetById(empId);
return View(emp);
}

Here, right click on the Action method “Details” and press Add View option. And write the below html code,

@model Example3.Models.Employee
<div>
<h4>Employee</h4>
<hr />
<dl class="dl-horizontal">
<dt>
@Html.DisplayNameFor(model => model.Name)
</dt>
<dd>
@Html.DisplayFor(model => model.Name)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Address)
</dt>
<dd>
@Html.DisplayFor(model => model.Address)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Email)
</dt>
<dd>
@Html.DisplayFor(model => model.Email)
</dd>
<dt>
@Html.DisplayNameFor(model => model.MobileNo)
</dt>
<dd>
@Html.DisplayFor(model => model.MobileNo)
</dd>
</dl>
</div>
<p>
@Html.ActionLink("Edit", "Edit", new { id = Model.EmpId }) |
@Html.ActionLink("Back to List", "EmpDetails")
</p>

MVC

Now, we will perform edit functionality so for this we will create edit action method.

public ActionResult Edit(string id)
{
int empId = Convert.ToInt32(id);
var emp = objRepository.GetById(empId);
return View(emp);
}

Now, right click on the Action method “Edit” and press Add View option. And write the below html code,

@model Example3.Models.Employee
@using (Html.BeginForm())
{
<div class="form-horizontal">
<h4>Employee</h4>
<hr />
@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" } })
</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" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.MobileNo, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.MobileNo, new { htmlAttributes = new { @class = "form-control" } })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Update" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "EmpDetails")
</div>

Now, we will write the code for updating the page.

[HttpPost]
public ActionResult Edit(Employee objEmp)
{
var data = objRepository.GetById(objEmp.EmpId);
if(data != null)
{
data.Name = objEmp.Name;
data.Address = objEmp.Address;
data.Email = objEmp.Email;
data.MobileNo = objEmp.MobileNo;
}
objRepository.SaveChanged();
return View();
}

So, finally we write code for Delete operation.

public ActionResult Delete(string id)
{
int empId = Convert.ToInt32(id);
var emp = objRepository.GetById(empId);
return View(emp);
}
[HttpPost]
public ActionResult Delete(Employee objEmp)
{
var emp = objRepository.GetById(objEmp.EmpId);
objRepository.Delete(emp);
objRepository.SaveChanged();
return View("EmpDetails");
}

Right click on the Delete Action method and add the view. And we will write the HTML code.

@model Example3.Models.Employee
<h3>Are you sure you want to delete this?</h3>
<div>
<h4>Employee</h4>
<hr />
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<dl class="dl-horizontal">
@Html.HiddenFor(model => model.EmpId)
<dt>
@Html.DisplayNameFor(model => model.Name)
</dt>
<dd>
@Html.DisplayFor(model => model.Name)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Address)
</dt>
<dd>
@Html.DisplayFor(model => model.Address)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Email)
</dt>
<dd>
@Html.DisplayFor(model => model.Email)
</dd>
<dt>
@Html.DisplayNameFor(model => model.MobileNo)
</dt>
<dd>
@Html.DisplayFor(model => model.MobileNo)
</dd>
</dl>
<div class="form-actions no-color">
<input type="submit" value="Delete" class="btn btn-default" /> |
@Html.ActionLink("Back to List", "EmpDetails")
</div>
}
</div>

MVC

After clicking the delete button it will be deleted,

Step 7

Now, we will do search functionality. Here, we will search by employee name so first go to employee details, view page and add html code on top,

@using (Html.BeginForm())
{
<div class="row">
<div class="col-md-2"> Search By Name</div>
<div class="col-md-3"><input type="text" name="Name" class="form-control" /></div>
<div class="col-md-3"><button type="submit" class="btn btn-info col-md-6 glyphicon-search" value="Search">Search</button></div>
</div>
}
<br />
<br />

Now, again we will go in model folder and add a class and write the below code,

public class EmployeeRepository : Repository<Employee>
{
public List<Employee> GetByName(string name)
{
return DbSet.Where(a => a.Name.Contains(name)).ToList();
}
}

Then, we will go in our controller and write the below method

[HttpPost]
public ActionResult EmpDetails(string Name)
{
if (Name == "")
{
return View(objRepository.GetAll());
}
else
{
EmployeeRepository obj = new EmployeeRepository();
return View(obj.GetByName(Name));
}
}

And Run the application and enter an employee name in text box and click search button and see the output.

Step 8

Now I explain about Database Initialization in brief.

A database initializer is a class. It takes the responsibility creation of database and initialization in a Code First application. When we will run the application using the code first approach it will automatically create a database but when we will need to modify the data base or alter the database according to our modified domain classes then we will use database initializer. For more details go this link.

Some required points of database initialization

Entity Framework provides database initializers to create state
Create a class that inherits from appropriate option
- CreatedatabaseIf Not Exist
- DropCreateDatabaseIfModelChanges
- DropCreateDatabaseAlways
- Custom DB Initializer
Override the Seed method to create database content
Register the method with SetInitializer

Now, we will go inside model folder and a class give then name EmpDataContextInitializer,

Write the below code.

public class EmpDataContextInitializer : DropCreateDatabaseAlways<EmpDataContext>
{
protected override void Seed(EmpDataContext context)
{
Employee empObj = new Employee { Name = "Mithilesh", Address = "Hyderabad", Email = "[email protected]", MobileNo = "9823423432" };
context.employees.Add(empObj);
context.SaveChanges();
}
}

And now, we will go Global.asax and go inside Application_Start().

Database.SetInitializer<EmpDataContext>(new EmpDataContextInitializer());

See the output.

Also, check in the database,

Summary

Finally, we knew how to perform CRUD Operation and also searching functionality by employee name using Code First approach and Repository pattern and also we knew about some brief Introduction of database initialization with a practical example. In our next article, we will see “How to give relationships among multiple tables using the Code First approach”.