Dev GuideAPI ReferenceUser Guide
Dev GuideAPI Reference
Dev GuideAPI ReferenceUser GuideGitHubNuGetDev CommunityDoc feedbackLog In
GitHubNuGetDev CommunityDoc feedback

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 <<product-name>>, 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


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

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

  • 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

## 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

Code Sample: ProductExtensionModel.cs

Code Sample: ProductExtensionCollectionModel.cs

Code Sample: ProductExtensionsController.cs

## 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

Code Sample: custom.productextension.service.ts

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

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