Web Deploy / MS Deploy Connection String Parameterisation

MsDeploy/WebDeploy parameterisation is pretty useful.  It allows a decent amount of flexibility to deploy a single artefact to multiple environments. 

But it doesn’t always work quite as expected, specifically for Connection Strings in the configuration file.

Scenario:

If you create Parameter entries in parameters.xml, when you deploy a web application project you will receive a SetParameters.xml file with entries based on the parameters.xml.  When defining the parameters, you can give them friendly parameter names (and even prompt text which is used when deploying via IIS management screens).

However if you look at the parameters.xml file that is created in the zip manifest, you will see non-friendly parameter entries for the connection strings, and if you created a ‘friendly’ parameter in the source parameters.xml, this friendly entry will have no transformation rules applied.

This means during msdeploy, the friendly entries in your SetParameters file are ignored, so the connection strings are not updated in web.config during the deployment.

Details:

So if your source Parameters.xml contains
<parameter defaultvalue="" name="Connection String 1">
    <parameterentry match="/configuration/connectionStrings/add[@name='ConnectionString1']/@connectionString" scope="\\web.config$" type="XmlFile">
    </parameterentry>
</parameter>
<parameter defaultvalue="" name="Connection String 2">
    <parameterentry match="/configuration/connectionStrings/add[@name='ConnectionString2']/@connectionString" scope="\\web.config$" type="XmlFile">
    </parameterentry>
</parameter>

And the following in your SetParameters.xml file using your friendly names

<setparameter name="Connection String 1" value="{connection string you want}">
</setparameter>
<setparameter name="Connection String 2" value="{connection string you want}">
</setparameter>


The compiled manifest parameters.xml will look like
<parameter name="Connection String 1" defaultValue="" />
<parameter name="Connection String 2" defaultValue="" />

<parameter name="ConnectionString1-Web.config Connection String" description="ConnectionString1 Connection String used in web.config by the application to access the database." defaultValue="{default value here}" tags="SqlConnectionString">
<parameterEntry kind="XmlFile" scope="{magic string representing web.config}" match="/configuration/connectionStrings/add[@name='ConnectionString1']/@connectionString" />
</parameter>
<parameter name="ConnectionString2-Web.config Connection String" description="ConnectionString2 Connection String used in web.config by the application to access the database." defaultValue="{default value here}" tags="SqlConnectionString">
<parameterEntry kind="XmlFile" scope="{magic string representing web.config}" match="/configuration/connectionStrings/add[@name='ConnectionString2']/@connectionString" />
</parameter>

As you can see, the friendly name entries have no content, so when deploying your SetParameters values are read from the file, but never applied to the config file.

Resolution:

The fix is pretty simple – remove the friendly entries from source control in parameters.xml and setparameters.xxx.xml, and add non-friendly name entries just to the setparameters.xxx.xml – the non-friendly names are ‘predictable’ although if you are desparate, just check the parameters.xml in the manifest after a build.

NHibernate Cross-database Hacks

Cross-Database Queries

Ok, so this is a bit of a hack, but it does work.  Thanks to this which set me down the "right" path.

I work with a few legacy systems that have central databases for common information, and individual databases for application specific information.  The data is queried using joins across the databases to retrieve the data required.
A separate service model was introduced for the common data, but when performing filtered queries across both data sets the service model was not efficient (this is a greater issue of context boundaries that I won’t go into here).  To perform the queries that were previously performed using stored procedures using cross-database joins in nHibernate required a bit of a cheat.

nHibernate mappings have a “schema” property as well as the more commonly used “table” property.  By manipulating this schema property you can convince nHibernate to perform cross-database queries.  Setting the schema to “{database}.{schema}” any join query to that element will effectively use the full cross-database query syntax when converted to a SQL query.

Neat (but ultimately not very satisfying because it is not a very nice design).

Bigger Hack, run away

If the target database name is not known until runtime, you can even hack it more to support this.

During the configuration of the nHibernate session factory, you can make a few modifications that will allow you to update the schema property of an entity.  This is useful if you have a different ‘other’ database name for each environment (e.g. OtherDatabase-dev, OtherDatabase-prd).

First we appropriately generate the fluent configuration, and build it.
We then iterate through the class mappings.  Each persistentClass.MappedClass is the underlying POCO model object of the entity.
We check if this is one that we want to override the schema property for (IOtherDatabaseEntity is a simple blank interface, it could be done via naming convention or whatever)
And then update the schema property on the mapping
Finally we create the session factory from the modified config

var fluentConfiguration = config.Mappings(m =>
        m.FluentMappings
        .AddFromAssemblyOf()
        );
var builtConfig = fluentConfiguration.BuildConfiguration();

foreach (PersistentClass persistentClass in builtConfig.ClassMappings)
{
        if (
            typeof(IOtherDatabaseEntity)
                .IsAssignableFrom(persistentClass.MappedClass)
            )
        {
            persistentClass.Table.Schema = cdrName;
        }
}
                  
ISessionFactory sessionFactory = builtConfig
        .BuildSessionFactory();

Hacks away!

NHibernate + LINQPad

While looking at ways to assess and improve performance some nHibernate queries, I was frustrated with the tools at my disposal.

I will start with the point: I don't have access to nhprof.  It seems to be the gold standard for any nHibernate shop, but them's the breaks.

What I was doing:

It was painful to execute the entire codebase to run one or two queries and view the output sql, so I started writing integration-unit tests for query optimisation.  This was slightly better, but still required a change-compile-run-review process which was annoying.

What was I thinking:

I then remembered I have a personal LINQPad license that I hadn't used in a while and wondered if I could get it working.  I saw this which helped me on my way, but we don't use nHibernate.Linq, so the steps were a bit different.

The outcome was extremely useful however, and now I am free to tinker with my queries a lot more freely.

How I did it:

To start you need to add the references to nHibernate that you require (in my case NHibernate, Iesi.Collections, and FluentNHibernate).  You then add the references to your Domain / Models and mapping assemblies.

The next step is to create a hibernate.cfg.xml file with the appropriate configuration for your database.  Make sure you set show_sql=true in the configuration so LINQPad can display the generated SQL.

Then you can call the following code

var cfg = new Configuration().Configure(@"D:\linqpad\ahs\hibernate.cfg.xml");
var factory = Fluently.Configure(cfg)
                .Mappings(m =>
                {
                    m.FluentMappings.AddFromAssemblyOf();
                })
                .BuildSessionFactory();


using (var session = factory.OpenSession()){
    var site = session.QueryOver()
    .List();
}

and viola, you can now tinker with queries as you will, with immediate feedback on the generated SQL and execution time.


You can then save this as a query, or save the assembly references/using statements as a snippet to get you up and running quickly for new queries. 

Caveats:

This method only works with pre-compiled entity mappings, so if you intend to improve performance at the entity mapping layer you still need to do this through your application and export the assemblies for LINQPad to use.

Extensions:

LINQPad allows you to create an 'application config' file that is used when your inner assemblies require web/app.config sections.  Run the code:

AppDomain.CurrentDomain.SetupInformation.ConfigurationFile

to find the location of the file, and if it does not exist create it.  Note that unlike most .NET apps, this is not the LINQPad.exe.config, but LINQPad.config.  Enter any configuration you need into this file.  This can include the nHibernate config  instead of the separate file (but limits configuration flexibility).

This allows you to configure things like nHibernate 2nd level cache instances, such as memcache.  As long as you include the necessary libraries in the query references, and the configuration in the linqpad.config file this will work and provide even greater flexibility for performance analysis and testing.

Conclusion:

So there you go, a "poor man's" guide to nHibernate performance analysis, thanks to the ever awesome LINQPad.