Customization Custom Tables

Custom table configuration

• Custom tables require three separate connection strings in the connectionStrings.config file for development and cloud environments.
• InSite.Commerce: read/write/execute privileges on all schemas, cannot be the database owner, and cannot execute SQL scripts or DDL.
• InSite.Commerce.Admin: database owner rights and can execute base scripts.
• InSite.Commerce.Extensions: DDL rights to the Extensions schema, read/write/execute sprocs in the whole database, and REFERENCES permission in the DBO schema.
• Custom tables cannot be displayed in the Admin Console for viewing, using, or exporting.

Creating custom tables

• Create a "DatabaseScripts" folder at the root level of your Extensions project.
• Database scripts should be named using the convention YYYY.MM.DD.SS.DescriptiveName.sql.
• Set the Build Action to "Embedded Resource" in the properties window for each script.
• Custom tables must have an Id field of type uniqueidentifier, a primary key with a default value of newsequentialid().
• Custom tables must include non-nullable CreatedOn, CreatedBy, ModifiedOn, and ModifiedBy fields with specified default values.
• Only allow fields to be nullable if it's meaningful to know if the field hasn't been set.
• Add indexes on fields used for data lookup.
• Foreign Key references can be added to tables in the DBO schema, as the Extensions database user has the REFERENCES permission for that schema.
• The bootstrapper will execute scripts and create entries in the DatabaseScript table on site startup.
• To re-run a script, drop the custom table, delete the entry from the DatabaseScript table, and restart the site.
• The bootstrapper will only run the script if an entry for it doesn't already exist in the DatabaseScript table.
• Define a NaturalKeyField attribute to specify the human-readable unique way to look up a record.
• Add StringLength attributes that match the database field definition.
• Collection properties should be defined as virtual with type ICollection and an initial value of new HashSet().
• Related objects should be defined as virtual and contain a property for the related entity id, named with the type of the related entity and "Id" (e.g., MyCustomEntityId).
• Create a mapping class that implements the ICommerceContextMapping interface.
• Inherit from the Insite.Data.Providers.EntityFramework.EntityMappings.EntityBaseTypeConfiguration class, which implements ICommerceContextMapping and maps the CustomProperties collection.
• Map the CustomProperties collection manually if not inheriting from EntityBaseTypeConfiguration.

Creating a WebApi

• Create a RESTful WebApi for the custom entity.
• The API controller should handle CRUD operations for the custom entity.
• The API should return collections of the custom entity in a paged and sorted format.
• Implement a parameter object to handle query string parameters for page, pagesize, and sort.
• The API should return a model object containing navigation links, paging information, and the custom entity objects.

Consuming the WebApi

• The client-side code should consume the WebApi using HTTP requests.
• The client-side code should handle parsing the response from the API.
• The client-side code should handle routing and navigation to different pages and actions based on the response.

This quiz covers essential aspects of configuring and creating custom tables in InSite Commerce environments. Test your knowledge on connection strings, permissions, script creation conventions, and the specifics of managing custom table visibility within the Admin Console.