Tables with an entity and WebAPI

Describes how to create custom tables with an entity and WebAPI in Optimizely Configured Commerce.

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

InSite.Commerce will have only read/write/execute privileges on all schemas and will no longer be the database owner and will not be allowed to execute SQL scripts or any DDL
InSite.Commerce.Admin will have database owner rights and be able to do anything on any schema - this connection will be used to execute base scripts
InSite.Commerce.Extensions will have DDL rights to the Extensions schema only

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

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:

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.

For example we will create a table to store additional information for a Product named ProductExtensions. I added a sql script named 2018.04.06.01.CreateProductExtension.sql and set the build action to embedded resource with the following contents:

Code Sample: 2018.04.06.01.CreateProductExtension.sql

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 can NOT perform any ALTER, CREATE or DELETE (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 not add Foreign Key references to tables that are not in the Extensions schema, note there is not a Foreign Key to Product.

On site startup, the bootstrapper will execute 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

You must add the EntityFramework v6.1.3 nuget package to your Extensions project.

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)]
ublic 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 just inherit from our Insite.Data.Providers.EntityFramework.EntityMappings.EntityBaseTypeConfiguration<T> class. This class implements ICommerceContextMapping and will map 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:

Code Sample: ProductExtensionMapping.cs

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

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.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);
    }
  }
}

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);
}

And then using that productExtension object in the ProductDetailView widget to display some of the data:

Updated over 1 year ago