Add Data Validation to SqlModelMapper

This tutorial explains the basics of adding and performing data validation with SqlModelMapper using SnapDevelop. It provides you an example on how to integrate the data validation to your CRUD operations.

In this tutorial you will learn:

What is data validation in SqlModelMapper
How to add data validation to your models
How to perform validation against tracked models

Prerequisites

SnapDevelop 2019
Sample database setup
1. Download the database backup file from https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2012.bak. (This specific version of the DB is required for this tutorial)
2. Install SQL Server Express or SQL Server if it is not installed.
3. Restore database using the downloaded database backup file.
Walk-through the steps in CRUD Operations with SqlModelMapper tutorial

What is Data Validation in SqlModelMapper

Data validation is performed based on the metadata that is associated with the data table. You use it to validate that the value of a property from a data table meets certain criteria.

With SqlModelMapper you can validate the values of tracked models against established criteria. For example, if you want to validate that the password column of a data model accepts strings between 4 to 12 characters long and that it is a Required value for the database table, then you can add the following data validation to your data model: [MinLength(4)], [MaxLength(12)], [Required]

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using SnapObjects.Data;

namespace WebAPI3.Models
{
    [Table("Users", Schema = "dbo")]
    public class Users
    {
        [Key]
        [Identity]
        public int UserId { get; set; }

        [Required]
        [StringLength(12)]
        public string UserName { get; set; }

        [Required]        // Validation attribute for a Required value
        [MinLength(4)]    // Validation attribute for Minimum Length of 4
        [MaxLength(12)]   // Validation attribute for Maximum Length of 12
        public string Password { get; set; }

        [StringLength(30)]
        public string Name { get; set; }
    }
}

Adding Data Validation to Models

Open your Web API created in the CRUD Operations with SqlModelMapper tutorial.

Right-click on the Models folder, and then click on Add > Class to add a new model to Web API. Name it Product.cs.

New Model

Map it to your Production.Product table.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using SnapObjects.Data;

namespace WebAPI3.Models
{
    [FromTable("Product", Schema = "production")]
    [SqlOrderBy("Productid ASC")]
    public class Product
    {
        [Key]
        [Identity]
        public int Productid { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }

        [Required]
        [StringLength(25)]
        public string Productnumber { get; set; }

        [Required]
        public bool Makeflag { get; set; }

        [StringLength(15)]
        public string Color { get; set; }

        public int Safetystocklevel { get; set; }

        public int Reorderpoint { get; set; }

        public decimal Standardcost { get; set; }

        public decimal Listprice { get; set; }

        [StringLength(5)]
        public string Size { get; set; }

        [StringLength(3)]
        public string Sizeunitmeasurecode { get; set; }

        [StringLength(3)]
        public string Weightunitmeasurecode { get; set; }

        public decimal? Weight { get; set; }

        public int Daystomanufacture { get; set; }

        public string Productline { get; set; }

        public string Class { get; set; }

        public string Style { get; set; }

        [Required]
        public int? Productsubcategoryid { get; set; }

        public int? Productmodelid { get; set; }

        public DateTime Sellstartdate { get; set; }

        public DateTime? Sellenddate { get; set; }

        public DateTime Modifieddate { get; set; }

        public bool Finishedgoodsflag { get; set; }
    }
}

Performing Data Validation in Tracked Models

Assuming a scenario of tracking models, it may get values generated by the database, set values to the other tracked models in the same transaction; or load data from the database, or cancel the Save operation and roll back the transaction by Save Context.

To perform validity check to all of the data in the model object tracked by the current SqlModelMapper, you can use the ValidateTrackedModels() method with the following set of instructions:

Adding a new service

Adding a new interface

The interface contains only the declaration of the methods, properties and events, but not the implementation.

Right-click on the Services folder and click on Add > Interface.

Add Interface

Name the interface as IProductService.cs and click OK.

New Interface

Adding the declaration of the methods to the service

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SnapObjects.Data;
using WebAPI3.Models;

namespace WebAPI3.Services
{
    public interface IProductService
    {
        IList<Product> Load();
        
        IList<Product> LoadByKey(int id);
        
        int Create(Product product);
    }
}

To add the Service right-click on the Service > Impl sub-folder and click on Add > Class. Name it ProductService.cs and click OK. (Notice the difference between the name of the interface "IProductService.cs" and the name of the service "ProductService.cs")

Tracking and validating model creation in the service

Modify the ProductService.cs as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using WebAPI3.Models;

namespace WebAPI3.Services.Impl
{
    public class ProductService : IProductService
    {
        private readonly AppeonDataContext _context;

        public ProductService(AppeonDataContext Context)
        {
            _context = Context;
        }

        public IList<Product> Load()
        {
            return _context.SqlModelMapper.Load<Product>().ToList();
        }

        public IList<Product> LoadByKey(int id)
        {
            return _context.SqlModelMapper.LoadByKey<Product>(id).ToList();
        }

        public int Create(Product product)
        {
            var results = new List<ValidationResult>();

            // Note: The Size property has the [StringLength(5)] attribute and
            // the Productsubcategoryid has the [Required] attribute.
            // 
            // The following code is only used to show how the ValidateTrackedModels method works.
            // It will throw an ValidiationException because the length of the Size is 6 and 
            // Productsubcategoryid is null.
            // 
            // You can set valid values for Size and Productsubcategoryid or just comment the 
            // following two lines to pass the validation.

            product.Size = "123456";
            product.Productsubcategoryid = null;

            _context.SqlModelMapper.TrackCreate(product);

            bool valid = _context.SqlModelMapper.ValidateTrackedModels(out results);

            if (!valid)
            {
                string errs = String.Empty;
                foreach (var result in results)
                {
                    errs += result.ErrorMessage + " ";
                }
                throw new ValidationException(errs);
            }
            else
            {
                var saved = _context.SqlModelMapper.SaveChanges();

                return saved.InsertedCount;
            }
        }
    }
}

In this example, the Create() method of the Service is tracking the create action of the Product Model. If all the validation tests are valid, then a true value is returned from calling the ValidateTrackedModels() method of the SqlModelMapper. But, if any code in service modifies a value to an invalid value, then a validation error would occur and the SaveChanges() method of the SqlModelMapper would not be called.

Registering the service

In ASP.NET Core, services such as the ProductService must be registered with the dependency injection (DI) container. The container provides the service to controllers.

Update Startup.cs according to the comments:

 // The service needs to be registered in the ConfigureServices method of the Startup class.
services.AddScoped<IProductService, ProductService>();

Adding a new controller

Create a new controller by right-clicking on the Controller folder then Add -> New Item -> Class. Name it ProductsController.cs. A controller determines what response to send back to a user when a client makes an HTTP request.

Add Controller

Named the controller as ProductsController.cs with the following codes:

using System;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Http;
using WebAPI3.Models;
using WebAPI3.Services;

namespace WebAPI3.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ProductsController : ControllerBase
    {
        private readonly IProductService _service;

        public ProductsController(IProductService Service)
        {
            _service = Service;
        }

        [HttpGet]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status404NotFound)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public ActionResult<IList<Product>> Load()
        {
            var products = new List<Product>();

            try
            {
                products = (List<Product>)_service.Load();
            }
            catch (Exception ex)
            {
                return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
            }

            if (products.Count <= 0 || products == null)
            {
                return NotFound("No rows retrieved.");
            }
            else
            {
                return Ok(products);
            }
        }

        [HttpGet("{id}")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status404NotFound)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public ActionResult<IList<Product>> LoadByKey(int id)
        {
            var products = new List<Product>();

            try
            {
                products = (List<Product>)_service.LoadByKey(id);
            }
            catch (Exception ex)
            {
                return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
            }

            if (products.Count <= 0 || products == null)
            {
                return NotFound("No rows retrieved.");
            }
            else
            {
                return Ok(products);
            }
        }

        [HttpPost]
        [ProducesResponseType(StatusCodes.Status201Created)]
        [ProducesResponseType(StatusCodes.Status404NotFound)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public ActionResult<int> Create([FromBody]Product product)
        {
            int insertedRows;

            try
            {
                insertedRows = _service.Create(product);
            }
            catch (Exception ex)
            {
                return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
            }

            if (insertedRows <= 0)
            {
                return NotFound("No rows inserted.");
            }
            else
            {
                return Ok(insertedRows);
            }
        }
    }
}