Using a different database for a feature

Occasionally you may develop a feature for mojoPortal where you need to access a different database than the mojoPortal database for various reasons. Of course in your data layer you may use a different connection string than the one used by mojoPortal to access your database. In some cases you may wish to still be able to use the mojoPortal Setup system to install your tables and other database object like stored procedures. As of 2010-08-29 I have implemented a simple convention based solution to make this possible. It does require the latest version of the code as of 2010-08-29 or newer.

Example Scenario

I recently came upon a need for this while developing my add on product In Site Analytics which retrieves data from the Google Analytics API and stores it in the database in order to keep a permanent record (since Google only keeps data for about 2 years), and also it supports live queries against the Google Analytics data API. Actually I've found I can retrieve data going back about 3 years in my account for mojoportal.com. The aggregate site level statistics does not require much data storage, but retrieving page level data for each url captured by google analytics for each day, turns out to be a very large amount of data. For www.mojooportal.com, page level data going back 3 years resulted in about 787,000 rows and one can imagine how much it may grow if we continue to capture the data for the next 3 years. This kind of data is more like a data warehouse kind of application where we want to be able to query the data for reporting purposes. This amount of data can really increase the size of your database quite a bit, and it may be preferable to store it in a separate database from the site, so it got me thinking about how to make it possible to use an alternate connection string but still be able to install it with the mojoPortal setup page, so that the tables and procedures for In Site Analytics can be installed in a different database.

The first piece of the puzzle is making the data layer use an alternate connection string if it is found in appSettings, or if not then just use the mojPortal connection string. Code like this can solve the problem:

using System.Configuration;

namespace sts.Analytics.Data
{
    internal static class ConnectionString
    {
        /// <summary>
        /// Gets the connection string for read.
        /// </summary>
        /// <returns></returns>
        public static string GetConnectionString()
        {
            if (ConfigurationManager.AppSettings["sts_analytics_ConnectionString"] != null)
            {
                return ConfigurationManager.AppSettings["sts_analytics_ConnectionString"];
            }

            return ConfigurationManager.AppSettings["MSSQLConnectionString"];

        }

 

    }
}

so in my data layer classes in the same assembly I get the connection string with ConnectionString.GetConnectionString()

Notice the convention in my alternate connection string is "[applicationname]_ConnectionString" where application name corresponds to the folder name under /Setup/applications/[application]

So, to use the setup page it requires that my scripts are located under /Setup/applications/sts_analytics/

below that folder is SchemaInstallationScripts/[dbplatform] and SchemUpgradeScripts/[dbplatform]

where dbplatform is mssql, mysql, sqlce, pgsq, sqlite, or firebirdsql

and version specific script files go into the dbplatform folder.

In order for it to work, the alternate connection string must be specified in Web.config or ideally user.config before you run the setup page. So the first step should be to establish the alternate connection string before installing any files. In my example, the connection string is added like this:

<add key="sts_analytics_ConnectionString" value="server=localhost\SQLExpress;UID=yyyyyy;PWD=xxxxx;database=mojo_analytics" />

The logic in the setup page is to look for the convention based connection string "[applicationname]_ConnectionString" based on the application folder name, if it finds a value it passes this in as an override connection string when running the scripts, otherwise it just uses the main connection string.

Note however that the schema version for the feature is still going to be recorded in the main site database in the mp_SchemaVersion table, and things like module settings also will still be stored in the main database. Also keep in mind that the use of an alternate database means you can't really join to any mojoPortal tables in your custom feature. It can only be useful when the data in the alternate database is completely self contained and independent of the site data.

Last Updated 2010-08-29 by Joe Audette