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

Microsoft MVC paradigm claims to ease out a lot of basic web application processes such as local login work flow, user registration work flow, sign in work flow, using external logins such as Facebook, Twitter, LinkedIn etc. We all 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 that how will we integrate our existing database with 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 huge amount of investment towards data science that is generally done on database level rather than code level.

So, for today's discussion, I will be demonstrating the following:

  1. How to integrate existing database in ASP.NET MVC 5 web application using ADO.NET database first approach.
  2. How to configure simple login work flow for integrating existing logins with the ASP.NET MVC 5 web application.

    login

the following are some prerequisites before you proceed any further in this tutorial:

Prerequisites:

Before moving further, you should have knowledge about the following: 
  1. ASP.NET MVC 5
  2. ADO.NET
  3. Entity Framework
  4. OWIN
  5. Claim Base Identity Model
  6. C Programming
  7. 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 database.

Let’s Begin now.

  1. First you need to create a sample database with "Login" table, I am using the following scripts to generate my sample database. My database name is "AdoNetIntegration", below is the snippet for it
    1. USE [AdoNetIntegration]   
    2. GO   
    3. /****** Object: StoredProcedure [dbo].[LoginByUsernamePassword] Script Date: 03/15/2016 21:33:52 ******/   
    4. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoginByUsernamePassword]'AND type in (N'P', N'PC'))   
    5. DROP PROCEDURE [dbo].[LoginByUsernamePassword]   
    6. GO   
    7. /****** Object: Table [dbo].[Login] Script Date: 03/15/2016 21:33:50 ******/   
    8. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Login]'AND type in (N'U'))   
    9. DROP TABLE [dbo].[Login]   
    10. GO   
    11. /****** Object: Table [dbo].[Login] Script Date: 03/15/2016 21:33:50 ******/   
    12. SET ANSI_NULLS ON   
    13. GO   
    14. SET QUOTED_IDENTIFIER ON   
    15. GO   
    16. SET ANSI_PADDING ON   
    17. GO   
    18. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Login]'AND type in (N'U'))   
    19. BEGIN   
    20. CREATE TABLE [dbo].[Login](   
    21. [id] [int] IDENTITY(1,1) NOT NULL,   
    22. [username] [varchar](50) NOT NULL,   
    23. [password] [varchar](50) NOT NULL,   
    24. CONSTRAINT [PK_Login] PRIMARY KEY CLUSTERED   
    25. (   
    26. [id] ASC   
    27. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]   
    28. ON [PRIMARY]   
    29. END   
    30. GO   
    31. SET ANSI_PADDING OFF   
    32. GO   
    33. SET IDENTITY_INSERT [dbo].[Login] ON   
    34. INSERT [dbo].[Login] ([id], [username], [password]) VALUES (1, N'my-login', N'my-password-123')   
    35. SET IDENTITY_INSERT [dbo].[Login] OFF   
    36. /****** Object: StoredProcedure [dbo].[LoginByUsernamePassword] Script Date: 03/15/2016 21:33:52 ******/   
    37. SET ANSI_NULLS ON   
    38. GO   
    39. SET QUOTED_IDENTIFIER ON   
    40. GO   
    41. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoginByUsernamePassword]'AND type in (N'P', N'PC'))   
    42. BEGIN   
    43. EXEC dbo.sp_executesql @statement = N'-- =============================================   
    44. -- Author: <Author,,Asma Khalid>  
    45. -- Create date: <Create Date,,15-Mar-2016>  
    46. -- Description: <Description,,You are Allow to Distribute this Code>  
    47. -- =============================================   
    48. CREATE PROCEDURE [dbo].[LoginByUsernamePassword]   
    49. @username varchar(50),   
    50. @password varchar(50)   
    51. AS   
    52. BEGIN   
    53. SELECT id, username, password   
    54. FROM Login   
    55. WHERE username = @username   
    56. AND password = @password   
    57. END   
    58. '   
    59. END   
    60. 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 new ASP.MVC 5 project with sample template and look at few things here.

  2. Create new Visual Studio Web MVC project and execute it. You will see the following on the web browser:

    mvc

    aspnet


    This is the basic template for ASP.MVC5.

  3. Let's observe 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 database. So, where will the database go when we actually use this template to register a login? Open "Server Explorer" window and observe that there is nothing under "Data Connection". Observe that the "App_Data" folder in "Solution Explorer" window is also empty as shown below:

    connection


  4. Lets register a random login and sign-in into this sample application as shown below:

    log in


    register

    aspnet


    Here, after creating a login, you will automatically get sign-in into this application and can see your sign-in email at top right before log off.

  5. You will now see a new connection string in "Web.config" as shown below:

    xml


  6. You will also see new database created in "App_Data" folder which is not included in the solution explorer as shown below:

    app data
    default


  7. Notice that in "Server Explorer" window, there is a new connection available under "Data Connection" and it is now expandable as shown below:

    connection


  8. Expand the "Data Connection" and you will see some tables created, these tables are created by the MVC user management automatic scaffold and is integrated with OWIN middle ware 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, lets delete this database and remove connection string information for it from the "Web.config" file as shown below.

  9. Now, right click on the project in "Solution Explorer" then select "Add" and click "ADO.NET Entity Data Model"as shown below:

    model


  10. Now, select "EF Designer from Database" option and click next as shown below:

    efd


  11. On the next window, click "New Connection", "Connection Properties" windows will appear, provide your SQL server credentials and select the database "AdoNetIntegration" which we have created earlier and click OK as shown below:

    connection


    properties


  12. Choose "yes, include sensitive data in connection string" to include your SQL server database credential into your application and click next as shown below:

    choose


  13. 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:

    tables


    efd


    You can observe here that after including my store procedure, 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 simple login work flow that is integrated with our database and uses OWIN middle ware security layer for secure authorization.

  14. Delete the following files from the project as we do not need them:

    1. Under "Controller" folder delete "HomeController.cs" file.
    2. Under "Model" folder delete "IdentitModel.cs" file.
    3. Under "App_Start" folder delete "IdentityConfig.cs" file.
    4. Under "Views->Account" folder delete all files except "Login.cshtml" file.
    5. Under "Views->Home" folder delete all files except "Index.cshtml" file.
    6. Under "Views->Shared" folder delete all files except "_Layout.cshtml, _LoginPartial.cshtml & Error.cshtml" files.

  15. Now, open "RouteConfig.cs" file under "App_Start" folder and replace existing code with the following:
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Web;  
    5. using System.Web.Mvc;  
    6. using System.Web.Routing;  
    7. namespace AodNetIntegration  
    8. {  
    9.     public class RouteConfig   
    10.     {  
    11.         public static void RegisterRoutes(RouteCollection routes)   
    12.       {  
    13.             routes.IgnoreRoute("{resource}.axd/{*pathInfo}");  
    14.             routes.MapRoute(  
    15.                 name: "Default",  
    16.                 url: "{controller}/{action}/{id}",  
    17.                 defaults: new  
    18.               {  
    19.                     controller = "Account", action = "Login", id = UrlParameter.Optional  
    20.                 }  
    21.             );  
    22.         }  
    23.     }  
    24. }  
    Here, I change my default controller to "Account" & action to "Login".

  16. Now, open "Startup.Auth.cs" file and replace existing code with the following:
    1. using Microsoft.AspNet.Identity;  
    2. using Microsoft.AspNet.Identity.EntityFramework;  
    3. using Microsoft.AspNet.Identity.Owin;  
    4. using Microsoft.Owin;  
    5. using Microsoft.Owin.Security.Cookies;  
    6. using Microsoft.Owin.Security.DataProtection;  
    7. using Microsoft.Owin.Security.Google;  
    8. using Owin;  
    9. using System;  
    10. using AodNetIntegration.Models;  
    11. namespace AodNetIntegration  
    12. {  
    13.     public partial class Startup  
    14.     {  
    15.         // For more information on configuring authentication, please visit http://go.microsoft.com/fwlink/?LinkId=301864   
    16.         public void ConfigureAuth(IAppBuilder app)  
    17.       {  
    18.             // Enable the application to use a cookie to store information for the signed in user   
    19.             // and to use a cookie to temporarily store information about a user logging in with a third party login provider   
    20.             // Configure the sign in cookie   
    21.             app.UseCookieAuthentication(new CookieAuthenticationOptions  
    22.             {  
    23.                 AuthenticationType = DefaultAuthenticationTypes.ApplicationCookie,  
    24.                     LoginPath = new PathString("/Account/Login"),  
    25.                     LogoutPath = new PathString("/Account/LogOff"),  
    26.                     ExpireTimeSpan = TimeSpan.FromMinutes(5.0)  
    27.             });  
    28.             app.UseExternalSignInCookie(DefaultAuthenticationTypes.ExternalCookie);  
    29.             // Uncomment the following lines to enable logging in with third party login providers   
    30.             //app.UseMicrosoftAccountAuthentication(   
    31.             // clientId: "",   
    32.             // clientSecret: "");   
    33.             //app.UseTwitterAuthentication(   
    34.             // consumerKey: "",   
    35.             // consumerSecret: "");   
    36.             //app.UseFacebookAuthentication(   
    37.             // appId: "",   
    38.             // appSecret: "");   
    39.             //app.UseGoogleAuthentication(new GoogleOAuth2AuthenticationOptions()   
    40.             //{   
    41.             // ClientId = "",   
    42.             // ClientSecret = ""   
    43.             //});   
    44.         }  
    45.     }  
    46. }  
    Here, we cleanup few things add "Logoff" path and login expiration after 5 minutes options.

  17. Open "Global.asax.cs" file and add the following code at the end of "Application_Start" method:
    1. // Fixing claim issue. https://stack247.wordpress.com/2013/02/22/antiforgerytoken-a-claim-of-type-nameidentifier-or-identityprovider-was-not-present-on-provided-claimsidentity/   
    2. AntiForgeryConfig.UniqueClaimTypeIdentifier = ClaimTypes.Name;   
    We add this line of code to save you from "Name Claim Identity" exception which occurs when you provide your user name for identity claim property in order to use OWIN middle ware security layer for secure authorization.

  18. Open "_Layout.cshtml" file under "Views->Shared" and replace existing code with the following:
    1. <!DOCTYPE html>  
    2. <html>  
    3.   
    4. <head>  
    5.     <meta charset="utf-8" />  
    6.     <meta name="viewport" content="width=device-width, initial-scale=1.0">  
    7.     <title>@ViewBag.Title</title>  
    8.     @Styles.Render("~/Content/css") @Scripts.Render("~/bundles/modernizr")  
    9. </head>  
    10.   
    11. <body>  
    12.     <div class="navbar navbar-inverse navbar-fixed-top">  
    13.         <div class="container">  
    14.             <div class="navbar-collapse collapse">  
    15.                 @Html.Partial("_LoginPartial")  
    16.             </div>  
    17.         </div>  
    18.     </div>  
    19.     <div class="container body-content">  
    20.         @RenderBody()  
    21.         <hr />  
    22.         <footer>  
    23.             <center>  
    24.                 <p><strong>Copyright © @DateTime.Now.Year - <a href="http://asmak9.blogspot.com/">Asma's Blog</a>.</strong> All rights reserved.</p>  
    25.             </center>  
    26.         </footer>  
    27.     </div>  
    28.     @Scripts.Render("~/bundles/jquery") @Scripts.Render("~/bundles/bootstrap") @RenderSection("scripts", required: false)  
    29. </body>  
    30.   
    31. </html>  

  19. Open "_LoginPartial.cshtml" file under "Views, then Shared" folder and replace with the following code:
    1. @ * @using Microsoft.AspNet.Identity * @  
    2. @if(Request.IsAuthenticated)  
    3. {  
    4.     using(Html.BeginForm("LogOff""Account", FormMethod.Post, new   
    5.     {  
    6.         id = "logoutForm", @class = "navbar-right"  
    7.     }))  
    8.     {  
    9.         @Html.AntiForgeryToken() < ul class = "nav navbar-nav navbar-right" >  
    10.             < li >  
    11.             @Html.ActionLink("Hello " + User.Identity.Name + "!""Index""Home", routeValues: null, htmlAttributes: new  
    12.             {  
    13.                 title = "Manage"  
    14.             }) < /li> < li > < a href = "javascript:document.getElementById('logoutForm').submit()" > Log off < /a></li >  
    15.             < /ul>  
    16.     }  
    17. }  
    18. else { < ul class = "nav navbar-nav navbar-right" >  
    19.         < li > @Html.ActionLink("Log in""Login""Account", routeValues: null, htmlAttributes: new  
    20.         {  
    21.             id = "loginLink"  
    22.         }) < /li> < /ul>  
    23. }  
  20. Now, open "Index.cshtml" under "Views->Home" folder and replace with the following code:
    1. @{   
    2. ViewBag.Title = "ADO.NET Integration - Home";   
    3. }   
    4. <div class="jumbotron">  
    5. <h1>Welcome</h1>  
    6. <p class="lead">Login from "@User.Identity.Name" Account.</p>  
    7. </div>  
  21. Now open "Login.cshtml" file under "Views->Account" folder and replace with the following code:
    1. @using AodNetIntegration.Models  
    2. @model LoginViewModel  
    3. @ {  
    4.     ViewBag.Title = "ADO.NET Integration - Login";  
    5. } < h2 > @ViewBag.Title. < /h2> < div class = "row" >  
    6.     < div class = "col-md-8" >  
    7.     < section id = "loginForm" >  
    8.     @using(Html.BeginForm("Login""Account"new   
    9.     {  
    10.         ReturnUrl = ViewBag.ReturnUrl  
    11.     }, FormMethod.Post, new {  
    12.         @class = "form-horizontal", role = "form"  
    13.     })) {  
    14.         @Html.AntiForgeryToken() < h4 > Use a local account to log in . < /h4> < hr / >  
    15.             @Html.ValidationSummary(true""new  
    16.             {  
    17.                 @class = "text-danger"  
    18.             }) < div class = "form-group" >  
    19.             @Html.LabelFor(m => m.Username, new  
    20.             {  
    21.                 @class = "col-md-2 control-label"  
    22.             }) < div class = "col-md-10" >  
    23.             @Html.TextBoxFor(m => m.Username, new  
    24.             {  
    25.                 @class = "form-control"  
    26.             })  
    27.         @Html.ValidationMessageFor(m => m.Username, ""new  
    28.             {  
    29.                 @class = "text-danger"  
    30.             }) < /div> < /div> < div class = "form-group" >  
    31.             @Html.LabelFor(m => m.Password, new {  
    32.                 @class = "col-md-2 control-label"  
    33.             }) < div class = "col-md-10" >  
    34.             @Html.PasswordFor(m => m.Password, new  
    35.             {  
    36.                 @class = "form-control"  
    37.             })  
    38.         @Html.ValidationMessageFor(m => m.Password, ""new  
    39.             {  
    40.                 @class = "text-danger"  
    41.             }) < /div> < /div> < div class = "form-group" >  
    42.             < div class = "col-md-offset-2 col-md-10" >  
    43.             < input type = "submit"  
    44.         value = "Log in"  
    45.         class = "btn btn-default" / >  
    46.             < /div> < /div>  
    47.     } < /section> < /div> < /div>  
    48. @section Scripts  
    49. {  
    50.     @Scripts.Render("~/bundles/jqueryval")  
    51. }  

  22. Now, open "AccountViewModels.cs" file under "Model" folder and replace it with the following code:
    1. using System.ComponentModel.DataAnnotations;  
    2. namespace AodNetIntegration.Models  
    3. {  
    4.     public class LoginViewModel  
    5.     {  
    6.         [Required]  
    7.         [Display(Name = "Username")]  
    8.         public string Username  
    9.         {  
    10.             get;  
    11.             set;  
    12.         }  
    13.         [Required]  
    14.         [DataType(DataType.Password)]  
    15.         [Display(Name = "Password")]  
    16.         public string Password   
    17.         {  
    18.             get;  
    19.             set;  
    20.         }  
    21.     }  
    22. }  

    Here, we have simply tweak the model according to our need.

  23. Now, open "HomeController.cs" file under "Controller" folder and replace it with the following code, do also observe here that we have used "[Authorize]" attribute at controller level to secure access to our home controller for authenticated users only:
    1. // <copyright file="HomeController.cs" company="None">  
    2. // Copyright (c) Allow to distribute this code.   
    3. // </copyright>  
    4. // <author>Asma Khalid</author>  
    5. //-----------------------------------------------------------------------   
    6. namespace AodNetIntegration.Controllers  
    7. {  
    8.     using System;  
    9.     using System.Collections.Generic;  
    10.     using System.Linq;  
    11.     using System.Web;  
    12.     using System.Web.Mvc;  
    13.     /// <summary>  
    14.     /// Home controller class.   
    15.     /// </summary>  
    16.     [Authorize]  
    17.     public class HomeController: Controller  
    18.     {  
    19.       #region Index method.  
    20.             /// <summary>  
    21.             /// Index method.   
    22.             /// </summary>  
    23.             /// <returns>Returns - Index view</returns>  
    24.         public ActionResult Index()  
    25.         {  
    26.             return this.View();  
    27.         }#endregion  
    28.     }  
    29. }  

  24. Now, open "AccountController.cs" file under "Controller" folder and replace it with the following code:
    1. //-----------------------------------------------------------------------    
    2. // <copyright file="AccountController.cs" company="None">  
    3. //   Copyright (c) Allow to distribute this code.    
    4. // </copyright>  
    5. // <author>Asma Khalid</author>  
    6. //-----------------------------------------------------------------------    
    7. namespace AppTracker.Controllers    
    8. {    
    9.   using System;    
    10.   using System.Collections.Generic;    
    11.   using System.Linq;    
    12.   using System.Security.Claims;    
    13.   using System.Web;    
    14.   using System.Web.Mvc;    
    15.   using AodNetIntegration;    
    16.   using AodNetIntegration.Models;    
    17.   using Microsoft.AspNet.Identity;    
    18.   using Microsoft.Owin.Security;    
    19.   /// <summary>  
    20.   /// Account controller class.    
    21.   /// </summary>  
    22.   public class AccountController : Controller    
    23.   {    
    24.     #region Private Properties    
    25.     /// <summary>  
    26.     /// Database Store property.    
    27.     /// </summary>  
    28.     private AdoNetIntegrationEntities databaseManager = new AdoNetIntegrationEntities();    
    29.     #endregion    
    30.     #region Default Constructor    
    31.     /// <summary>  
    32.     /// Initializes a new instance of the <see cref="AccountController" /> class.    
    33.     /// </summary>  
    34.     public AccountController()    
    35.     {    
    36.     }    
    37.     #endregion    
    38.     #region Login methods    
    39.     /// <summary>  
    40.     /// GET: /Account/Login    
    41.     /// </summary>  
    42.     /// <param name="returnUrl">Return URL parameter</param>  
    43.     /// <returns>Return login view</returns>  
    44.     [AllowAnonymous]    
    45.     public ActionResult Login(string returnUrl)    
    46.     {    
    47.       try    
    48.       {    
    49.         // Verification.    
    50.         if (this.Request.IsAuthenticated)    
    51.         {    
    52.           // Info.    
    53.           return this.RedirectToLocal(returnUrl);    
    54.         }    
    55.       }    
    56.       catch (Exception ex)    
    57.       {    
    58.         // Info    
    59.         Console.Write(ex);    
    60.       }    
    61.       // Info.    
    62.       return this.View();    
    63.     }    
    64.     /// <summary>  
    65.     /// POST: /Account/Login    
    66.     /// </summary>  
    67.     /// <param name="model">Model parameter</param>  
    68.     /// <param name="returnUrl">Return URL parameter</param>  
    69.     /// <returns>Return login view</returns>  
    70.     [HttpPost]    
    71.     [AllowAnonymous]    
    72.     [ValidateAntiForgeryToken]    
    73.     public ActionResult Login(LoginViewModel model, string returnUrl)    
    74.     {    
    75.       try    
    76.       {    
    77.         // Verification.    
    78.         if (ModelState.IsValid)    
    79.         {    
    80.           // Initialization.    
    81.           var loginInfo = this.databaseManager.LoginByUsernamePassword(model.Username, model.Password).ToList();    
    82.           // Verification.    
    83.           if (loginInfo != null && loginInfo.Count() > 0)    
    84.           {    
    85.             // Initialization.    
    86.             var logindetails = loginInfo.First();    
    87.             // Login In.    
    88.             this.SignInUser(logindetails.username, false);    
    89.             // Info.    
    90.             return this.RedirectToLocal(returnUrl);    
    91.           }    
    92.           else    
    93.           {    
    94.             // Setting.    
    95.             ModelState.AddModelError(string.Empty, "Invalid username or password.");    
    96.           }    
    97.         }    
    98.       }    
    99.       catch (Exception ex)    
    100.       {    
    101.         // Info    
    102.         Console.Write(ex);    
    103.       }    
    104.       // If we got this far, something failed, redisplay form    
    105.       return this.View(model);    
    106.     }    
    107.     #endregion    
    108.     #region Log Out method.    
    109.     /// <summary>  
    110.     /// POST: /Account/LogOff    
    111.     /// </summary>  
    112.     /// <returns>Return log off action</returns>  
    113.     [HttpPost]    
    114.     [ValidateAntiForgeryToken]    
    115.     public ActionResult LogOff()    
    116.     {    
    117.       try    
    118.       {    
    119.         // Setting.    
    120.         var ctx = Request.GetOwinContext();    
    121.         var authenticationManager = ctx.Authentication;    
    122.         // Sign Out.    
    123.         authenticationManager.SignOut();    
    124.       }    
    125.       catch (Exception ex)    
    126.       {    
    127.         // Info    
    128.         throw ex;    
    129.       }    
    130.       // Info.    
    131.       return this.RedirectToAction("Login""Account");    
    132.     }    
    133.     #endregion    
    134.     #region Helpers    
    135.     #region Sign In method.    
    136.     /// <summary>  
    137.     /// Sign In User method.    
    138.     /// </summary>  
    139.     /// <param name="username">Username parameter.</param>  
    140.     /// <param name="isPersistent">Is persistent parameter.</param>  
    141.     private void SignInUser(string username, bool isPersistent)    
    142.     {    
    143.       // Initialization.    
    144.       var claims = new List<Claim>();    
    145.       try    
    146.       {    
    147.         // Setting    
    148.         claims.Add(new Claim(ClaimTypes.Name, username));    
    149.         var claimIdenties = new ClaimsIdentity(claims, DefaultAuthenticationTypes.ApplicationCookie);    
    150.         var ctx = Request.GetOwinContext();    
    151.         var authenticationManager = ctx.Authentication;    
    152.         // Sign In.    
    153.         authenticationManager.SignIn(new AuthenticationProperties() { IsPersistent = isPersistent }, claimIdenties);    
    154.       }    
    155.       catch (Exception ex)    
    156.       {    
    157.         // Info    
    158.         throw ex;    
    159.       }    
    160.     }    
    161.     #endregion    
    162.     #region Redirect to local method.    
    163.     /// <summary>  
    164.     /// Redirect to local method.    
    165.     /// </summary>  
    166.     /// <param name="returnUrl">Return URL parameter.</param>  
    167.     /// <returns>Return redirection action</returns>  
    168.     private ActionResult RedirectToLocal(string returnUrl)    
    169.     {    
    170.       try    
    171.       {    
    172.         // Verification.    
    173.         if (Url.IsLocalUrl(returnUrl))    
    174.         {    
    175.           // Info.    
    176.           return this.Redirect(returnUrl);    
    177.         }    
    178.       }    
    179.       catch (Exception ex)    
    180.       {    
    181.         // Info    
    182.         throw ex;    
    183.       }    
    184.       // Info.    
    185.       return this.RedirectToAction("Index""Home");    
    186.     }    
    187.     #endregion    
    188.     #endregion    
    189.   }    
    190. }  
    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:
    1. #region Private Properties    
    2.      /// <summary>  
    3.      /// Database Store property.    
    4.      /// </summary>  
    5.      private AdoNetIntegrationEntities databaseManager = new AdoNetIntegrationEntities();    
    6.      #endregion    
    The following piece of code will create our default action with both get and post methods, also notice in 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:
    1. #region Login methods    
    2.      /// <summary>  
    3.      /// GET: /Account/Login    
    4.      /// </summary>  
    5.      /// <param name="returnUrl">Return URL parameter</param>  
    6.      /// <returns>Return login view</returns>  
    7.      [AllowAnonymous]    
    8.      public ActionResult Login(string returnUrl)    
    9.      {    
    10.        try    
    11.        {    
    12.          // Verification.    
    13.          if (this.Request.IsAuthenticated)    
    14.          {    
    15.            // Info.    
    16.            return this.RedirectToLocal(returnUrl);    
    17.          }    
    18.        }    
    19.        catch (Exception ex)    
    20.        {    
    21.          // Info    
    22.          Console.Write(ex);    
    23.        }    
    24.        // Info.    
    25.        return this.View();    
    26.      }    
    27.      /// <summary>  
    28.      /// POST: /Account/Login    
    29.      /// </summary>  
    30.      /// <param name="model">Model parameter</param>  
    31.      /// <param name="returnUrl">Return URL parameter</param>  
    32.      /// <returns>Return login view</returns>  
    33.      [HttpPost]    
    34.      [AllowAnonymous]    
    35.      [ValidateAntiForgeryToken]    
    36.      public ActionResult Login(LoginViewModel model, string returnUrl)    
    37.      {    
    38.        try    
    39.        {    
    40.          // Verification.    
    41.          if (ModelState.IsValid)    
    42.          {    
    43.            // Initialization.    
    44.            var loginInfo = this.databaseManager.LoginByUsernamePassword(model.Username, model.Password).ToList();    
    45.            // Verification.    
    46.            if (loginInfo != null && loginInfo.Count() > 0)    
    47.            {    
    48.              // Initialization.    
    49.              var logindetails = loginInfo.First();    
    50.              // Login In.    
    51.              this.SignInUser(logindetails.username, false);    
    52.              // Info.    
    53.              return this.RedirectToLocal(returnUrl);    
    54.            }    
    55.            else    
    56.            {    
    57.              // Setting.    
    58.              ModelState.AddModelError(string.Empty, "Invalid username or password.");    
    59.            }    
    60.          }    
    61.        }    
    62.        catch (Exception ex)    
    63.        {    
    64.          // Info    
    65.          Console.Write(ex);    
    66.        }    
    67.        // If we got this far, something failed, redisplay form    
    68.        return this.View(model);    
    69.      }    
    70.      #endregion  
    The following piece of code is used to sign-off user from our application:
    1. #region Log Out method.    
    2. /// <summary>  
    3. /// POST: /Account/LogOff    
    4. /// </summary>  
    5. /// <returns>Return log off action</returns>  
    6. [HttpPost]    
    7. [ValidateAntiForgeryToken]    
    8. public ActionResult LogOff()    
    9. {    
    10.   try    
    11.   {    
    12.     // Setting.    
    13.     var ctx = Request.GetOwinContext();    
    14.     var authenticationManager = ctx.Authentication;    
    15.     // Sign Out.    
    16.     authenticationManager.SignOut();    
    17.   }    
    18.   catch (Exception ex)    
    19.   {    
    20.     // Info    
    21.     throw ex;    
    22.   }    
    23.   // Info.    
    24.   return this.RedirectToAction("Login""Account");    
    25. }    
    26. #endregion    

    The following piece of code claims our identity for authentication with OWIN middle ware security layer:
    1. #region Sign In method.    
    2. /// <summary>  
    3. /// Sign In User method.    
    4. /// </summary>  
    5. /// <param name="username">Username parameter.</param>  
    6. /// <param name="isPersistent">Is persistent parameter.</param>  
    7. private void SignInUser(string username, bool isPersistent)    
    8. {    
    9.   // Initialization.    
    10.   var claims = new List<Claim>();    
    11.   try    
    12.   {    
    13.     // Setting    
    14.     claims.Add(new Claim(ClaimTypes.Name, username));    
    15.     var claimIdenties = new ClaimsIdentity(claims, DefaultAuthenticationTypes.ApplicationCookie);    
    16.     var ctx = Request.GetOwinContext();    
    17.     var authenticationManager = ctx.Authentication;    
    18.     // Sign In.    
    19.     authenticationManager.SignIn(new AuthenticationProperties() { IsPersistent = isPersistent }, claimIdenties);    
    20.   }    
    21.   catch (Exception ex)    
    22.   {    
    23.     // Info    
    24.     throw ex;    
    25.   }    
    26. }    
    27. #endregion    

    This is how the application will look like now:

    login

    welcome

Conclusion

This article is about integrating existing databases with ASP.NET MVC5 web application using database first approach along with a creation of basic login/logoff work flow. So, you have learned, how to integrate existing database in ASP.NET MVC 5 web application using ADO.NET database first approach and how to configure simple login work flow for integrating existing logins with the ASP.NET MVC 5 web application with OWIN middle ware secure authorization mechanism.


Similar Articles