The installation project is a big piece of the functionality provided by the framework. One of the big challenges of any database driven project is keeping a database and code in sync. When changes are made to a model, the existing database is by definition
out of date. Normally somebody must manually keep these two layers in sync. Sometimes it is a developer writing a one off change script for his modification. Other times there is a DBA or some other singular person responsible for maintaining changes. Either
solution takes time and effort and is prone to human error and forgetfulness.
The modeler keeps these two layers in sync by generating an installer project. Moreover it generates the SQL change scripts. Each generation is a marked version internally. On the next generation, the present and past version will be compared and a change script
emitted that will be run at the appropriate time. Each change script is marked with a version number made up of the four part build number you have specified in the model and a fifth, internal generation number.
When the installer is run on an existing, versioned database, it checks the database version, which is this five part, version number of the installer last run on it. The installer then calculates what are the remaining change scripts from the last point to
the present point and runs them.
There are some databases with a large number of dependencies. Some applications that require numerous views, stored procedures, and functions all with inter-dependencies. By default, the model emits scripts in the order they are entered into the model. This
works fine most of the time since people tend to enter objects in the order they need and run them which naturally build a dependency tree correctly. However when you import objects from an existing database en masse, there is a good chance that the dependency
tree will not be built correctly if you have a large number of inter-dependencies.
To address this issue the modeller has some built-in tools. First, you can order your objects manually. On the nHydrate Utilities menu, there is an option for Precedence Order. Use this to view a list of all objects in defined, dependent order. You may move
objects (views, stored procedures, and functions) up or down the order.
Also notice that there is also an Import button. This functionality allows you to specify an file with an “nOrder” extension if you have one. An nOrder file is an XML key file that orders objects in the model. The installer application will emit one if necessary.
When the installer is run it tries to figure out the dependency tree of all objects it contains. If any of the objects are out of order, it will create an nOrder file in the same folder as the installer assembly file. After a successful installation you can
check this folder for an nOrder file. If one exists, it can be used on the Precedence Order screen to define a order determined by the installer. Once the model, object order is defined correctly and the installer project is re-generated, you will not see
the nOrder file re-created. It is only created if one or more objects are defined out of order.
There are many generated scripts and all of them may not apply to your installation. The installer may have scripts blocked into sections. These sections can be selectively excluded from a particular installation or upgrade on demand. The pre-defined scripts
are blocked off automatically. You can also define your own sections. This is a two step process. First you may define a section block. The syntax is as follows in one of the SQL files. The section below is named “MySection”. You may define whatever name you
wish to a section. It must have a Begin block and an End block.
Database script section syntax
--##SECTION BEGIN [MySection]
--Add some SQL script here
--##SECTION END [MySection]
If you wish to exclude this section from the install, simply add it to a string list of skipped sections and pass it into the installer.
var skipSections = new List<string>();
var dbInstaller = new Acme.MyProj.Install.DatabaseInstaller();
MasterConnectionString = "...",
NewDatabaseName = "...",
ConnectionString = "...",
SkipSections = skipSections,