Data access using entity framework in MVC

In Brief: In this article let us understand how to making use of database in MVC by using Entity framework . or Retrieve the data stored in database table by using the entity framework. In the Model in MVC i have explained about how to making use of model in MVC.

In Detail:  Let us understand this concept by using the following example in steps. Before proceeding here go through Model in MVC , here i will continue with the same code.

Step1:  Check whether Entiy-framework installed in the visual studio, go to Tools->Library Package Manager->Manage Nuget Packages for solution.
  

If it is already installed shows green mark, otherwise  press install and proceed.

Or it can also be done by using the following command. Go to Tools->Library Package Manager->Package manager console.

PM> Install-Package EntityFramework -Version 5.0.0 

Press enter it will start downloading and install the EntityFramework 5.0.

Step2 : Configure the database access to the project.

For this need add Context class file to model. Right click on model ->Add->Class->Say EmployeeContext.cs.

Add the following code,

using System.Data.Entity
public class EmployeeContext :DbContext
{
public DbSetEmployees{get; set;}
}

EmployeeContext class derived from the base class DbContext. This EmployeeContext class provides the access to database. DbContext defined in System.Data.Entity includes the definition for data access. 

Inside the context class the public property  ‘Employees’ which returns the DbSet of Employee. Type clsEmployee is our Model Class. This property is used to fetch data from the table tblEmployee. How it is, you can see in the bellow steps.

Step3:  Add the  connection string for the Context  class to get connected to the database. Open Web.config add the connection string.

Observe the above code where connection string name should be same as the ContextClass name i.e. EmployeeContext. When we create the object for EmployeeContext class first it looks for the connection string defined in the Web.config file.

Step4 :  Map the Model class "clsEmployee" to a database table "tblEmployee".

By default entity-framework searches for a name of the model class(i.e.clsEmployee) in side Database for a table with name clsEmployee .

But here we have already created the database with table name tblEmployee. Model class name and Database table names are different  then we need to Map model class to table by using the attribute "Table"  as follows.

Here another important thing is primary key field should be named as Id or [tablename]Id i.e clsEmployeeId.

using System.ComponentModel.DataAnnotations.Schema;
  [Table("tblEmployee")]
    public class clsEmployee
    {
        public int Id { get;set;}
        public string Name { get; set; }
        public string Gender { get; set; }
        public string Designation { get; set; }
        public string City { get; set; }
 } 

Add the suggested namespace(ctrl + .), where this "Table" attribute is defined.

Step5: Modify the Controller method to fetch data from the model.

From the previous article where we are hardcoded the data during object instantiation, but now we are going to fetch the same from the database table.

We need to display the single employee detail according to the employee id.

Create object for EmployeContext, which holds clsEmployee class and returns the entire database table data. Instead of returning entire data , used the method "single<>"  to return the single matching record using LINQ. 

using MvcSample.Models;
namespace MvcSample.Controllers
{
   public class EmployeeController : Controller
    {
        public ActionResult fnDetails(int id)
        {
            EmployeeContext objEmployeeContext =new EmployeeContext();
            clsEmployee objClsEmployee=   objEmployeeContext.cntxtEmployees.Single(emp=>emp.EmployeeId==id); 
            return View(objClsEmployee);
        }
    }
}

objEmployee is passed to View for the rendering the employee details.

Step 6: create the database and table.

create database MvcSample
use   MvcSample 
create table tblEmployee (Id int identity(1,1) primary key,
Name varchar(20),
Gender varchar(12),
Designation varchar(25),
City varchar(20))

insert into tblEmployee(Name,Gender,Designation,City)values('Suchith Madavu','Male','Dotnet Developer','Bangalore')
insert into tblEmployee(Name,Gender,Designation,City)values('Chetan Shetty','Male','iOS/Android Developer','Bangalore')
insert into tblEmployee(Name,Gender,Designation,City)values('Anush Narayan','Male','Dotnet Developer','Bangalore')

select * from tblEmployee

Step 7:  Turn off database initializer in Global.asax file.

Open Global.asax file, inside the Application_Start() add the following code,

Database.SetInitializer(null);

By default on application start it searches for the existing database (with the project name) if you don’t have, it will creates and initializes on behalf of you.

 But here we don’t  need this initialization feature  because database configuration is done manually in the previous steps(3) .  By passing ‘null’ to the EmployeeContext Database initialization is disabled.

After completing these steps now run the application, if the  RouteConfig is not done then  change the URL as,

/ControllerName/ActionMethod/id

in my example, http://localhost:1032/Employee/fnDetails/1

Now change the optional index  with employee id

Final Touch : Model interacted with the database by making use of Entity  framework  and on request of user, controller fetches data from the Model and hands over to the View. View does its job by presenting data back to user.

Recommended reading : 
Model in Mvc

Passing Data from Controller to View