Database Connectivity With And Without Entity Framework In MVC

Introduction

I am writing this article because a beginner requested me to do so. Hopefully, this will be helpful for all MVC beginners. First, I will discuss the process without Entity Framework and then, I will discuss the same with Entity Framework. I will just retrieve data from the SQL database. I am not going to explain CRUD (Create Update Read and Delete) operations in this article.

Database connection without Entity Framework

Step 1

Open up your favorite SQL Server database with any version. It really doesn’t matter what version it is.

CREATE TABLE [dbo].[Employees](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Gender] [char](10) NULL,
[Age] [int] NULL,
[Position] [nvarchar](50) NULL,
[Office] [nvarchar](50) NULL,
[HireDate] [datetime] NULL,
[Salary] [int] NULL,
CONSTRAINT [PK__Employee__7AD04F11A993CC91] PRIMARY KEY CLUSTERED
(
[EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Step 1

Open Visual Studio 2015 or an editor of your choice and create a new project.

Step 2

Choose the "web application" project and give an appropriate name to your project.

Database Connectivity With And Without Entity Framework In MVC

Step 3

Select "empty" template, check on MVC checkbox and click OK.

Step 4

Right-click the Models folder and add a class with name employee and create similar property and data type as we have in SQL database table.

using System;
namespace MvcDatabaseConnectivity_Demo.Models
{
public class Emplyee
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public int Age { get; set; }
public string Position { get; set; }
public string Office { get; set; }
public DateTime HireDate { get; set; }
public int Salary { get; set; }
}
}

Step 5

Open Web.config file and add your database connection, and write the below code

<connectionStrings>
<add name ="DBCS" connectionString="data source=FARHAN\SQLEXPRESS; database=Sample; Integrated Security=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

Step 6

Right-click on the Controllers folder and add a controller.

A window will appear. Choose MVC5 Controller-Empty and click "Add".

After clicking on "Add", another window will appear with DefaultController. Change the name to HomeController and click "Add". The HomeController will be added under the Controllers folder. Don’t change the Controller suffix for all controllers, change only the highlight, and instead of Default, just change Home:

using MvcDatabaseConnectivity_Demo.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;
namespace MvcDatabaseConnectivity_Demo.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
List<Emplyee> employeeList = new List<Emplyee>();
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", con);
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var employee = new Emplyee();
employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
employee.Name = rdr["Name"].ToString();
employee.Gender = rdr["Gender"].ToString();
employee.Age = Convert.ToInt32(rdr["Age"]);
employee.Position = rdr["Position"].ToString();
employee.Office = rdr["Office"].ToString();
employee.HireDate =Convert.ToDateTime(rdr["HireDate"]);
employee.Salary = Convert.ToInt32(rdr["Salary"]);
employeeList.Add(employee);
}
}
return View(employeeList);
}
}
}

Step 7

Right-click on the Index method in HomeController; the "Add View" window will appear with the default index name checked (use a Layout page). Click on "Add.

Code for Index View

@model IEnumerable<MvcDatabaseConnectivity_Demo.Models.Employee>
@{
ViewBag.Title = "Index";
}
<h2>List of Employee</h2>
<table class="table table-bordered">
<thead>
<tr>
<th>@Html.DisplayNameFor(m => m.Name)</th>
<th>@Html.DisplayNameFor(m => m.Gender)</th>
<th>@Html.DisplayNameFor(m => m.Age)</th>
<th>@Html.DisplayNameFor(m => m.Position)</th>
<th>@Html.DisplayNameFor(m => m.Office)</th>
<th>@Html.DisplayNameFor(m => m.HireDate)</th>
<th>@Html.DisplayNameFor(m => m.Salary)</th>
</tr>
</thead>
<tbody>
@foreach (var emp in Model)
{
<tr>
<td>@emp.Name</td>
<td>@emp.Gender</td>
<td>@emp.Age</td>
<td>@emp.Position</td>
<td>@emp.Office</td>
<td>
@if (emp.HireDate != null)
{
@emp.HireDate
}
</td>
<td>@emp.Salary</td>
</tr>
}
</tbody>
</table>

Step 8

Build and run the project by pressing Ctrl+F5.

Database connection with Entity Framework

Step 1

Open Visual Studio 2015 or an editor of your choice and create a new project.

Step 2

Choose the "web application" project and give an appropriate name to your project.

Step 3

Select "empty" template, check on MVC checkbox and click OK.

Step 4

Right-click the Models folder and add a database model. Add Entity Framework now. For that, right-click on Models folder, select Add, then select "New Item".

You will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name EmployeeModel (this name is not mandatory, you can give any name) and click "Add".

After you click on "Add a window", the wizard will open. Choose EF Designer from the database and click "Next".

After clicking on "Next", a window will appear. Choose "New Connection". Another window will appear. Add your server name - if it is local, then enter a dot (.). Choose your database and click "OK".

The connection will be added. If you wish, save the connection name as you want. You can change the name of your connection below. It will save the connection in the web config. Now, click "Next".

After clicking on NEXT, another window will appear. Choose the database table name as shown in the below screenshot and click "Finish".

Entity Framework gets added and the respective class gets generated under the Models folder.

Employee class

namespace MvcDatabaseConnectivity_Demo.Models
{
using System;
public partial class Employee
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public Nullable<int> Age { get; set; }
public string Position { get; set; }
public string Office { get; set; }
public Nullable<System.DateTime> HireDate { get; set; }
public Nullable<int> Salary { get; set; }
}
}

Step 5

Right-click on Controllers folder and add a controller.

A window will appear. Choose MVC5 Controller-Empty and click "Add".

Complete controller code

using MvcDatabaseConnectivity_Demo.Models;
using System.Linq;
using System.Web.Mvc;
namespace MvcDatabaseConnectivity_Demo.Controllers
{
public class HomeController : Controller
{
private readonly EmployeeContext _dbContext = new EmployeeContext();
public ActionResult Index()
{
var employee = _dbContext.Employees.ToList();
return View(employee);
}
}
}

Step 6

Right-click on the Index method in HomeController; the "Add View" window will appear with default index name checked (use a Layout page). Click on "Add.

@model IEnumerable<MvcDatabaseConnectivity_Demo.Models.Employee>
@{
ViewBag.Title = "Index";
}
<h2>List of Employee</h2>
<table class="table table-bordered">
<thead>
<tr>
<th>@Html.DisplayNameFor(m => m.Name)</th>
<th>@Html.DisplayNameFor(m => m.Gender)</th>
<th>@Html.DisplayNameFor(m => m.Age)</th>
<th>@Html.DisplayNameFor(m => m.Position)</th>
<th>@Html.DisplayNameFor(m => m.Office)</th>
<th>@Html.DisplayNameFor(m => m.HireDate)</th>
<th>@Html.DisplayNameFor(m => m.Salary)</th>
</tr>
</thead>
<tbody>
@foreach (var emp in Model)
{
<tr>
<td>@emp.Name</td>
<td>@emp.Gender</td>
<td>@emp.Age</td>
<td>@emp.Position</td>
<td>@emp.Office</td>
<td>
@if (emp.HireDate != null)
{
@emp.HireDate
}
</td>
<td>@emp.Salary</td>
</tr>
}
</tbody>
</table>

Step 7

Build and run the project by pressing Ctrl+F5.

12 Comments

Join the conversation! Your thoughts help the community grow.

Kelly DonhamHarris4y ago
Great guide! I have inherited a website (MVC Core, Entity Framework, Razor pages) and have no background in Entity nor Razor and very little in MVC. This was a huge help to understand some of the differences between data handling from what I am used to.
Thobani Ngcoya5y ago
Thank you, great article. Can you help me do it the other way now? Post to DB from Textboxes?
Priyanka Singh5y ago
Thank you so much because this article helps me to crack the interview process..
Đoàn Trần6y ago
How To function oracle using asp.net mvc without Entityfamework ??? Help me
khalid malik6y ago
Very Good topic Just Same Example with drop down list 2 Example 1) Drop down List --> Country 2) Drop down list --> State Depend on first drop down list Second drop down should be listed
Carlos Ramirez6y ago
GRACIASSSSSS
R O D O6y ago
Where I find a connection without entity with:ASP.net Core Angular SQL Server
Carlos AC7y ago
Very interesting article, nice to see how to achieve the same result with 2 different aproaches. But also it would be cool to read your opinion on the advantages and disadvantages for each one. Thanks.
kalai romi7y ago
Awesome article sir..its more useful..Thanks
- Farhan Ahmed7y ago
  Thank you.........
Amit Mohanty7y ago
Useful article
- Farhan Ahmed7y ago
  Thank you.............