Create custom tables with an entity and WebApi

14 min

Part of the implementation of custom tables support is to change the data model so that the IIS-connected user will no longer be able to execute SQL DDL statements directly. When this feature is implemented, three separate connection strings must be included in the connectionStrings.config. For local development, the three different connections can all have the same rights to the database, but in Configured Commerce Cloud, they have different rights as follows:

InSite.Commerce has only read/write/execute privileges on all schemas, can no longer be the database owner, and is not allowed to execute SQL scripts or any DDL.

InSite.Commerce.Admin has database owner rights and can do anything on any schema. This connection is used to execute base scripts.

InSite.Commerce.Extensions has DDL rights to the Extensions schema and also has read, write, and execute sprocs in the whole database. This user also has the REFERENCES permission in the DBO schema.

These configurations are included in the SDK web project, but you may need to add them manually on existing projects being upgraded.

📘 Note You cannot display custom tables in the Admin Console for console users to view, use or export.

Data considerations

Optimizely does not support customers storing large amounts of data in the extensions schema of the database. Configured Commerce databases default to 200GB of total size, and while there are legitimate customer use cases (large numbers of orders, products, and so on) that may require Optimizely to raise this, you should not implement the following use cases in the database and instead store using the UserFiles functionality:

Images

PDFs

nvarchar(max) records of size 1MB+

Optimizely reserves the right to truncate these tables if discovered.

Create a custom table

To add a SQL script to your Extensions project and have it automatically run by the bootstrapper, you must first create a DatabaseScripts folder at the root level of your Extensions project:

Custom_Tables_DatabaseScriptsFolder.png

To ensure that your database scripts run in the proper order, follow the naming convention of YYYY.MM.DD.SS.DescriptiveName.sql where YYYY is the current four digit year, MM is the two digit month, DD is the two digit day and SS is the sequence of the script to run in for that day if you have multiple scripts added on the same day. In order for the script to be picked up and run by the bootstrapper you MUST set the Build Action to Embedded Resource in the properties window for your script.

Custom_Tables_SetEmbeddedResource.png

This example creates a table to store additional information for a Product named ProductExtensions. A sql script named 2018.04.06.01.CreateProductExtension.sql was added and set the build action to embedded resource with the following contents:

CREATE TABLE [Extensions].[ProductExtension](
    [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ProductExtension_Id]  DEFAULT (newsequentialid()),
    [ProductId] [uniqueidentifier] NOT NULL,
    [ERPNumber] [nvarchar](50) NOT NULL CONSTRAINT [DF_ProductExtension_ERPNumber]  DEFAULT (''),
    [BrandName] [nvarchar](50) NOT NULL CONSTRAINT [DF_ProductExtension_BrandName]  DEFAULT (''),
    [SecondaryDescription] [nvarchar](100) NOT NULL CONSTRAINT [DF_ProductExtension_SecondaryDescription]  DEFAULT (''),
    [BulletPoint1] [nvarchar](100) NOT NULL CONSTRAINT [DF_ProductExtension_BulletPoint1]  DEFAULT (''),
    [BulletPoint2] [nvarchar](100) NOT NULL CONSTRAINT [DF_ProductExtension_BulletPoint2]  DEFAULT (''),
    [BulletPoint3] [nvarchar](100) NOT NULL CONSTRAINT [DF_ProductExtension_BulletPoint3]  DEFAULT (''),
    [CreatedOn] [datetimeoffset](7) NOT NULL CONSTRAINT [DF_ProductExtension_CreatedOn]  DEFAULT (getutcdate()),
    [CreatedBy] [nvarchar](100) NOT NULL CONSTRAINT [DF_ProductExtension_CreatedBy]  DEFAULT (''),
    [ModifiedOn] [datetimeoffset](7) NOT NULL CONSTRAINT [DF_ProductExtension_ModifiedOn]  DEFAULT (getutcdate()),
    [ModifiedBy] [nvarchar](100) NOT NULL CONSTRAINT [DF_ProductExtension_ModifiedBy]  DEFAULT (''),
 CONSTRAINT [PK_ProductExtension] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
SET ANSI_PADDING ON
GO
 
CREATE UNIQUE NONCLUSTERED INDEX [IX_ProductExtension_ERPNumber] ON [Extensions].[ProductExtension]
(
    [ERPNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
CREATE UNIQUE NONCLUSTERED INDEX [IX_ProductExtension_ProductId] ON [Extensions].[ProductExtension]
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Key points and conventions to follow:

All custom tables (stored procedures, views) must be defined in the Extensions schema. You have complete control over the Extensions schema, but you cannot perform any ALTER, CREATE, DROP or TRUNCATE (or any other DDL) statements in any other schema (you can modify and/or load data in other schemas with scripts).

All custom tables must have an Id field that is of type uniqueidentifier that is the primary key with a default value of newsequentialid().

All custom tables must have CreatedOn, CreatedBy, ModifiedOn and ModifiedBy fields as defined above that are not nullable and have the specified default values.

Fields should only allow null if it is meaningful to know if the field has not been set, for example, most (if not all) nvarchar fields should not allow null and have a default value of '', on the other hand a lot of datetimeoffset fields like our CustomerOrder.RequestedShipDate should allow null. In other words, don't just allow all fields to be nullable as a convenience, take the time to think about if the field should allow null, and if not, do not allow nulls and set a sensible default.

You should add indexes on fields that you are likely to use to look up data, in this case ErpNumber and ProductId.

You can add Foreign Key references to tables that are in the DBO schema, as the Extensions database user has the REFERENCES permission for the DBO schema.

On site startup, the bootstrapper executes this script and create an entry for it in the DatabaseScript table, if you need to re-run your script for whatever reason, you need to drop your custom table and delete this entry from the DatabaseScript table and on next site startup, it will run your script and re-create that entry. If an entry for that script already exists in the DatabaseScript table, it will not run the script again.

Create a custom entity for your custom table

The following code is the class for the ProductExtension entity that is mapped to the ProductExtension table created above:

Code Sample: ProductExtension.cs

namespace Extensions.Entities
{
  using System;
  using System.ComponentModel.DataAnnotations;
  using System.ComponentModel.DataAnnotations.Schema;
  using Insite.Core.Interfaces.Data;
  using Insite.Data.Entities;
 
  [Table("ProductExtension", Schema = "Extensions")]
  public class ProductExtension : EntityBase
  {
    [Required]
    public Guid ProductId { get; set; }
 
    [Required(AllowEmptyStrings = true)]
    [StringLength(50)]
    [NaturalKeyField]
    public string ErpNumber { get; set; } = string.Empty;
 
    [Required(AllowEmptyStrings = true)]
    [StringLength(50)]
    public string BrandName { get; set; } = string.Empty;
 
    [Required(AllowEmptyStrings = true)]
    [StringLength(100)]
    public string SecondaryDescription { get; set; } = string.Empty;
 
    [Required(AllowEmptyStrings = true)]
    [StringLength(100)]
    public string BulletPoint1 { get; set; } = string.Empty;
 
    [Required(AllowEmptyStrings = true)]
    [StringLength(100)]
    public string BulletPoint2 { get; set; } = string.Empty;
 
    [Required(AllowEmptyStrings = true)]
    [StringLength(100)]
    public string BulletPoint3 { get; set; } = string.Empty;
  }
}

Key points and conventions to follow:

You must add the Table attribute to the class with the table name and the Schema set to "Extensions".

You must inherit from EntityBase, EntityBase will define the Id, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy and CustomProperties collection properties.

Required properties should have the Required attribute and string properties in general should have AllowEmptyStrings = true.

You should initialize properties where possible (like setting string properties to string.Empty) so they have valid values when you create a new instance of the object.

You must have a NaturalKeyField attribute that marks the human readable unique way to look the record up, this can include multiple properties, when there are multiple properties add the NaturalKeyField attribute to each property setting the Order, for example in our Customer.cs class:

Code Sample: NaturalKeyField Order

[Required(AllowEmptyStrings = true)]
[StringLength(50)]
[NaturalKeyField(Order = 0)]
public virtual string ErpNumber { get; set; } = string.Empty;
 
[Required(AllowEmptyStrings = true)]
[StringLength(50)]
[NaturalKeyField(Order = 1)]
public virtual string ErpSequence { get; set; } = string.Empty;

You should add StringLength attributes that match the definition of the database field.

Collection properties should be defined as virtual with type ICollection<T> with an initial value of new HashSet<T>().

Related objects (if they are also custom) should be defined as virtual and also contain a property for the related entity id named the type of the related entity and Id, for example MyCustomEntityId.

Once you have the Entity class, you must then create a mapping class, this mapping class must implement the interface ICommerceContextMapping, the best way to accomplish this is to inherit from the Insite.Data.Providers.EntityFramework.EntityMappings.EntityBaseTypeConfiguration<T> class. This class implements ICommerceContextMapping and maps the CustomProperties collection for you. If you do not inherit from EntityBaseTypeConfiguration<T>, then you must map the CustomProperties collection property yourself. Here is the mapping class for ProductExtension:

namespace Extensions.Entities.Mapping
{
  using Insite.Data.Providers.EntityFramework.EntityMappings;
     
  public class ProductExtensionMapping : EntityBaseTypeConfiguration<ProductExtension>
  {
    public ProductExtensionMapping()
    {
      // your Entity Framework mapping code goes here
    }
  }
}

Where the entity is registered

Configured Commerce runs a startup task named GenerateCustomEntityDefinitions that scans the Extensions assembly and registers every entity it finds. The startup task runs in the Storefront, Admin, and Integration apps, so a single custom entity definition is available to:

The Storefront WebAPI for shopper-facing flows.

The Admin Console for back-end administration.

The Integration API for connector and middleware integrations.

If the Extensions assembly is missing on an app, the task logs Skipping SetupCustomEntityDefinitions because no extensions dll and skips registration on that app only.

Create a WebApi for your custom entity

The following is a reference implementation of a RESTful web api for the ProductExtension entity.

Code Sample: ProductExtensionParameter

namespace Extensions.WebApi.DogFood.ApiModels
{
  using Insite.Core.WebApi;
 
  /// <summary>
  /// ProductExtensionParameter is the type for the parameter of the GET collection controller method, the query string parameters page, pagesize and sort
  /// will be automatically deserialized in to this object if they are supplied.
  /// </summary>
  public class ProductExtensionParameter : BaseParameter
  {
    public int? Page { get; set; }
    public int? PageSize { get; set; }
    public string Sort { get; set; }
  }
}

Code Sample: ProductExtensionModel.cs

namespace Extensions.WebApi.DogFood.ApiModels
{
  using System;
  using System.Net.Http;
  using Extensions.Entities;
  using Insite.Core.WebApi;
  using Insite.Core.WebApi.Interfaces;
 
  /// <summary>
  /// ProductExtensionModel is the representation of the json data for the ProductExtension object that will be returned by the api.
  /// </summary>
  public class ProductExtensionModel : BaseModel
  {
    public ProductExtensionModel(ProductExtension productExtension, HttpRequestMessage request, IUrlHelper urlHelper)
    {
      this.Uri = urlHelper.Link("ProductExtension", new { productId = productExtension.ProductId }, request);
      this.Id = productExtension.Id;
      this.ProductId = productExtension.ProductId;
      this.ErpNumber = productExtension.ErpNumber;
      this.BrandName = productExtension.BrandName;
      this.SecondaryDescription = productExtension.SecondaryDescription;
      this.BulletPoint1 = productExtension.BulletPoint1;
      this.BulletPoint2 = productExtension.BulletPoint2;
      this.BulletPoint3 = productExtension.BulletPoint3;
    }
         
    public Guid Id { get; set; }
    public Guid ProductId { get; set; }
    public string ErpNumber { get; set; }
    public string BrandName { get; set; }
    public string SecondaryDescription { get; set; }
    public string BulletPoint1 { get; set; }
    public string BulletPoint2 { get; set; }
    public string BulletPoint3 { get; set; }
  }
}

Code Sample: ProductExtensionCollectionModel.cs

namespace Extensions.WebApi.DogFood.ApiModels
{
  using System.Collections.Generic;
  using System.Net.Http;
  using Microsoft.Practices.ObjectBuilder2;
  using Extensions.Entities;
  using Insite.Core.WebApi.Interfaces;
  using Insite.Core.WebApi;
 
  /// <summary>
  /// ProductExtensionCollectionModel is the representation of the json data that is returned from the api for the GET collection api method.
  /// </summary>
  public class ProductExtensionCollectionModel : BaseModel
  {
    public ProductExtensionCollectionModel(IList<ProductExtension> productExtensions, string sort, PaginationModel pagination, HttpRequestMessage request, IUrlHelper urlHelper)
    {
      this.Pagination = pagination;
      this.Uri = this.GetCollectionLink(request, urlHelper, pagination.PageSize, pagination.Page, sort);
      if (pagination.Page > 1)
      {
        this.Pagination.PrevPageUri = this.GetCollectionLink(request, urlHelper, pagination.PageSize, pagination.Page - 1, sort);
      }
 
      if (pagination.Page < pagination.NumberOfPages)
      {
        this.Pagination.NextPageUri = this.GetCollectionLink(request, urlHelper, pagination.PageSize, pagination.Page + 1, sort);
      }
  
      productExtensions.ForEach(o => this.ProductExtensions.Add(new ProductExtensionModel(o, request, urlHelper)));
    }
 
    public ICollection<ProductExtensionModel> ProductExtensions { get; set; } = new List<ProductExtensionModel>();
 
    public PaginationModel Pagination { get; set; }
     
    private string GetCollectionLink(HttpRequestMessage request, IUrlHelper urlHelper, int pageSize, int page, string sort)
    {
      return urlHelper.Link("ProductExtensions", new { pageSize, page, sort }, request);
    }
  }
}

Code Sample: ProductExtensionsController.cs

namespace Extensions.WebApi.DogFood
{
  using System;
  using System.Linq;
  using System.Reflection;
  using System.Web.Http;
  using System.Web.Http.Description;
  using Extensions.Entities;
  using Extensions.WebApi.DogFood.ApiModels;
  using Insite.Core.Interfaces.Data;
  using Insite.Core.SystemSetting.Groups.SiteConfigurations;
  using Insite.Core.WebApi;
  using Insite.Core.WebApi.Interfaces;
  using Insite.Data.Extensions;
 
  /// <summary>
  /// ProductExtensionController is the api controller that implements the RESTful api methods for the ProductExtension entity.
  /// </summary>
  [RoutePrefix("api/dogfood/productextensions")]
  public class ProductExtensionsController : ApiController
  {
    private readonly StorefrontApiSettings storefrontApiSettings;
     
    private readonly IUnitOfWorkFactory unitOfWorkFactory;
 
    private readonly IUrlHelper urlHelper;
 
    public ProductExtensionsController(IUnitOfWorkFactory unitOfWorkFactory, IUrlHelper urlHelper, StorefrontApiSettings storefrontApiSettings)
    {
      this.unitOfWorkFactory = unitOfWorkFactory;
      this.urlHelper = urlHelper;
      this.storefrontApiSettings = storefrontApiSettings;
    }
     
    /// <summary>
    /// This is the GET collection method that will return a sorted, paged list of ProductExtension objects.
    /// </summary>
    /// <param name="parameter">The parameter object that the query string parameters for page, pagesize and sort get supplied in.</param>
    /// <returns>A ProductExtensionCollectionModel containing the navigation links, paging information and the list of ProductExtension objects.</returns>
    [Route("", Name = "ProductExtensions")]
    [ResponseType(typeof(ProductExtensionCollectionModel))]
    public IHttpActionResult Get([FromUri] ProductExtensionParameter parameter)
    {
      var unitOfWork = this.unitOfWorkFactory.GetUnitOfWork();
      var query = unitOfWork.GetRepository<ProductExtension>().GetTableAsNoTracking().Expand(o => o.CustomProperties);
      var pageSize = storefrontApiSettings.DefaultPageSize;
      if (parameter?.PageSize != null)
      {
        pageSize = parameter.PageSize.Value;
      }
       
      var page = parameter?.Page ?? 1;
      page = page <= 0 ? 1 : page;
       
      var sort = parameter?.Sort ?? "ErpNumber";
      if (typeof(ProductExtension).GetProperty(sort, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance) == null)
      {
        return this.BadRequest("Invalid sort");
      }
 
      var totalCount = query.Count();
       
      // Apply sorting
      query = Insite.Common.DynamicLinq.DynamicQueryable.OrderBy(query, sort);
  
      // Apply paging
      query = query.Skip((page - 1) * pageSize).Take(pageSize);
 
      return this.Ok(new ProductExtensionCollectionModel(
        query.ToList(),
        sort,
        new PaginationModel(page, pageSize, storefrontApiSettings.DefaultPageSize, totalCount),
        this.Request,
        this.urlHelper));
    }
  
    /// <summary>
    /// This is the Get individual ProductExtension resource object.
    /// </summary>
    /// <param name="productId">The ProductId of the ProductExtension to get.</param>
    /// <returns>A ProductExtensionModel with the data for the ProductExtension object requested or 404 Not Found if the requested resource is not found.</returns>
    [Route("{productId}", Name = "ProductExtension")]
    [ResponseType(typeof(ProductExtensionModel))]
    public IHttpActionResult Get(Guid productId)
    {
      var unitOfWork = this.unitOfWorkFactory.GetUnitOfWork();
      var productExtension = unitOfWork.GetRepository<ProductExtension>().GetTableAsNoTracking()
        .Expand(o => o.CustomProperties).FirstOrDefault(o => o.ProductId == productId);
      if (productExtension == null)
      {
        return this.NotFound();
      }
 
      return this.Ok(new ProductExtensionModel(productExtension, this.Request, this.urlHelper));
    }
  }
}

Consume the WebApi from the Client

The following is a reference implementation of consuming this api from the client side.

Code Sample: custom.models.ts

declare module Extensions.WebApi.DogFood.ApiModels {
  import Guid = System.Guid;
 
  interface ProductExtensionModel extends Insite.Core.WebApi.BaseModel {
    uri: string;
    id: Guid;
    productId: Guid;
    erpNumber: string;
    brandName: string;
    secondaryDescription: string;
    bulletPoint1: string;
    bulletPoint2: string;
    bulletPoint3: string;
  }
}

Code Sample: custom.productextension.service.ts

module insite.catalog {
  "use strict";
 
  import ProductExtensionModel = Extensions.WebApi.DogFood.ApiModels.ProductExtensionModel;
 
  export class ProductExtensionServiceCustom {
    productExtensionServiceUri = "/api/dogfood/productextensions/";
 
    static $inject = ["$http", "httpWrapperService"];
 
    constructor(
      protected $http: ng.IHttpService,
      protected httpWrapperService: core.HttpWrapperService) {
      }
 
      getProductExtension(productId: string) {
        return this.httpWrapperService.executeHttpRequest(
          this,
          this.$http({ method: "GET", url: this.productExtensionServiceUri + productId }),
          this.getProductExtensionCompleted,
          this.getProductExtensionFailed);
      }
     
      protected getProductExtensionCompleted(response: ng.IHttpPromiseCallbackArg<ProductExtensionModel>): void {
      }
 
      protected getProductExtensionFailed(error: ng.IHttpPromiseCallbackArg<any>): void {
      }
    }
   
    angular
      .module("insite")
      .service("productExtensionService", ProductExtensionServiceCustom);
}

Code Sample: custom.product-detail.controller.ts

module insite.catalog {
    "use strict";
 
    import ProductDetailController = insite.catalog.ProductDetailController;
    import ProductExtensionModel = Extensions.WebApi.DogFood.ApiModels.ProductExtensionModel;
 
    export class ProductDetailControllerCustom extends ProductDetailController {
        productExtension: ProductExtensionModel;
         
        protected getProductCompleted(productModel: ProductModel) : void {
            super.getProductCompleted(productModel);
            const injector = angular.element(document.body).injector();
            let productExtensionService = injector.get("productExtensionService");
             
            productExtensionService.getProductExtension(productModel.product.id).then(
                (productExtension: ProductExtensionModel) => {
                    this.productExtension = productExtension;
                },
                (error: any) => {
                });
        }
   }
 
    angular
        .module("insite")
        .controller("ProductDetailController", ProductDetailControllerCustom);
}

Use that productExtension object in the ProductDetailView widget to display some of the data:

Custom_Tables_ProductDetailView.png