Applying database updates

Using the DynamicWeb UpdateProvider to roll out database changes

In some situations, the built-in data customization options in DynamicWeb may not be enough. You might need to extend existing database tables or even create entirely new custom tables.

When that happens, you can use the UpdateProvider. It lets you apply database changes in a structured way, so you don’t have to manually update multiple environments one by one. The UpdateProvider is easy to set up. All it does is expose a list of Update objects, which are executed automatically when DynamicWeb starts.

To implement an UpdateProvider must:

Inherit from the base class UpdateProvider
Override the GetUpdates method

The GetUpdates method should then return a list of the individual updates you want the provider to perform. Each of these updates must have their own unique ID, which should not only be unique within the provider, but should be unique across all UpdateProviders, so we recommend that you generate a GUID everytime you add a new update to a provider.

    public sealed class CustomUpdateProvider : UpdateProvider
    {
        public override IEnumerable<Update> GetUpdates() => new List<Update>()
        {
            SqlUpdate.InitializeDatabase(this, "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DataProcessingActivity'")
        };
    }

For the SqlUpdate we have provided some helper methods which make it easier to perform certain updates, but it's also possible to execute your own SQL updates.

Adding column

If you want to add a new column to an existing table, you can use SqlUpdate.AddColumn. Here you need to provide:

Id for the update (GUID)
The update provider handling this update
Name of the table where you want to add the column
Name of the column you want to add
Definition of the column

    SqlUpdate.AddColumn("2a8b0740-1f02-4ed2-a78d-62052c3cecc7", this, "GridRow", "GridRowFlexibleColumns", "[nvarchar](50) NULL"),

This method will make sure to check if the column already exist, and if it does it will not do make any changes to the database schema.

Changing existing column

If you want to make a change to an existing column in a table, you can use SqlUpdate.ChangeColumn. Here you need to provide:

Id for the update (GUID)
The update provider handling this update
Name of the table where you want to change the column
Name of the column you want to change
Definition of the column

    SqlUpdate.ChangeColumn("5ac3a147-e79a-439b-a7a4-3d9df2ba40d8", this, "EcomProducts", "ProductExternalId", "[nvarchar](40) NULL"),

Removing existing column

If you want to remove an existing column in a table, you can use SqlUpdate.DropColumn. Here you need to provide:

Id for the update (GUID)
The update provider handling this update
Name of the table where you want to remove the column
Name of the column you want to remove

    SqlUpdate.DropColumn("2b8d935e-fa50-445e-bfe3-947284cdba63", this, "ScreenPreset", "ScreenPresetScreen"),

This method will check if the column already exist, and if it does then it will remove all the data from the column and delete the column. This operation cannot be undone, and the data will be forever lost.

Adding table

If you want to add a new table to the database, you can use SqlUpdate.AddTable. Here you need to provide:

Id for the update (GUID)
The update provider handling this update
Name of the table you want to add
The definition of the table

           SqlUpdate.AddTable("b9af52b6-dc2f-4c1d-840c-7e92aa3cf438", this, "AuthorizedHosts", @"
           (
            [AuthorizedHostId] [int] IDENTITY(1,1) NOT NULL,
            [AuthorizedHostName] [nvarchar](50) NULL,
            [AuthorizedHostDomain] [nvarchar](50) NULL,
            [AuthorizedHostPermissions] [int] NULL,
            [AuthorizedHostCreated] [datetime] NULL,
            [AuthorizedHostUpdated] [datetime] NULL,
               CONSTRAINT [DW_PK_AuthorizedHosts] PRIMARY KEY CLUSTERED
               (
                   [AuthorizedHostId] ASC
               )
           )")

Adding index

If you want to add a new index to a table in the database, you can use SqlUpdate.AddIndex. Here you need to provide:

Id for the update (GUID)
The update provider handling this update
Name of the table where you want to add the index
Name of the index you want to add
The definition of the table

    SqlUpdate.AddIndex("c59478a0-618f-45e9-8726-48be1bf217b1", this, "Paragraph", "IX_Paragraph_ParagraphDeleted", "([ParagraphDeleted])

The UpdateProvider supports different kinds of updates:

SQLUpdate SettingUpdate MethodUpdate FileUpdate