How to: Handle Schema Migrations Programmatically

Telerik Data Access provides you an API for creating/migrating your database programmatically. The OpenAccessContext exposes a method named GetSchemaHandler that returns the schema handling instance. With the schema handler in hand, there are several methods you can use to automatically create a database script, see if the database exists, and/or create the database.

• CreateDatabase - creates the database using the current connection string and the metadata provided by the OpenAccessContext.
• DatabaseExists - checks if the database that is specified as the database in the current connection string exists on the data source.
• CreateDDLScript - generates a data definition language (DDL) script that creates schema objects (tables, primary keys, foreign keys) by using the metadata provided by the OpenAccessContext.
• CreateUpdateDDLScript - generates a migration data definition language (DDL) script.
• ExecuteDDLScript - executes a data definition language (DDL) script.

Additionally, the ISchemaHandler interface exposes a method named CreateUpdateInfo, which provides information about the statements included in the script.

In this topic:

• How to Generate Database Based On an Existing Model
• Schema Migration Details
• How to Force Schema Update

How to Generate Database Based On an Existing Model

The following example shows you how to generate a database based on an existing model.

using (FluentModel dbContext = new FluentModel())
{
   Telerik.OpenAccess.ISchemaHandler schemaHandler = dbContext.GetSchemaHandler();
   string script = null;
   if (schemaHandler.DatabaseExists())
   {
       script = schemaHandler.CreateUpdateDDLScript(null);
   }
   else
   {
       schemaHandler.CreateDatabase();
       script = schemaHandler.CreateDDLScript();
   }
   if (!string.IsNullOrEmpty(script))
   {
       schemaHandler.ExecuteDDLScript(script);
   }
}

Using dbContext As New FluentModel()
 Dim schemaHandler As Telerik.OpenAccess.ISchemaHandler = dbContext.GetSchemaHandler()
 Dim script As String = Nothing
 If schemaHandler.DatabaseExists() Then
  script = schemaHandler.CreateUpdateDDLScript(Nothing)
 Else
  schemaHandler.CreateDatabase()
  script = schemaHandler.CreateDDLScript()
 End If
 If Not String.IsNullOrEmpty(script) Then
  schemaHandler.ExecuteDDLScript(script)
 End If
End Using

Schema Migration Details

In the previous example, the SchemaHandler API generates generates a DDL script in the form of a string containing the needed SQL statements to synchronize the database. However, sometimes this could introduce breaking changes for the application already running against the current schema. And there is no way to be aware of those changes without parsing/looking at the script itself.

The ISchemaHandler interface exposes a method named CreateUpdateInfo. It returns a new SchemaUpdateInfo object. This object contains not only the DDL script but many Boolean flags, which indicate what kind of changes are contained in the script. This way you could easily detect whether a breaking change will be introduced and cancel the database migration.

The most important properties exposed by the SchemaUpdateInfo class are:

• IsTrivial - the generated script contains schema artifacts without influencing the existing schema.
• IsExtending - the generated script contains schema artifacts that extend the existing schema.
• IsComplex - the generated script contains schema artifacts with structural changes.

Those properties are calculated based on the other bool flags and represent the level of complexity of the generated script. For more details about the meaning of each property, please refer to Working with Data Definition Language (DDL).

The following example demonstrates a sample usage of the CreateUpdateInfo method.

SchemaUpdateInfo schemaUpdateInfo =
   schemaHandler.CreateUpdateInfo(new SchemaUpdateProperties() { CheckExtraColumns = false });
if (schemaUpdateInfo.HasScript)
{
   if (schemaUpdateInfo.IsExtending)
   {
       schemaHandler.ExecuteDDLScript(schemaUpdateInfo.Script);
   }
}

Dim _schemaUpdateInfo As SchemaUpdateInfo =
 schemaHandler.CreateUpdateInfo(New SchemaUpdateProperties() With {.CheckExtraColumns = False})
If _schemaUpdateInfo.HasScript Then
 If _schemaUpdateInfo.IsExtending Then
  schemaHandler.ExecuteDDLScript(_schemaUpdateInfo.Script)
 End If
End If

How to Force Schema Update

By default the schema migration is done only if there are no open contexts for particular Database object. If there are open context, there is a risk that the database is unsynchronized with the metadata used by the existing contexts, which could lead to exceptions. If the user explicitly wants to update the database, he can do that using the new "Force" methods exposed by the ISchemaHandler interface:

• ForceExecuteDDLScript - executes a DDL script on a used database instance.
• ForceUpdateSchema - executes DDL statements on a database instance with open contexts.