Is there a way to inject custom sql during the build call? We are going to create IndexedViews and would like to keep all of our creation code in DO.Net rather than script files.

asked Apr 24 '12 at 10:10

MikeBenson's gravatar image


One Answer:

Hello Mike,

all you need is to create upgrade handler and override OnStage() method

public class MyUpgradeHandler : UpgradeHandler
  public void override OnStage()
    base.OnStage(); // Don't forget to run base logic

    // You upgrade code goes here
    var session = Session.Demand();
    var sqlAccessor = session.Services.Demand<DirectSqlAccessor>();
    // You can use sqlAccessor to access DbConnection and DbTransaction

OnStage() might be called multiple times depending on your upgrade mode:

For Skip, Validate and Recreate it is called once. For Perform and PerformSafely it is called 3 times.

In the last case you can use UpgradeContext.Stage to check what stage is current. You might also want to check for current domain upgrade mode by reading UpgradeContext.Configuration.UpgradeMode property.

There are 3 stages: Initializing, Upgrade and Final.

Initializing stage occurs before doing any actual upgrade actions (i.e. schema is not changed). You can't access your persistent types here.

Upgrade stage occurs just after database schema has been changed to be superset of target schema. If you have any [Recycled] types or columns they are available. In other words database schema consists of all tables that are required to run your domain with current configuration plus any tables and columns you requested to keep. This allows you to migrate your data before tables for removed types will be dropped.

Final stage occurs just after database schema is cleaned up. All [Recycled] tables and columns are already removed at this stage. Also this is the only stage for Skip, Validate and Recreate upgrade modes.

All stages occur even no schema changes are required. If you need to create indexed views, you need to check if they already exist in database. Also it might be reasonable not to create views if upgrade mode is Validate or Skip since those mode are expected not to change database schema.

answered Apr 25 '12 at 04:20

Denis%20Krjuchkov's gravatar image

Denis Krjuchkov

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview


Once you sign in you will be able to subscribe for any updates here



Asked: Apr 24 '12 at 10:10

Seen: 3,202 times

Last updated: Apr 25 '12 at 04:20

powered by OSQA