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: