The AppLife Update Blog

  • Using Callable Action Lists to Update Multiple Databases During a Client Update

    The recently released AppLife Update 4.5 includes many new features, one of which is the ability to create independent, callable action lists. By combining callable action lists with Shared Properties and applying conditional statements to update actions we can more easily accomplish advanced updating activity. One scenario which we can use to demonstrate this functionality is when an application uses multiple local databases. In order to update the installed application, all of the databases that are present must be updated. In this scenario an update will need to be able to:

    1. Identify all of the databases that are present.

    2. Iterate each of the local databases, applying the necessary change script to each one.

    3. Update the application assemblies.

    For this example, the list of databases will be discovered by inspecting the application configuration file. Each database connection string is listed in the connectionStrings segment of the configuration file. For each of the listed databases, we’ll connect to the database, start a transaction, run a SQL script, commit the transaction and close the connection.

    Note that all of the update actions and logic is packaged into a single, stand-alone update package. The update process embedded into the application discovers the new update, downloads it, and then initiates it. Once initiated, the Update Engine executes the update actions that perform the work. This example focuses completely on the update actions. No mention is made of the update process integrated into the application.

    Identify and Manage the Local Database List

    To read the databases that are present and manage the list as the databases are updated, I am going to read an item from the connectionStrings configuration element, update the database identified within it, and then remove the element from the configuration file. Using this method, I can accomplish all of the work using built-in update actions. This is not the only option available. Using Dynamic Code Actions offers more flexibility and an alternative approach. I’ll perform the same work using Dynamic Code actions in my next blog post.

    Now, I mentioned that as we update the databases I am going to remove the element from the config file. We don’t want to modify the actual application configuration file so the first thing that I am going to do is copy the config file to the working update directory. This is a directory created by the update engine and it is deleted when the update ends. I’ll accomplish this by using a Run command line update action. This action includes a helper feature that allows us to use common directories to define two path variables. The Application Directory is the physical path of the application that launched the update. This is where the application configuration file resides. The Update Package Directory is the physical path to the temporary working directory that is created for this update by the update engine. I define <path1> to be the path to the application configuration file, and I define <path2> to be the path of my copy of the config file. I’ll call it config.xml. With the two paths specified, I’ll use them in the actual command line that copies the config file:

    copy “<path1>” “<path2>”

    clip_image001

    To read the entries in the config file, I’ll use a Read Xml Node action. To use this action we specify an Xml file to read, define an XPath expression to identify the node value we are interested in, and then provide a name of a Shared Property to hold the string value of the Xml Node. The Shared Property can then be accessed and used by other actions as well as in conditional statements. I’ll be doing both with this Shared Property.

    clip_image002

    Here is what the config file section looks like:

    <configuration>

    <connectionStrings>

    <add name="Db1" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TestDb1;Integrated Security=True"/>

    <add name="Db2" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TestDb2;Integrated Security=True"/>

    </connectionStrings>

    </configuration>

    I am interested in the connectionString, so my XPath expression is /configuration/connectionStrings/add/@connectionString

    If multiple nodes are found, the action is configured to read the first node, and if no node is present a default value of None is assigned. We’ll use this in a conditional statement. If a node is present, a Shared Property with a key of ConnStr will be populated with a local database connection string. Next we’ll use a callable action list to update this database using SQL Server update actions, remove the connection string from our working config file, and recursively call itself until all of the databases represented in the config file are updated. If the ConnStr Shared Property does not equal the value “None”, we call the action list.

    clip_image003

    Update the Database

    The databases I am updating are SQL Server databases. Therefore I am using the SQL Server Update Actions that are also new to AppLife Update 4.5. Note that there are separate provider-Independent database actions available that can be used for updating any type of database. Using the SQL Server Actions, for each database we are going to:

    1. Open a Database Connection. I’ll configure the action to place the database in single-user mode.

    2. Begin a SQL Server Transaction.

    3. Execute a database query.

    4. Commit the SQL Server transaction

    5. Close the SQL Connection

    To open the connection, I set the connection string directly, and then use the ConnStr Shared Property we already defined. For many built-in actions, you can use Shared Properties that are strings by using a $SharedPropertyKey$ syntax. Wherever this is used, the Shared Property string value is expanded in-line during update execution. Notice that the connection itself is added to the Shared Properties collection using the defined key. Here the key is named SqlConection. We’ll use this Shared Property in the other database actions.

    clip_image004

    With a SQL Connection open, I’ll start a transaction using the Begin SQL Server Transaction action. To use this action we specify the connection to use and provide a Shared Property key name for the transaction. The transaction Isolation Level can also be specified. If the update fails, this transaction will be rolled back automatically unless the transaction is committed by a Commit SQL Server transaction action.

    clip_image005

    With the transaction started, it can be used by one or more Run SQL Server query actions. In this example, I’ll just add a table.

    clip_image006

    Next, I’ll commit the SQL transaction and close the database connection. Taking this approach means that should any of the database updates fail, the database changes made in all previously updated databases would not be rolled back because they have already been committed. I chose this approach for simplicity, however the update could be refactored to maintain all transactions open until all of the databases have been updated, then iterate the transactions and commit each one. Using this alternate approach, should any SQL query fail, all databases would roll back their changes.

    clip_image007

    Closing the connection by specifying the Shared Property key…

    clip_image008

    Remove the Database from the Working Xml File

    With the designated database updated, I am going to remove the connection string from the working Xml file using a Delete Xml Node action. To use this action, I specify the Xml file and as XPath expression identifying the node of interest, just like we did when defining the connection string. The node we are interested in is the connection string entry with the attribute value that matches the connection string of the database that we just updated.

    clip_image009

    With the node removed from the working file, we’ll again use a Read Xml Node action to read the next connection string in the file. If there is another connection string available, we’ll assign it to the ConnStr Shared Property. Otherwise, just like before, if no connection strings are left the value of the Shared Property will be set to “None”.

    clip_image010

    And now as long as the value of the ConnStr Shared property is not “None” we’ll call this same action list again, updating the next database in the list.

    clip_image011

    This process is repeated for all of the databases identified in the connectionStrings configuration element.

    Replace the Application Assemblies

    To complete this update, I’ll use an Add & Replace Files action to replace the main application executable.

    clip_image012

    Summary

    By using the new callable action list feature and the Shared Properties collection, we are able to read the list of local databases from the application configuration file and iteratively update each one. The new features of AppLife Update 4.5 provide more power and flexibility while maintaining the robust, transactable operation of the AppLife updating engine. In a follow-up blog post, I’ll use Dynamic Code actions and custom C# code instead of Xml Actions to read the connection strings and use a collection object instead of a temporary file to manage the databases.

    Download Example Project

    View Execution Log

    Posted at 20 April, 2012 | By :Brian Haas | Categories : AppLife Update | Software Updating |