ASP.NET MVC5 - Validate against other table

Question

Using VS2013 and building my first MVC app with EF6 (database first). I have a jobs table and a related items table (there can be millions of records per job). I need to give the user a way to export a subset of items (e.g. item 1,000 - 10,000).

So my controller contains a get method that opens a new view where they can enter the start and end values.

I want to default these to the min and max values from the items table and then I need to validate that the two numbers entered exist in the items table.

Here's my view:

@model PSAMVC.Models.Job

@{
    ViewBag.Title = "ExportToLake";
}

<h2>ExportToLake</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Job</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.ID)

        <div class="form-group">
            @Html.LabelFor(model => model.JobNo, "JobNo", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DisplayFor(model => model.JobNo, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.VersionRef, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DisplayFor(model => model.VersionRef, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.PSAJobRef, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.DisplayFor(model => model.PSAJobRef, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        <div class="form-group">
            @Html.Label("Start Seq No", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.TextBox("StartSeqNo")
            </div>
        </div>

        <div class="form-group">
            @Html.Label("End Seq No", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.TextBox("EndSeqNo")
            </div>
        </div>




        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Export" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

How/where would I enter the code to validate the two numbers against the items table?

I would think that doing in the view is the best place as the user would get immediate feedback and I can code the controller method knowing it will always be passed valid values.

I could add a view to Db that contains the job no and min and max item no, but it seems like a bit of a hack.

TIA

Mark

Update: here's my Jobs model:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace PSAMVC.Models
{
    using System;
    using System.Collections.Generic;

    public partial class Job
    {
        public Job()
        {
            this.Items = new HashSet<Item>();
            this.Reprints = new HashSet<Reprint>();
            this.Scans = new HashSet<Scan>();
            this.LabelTypes = new HashSet<LabelType>();
        }

        public int ID { get; set; }
        public string JobNo { get; set; }
        public string VersionRef { get; set; }
        public string PSAJobRef { get; set; }
        public int TotalCopies { get; set; }
        public int CopiesPerBundle { get; set; }
        public int CopiesPerCarton { get; set; }
        public int CopiesPerMasterCarton { get; set; }
        public Nullable<int> CopiesPerPallet { get; set; }
        public int CardType { get; set; }
        public string CardTitle { get; set; }
        public string CardMMYY { get; set; }
        public string StartSerialNo { get; set; }
        public int StartBundleNo { get; set; }
        public int StartCartonNo { get; set; }
        public Nullable<int> StartMasterCartonNo { get; set; }
        public Nullable<int> StartPalletNo { get; set; }
        public string ProductUPC { get; set; }
        public string PackagingUPC { get; set; }
        public bool PreProcessed { get; set; }
        public bool Completed { get; set; }
        public Nullable<int> FormatFileID { get; set; }
        public bool UseDummyBarcode { get; set; }
        public bool Samples { get; set; }
        public string PartNo { get; set; }
        public string ProductEAN { get; set; }
        public string PONo { get; set; }
        public string ImportedFileList { get; set; }
        public bool ExportedToLake { get; set; }
        public Nullable<int> TotalPalletsOverride { get; set; }

        public virtual CardType CardType1 { get; set; }
        public virtual FormatFile FormatFile { get; set; }
        public virtual ICollection<Item> Items { get; set; }
        public virtual SG360JobNos SG360JobNos { get; set; }
        public virtual ICollection<Reprint> Reprints { get; set; }
        public virtual ICollection<Scan> Scans { get; set; }
        public virtual ICollection<LabelType> LabelTypes { get; set; }
    }
}

and here's my jobs controller

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using PSAMVC.Models;
using System.Data.SqlClient;
using System.Configuration;


namespace PSAMVC.Controllers
{
    public class JobsController : Controller
    {
        private PSAMVCEntities db = new PSAMVCEntities();

        // GET: Jobs
        public ActionResult Index()
        {
            var jobs = db.Jobs.Include(j => j.CardType1).Include(j => j.FormatFile).Include(j => j.SG360JobNos);
            return View(jobs.ToList());
        }

        // GET: Jobs/Details/5
        public ActionResult Details(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Job job = db.Jobs.Find(id);
            if (job == null)
            {
                return HttpNotFound();
            }
            return View(job);
        }

        // GET: Jobs/Create
        public ActionResult Create()
        {
            ViewBag.CardType = new SelectList(db.CardTypes, "ID", "Description");
            ViewBag.FormatFileID = new SelectList(db.FormatFiles, "ID", "Name");
            ViewBag.JobNo = new SelectList(db.SG360JobNos, "JobNo", "JobNo");
            return View();
        }

        // GET: CreateBundlesAndCartons
        public ActionResult CreateBandC(Int32 id)
        {
            string ReturnMessage;
            ReturnMessage = "";
            using (SqlConnection connection = new SqlConnection())
            {
                //string connectionStringName = this.DataWorkspace.CooperData.Details.Name;
                connection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["PSAContext"].ConnectionString;
                string procedure = "PSA.dbo.CreateBundlesAndCartons";
                using (SqlCommand command = new SqlCommand(procedure, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandTimeout = 300;

                    command.Parameters.Add(
                        new SqlParameter("@JobID", id));
                    SqlParameter ErrorString = new SqlParameter("@ErrorString", ReturnMessage);
                    ErrorString.Direction = ParameterDirection.Output;
                    ErrorString.Size = 4000;
                    command.Parameters.Add(ErrorString);

                    connection.Open();
                    command.ExecuteNonQuery();

                    // Save Outout Param
                    ReturnMessage = ErrorString.Value.ToString();
                    @ViewBag.Results = ReturnMessage;
                }
            }
            //return Content("You requested the to create bundles and cartons for job ID " + id.ToString() + "<br />Result: " + ReturnMessage + "<br /> <a href=\"~/Jobs/\">Return to Jobs</a>");
            return PartialView("_SPResults");
        }

        // GET: Jobs/ExportToLake/5
        public ActionResult ExportToLake(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Job job = db.Jobs.Find(id);
            if (job == null)
            {
                return HttpNotFound();
            }
            ViewBag.JobNo = new SelectList(db.SG360JobNos, "JobNo", "JobNo", job.JobNo);
            return View(job);
        }


        // GET: ExportToLake1
        public ActionResult ExportToLake1(Int32 id, Int64 StartSeqNo, Int64 EndSeqNo, Boolean ReverseOrder, String FileNameSuffix)
        {
            string ReturnMessage;
            ReturnMessage = "";


            using (SqlConnection connection = new SqlConnection())
            {
                //string connectionStringName = this.DataWorkspace.CooperData.Details.Name;
                connection.ConnectionString =
                    ConfigurationManager.ConnectionStrings["PSAContext"].ConnectionString;
                string procedure = "PSA.dbo.ExportToLakeBulk";
                using (SqlCommand command = new SqlCommand(procedure, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandTimeout = 1200;

                    command.Parameters.Add(
                        new SqlParameter("@JobID", id));
                    command.Parameters.Add(
                        new SqlParameter("@ReverseOrder", ReverseOrder));
                    command.Parameters.Add(
                        new SqlParameter("@StartSeqNo", StartSeqNo));
                    command.Parameters.Add(
                        new SqlParameter("@EndSeqNo", EndSeqNo));
                    command.Parameters.Add(
                        new SqlParameter("@Suffix", FileNameSuffix));
                    SqlParameter ErrorString = new SqlParameter("@ErrorString", ReturnMessage);
                    ErrorString.Direction = ParameterDirection.Output;
                    ErrorString.Size = 4000;
                    command.Parameters.Add(ErrorString);

                    connection.Open();
                    command.ExecuteNonQuery();

                    // Save Outout Param
                    ReturnMessage = ErrorString.Value.ToString();
                    @ViewBag.Results = ReturnMessage;
                }
            }
            //return Content("You requested the to create bundles and cartons for job ID " + id.ToString() + "<br />Result: " + ReturnMessage + "<br /> <a href=\"~/Jobs/\">Return to Jobs</a>");
            return PartialView("_SPResults");
        }

        // POST: Jobs/ExportToLake
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult ExportToLake2([Bind(Include = "ID,StartSeqNo,EndSeqNo,ReverseOrder")] Job job)
        {
            if (ModelState.IsValid)
            {
                //db.Jobs.Add(job);
                //db.SaveChanges();
                return RedirectToAction("Index");
            }

            ViewBag.CardType = new SelectList(db.CardTypes, "ID", "Description", job.CardType);
            ViewBag.FormatFileID = new SelectList(db.FormatFiles, "ID", "Name", job.FormatFileID);
            ViewBag.JobNo = new SelectList(db.SG360JobNos, "JobNo", "JobNo", job.JobNo);
            return View(job);
        }


        // POST: Jobs/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create([Bind(Include = "ID,JobNo,VersionRef,PSAJobRef,TotalCopies,CopiesPerBundle,CopiesPerCarton,CopiesPerMasterCarton,CopiesPerPallet,CardType,CardTitle,CardMMYY,StartSerialNo,StartBundleNo,StartCartonNo,StartMasterCartonNo,StartPalletNo,ProductUPC,PackagingUPC,PreProcessed,Completed,FormatFileID,UseDummyBarcode,Samples,PartNo,ProductEAN,PONo,ImportedFileList,ExportedToLake,TotalPalletsOverride")] Job job)
        {
            if (ModelState.IsValid)
            {
                db.Jobs.Add(job);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            ViewBag.CardType = new SelectList(db.CardTypes, "ID", "Description", job.CardType);
            ViewBag.FormatFileID = new SelectList(db.FormatFiles, "ID", "Name", job.FormatFileID);
            ViewBag.JobNo = new SelectList(db.SG360JobNos, "JobNo", "JobNo", job.JobNo);
            return View(job);
        }




        // GET: Jobs/Edit/5
        public ActionResult Edit(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Job job = db.Jobs.Find(id);
            if (job == null)
            {
                return HttpNotFound();
            }
            ViewBag.CardType = new SelectList(db.CardTypes, "ID", "Description", job.CardType);
            ViewBag.FormatFileID = new SelectList(db.FormatFiles, "ID", "Name", job.FormatFileID);
            ViewBag.JobNo = new SelectList(db.SG360JobNos, "JobNo", "JobNo", job.JobNo);
            return View(job);
        }

        // POST: Jobs/Edit/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include = "ID,JobNo,VersionRef,PSAJobRef,TotalCopies,CopiesPerBundle,CopiesPerCarton,CopiesPerMasterCarton,CopiesPerPallet,CardType,CardTitle,CardMMYY,StartSerialNo,StartBundleNo,StartCartonNo,StartMasterCartonNo,StartPalletNo,ProductUPC,PackagingUPC,PreProcessed,Completed,FormatFileID,UseDummyBarcode,Samples,PartNo,ProductEAN,PONo,ImportedFileList,ExportedToLake,TotalPalletsOverride")] Job job)
        {
            if (ModelState.IsValid)
            {
                db.Entry(job).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            ViewBag.CardType = new SelectList(db.CardTypes, "ID", "Description", job.CardType);
            ViewBag.FormatFileID = new SelectList(db.FormatFiles, "ID", "Name", job.FormatFileID);
            ViewBag.JobNo = new SelectList(db.SG360JobNos, "JobNo", "JobNo", job.JobNo);
            return View(job);
        }

        // GET: Jobs/Delete/5
        public ActionResult Delete(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Job job = db.Jobs.Find(id);
            if (job == null)
            {
                return HttpNotFound();
            }
            return View(job);
        }

        // POST: Jobs/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(int id)
        {
            Job job = db.Jobs.Find(id);
            db.Jobs.Remove(job);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}

Answer 1

Firstly the server side code should not assume that the values being passed are valid. Always validate the values and handle errors correctly. Client side validation can be bypassed.

In terms of providing instant feedback, one approach is to have an action on a controller that accepts the value to validate as a parameter and returns json containing whether the value was valid and if not the error.

This action can then be called on the input fields blur event or change even providing close to real time feedback on whether the values are valid.

Another approach is to have the valid values determined during the page rendering process and embedded into the client side validation framework if you have one (or use custom JS).

Client Code

    function performValidate(data, url) {

        var result = $.ajax({
            type: "POST",
            url: url,
            data: data,
            success: function (data) {
                if (!data.success) {
        //HandleIncorrectValue
                }

                //HandleCorrectValue
            },
            error: function (data) {
              //HandleError
            }
        });

Controller Code

[HttpPost]
public ActionResult Validate(int value)
{
    var response = ValidateValue(value);
    return Json(new { success = response.Success, message = response.Message });
}