Add custom database scripts

Optimizely Configured Commerce uses database scripts internally to alter the state of the database when necessary, such as when a new feature is released or a bug must be fixed. As an added feature, custom database scripts can also be used within your own Configured Commerce solution.

Custom database scripts are executed during application startup and are only executed once for the entire life of the application. Before execution, these scripts are gathered and sorted alphabetically in ascending order (A-Z). This is the order in which they will be executed. After execution, Configured Commerce will note which scripts were executed so they are not executed another time.

As a best practice, the naming of the database scripts should follow a convention to ensure execution in the proper order. Internally, Insite Software uses the following format for naming database scripts.

The {date_of_script_creation} should follow the format YYYY.MM.DD. The {sequential_order} is a two-digit number starting at "01". This number solves the ordering problem when multiple scripts were created on the same date. Below is an example showing some custom database scripts within Configured Commerce.

{date_of_script_creation}.{sequential_order}.{script_name}.sql       ```

Add a custom script to Configured Commerce

Preconditions

• Configured Commerce solution installed
• Custom database script created

Steps

1. In Visual Studio, open Configured Commerce
2. Create a "DatabaseScripts" directory within the InSiteCommerce.Web project.
3. Add the custom database script to that directory and include it in the project as an "Embedded Resource". This allows Configured Commerce to read and execute the script during application startup. Additionally, this gives the benefit of being able to deploy and execute these scripts in other environments, such as a sandbox or production.
4. Build the solution. The database scripts in this directory should be executed on the next application startup.