Whilst creating an application to run the excellent SQL migration tool DbUp as part of a continuous deployment setup, I stumbled across a question I hadn’t thought about before. Is it possible to enforce a particular build action on certain files in a Visual Studio project? In this particular case, making sure all SQL files are marked as EmbeddedResource.

With DbUp, there are several ways of supplying SQL files to be run during a migration. In my case, I don’t know which set of scripts need to be run until the deployment is underway. After toying around with several ideas, I settled on the approach of embedding the SQL files in to the migration tool. I can then use the connection string that’s passed in to determine a specific folder of scripts to be executed. Having the scripts as embedded resources will allow me to package up the migration tool as an artefact that can be run only when needed, i.e. during a deployment and not during a build.

So how does it work?

There are several ways of adding custom targets to a project file. After Googling around I came across the ever helpful StackOverflow which offered up some solutions. The first approach is simple, but would require you to add a case for each of the BuildAction types that exist. And if a new one is added at some point, you have to update your code. No thanks, I’m too lazy for that.

The second approach looked much more maintainable by focusing on everything that isn’t what you want. The problem was that it didn’t work! Nothing would ever be matched. I wasn’t able to see any information in Visual Studio, so ran the build from the command line which told me the target was being run, but no elements were matched.

After lots of trial and error and head scratching, I came across another StackOverflow post detailing that you need to specify the namespace for elements in the XPath query. Of course!

The following code is placed in to the project file that you wish to run the check in.

<Target Name="EnsureSQLScriptsAreEmbeddedResource" BeforeTargets="BeforeBuild">
    <XmlPeek 
        XmlInputPath="$(MSBuildProjectFile)" 
        Namespaces="&lt;Namespace Prefix='msb' Uri='http://schemas.microsoft.com/developer/msbuild/2003'/&gt;" 
        Query="/msb:Project/msb:ItemGroup/*[not(self::msb:EmbeddedResource)]/@Include">
            <Output TaskParameter="Result" ItemName="AllItems" />
    </XmlPeek>
	
    <ItemGroup>
        <Filtered Include="@(AllItems)" Exclude="SqlTemplate.sql" Condition="'%(Extension)' == '.sql'" />
    </ItemGroup>
    <Error 
        Code="SQL" 
        ContinueOnError="ErrorAndContinue"
        File="$(MSBuildProjectDirectory)\%(Filtered.Identity)" 
        Text="All scripts require a BuildAction of EmbeddedResource" 
        Condition="'@(Filtered)'!=''" />
</Target>

What’s going on here then?

1. The XmlPeek task will take the project file and run the XPath query, collecting all matching attributes and sticking them in to a new parameter called AllItems.
2. The XPath query works by looking at child elements of each ItemGroup element, matching against any that are not of type EmbeddedResource.
3. For each of these, it will take the value of the Include attribute and use that to populate AllItems.
4. The ItemGroup that follows will filter the AllItems collection to include only things which end with ‘.sql’. These will be stored in a new parameter called Filtered.
5. Finally, the Error target will be invoked if any items made it through. Including the ContinueOnError attribute makes it possible to see all of the files that cause the build to fail, rather than stopping on the first one.

Props to StackOverflow users jessehouwing and Teun D for getting me on the right path!