Post
Ask Question

SQL Server Database Connection To MVC Application

Introduction

This article demonstrates how to establish a SQL Server database connection to MVC 5 application using Entity Framework. It is beneficial for beginners and for students as well. This article is focused on the existing database in SQL Server, therefore, create a database in SQL Server then create a new project of ASP.NET MVC 5 in Visual Studio.NET 2015.

Database

In this article, a database is created in SQL Server 2014. The name of the database is Students. The Students database contains two tables StudentsInfo and StudentsDept. The StudentsInfo table contains the student's general data such as student registration number, student name, student data of birth, student contact etc. The StudentsDept table contains department information such as department name and department code. Following is the script of StudentsInfo table,

  1. CREATE TABLE [dbo].[StudentsInfo](  
  2.     [StdRegNumber] [nvarchar](50) NOT NULL,  
  3.     [StdName] [nvarchar](50) NULL,  
  4.     [StdDoBirth] [date] NULL,  
  5.     [StdRegDate] [date] NULL,  
  6.     [StdContact] [nvarchar](14) NULL,  
  7.     [StdEmail] [nvarchar](50) NULL,  
  8.     [StdDeptCode] [int] NULL,  
  9.  CONSTRAINT [PK_StudentsInfo] PRIMARY KEY CLUSTERED   
  10. (  
  11.     [StdRegNumber] ASC  
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  13. ) ON [PRIMARY]  

Following is the script of StudentsDept table,

  1. CREATE TABLE [dbo].[StudentsDept](  
  2.     [DeptCode] [int] NOT NULL,  
  3.     [DeptName] [nvarchar](80) NULL,  
  4.  CONSTRAINT [PK_StudentsDept] PRIMARY KEY CLUSTERED   
  5. (  
  6.     [DeptCode] ASC  
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  8. ) ON [PRIMARY]  

Connect Database to MVC Application using Entity Framework

Create a new project of ASP.NET MVC 5 web application using Visual Studio.NET 2015. Now, to connect a database to MVC 5 application using Entity Framework, the following three components must be added to MVC application,

  • Database Model
  • Database Controller
  • Database View

Database Model

To add database Model to MVC application follow the following steps,

  1. In the Solution Explorer window of Visual Studio, right click on the MVC project name, select the Add option then select the New Item option. The New Item option opens the Add New Item window. The Add New Item window contains three panes that are left side pane, middle or center pane and right side pane. From the left pane select Visual C# and from the middle pane select ADO.NET Entity Data Model and give a name to it using the name text box which is located below the middle pane of the Add New Item window then press the Add button. Suppose, the name of the ADO.NET Entity Data Model is StudentsModel. Its default name is Model1. Following is its snapshot,

    MVC

  2. When we press the Add button, it displays the Entity Data Model Wizard window that contains different model contents that are EF Designer from the database, Empty EF Designer model, Empty Code First model, Code First from the database. Select EF Designer from database option and click the Next Button. Following is its snapshot,

    MVC

  3. When we click the Next button, it opens another window from where to choose the Data Connection. To choose the Data Connection, click the New Connection button. The New Connection button opens the Connection Properties window. From the Connection Properties window, enter the Server computer name where the SQL Server database is installed and the instance of the SQL Server database using the Server name text box. Now, from the Authentication drop-down control select the SQL Server Authentication and put the username and password in the Username and Password text boxes respectively and then select the desired database from the Select or enter a database name drop-down list. When you select the database then press the Ok button. Following is its snapshot,

    MVC

  4. Now, select the radio button option entitled Yes, include the sensitive data in the connection string and enter the context name in the text box entitled Save connection settings in Web.Config as. Suppose the name of the context is StudentsContext. When you entered the context name then press the Next button. Following is its snapshot,

    MVC

  5. When we press the Next button, it opens another window from which we can include tables from the database. This window displays three types of database objects that are Tables, Views, and Stored Procedures. Each type of object has a checkbox option. Now click the checkbox option of the Tables, make it checked and select the desired tables to add to the project. Now enter the name of the Model Namespace using the Model Namespace text box. Suppose the name of the Model Namespace is StudentsModel. When you entered a name of the Model Namespace then press the Finish button. Following is its snapshot,

    MVC

We added two tables StudentsDept and StudentsInfo of Students database. The Students database is discussed in chapter number 10. When we press the Finish button, a new file StudentsModel.edmx is added to the MVC project. The name of the .edmx file is the name we entered in the Model Namespace text box. The .edmx is an XML file that defines the schema for a data model. The .edmx file contains different classes. It contains a separate class for each database table we selected during the connection wizard.

Database Controller

To add a database Controller follows the following steps,

  1. Right click on Controller folder of the ASP.NET MVC web application project, click Add then click the Controller option. The Controller option opens the Add Scaffold window. The Add Scaffold window contains different templates for the new Controller that are MVC 5 Controller empty, MVC 5 Controller with reading/write actions, MVC 5 Controller with views using entity framework, Web API 2 Controller empty etc. Following is the snapshot of the Add Scaffold window of Controller,

    MVC

  2. Now from the Add Scaffold window select MVC 5 Controller with views, using Entity Framework and press the Add button. The Add button opens the Add Controller window. The Add Controller window contains two dropdown lists to select Modal class and Data Context class for the Controller. Each Controller needs a Modal and Data Context classes. From the first dropdown list select the Modal class and from the second dropdown list select the Data Context class. The Modal class is usually a database table from which you want to retrieve or add data.

  3. Now, give a name to the Controller using the Controller name text box of the Add Controller window and click the Add button. The Controller name always ends with the word Controller. Suppose the name of the Controller is StudentsController. Following is the snapshot of the Add Controller window,

    MVC

When we click the Add button, a new Controller StudentsController is added to our ASP.NET MVC web application project. The new Controller StudentsController contains different actions such as Index, Create, Details, Edit, and Delete. Each action returns ActionResult.

Database View

When we create a Controller by selecting the option MVC 5 Controller with views, using Entity Framework of the Add Scaffold window, it automatically creates the Views. For each action of the Controller, a separate View is created. A view is a C# or Visual Basic and HTML page that provides a visual interface to the user. The file extension of a View is cshtml in C# and .vbhtml in Visual Basic. A View takes input data from the user and sends to the Controller and receives a result from the Controller and displays it to the user using different Form Controls.


Similar Articles