ASP.NET MVC 5: Integrating Existing Database With Login Using ADO.NET Database First Approach

1. Under the "Controller" folder delete the "HomeController.cs" file.
2. Under the "Model" folder delete the "IdentitModel.cs" file.
3. Under the "App_Start" folder delete the "IdentityConfig.cs" file.
4. Under the "Views->Account" folder delete all files except the "Login.cshtml" file.
5. Under the "Views->Home" folder delete all files except the "Index.cshtml" file.
6. 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.