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.

 

 

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".

 

 

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,

 

 

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.