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.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
</head>
<body>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-collapse collapse">
@Html.Partial("_LoginPartial")
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<center>
<p><strong>Copyright © @DateTime.Now.Year - <a href="http://asmak9.blogspot.com/">Asma's Blog</a>.</strong> All rights reserved.</p>
</center>
</footer>
</div>
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html>
@using AodNetIntegration.Models
@model LoginViewModel
@{
ViewBag.Title = "ADO.NET Integration - Login";
}
<h2>@ViewBag.Title.</h2>
<div class="row">
<div class="col-md-8">
<section id="loginForm">
@using (Html.BeginForm("Login", "Account", new { ReturnUrl = ViewBag.ReturnUrl }, FormMethod.Post, new { @class = "form-horizontal", role = "form" }))
{
@Html.AntiForgeryToken()
<h4>Use a local account to log in.</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(m => m.Username, new { @class = "col-md-2 control-label" })
<div class="col-md-10">
@Html.TextBoxFor(m => m.Username, new { @class = "form-control" })
@Html.ValidationMessageFor(m => m.Username, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(m => m.Password, new { @class = "col-md-2 control-label" })
<div class="col-md-10">
@Html.PasswordFor(m => m.Password, new { @class = "form-control" })
@Html.ValidationMessageFor(m => m.Password, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Log in" class="btn btn-default" />
</div>
</div>
}
</section>
</div>
</div>
@section Scripts
{
@Scripts.Render("~/bundles/jqueryval")
}
// <copyright file="HomeController.cs" company="None">
// Copyright (c) Allow to distribute this code.
// </copyright>
// <author>Asma Khalid</author>
//-----------------------------------------------------------------------
namespace AodNetIntegration.Controllers
{
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
/// <summary>
/// Home controller class.
/// </summary>
[Authorize]
public class HomeController : Controller
{
#region Index method.
/// <summary>
/// Index method.
/// </summary>
/// <returns>Returns - Index view</returns>
public ActionResult Index()
{
return this.View();
}
#endregion
}
}
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.
USE [AdoNetIntegration]
GO
/****** Object: StoredProcedure [dbo].[LoginByUsernamePassword] Script Date: 03/15/2016 21:33:52 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoginByUsernamePassword]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LoginByUsernamePassword]
GO
/****** Object: Table [dbo].[Login] Script Date: 03/15/2016 21:33:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Login]') AND type in (N'U'))
DROP TABLE [dbo].[Login]
GO
/****** Object: Table [dbo].[Login] Script Date: 03/15/2016 21:33:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Login]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Login](
[id] [int] IDENTITY(1,1) NOT NULL,
NOT NULL,
NOT NULL,
CONSTRAINT [PK_Login] 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]
END
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Login] ON
INSERT [dbo].[Login] ([id], [username], [password]) VALUES (1, N'my-login', N'my-password-123')
SET IDENTITY_INSERT [dbo].[Login] OFF
GO
/****** Object: StoredProcedure [dbo].[LoginByUsernamePassword] Script Date: 03/15/2016 21:33:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoginByUsernamePassword]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Asma Khalid>
-- Create date: <Create Date,,15-Mar-2016>
-- Description: <Description,,You are Allow to Distribute this Code>
-- =============================================
CREATE PROCEDURE [dbo].[LoginByUsernamePassword]
@username varchar(50),
@password varchar(50)
AS
BEGIN
SELECT id, username, password
FROM Login
WHERE username = @username
AND password = @password
END
'
END
GO
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.
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.
Let's register a random login and sign in to this sample application as shown below.
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.
You will also see a new database created in the "App_Data" folder which is not included in the solution explorer as shown below.
Notice that in the "Server Explorer" window, there is a new connection available under "Data Connection" and it is now expandable as shown below.
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.
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.
Now, select the "EF Designer from Database" option and click next as shown below.
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.
Choose "yes, include sensitive data in connection string" to include your SQL server database credential into your application and click next as shown below.
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.
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.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
namespace AodNetIntegration
{
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new
{
controller = "Account",
action = "Login",
id = UrlParameter.Optional
}
);
}
}
}
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.
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using Microsoft.AspNet.Identity.Owin;
using Microsoft.Owin;
using Microsoft.Owin.Security.Cookies;
using Microsoft.Owin.Security.DataProtection;
using Microsoft.Owin.Security.Google;
using Owin;
using System;
using AodNetIntegration.Models;
namespace AodNetIntegration
{
public partial class Startup
{
// For more information on configuring authentication, please visit http://go.microsoft.com/fwlink/?LinkId=301864
public void ConfigureAuth(IAppBuilder app)
{
// Enable the application to use a cookie to store information for the signed in user
// and to use a cookie to temporarily store information about a user logging in with a third party login provider
// Configure the sign in cookie
app.UseCookieAuthentication(new CookieAuthenticationOptions
{
AuthenticationType = DefaultAuthenticationTypes.ApplicationCookie,
LoginPath = new PathString("/Account/Login"),
LogoutPath = new PathString("/Account/LogOff"),
ExpireTimeSpan = TimeSpan.FromMinutes(5.0)
});
app.UseExternalSignInCookie(DefaultAuthenticationTypes.ExternalCookie);
// Uncomment the following lines to enable logging in with third party login providers
//app.UseMicrosoftAccountAuthentication(
// clientId: "",
// clientSecret: "");
//app.UseTwitterAuthentication(
// consumerKey: "",
// consumerSecret: "");
//app.UseFacebookAuthentication(
// appId: "",
// appSecret: "");
//app.UseGoogleAuthentication(new GoogleOAuth2AuthenticationOptions()
//{
// ClientId = "",
// ClientSecret = ""
//});
}
}
}
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.
// Fixing claim issue. https://stack247.wordpress.com/2013/02/22/antiforgerytoken-a-claim-of-type-nameidentifier-or-identityprovider-was-not-present-on-provided-claimsidentity/
AntiForgeryConfig.UniqueClaimTypeIdentifier = ClaimTypes.Name;
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.
@* @using Microsoft.AspNet.Identity *@
@if (Request.IsAuthenticated)
{
using (Html.BeginForm("LogOff", "Account", FormMethod.Post, new
{
id = "logoutForm",
@class = "navbar-right"
}))
{
@Html.AntiForgeryToken()
<ul class="nav navbar-nav navbar-right">
<li>
@Html.ActionLink("Hello " + User.Identity.Name + "!", "Index", "Home", routeValues: null, htmlAttributes: new
{
title = "Manage"
})
</li>
<li>
<a href="javascript:document.getElementById('logoutForm').submit()">Log off</a>
</li>
</ul>
}
}
else
{
<ul class="nav navbar-nav navbar-right">
<li>
@Html.ActionLink("Log in", "Login", "Account", routeValues: null, htmlAttributes: new
{
id = "loginLink"
})
</li>
</ul>
}
Now, open "Index.cshtml" under the "Views->Home" folder and replace it with the following code.
@{
ViewBag.Title = "ADO.NET Integration - Home";
}
<div class="jumbotron">
<h1>Welcome</h1>
<p class="lead">Login from "@User.Identity.Name" Account.</p>
</div>
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.
using System.ComponentModel.DataAnnotations;
namespace AodNetIntegration.Models
{
public class LoginViewModel
{
[Required]
[Display(Name = "Username")]
public string Username { get; set; }
[Required]
[DataType(DataType.Password)]
[Display(Name = "Password")]
public string Password { get; set; }
}
}
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.
//-----------------------------------------------------------------------
// <copyright file="AccountController.cs" company="None">
// Copyright (c) Allow to distribute this code.
// </copyright>
// <author>Asma Khalid</author>
//-----------------------------------------------------------------------
namespace AppTracker.Controllers
{
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Claims;
using System.Web;
using System.Web.Mvc;
using AodNetIntegration;
using AodNetIntegration.Models;
using Microsoft.AspNet.Identity;
using Microsoft.Owin.Security;
/// <summary>
/// Account controller class.
/// </summary>
public class AccountController : Controller
{
#region Private Properties
/// <summary>
/// Database Store property.
/// </summary>
private AdoNetIntegrationEntities databaseManager = new AdoNetIntegrationEntities();
#endregion
#region Default Constructor
/// <summary>
/// Initializes a new instance of the <see cref="AccountController"/> class.
/// </summary>
public AccountController()
{
}
#endregion
#region Login methods
/// <summary>
/// GET: /Account/Login
/// </summary>
/// <param name="returnUrl">Return URL parameter</param>
/// <returns>Return login view</returns>
[AllowAnonymous]
public ActionResult Login(string returnUrl)
{
try
{
// Verification.
if (this.Request.IsAuthenticated)
{
// Info.
return this.RedirectToLocal(returnUrl);
}
}
catch (Exception ex)
{
// Info
Console.Write(ex);
}
// Info.
return this.View();
}
/// <summary>
/// POST: /Account/Login
/// </summary>
/// <param name="model">Model parameter</param>
/// <param name="returnUrl">Return URL parameter</param>
/// <returns>Return login view</returns>
[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public ActionResult Login(LoginViewModel model, string returnUrl)
{
try
{
// Verification.
if (ModelState.IsValid)
{
// Initialization.
var loginInfo = this.databaseManager.LoginByUsernamePassword(model.Username, model.Password).ToList();
// Verification.
if (loginInfo != null && loginInfo.Count() > 0)
{
// Initialization.
var logindetails = loginInfo.First();
// Login In.
this.SignInUser(logindetails.username, false);
// Info.
return this.RedirectToLocal(returnUrl);
}
else
{
// Setting.
ModelState.AddModelError(string.Empty, "Invalid username or password.");
}
}
}
catch (Exception ex)
{
// Info
Console.Write(ex);
}
// If we got this far, something failed, redisplay form.
return this.View(model);
}
#endregion
#region Log Out method.
/// <summary>
/// POST: /Account/LogOff
/// </summary>
/// <returns>Return log off action</returns>
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult LogOff()
{
try
{
// Setting.
var ctx = Request.GetOwinContext();
var authenticationManager = ctx.Authentication;
// Sign Out.
authenticationManager.SignOut();
}
catch (Exception ex)
{
// Info
throw ex;
}
// Info.
return this.RedirectToAction("Login", "Account");
}
#endregion
#region Helpers
#region Sign In method.
/// <summary>
/// Sign In User method.
/// </summary>
/// <param name="username">Username parameter.</param>
/// <param name="isPersistent">Is persistent parameter.</param>
private void SignInUser(string username, bool isPersistent)
{
// Initialization.
var claims = new List<Claim>();
try
{
// Setting
claims.Add(new Claim(ClaimTypes.Name, username));
var claimIdenties = new ClaimsIdentity(claims, DefaultAuthenticationTypes.ApplicationCookie);
var ctx = Request.GetOwinContext();
var authenticationManager = ctx.Authentication;
// Sign In.
authenticationManager.SignIn(new AuthenticationProperties() { IsPersistent = isPersistent }, claimIdenties);
}
catch (Exception ex)
{
// Info
throw ex;
}
}
#endregion
#region Redirect to local method.
/// <summary>
/// Redirect to local method.
/// </summary>
/// <param name="returnUrl">Return URL parameter.</param>
/// <returns>Return redirection action</returns>
private ActionResult RedirectToLocal(string returnUrl)
{
try
{
// Verification.
if (Url.IsLocalUrl(returnUrl))
{
// Info.
return this.Redirect(returnUrl);
}
}
catch (Exception ex)
{
// Info
throw ex;
}
// Info.
return this.RedirectToAction("Index", "Home");
}
#endregion
#endregion
}
}
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.
#region Private Properties
/// <summary>
/// Database Store property.
/// </summary>
private AdoNetIntegrationEntities databaseManager = new AdoNetIntegrationEntities();
#endregion
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.
#region Login methods
/// <summary>
/// GET: /Account/Login
/// </summary>
/// <param name="returnUrl">Return URL parameter</param>
/// <returns>Return login view</returns>
[AllowAnonymous]
public ActionResult Login(string returnUrl)
{
try
{
// Verification.
if (this.Request.IsAuthenticated)
{
// Info.
return this.RedirectToLocal(returnUrl);
}
}
catch (Exception ex)
{
// Info
Console.Write(ex);
}
// Info.
return this.View();
}
/// <summary>
/// POST: /Account/Login
/// </summary>
/// <param name="model">Model parameter</param>
/// <param name="returnUrl">Return URL parameter</param>
/// <returns>Return login view</returns>
[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public ActionResult Login(LoginViewModel model, string returnUrl)
{
try
{
// Verification.
if (ModelState.IsValid)
{
// Initialization.
var loginInfo = this.databaseManager.LoginByUsernamePassword(model.Username, model.Password).ToList();
// Verification.
if (loginInfo != null && loginInfo.Count() > 0)
{
// Initialization.
var logindetails = loginInfo.First();
// Login In.
this.SignInUser(logindetails.username, false);
// Info.
return this.RedirectToLocal(returnUrl);
}
else
{
// Setting.
ModelState.AddModelError(string.Empty, "Invalid username or password.");
}
}
}
catch (Exception ex)
{
// Info
Console.Write(ex);
}
// If we got this far, something failed, redisplay form.
return this.View(model);
}
#endregion
The following piece of code is used to sign off users from our application.
#region Log Out method
/// <summary>
/// POST: /Account/LogOff
/// </summary>
/// <returns>Return log off action</returns>
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult LogOff()
{
try
{
// Setting.
var ctx = Request.GetOwinContext();
var authenticationManager = ctx.Authentication;
// Sign Out.
authenticationManager.SignOut();
}
catch (Exception ex)
{
// Info
throw ex;
}
// Info.
return this.RedirectToAction("Login", "Account");
}
#endregion
The following piece of code claims our identity for authentication with OWIN middleware security layer.
#region Sign In method
/// <summary>
/// Sign In User method.
/// </summary>
/// <param name="username">Username parameter.</param>
/// <param name="isPersistent">Is persistent parameter.</param>
private void SignInUser(string username, bool isPersistent)
{
// Initialization.
var claims = new List<Claim>();
try
{
// Setting
claims.Add(new Claim(ClaimTypes.Name, username));
var claimIdenties = new ClaimsIdentity(claims, DefaultAuthenticationTypes.ApplicationCookie);
var ctx = Request.GetOwinContext();
var authenticationManager = ctx.Authentication;
// Sign In.
authenticationManager.SignIn(new AuthenticationProperties() { IsPersistent = isPersistent }, claimIdenties);
}
catch (Exception ex)
{
// Info
throw ex;
}
}
#endregion
This is how the application will look like now.
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.