Introduction
Microsoft MVC paradigm claims to ease out a lot of basic web application processes such as local login workflow, user registration workflow, sign-in workflow, using external logins such as Facebook, Twitter, LinkedIn, etc. We can already test that with the built-in templates for MVC-type web applications. But, when we talk about converting existing classic ASP.NET webform applications to MVC entity framework paradigms, the first thing that comes to mind is how will we integrate our existing database with the new MVC paradigm. More importantly, how will the basic login flow be done even if the database is successfully integrated? In such situations, a code-first approach is never favored; rather, a database-first approach is favorable, why? Because large enterprise applications have a huge amount of investment towards data science that is generally done on the database level rather than the code level.
So, for today's discussion, I will be demonstrating the following.
How to integrate the existing database in ASP.NET MVC 5 web application using ADO.NET database first approach.
How to configure simple login workflow for integrating existing logins with the ASP.NET MVC 5 web application.

the following are some prerequisites before you proceed any further in this tutorial.
Prerequisites
Before moving further, you should have knowledge about the following.
- ASP.NET MVC 5
- ADO.NET
- Entity Framework
- OWIN'
- Claim Base Identity Model
- C Programming
- C# LINQ
You can download the complete source code or you can follow the step-by-step discussion below. The sample code is developed in Microsoft Visual Studio 2013 Ultimate. I am using SQL Server 2008 as a database.
Let’s Begin now.
First, you need to create a sample database with a "Login" table, I am using the following scripts to generate my sample database. My database name is "AdoNetIntegration", below is the snippet for it.
Here I have created a simple login table with sample data and a stored procedure to retrieve the data. Before integrating the existing database, let’s create a new ASP.MVC 5 project with sample template and look at a few things here.
Create a new Visual Studio Web MVC project and execute it. You will see the following on the web browser.
![MVC project]()
![ASP.NET]()
This is the basic template for ASP.MVC5.
Let's observe a few things here; the first is that in this existing template which is code first approach, we cannot use the login feature because we do not have any login information created. Secondly, we do not have any database created with any configurations for a database. So, where will the database go when we actually use this template to register a login? Open the "Server Explorer" window and observe that there is nothing under "Data Connection". Observe that the "App_Data" folder in the "Solution Explorer" window is also empty as shown below.
![Data Connection]()
Let's register a random login and sign in to this sample application as shown below.
![Login]()
![Register]()
![.NET]()
Here, after creating a login, you will automatically get sign-in into this application and can see your sign-in email at the top right before logging off.
You will now see a new connection string in "Web. config" as shown below.
![XML]()
You will also see a new database created in the "App_Data" folder which is not included in the solution explorer as shown below.
![App Data]()
![Default]()
Notice that in the "Server Explorer" window, there is a new connection available under "Data Connection" and it is now expandable as shown below.
![Connection]()
Expand the "Data Connection" and you will see some tables created, these tables are created by the MVC user management automatic scaffold and are integrated with OWIN middleware security layer to allow us to use a secure authorization throughout the application with "[Authorize]" attribute instead of creating session variables like in classic ASP.NET Web Form to secure log-in, log-off flow as shown below.
![Table]()
Now, we do not need this database, instead, we need our existing database to get integrated into this application. In order to integrate our existing database, let's delete this database and remove connection string information for it from the "Web.config" file as shown below.
Now, right-click on the project in "Solution Explorer" then select "Add" and click "ADO.NET Entity Data Model" as shown below.
![ADO.NET Entity Data Model]()
Now, select the "EF Designer from Database" option and click next as shown below.
![EF Designer]()
On the next window, click "New Connection", "Connection Properties" windows will appear, provide your SQL server credentials select the database "AdoNetIntegration" which we have created earlier, and click OK as shown below.
![New Connection]()
![Properties]()
Choose "yes, include sensitive data in connection string" to include your SQL server database credential into your application and click next as shown below.
![SQL server database]()
In the "Entity Data Model Wizard" window choose what you want to import into the web application. I will import my stored procedure only and click next as shown below.
![Entity Data Model Wizard]()
![Entity Model]()
You can observe here that after including my store procedure, the entity framework paradigm will automatically create complex type model classes.
Now that we have integrated our existing database, let's tweak this scaffold existing template to create a simple login workflow that is integrated with our database and uses the OWIN middleware security layer for secure authorization.
Delete the following files from the project as we do not need them.
- Under the "Controller" folder delete the "HomeController.cs" file.
- Under the "Model" folder delete the "IdentitModel.cs" file.
- Under the "App_Start" folder delete the "IdentityConfig.cs" file.
- Under the "Views->Account" folder delete all files except the "Login.cshtml" file.
- Under the "Views->Home" folder delete all files except the "Index.cshtml" file.
- Under the "Views->Shared" folder delete all files except "_Layout.cshtml, _LoginPartial.cshtml & Error. cshtml" files.
Now, open the "RouteConfig.cs" file under the "App_Start" folder and replace the existing code with the following.
Here, I change my default controller to "Account" & action to "Login".
Now, open the "Startup.Auth.cs" file and replace the existing code with the following.
Here, we clean up a few things by adding the "Logoff" path and login expiration after 5 minutes options.
Open the "Global.asax.cs" file and add the following code at the end of the "Application_Start" method.
We add this line of code to save you from the "Name Claim Identity" exception which occurs when you provide your user name for identity claim property in order to use the OWIN middleware security layer for secure authorization.
- Open the "_Layout.cshtml" file under "Views->Shared" and replace the existing code with the following.
- Open the "_LoginPartial.cshtml" file under the "Views, then Shared" folder and replace it with the following code.
Now, open "Index.cshtml" under the "Views->Home" folder and replace it with the following code.
Now open the "Login.cshtml" file under the "Views->Account" folder and replace it with the following code.
Now, open the "AccountViewModels.cs" file under the "Model" folder and replace it with the following code.
Here, we have simply tweaked the model according to our needs.
Now, open the "HomeController.cs" file under the "Controller" folder and replace it with the following code, do also observe here that we have used the "[Authorize]" attribute at the controller level to secure access to our home controller for the authenticated users only.
Now, open the "AccountController.cs" file under the "Controller" folder and replace it with the following code.
This controller is important and let me explain method by method. The following code simply creates a variable that allows us to access our database methods.
The following piece of code will create our default action with both get and post methods, also notice in the post method that we are accessing our stored procedure method through the database access variable, so we can verify whether to allow sign-in or not.
The following piece of code is used to sign off users from our application.
The following piece of code claims our identity for authentication with OWIN middleware security layer.
This is how the application will look like now.
![Application]()
![Welcome]()
Conclusion
This article is about integrating existing databases with ASP.NET MVC5 web application using the database first approach along with the creation of a basic login/logoff workflow. So, you have learned, how to integrate existing databases in ASP.NET MVC 5 web application using ADO.NET database first approach and how to configure simple login workflow for integrating existing logins with the ASP.NET MVC 5 web application with OWIN middleware secure authorization mechanism.