Database conversion using the structure´s XML file

This article shows an example of database conversion with an XML file that passes object values from one column (integer data type) to another column (string data type).

DbStructureExtension base class methods

/// <summary>
/// Database provider Uid
/// </summary>
public abstract Guid ProviderUid
 
/// <summary>
/// Structure
/// </summary>
public virtual DbStructure Structure
 
/// <summary>
/// Version (be default, version of the assembly with the current class)
/// </summary>
public virtual Version Version
 
/// <summary>
/// Parent data mapper (for providers of certain DB)
/// </summary>
public virtual Type Parent 
 
/// <summary>
/// Links to DB converters, on which this exact data mapper depends
/// </summary>
public virtual Type[] References 
 
/// <summary>
/// Assembly that contains the resource with the structure (by default, assembly of the current class)
/// </summary>
protected virtual Assembly Assembly
 
/// <summary>
/// Name of the file containing the structure in the assembly resources (by default it is the same as the name of the current class, with.xml added)
/// </summary>
protected virtual string ResourceFileName

Example of implementing the DbStructureExtension class

[Component]
internal class MyModuleDbStructureCs : DbStructureExtension
{
  public override Guid ProviderUid
  {
    get { return Guid.Empty; } //If necessary, for example, for a Firebird database, you can use FirebirdProvider.UID
  }
     
  public void MoveColumn()
  {
    const string tableName = "CALLS";
    const string newColumn = "NUMBEROFCALLS";
    string oldColumn = Locator.GetServiceNotNull<DbModelUpdater>().GetDeletingColumnTemporaryName(tableName, newColumn); //Old column 
//is renamed before deleting, so it is necessary to get the new name  
// rewrite the data to the new column, then it will be deleted 
 
    var transformationProvider = Locator.GetServiceNotNull<ITransformationProvider>();
    if (transformationProvider.ColumnExists(tableName, newColumn) &&
      transformationProvider.ColumnExists(tableName, oldColumn))
    {
      transformationProvider.ExecuteNonQuery(string.Format(
        "UPDATE {0} SET {1} = CAST({2} as varchar(255))",
        transformationProvider.Dialect.QuoteIfNeeded(tableName),
        transformationProvider.Dialect.QuoteIfNeeded(newColumn),
        transformationProvider.Dialect.QuoteIfNeeded(oldColumn)));
    }
  }
}

In this example, the object CALLS has the NUMBEROFCALLS property of Integer data type; after instances were added to the object, it was required to the old property with the NUMBEROFCALLS property of the String data type. For that, the old property is deleted from the object and a new one is created with the same display name, property name and database field name but with the String data type. During database conversion, the old column is renamed and then deleted. The code written above is executed between these two events.    

For database conversion, you need an .xml, file with the same name as the one of the class inherited from DbStructureExtension (it is so by default). The extension point code and the xml file must be located in the same place. Also, the xml file must have Build Action – Embedded Resource:

Fig. 1. Location of MyModuleDbStructureCs.cs and MyModuleDbStructureCs.xml

In this example, the name of the xml file is MyModuleDbStructureCs.xml. Example of code:

<?xml version="1.0" encoding="utf-8" ?>
<root uid="{3B204C3F-B611-4fc3-A859-AAD3F355CE71}">
  <methods>
    <method name="MoveColumn" ExecuteTime="OnTablesDeleting"/>
  </methods>
</root>

When the server is restarted after the module’s activation, the system will look for an .xml file with the name of the class inherited from DbStructureExtension. Then, the names of the methods required to convert the database will be found in the xml file according to the <method name="" /> tags.  As you can see, the class code is using the CreateDefaultFilters method, so it is tagged correspondingly in the xml file. It is required to place the database conversion class to the same place as the xml file.  

The <method/> tags have additional parameters, for example: <method name="DataSecondPassConverting" ExecuteTime="OnTablesDeleted" AlwaysExecute="true" />

Parameters:

Also, in this xml you can use SQL queries. For that, use the corresponding tag:

<script name="fixTask">
      <text>
        UPDATE TaskBase SET Executor = 2 WHERE id = 1
      </text>
</script>

Additional parameters used in the <method/> tag can also be applied to the <script> tag. 

Links to API elements

DbStructureExtension

DbStructureExtension