Customization Custom Tables

Podcast Beta

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the primary key requirement for all custom tables?

An Id field with a default value of NULL

An Id field of type varchar

An Id field of type uniqueidentifier with a default value of newsequentialid() (correct)

An Id field of type integer

Which fields in custom tables are required to be non-nullable?

All fields except modified fields

No fields are required to be non-nullable

CreatedOn, CreatedBy, ModifiedOn, and ModifiedBy fields (correct)

Only the CreatedOn field

What should be considered when deciding if a field should allow null values?

Consider if it is meaningful to know if the field has not been set (correct)

All fields should never allow nulls.

Fields should always allow nulls for flexibility

Only numeric fields should allow nulls

What is the procedure for re-running a script that has already been executed?

Drop the custom table and delete the entry from the DatabaseScript table Signup and view all the answers

Which fields should likely have indexes added?

Fields such as ErpNumber and ProductId that are used to look up data Signup and view all the answers

What is the primary function of the ProductExtensionCollectionModel constructor?

To generate URIs for pagination links Signup and view all the answers

Which method is responsible for generating collection links based on pagination?

GetCollectionLink Signup and view all the answers

What data structure does ProductExtensions use to store the collection of product extensions?

List Signup and view all the answers

How does the ProductExtensionCollectionModel handle pagination when the current page is greater than 1?

It generates a URI for the previous page Signup and view all the answers

In the context of the code provided, which of the following is true about the PaginationModel?

It determines the total number of available pages Signup and view all the answers

What is the purpose of changing the data model in the implementation of custom tables?

To restrict IIS-connected users from executing SQL DDL statements. Signup and view all the answers

Which connection string has database owner rights?

InSite.Commerce.Admin Signup and view all the answers

What permissions does InSite.Commerce.Extensions have in the database?

DDL rights to the Extensions schema and additional permissions in the whole database. Signup and view all the answers

What is the correct naming convention for SQL scripts in the Extensions project?

YYYY.MM.DD.SS.DescriptiveName.sql Signup and view all the answers

What must be set for a script to be run by the bootstrapper?

The Build Action must be set to Embedded Resource. Signup and view all the answers

Which of the following statements is true regarding custom tables?

Only specific users can create custom tables, excluding console users. Signup and view all the answers

What type of permissions does InSite.Commerce have?

Read/write/execute privileges on all schemas. Signup and view all the answers

What is a limitation placed on the InSite.Commerce connection in the cloud implementation?

It cannot execute SQL scripts or DDL. Signup and view all the answers

What attribute marks a property as a human-readable unique identifier in an entity class?

NaturalKeyField Signup and view all the answers

What should be done when multiple properties require the NaturalKeyField attribute?

Add NaturalKeyField to each property with an Order Signup and view all the answers

What is the correct way to define collection properties in an entity class?

Define as virtual and use ICollection with a HashSet Signup and view all the answers

What must a mapping class implement for proper Entity Framework mapping?

ICommerceContextMapping Signup and view all the answers

What does the Order parameter in the NaturalKeyField attribute indicate?

The sequence for record uniqueness Signup and view all the answers

What is the purpose of the EntityBaseTypeConfiguration class when creating a mapping class?

To simplify entity property configuration mappings Signup and view all the answers

What is a key requirement for creating a WebApi for a custom entity?

It should follow RESTful principles Signup and view all the answers

What namespace should be used for mapping classes in the provided example?

Extensions.Entities.Mapping Signup and view all the answers

What is the primary purpose of the Get method in the ProductExtensionsController?

To return a sorted, paged list of ProductExtension objects Signup and view all the answers

Which parameter allows customization of the number of items returned by the Get method?

pageSize Signup and view all the answers

What does the Get method return if no ProductExtension is found for the provided productId?

A NotFound response Signup and view all the answers

What role does the IUnitOfWorkFactory serve in the ProductExtensionsController?

To provide database access abstractions Signup and view all the answers

How does the controller determine the page number to return in the Get method?

It reads the Page property from the parameter Signup and view all the answers

Study Notes

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.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

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.