Global Application Settings Override (Now with examples!)

My previous posting about TableAdapter connection strings was a pretty big hit with a lot of people. It’s still being referenced on the MSDN forums from time to time when someone needs a solution to the problem of having a connection string for a TableAdapter in the app.config that they need to override at runtime.

Since that post was in VB.Net and over 2 years old now, I’ve decided to resurrect it and work up a quick sample project with both C# and VB.Net code. Someone had request C# and I have no idea why I didn’t include it when I did that post, but better late than never!

Click here for the sample solution. (VS2010) If you don’t have that version of visual studio, you can download the express version from Microsoft, or just open the projects individually in VS2008.

Wix

In finally get CruiseControl.Net set up to not only automate our build process, but notify us of any breaking changes on checkin, I discovered some issues in using the standard setup and deployment projects. Not wanting to spend a lot of time on it, I decided to look into Wix and see how it could help me streamline things.

It turns out Wix is a whole lot more work. Instead of a simple point and click interface, I’m presented with hand editing the XML file. Boo.

There are a few decent open source projects to give me a GUI interface so I can get my point and click back, but they turned out to either be for Wix 2 or they had some serious bugs in them.

In the end I used Dark to script my MSI file. While this worked out ok, Wix 3 wouldn’t compile the script without me making some changes. I dug through the script it created and I started to wonder how I was going to automate this. The trick is really not to automate it. The trick is to set up your script once and then leave it alone. Modifications are only made when things change in your build. That’s fine, but what about setting it up for the first time? You have to create a GUID for each component (read, every file if you want the files updateable) that gets installed. This can be time consuming if you have quite a few ancilary files that need to be shipped with the release.

Well, I automated that part. I created myself a small console application that will take a folder and a output file name as arguments. The program will traverse this folder and create an include script. It treats every file, directory, and directory of files as individual components.

In case anyone wants to do this but doesn’t want to take the time to write all the code, here it is.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Reflection;

namespace CreateIncludeScript
{
    class Program
    {
        static int Main(string[] args)
        {
            if (args.Length < 2 || args.Length > 2)
            {
                return (-1);
            }
            string folder = args[0];
            string outputfile = args[1];
            XmlDocument xmlDoc = new XmlDocument();
            XmlNode root = xmlDoc.AppendChild(xmlDoc.CreateElement("Include", "http://schemas.microsoft.com/wix/2006/wi"));

            XmlNode dir = root.AppendChild(xmlDoc.CreateElement("Directory", "http://schemas.microsoft.com/wix/2006/wi"));
            SetAttr(xmlDoc, dir, "Id", "TARGETDIR");
            SetAttr(xmlDoc, dir, "Name", "SourceDir");

            ProcessDirectory(xmlDoc, dir, folder);

            XmlNode feat = root.AppendChild(xmlDoc.CreateElement("Feature", "http://schemas.microsoft.com/wix/2006/wi"));
            SetAttr(xmlDoc, feat, "Id", "DefaultFeature");
            SetAttr(xmlDoc, feat, "Level", "1");
            SetAttr(xmlDoc, feat, "ConfigurableDirectory", "TARGETDIR");
            ProcessFeatures(xmlDoc, feat, dir);

            xmlDoc.Save(outputfile);
            return (0);
        }

        static void SetAttr(XmlDocument doc, XmlNode node, string name, string value)
        {
            XmlAttribute attr = node.Attributes.Append(doc.CreateAttribute(name));
            attr.Value = value;
        }

        static void ProcessDirectory(XmlDocument xmlDoc, XmlNode target, string directory)
        {
            string[] files = Directory.GetFiles(directory);
            string[] dirs = Directory.GetDirectories(directory);

            foreach (string file in files)
            {
                FileInfo finfo = new FileInfo(file);

                XmlNode comp = target.AppendChild(xmlDoc.CreateElement("Component", "http://schemas.microsoft.com/wix/2006/wi"));
                string guid = System.Guid.NewGuid().ToString().ToUpper();
                string id = "C_" + guid.Replace("-", "");
                SetAttr(xmlDoc, comp, "Id", id);
                SetAttr(xmlDoc, comp, "Guid", "{" + guid + "}");

                XmlNode fnode = comp.AppendChild(xmlDoc.CreateElement("File", "http://schemas.microsoft.com/wix/2006/wi"));
                SetAttr(xmlDoc, fnode, "Id", "F_" + guid.Replace("-",""));
                SetAttr(xmlDoc, fnode, "Name", finfo.Name);
                SetAttr(xmlDoc, fnode, "KeyPath", "yes");
                SetAttr(xmlDoc, fnode, "DiskId", "1");
                SetAttr(xmlDoc, fnode, "Source", finfo.FullName);

                if (finfo.Extension.ToLower() == ".dll" || finfo.Extension.ToLower() == ".exe")
                {
                    if (IsDotNetAssembly(finfo.FullName))
                    {
                        SetAttr(xmlDoc, fnode, "Assembly", ".net");
                        SetAttr(xmlDoc, fnode, "AssemblyManifest", "F_" + guid.Replace("-", ""));
                        SetAttr(xmlDoc, fnode, "AssemblyApplication", "F_" + guid.Replace("-", ""));
                    }
                }
            }

            foreach (string dir in dirs)
            {
                DirectoryInfo dinfo = new DirectoryInfo(dir);

                XmlNode dnode = target.AppendChild(xmlDoc.CreateElement("Directory", "http://schemas.microsoft.com/wix/2006/wi"));
                SetAttr(xmlDoc, dnode, "Id", dinfo.Name.Replace("-",""));
                SetAttr(xmlDoc, dnode, "Name", dinfo.Name);

                ProcessDirectory(xmlDoc, dnode, dinfo.FullName);
            }
        }

        static void ProcessFeatures(XmlDocument xmlDoc, XmlNode target, XmlNode root)
        {
            XmlNamespaceManager xmlmgr = new XmlNamespaceManager(xmlDoc.NameTable);
            xmlmgr.AddNamespace("wi", "http://schemas.microsoft.com/wix/2006/wi");
            XmlNodeList comps = root.SelectNodes("descendant::wi:Component", xmlmgr);

            foreach (XmlNode node in comps)
            {
                XmlNode compfeat = target.AppendChild(xmlDoc.CreateElement("ComponentRef", "http://schemas.microsoft.com/wix/2006/wi"));
                SetAttr(xmlDoc, compfeat, "Id", node.Attributes.GetNamedItem("Id").Value);
                XmlNode file = node.SelectSingleNode("wi:File", xmlmgr);
                if (file.Attributes.GetNamedItem("Assembly") != null)
                {
                    SetAttr(xmlDoc, compfeat, "Primary", "yes");
                }
            }
        }

        static bool IsDotNetAssembly(string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                try
                {
                    using (BinaryReader binReader = new BinaryReader(fs))
                    {
                        try
                        {
                            fs.Position = 0x3C; //PE Header start offset
                            uint headerOffset = binReader.ReadUInt32();
                                                         fs.Position = headerOffset + 0x18;
                            UInt16 magicNumber = binReader.ReadUInt16();
                                                         int dictionaryOffset;
                            switch (magicNumber)
                            {
                                case 0x010B: dictionaryOffset = 0x60; break;
                                case 0x020B: dictionaryOffset = 0x70; break;
                                default:
                                    throw new Exception("Invalid Image Format");
                            }

                            //position to RVA 15
                            fs.Position = headerOffset + 0x18 + dictionaryOffset + 0x70;

                            //Read the value
                            uint rva15value = binReader.ReadUInt32();
                            return (rva15value != 0);
                        }
                        finally
                        {
                            binReader.Close();
                        }
                    }
                }
                finally
                {
                    fs.Close();
                }
            }
        }
    }
}

Make it easy for the IT department.

For quite a while I have noticed that there seems to be this huge divide between IT departments and the software developers. I say IT meaning the hardware/software support department and the programmers that write software. In my organization, we are only software developers. Any hardware support is done by me because I have had the most experience with it. Everyone else handles their own software issues. If they have an issue with Word or with their virus scanner of choice, they have to fix it themselves or ask questions to find out who knows what to do. Whenever I have had to call someone in IT at our customer’s location, we constantly are met with resistance and multitudes of questions that point to a certain mistrust of software developers. They almost treat us like we are Microsoft and are out to install a patch that will bring their entire system down for two days.

I start to gain a little insight on why it’s like this the other day. When we install our server components, which is solely SQL Server 2005 Express with a database including stored procedures and triggers, we have an installation package where you just have to click on setup.exe and let it do its thing. When I called the main contact at the IT department, I heard the hesitancy in his voice. I assured him he could do the installation if he preferred not giving me remote access to the server, but he insisted that I do it for him. No problem. Since we are customer focused, we always try to do the client and server installs of our software, anyway. I downloaded the files I needed and clicked on the setup program and just sat there while it went through the entire process. We chit-chatted about servers and Microsoft and had a few laughs over the new security requirements. He then described multiple instances where servers were down for a day or two because of patches that didn’t apply right and how he has had to call Microsoft dozens of times to get hotfixes or special patches because the combination of hardware and software they use is not normal. I usually do not get concerned when I hear this because we don’t usually care what they use, as long as Exchange Server is not on the same server as SQL Server.

After 15 minutes and clicking next a few times, our install was done. He said, “Ok, so what do we do now?” My response was simple, “Nothing. It’s all ready.” He was disbelieving and ran the client software and tried a few things to check. The next 10 minutes was eye opening. He described how this was the best install he has had to watch in a long time and was very pleased that we were so IT friendly. He described a situation that made me, as a software developer, cringe. They had purchased a brand new accounting system that cost their organization almost a hundred thousand dollars (US funds) and the installation took about 3 days. It took 3 days because they gave him the CDs and said to install it, but never gave him any instructions on how. Being used to installing software, he put in the CD marked #1 and ran the setup. It half way installed and then gave a half dozen error messages that consisted of things like “Error Occurs” and “Cannot Continue.” He tried called technical support and all he got was voice mails. He left messages but never received any phone calls in return.

He spent the next 3 days trying to solve the all problems himself, but with no documentation on requirements or procedures. When technical support finally called him back, the solution was to install the third CD first and then try the first CD. Why would you install a CD marked #3 before the CD marked #1? He told me that now he is highly concerned that they made the right choice because it’s their accounting system and how can they trust the vendor’s technical support department if he can’t get an answer in less than 3 days? What if they need to do payroll and “error occurs” and technical support doesn’t return phone calls?

This is why there is animosity between IT and software developers. Make it easy for them. Give descriptive error messages and most certainly answer your phone if it rings! Even if you know it’s a stupid problem they should be able to solve just by reading the screen, at least that phone call will keep their confidence that you will be there where there is a problem.

Windows Installer – SQL Server Installation Issues and a Solution.

Here is the problem in a nutshell: You have a custom client application that uses SQL Server 2005 Express. The server installation is a little bit to be desired. First, you have to install SQL Server Express, then you have to configure the options (unless you did command line options), then you have to execute the scripts to create your database, tables, and possibly populate it with some data. Or conversely, execute scripts or install and use the management console to attach a database. Messy, at best.

So what if you want to have a single installation program that will install all the prerequisites (.Net), then install SQL Server 2005 with your custom options (instance name for example), and then have it execute scripts and fill it with data? After working at it for hours, I came to the conclusion that you could be out of luck. Until I did some reconfiguring.

First things first, you need to get SQL Server Express to install properly. I struggled with this for a while, until I had the bright idea of using a bootstrapper… But can’t we use the one that comes with the Visual Studio Package and Deployment Wizard? Yes you can!

Go into your Visual Studio 8 folder and find the SDK/v2.0/Bootstrapper/Packages folder. Make a copy of the SqlExpress folder and name it something like, MyAppSqlExpress. Inside that folder, there is a product XML file. Edit that product.xml file and change the product code. Mine was Microsoft.Sql.Server.Express.1.0 and I changed it to MyAppName.Microsoft.Sql.Server.Express.1.0 … Just change the MyAppName to the app name of the product it goes with. You will also see a En folder for the English installation. The package.xml file is the one you want to edit in that folder. The first item to edit, is the arguments line. This passes arguments to the SqlExpr32.exe file (the setup for Sql Express). The arguments are the same as any other SQL Express install… So change it to be something like this:

Arguments='-q /norebootchk /qb reboot=ReallySuppress addlocal=all 
  INSTANCENAME=MyApp SECURITYMODE=SQL SAPWD=MyStrongPassword 
  DISABLENETWORKPROTOCOLS=0 SQLAUTOSTART=1'

This will do a silent installation of SQL Server Express using all protocols, a custom instance name (MyApp), turn on the SQL security mode and specify a strong SA password.

Next, there is the strings block at the bottom. Edit the StringName “DisplayName” to look like this:

MyApp SQL Server 2005 Express Edition

Save it, and go into your setup and deployment project you created, go to the project properties, then to the prerequisites, and in the list you should now see your custom install of SQL Server Express!

This does work, I just tested it out today.

So that solves problem #1. What about the problem of executing scripts? That seems to be the easy part… From my earlier post you can incorporate that code into a custom action.

Create a new project, and select Installer Class from the templates. This creates a new installer class for you to augment.

  Private Sub ExecuteSQL(ByVal SQL As String, _
    ByVal ConnectionString As String)
    
    Dim srv As New Server(New ServerConnection( _
    New SqlClient.SqlConnection(ConnectionString)))
    
    srv.ConnectionContext.ExecuteNonQuery(SQL)
    srv.ConnectionContext.Disconnect()
  End Sub

  Private Sub BulkLoad(ByVal ConnectionString As String, _
    ByVal TableName As String)
    
    Dim bulk As New SqlClient.SqlBulkCopy(ConnectionString, _
    SqlClient.SqlBulkCopyOptions.KeepIdentity)
    
    Dim ds As New DataSet
    Dim path As String = My.Application.Info.DirectoryPath
    ds.ReadXmlSchema(path & "\" & TableName & ".xsd")
    ds.ReadXml(path & "\" & TableName & ".xml")
    bulk.BulkCopyTimeout = 0
    bulk.DestinationTableName = TableName
    bulk.WriteToServer(ds.Tables(TableName))
    bulk.Close()
  End Sub

  Public Overrides Sub Install(ByVal stateSaver As _
    System.Collections.IDictionary)
    
    MyBase.Install(stateSaver)
    Dim ConnBuilder As New SqlClient.SqlConnectionStringBuilder()
    ConnBuilder.UserID = "sa"
    ConnBuilder.Password = "MyStrongPassword"
    ConnBuilder.DataSource = "localhost\MyApp"
    ConnBuilder.InitialCatalog = "master"
    ExecuteSQL(My.Resources.CreateDatabaseSQL, _
    ConnBuilder.ConnectionString())
    
    ConnBuilder.InitialCatalog = "MyDatabase"
    BulkLoad(ConnBuilder.ConnectionString(), "MyTable")
  End Sub

In this example, I just stored the data files (an XML data file and an XSD schema file) in the setup project, which gets installed along with the custom installer DLL. The create database SQL stuff was all saved as a store resource in the installer class project. I did this so that some of the structure was hidden, which may or may not be an issue for others.

Now, add the custom action to a setup project and you are finished!