How to change the database structure

Zonal OCR uses a file-based SQL CE database stored on the user's computer to persist templates, fields, etc. If you need to make a change to the structure of the database (tables, columns, or whatever) follow these instructions.

Update the UpdateDbTool project

UpdateDbTool automatically runs on the user's machine every time they install a new version of Zonal OCR. First, we need to update the tool so that it can detect if the user's database requires an update, and then how to apply that update.

The general approach is to execute a query to check if the user's database needs an update and, if so, execute the commands to change the database.

Example
As a specific example, here's how you would modify UpdateDbManager.cs to add a new ExcludeFirstPage column to the existing Forms table. (This example assumes this will be the 6th time we've updated the Zonal OCR database since its initial release.)

1. Add a string property to the class called AlterFormsAddExcludeFirstPage that contains an ALTER statement that, when executed, adds the column to the table.
2. Add a string property to the class called CheckVersion6Installed that contains a query that, when executed, checks if the ExcludeFirstPage column exists in the Forms table.
3. Create a PrepareUpdateQueriesVersion6 method that adds the AlterFormsAddExcludeFirstPage property to the list of queries to be run.
4. In the PrepareUpdateQueries method, write a conditional that calls the CheckVersionInstalled method, passing the CheckVersion6Installed string as an argument, to determine if the database needs updated.
   
5. Inside the conditional, call your PrepareUpdateQueriesVersion6 method.

Apply Changes
To test your code and apply the changes to your local installation of Zonal OCR, you can simply set the UpdateDbTool project as your startup project in Visual Studio and run the solution. (You may be prompted to re-open Visual Studio as an administrator.) You do NOT need to create a new installer just to test your changes. 

Apply migration to the empty database

When

• Updatea user first installs Zonal OCR, an empty copy of the database file is placed in their documents folder. Be sure to update this file in the ZonalOCR solution and include it when committing the rest of your changes to source control.

  Update these files using UpdateDbTool:

  • \DataAccess\ZonalOcrDb.sdf
  • \ZonalOCR\ZonalOcrDb.sdf

  Update the ZonalOCR project

  After the database structure has been updated, you need to update some files in the DataAccess project in order to read/write to it

  • DataAccess
    • ZonalOcrDb.edmx.sql - update initial creation queries
    • ZonalOcrDb.edmx
      • <edmx:StorageModels> -> <EntityType>
      • <edmx:ConceptualModels> -> <EntityType>
      • <EntitySetMapping>
    • Update models in /EntityModel/