SQL Server Database Connection To MVC With Entity Framework
Introduction
In the previous article, you have learned about how to create a new MVC project step by step in a visual studio.
In this article, you will learn how to establish a SQL Server database connection to MVC 5 applications using Entity Framework(ORM), and connect SQL databases to perform operations in a project.
This article will be helpful to beginners and students who are learning MVC.
Pre-requisites
- You need to create ASP.NET MVC Project(You can refer our Previous article for that), Or you can add this into the existing project also A SQL server Installed where you can create a database.
- A Database created on the SQL server.
What is Entity Framework(ORM)?
It’s easy to understand what EntityFrameWork and ORM are. ORM stands for Object Relational Mapping.
ORM is basically an approach for storing data from entity objects to a relational database in an automated way without writing much code.
ORMs are database independent and can run with any database server having a similar database with a structure needed by the application.
ORM has 3 parts: tables, views & Stored Procedures.
It also automates standard CRUD operation (Create, Read, Update & Delete) so the developer doesn’t need to code them manually.
Now let’s discuss the Entity Framework:
Entity Framework is an ORM approach based Framework that enables developers to work with relational data as domain-specific objects and eliminates the need for writing code for most of the data access that developers usually code.
Using the Entity Framework, developers issue queries using LINQ and then retrieve and manipulate data. It provides services like identity resolution, lazy loading, and query translation so that it can be easy for developers to focus on their application-specific business logic instead of the data access fundamentals.
DataBase
Run the following code into your created database.
CREATE TABLE [dbo].[Holidays]( [Id] [int] IDENTITY(1,1) NOT NULL, [Date] [datetime] NOT NULL, [Type] [nvarchar](50) NOT NULL, [Name] [nvarchar](50) NOT NULL, [CreatedBy] [int] NOT NULL, [UpdatedBy] [int] NOT NULL, [CreatedDate] [datetime] NOT NULL, [UpdatedDate] [datetime] NOT NULL, CONSTRAINT [PK_Holidays] PRIMARY KEY CLUSTERED ( [Id] 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
Display data using EntityFramework On MVC
Step 1
To install the entity framework right click on the project where you want to add the entity framework.
Select Manage NuGet Packages for Solution… option.
NuGet is the package manager for .NET. There are thousands of Nuget Packages available to use in the NuGet Package Manager.
The NuGet Package Manager window will be open. Search for Entity framework in the search box select the EntityFramework option as shown below and hit the install button as shown below.
Step 2
There will be one folder named Models (If not there right-click on the project and add a folder named Models). Right-click on the folder and click on the add new item option, It will open the window as shown below.
Select the Ado.NET Entity Data Model option and give it the name you want to give to your database model as shown below and hit the add button.
By hitting on the Add button, It will redirect to the below screen and select the EF Designer from Database and hit the next button.
By hitting on the next it will redirect to the below screen where you have to select the database you want to connect and give the appropriate name of the DB entity.
It will create a connection string in Web.config file like this :
By hitting on the next button it will open the below screen where you can select manually how many tables, Views, Store Procedures and functions you want to add in this edmx.
Give the appropriate name to the database model.
Click on Finish button And there will be Open the file with .edmx extension as shown below
A file with the edmx extension is an XML file. It defines a storage model and the mapping between storage models. An edmx file contains information that is used by the ADO.NET Entity Data Model Designer to render a model graphically.
Code
Now write some code to display data on the web page from the database.
Create an object of the context class and get the data from the table and send it to the view as shown below.
using MVCSampleProject.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace MVCSampleProject.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
SampleDataEntities dbContect = new SampleDataEntities();
var holidays=dbContect.Holidays.ToList();
return View(holidays);
}
}
}
Then add the below code in the view page to display the data of the database into the table and then hit the F5 button and run the code.
@model IEnumerable
@{
ViewBag.Title = "Home Page";
}
Holiday List
Name
Type
Date
@foreach (var item in Model)
{
@item.Name
@item.Type
@item.Date.ToString("dd/MM/yyyy")
}
When you run the code. It will redirect you to the Index method of Home Controller.
Where the object of the entity will request for the Holiday table data, and we will pass the data in the form of list type of object in the view.
On the View page, we have taken the list of the model. So by using for each Statement, we can display the data into the table easily.
Here I have used the bootstrap class to display the table well-formatted.
You can see the below screen in the browser where you can see the list of holiday data from the database.
I hope this article will help you to establish a connection between the SQL server database and the MVC application. we are to help you if you have any issues or queries. comments on your suggestions and issues we try to resolve it.
