Saturday, November 4, 2017

MVP, Logging in to the Database Using a stored procedure

Overview

The goal of this tutorial is to show how to log in to the BookReviewDB using the stored procedure usp_Reviewerlogin. Microsoft's MVC template has an authentication setup called "Identity" that allows the developer to authenticate users and specify which methods in the controllers can only be accessed by authenticated users. To do this it makes its own local database to store users. Our database though has its own list of users and passwords, so we don't want to use the built in systems.

This tutorial is going to contain just the login and the authentication. When a user successfully logs in, the program will return their information including the reviewerkey to a response page. This key can be used as a criteria for allowing the user to post reviews. If the key is absent (equals 0), they will be directed to retry the login or register.

For this tutorial, also, I won't repeat all the setup. It assumes that you have an MVC 5 project, a connection with the database through ADO Entities and that you have included the stored procedure usp_ReviewerLogin in the Entities.

If you need a review of the setup, look at the MVC, What you get for Free blog entry.

Creating a login class

The first thing I am going to do is add a LoginClass to the Model. This class will contain what is needed for the login: the user name and the password. It will also contain the ReviewerKey, which will be assigned when the user is successfully authenticated.

Right click on the Models folder and add a class. Name it LoginClass.

Add the fields to the class with their gets and sets.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace BookReviewLoginExample.Models
{
    public class LoginClass
    {
        public string UserName { get; set; }
        public string Password { get; set; }
        public int ReviewerKey { get; set; }
    }
}

Adding a Controller

Next we will add a controller for the login. Right click on the Controller folder and choose Add Controller. We will call it LoginController and it will be empty.

We get this code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace BookReviewLoginExample.Controllers
{
    public class LoginController : Controller
    {
        // GET: Login
        public ActionResult Index()
        {
            return View();
        }
    }
}

We could change the name of the method, but I will leave it at Index. (The name must match the name of the view we create.). I am going to leave the first Index method as it is. You need an essentially empty method to first load the view. But we are going to add a second index method that takes a LoginClass as an argument. To get access to the Login class we must add a using statement that gives access to the Model. We must also add the directives stating that it is an HTTP Post and a validate anti forgery token. Otherwise it won't allow a second Index method. The idea is that the first Index method will load the empty login form. The second Index method handles the results of clicking the submit button.

In the second Index:

  • we receive the LoginClass from the submit.
  • We pass it to the stored procedure to validate.
  • If it validates, we get the ReviewKey and assign it to the field in the LoginClass
  • Then we pass the class with the key to the Result view
  • If it fails to validate, the key will be 0. We still pass it to the Result veiw

We will look at the result method after we are finished with the index. So here is the second Index code with comments.

 //post and validate directives
        [HttpPost]

        [ValidateAntiForgeryToken]
        //overloaded Index method
        public ActionResult Index([Bind(Include ="UserName, Password, ReviewerKey")]LoginClass loginClass)
        {
            //make connection to Ado Entity model classes
            BookReviewDbEntities br = new BookReviewDbEntities();
            //Assign review key a value of 0
            loginClass.ReviewerKey = 0;
            //pass the values to the stored procedure and get result (-1 = failure)
            int result = br.usp_ReviewerLogin(loginClass.UserName, loginClass.Password);
            //test the results
            if(result != -1)
            {
                //run a query to get the ReviewerKey
                var ukey = (from r in br.Reviewers
                            where r.ReviewerUserName.Equals(loginClass.UserName)
                            select r.ReviewerKey).FirstOrDefault();
                loginClass.ReviewerKey = (int)ukey;
            }

            //return the class to the Result view
            return View("Result", loginClass);
        }
    }
}

Next we must add a method for the Result View. It is really very simple. We just have to pass through the loginClass.

  public ActionResult Result(LoginClass loginClass)
        {
            return View(loginClass);
        }

Here is the whole controller:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using BookReviewLoginExample.Models; //access to Models

namespace BookReviewLoginExample.Controllers
{
    public class LoginController : Controller
    {
        // GET: Login
        public ActionResult Index()
        {
            return View();
        }

        //post and validate directives
        [HttpPost]

        [ValidateAntiForgeryToken]
        //overloaded Index method
        public ActionResult Index([Bind(Include ="UserName, Password, ReviewerKey")]LoginClass loginClass)
        {
            //make connection to Ado Entity model classes
            BookReviewDbEntities br = new BookReviewDbEntities();
            //Assign review key a value of 0
            loginClass.ReviewerKey = 0;
            //pass the values to the stored procedure and get result (-1 = failure)
            int result = br.usp_ReviewerLogin(loginClass.UserName, loginClass.Password);
            //test the results
            if(result != -1)
            {
                //run a query to get the ReviewerKey
                var ukey = (from r in br.Reviewers
                            where r.ReviewerUserName.Equals(loginClass.UserName)
                            select r.ReviewerKey).FirstOrDefault();
                loginClass.ReviewerKey = (int)ukey;
            }

            //return the class to the Result view
            return View("Result", loginClass);
        }

        public ActionResult Result(LoginClass loginClass)
        {
            return View(loginClass);
        }
    }


}

Adding Views

Now we need to add the Index and the Result views.

If you expand the Views folder you will see that when we created our LoginController it also created a Views folder called login.

Right click on the views folder and add a view. We need to name it "Index."

This is what we get:

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

First we need to reference the model. Add this at the top of the page

@model BookReviewLoginExample.Models.LoginClass

Next we need to add the form with fields for user name and password. Here is the whole page with the form completed.

@model BookReviewLoginExample.Models.LoginClass
@{
    ViewBag.Title = "Index";
}

<h2>Login</h2>


<h4>Please Login</h4>

<hr />

@using (Html.BeginForm())

{

    @Html.AntiForgeryToken()
    @Html.ValidationSummary(true, "", new { @class = "text-danger" })
    <div class="form-horizontal">
        <div class="form-group">

            @Html.LabelFor(model => model.UserName, htmlAttributes: new { @class = "control-label col-md-2" })

            <div class="col-md-10">

                @Html.EditorFor(model => model.UserName, new { htmlAttributes = new { @class = "form-control" } })

                @Html.ValidationMessageFor(model => model.UserName, "", new { @class = "text-danger" })

            </div>
        </div>
        <div class="form-group">

            @Html.LabelFor(model => model.Password, htmlAttributes: new { @class = "control-label col-md-2" })

            <div class="col-md-10">

                @Html.EditorFor(model => model.Password, new { htmlAttributes = new { @class = "form-control" } })

                @Html.ValidationMessageFor(model => model.Password, "", new { @class = "text-danger" })

            </div>

        </div>


        <div class="form-group">

            <div class="col-md-offset-2 col-md-10">

                <input type="submit" value="Login" class="btn btn-default" />

            </div>

        </div>

    </div>
}
@section Scripts {

    @Scripts.Render("~/bundles/jqueryval")

}

Before we can run it, we must also make the Result View. In that view we must test to see what the Reviewer key is. If it is not 0 we can welcome them, if it is 0 we should tell them to retry or register.

Again, like before, right click on the Login folder and add a new view. It must be named "Result". The view name and the method name in the controller must be the same. Again we will add the Model reference to the top. Then we will test the key value. Here is the code for the Result page:

@model BookReviewLoginExample.Models.LoginClass
@{
    ViewBag.Title = "Result";
}

<h2>Result</h2>

@if(Model.ReviewerKey != 0)
{
    <p>Welcome, @Html.DisplayFor(mode=>Model.UserName), Please feel free to review a book.</p>
}
else
{
   <p>Invalid login, please try to login again or register as a Reviewer.</p>
}

The Next step is to test all this. From the database I know that Wally Lewis is a registered Reviewer. His user name is "wlewis" and his password is "wlewispass." First, I will try a successful login. Run this from the Index page.

Now let's try an unsuccessful login

The next step, which I am not going to include here, is to provide links to redirect the user to add a review or back to login. When directing a reviewer to the form to add reviews the syntax would be something like this, and would pass the key to the next page:

@Html.ActionLink("Review", "Review", new { id=model.ReviewerKey }) |
at

No comments:

Post a Comment