Bundle SQL script with build process using MSBuild

We are making a sample application, demo or startup kit or a toolkit with test project that requires some SQL scripts. We generally ship in a readme.txt that says it all. But hey, how about having the script under automated process , like if my SQL Server and VS 2008 is in place, with the click of Ctrl + Shift + B everything gets ready. Here, we will do just right that.

MSBuild provides a lot of community tasks that includes zip, XmlUpdater to SqlServer. You can get it all from tigris.org. After having it installed you can either copy it to your local app directory or reference it using $(MSBuildExtensionsPath). In my case I have copied the dll into the Tasks directory of my application (which is the example app supplied with LinqExtender). Anyway, there is a nice task called ExecuteDDL that will take in one or more sql script files , join with GO separator if required  and run it against your database.

To start things off I have created a Sql.Scripts.Targets. About *.Targets file, you can either include them in other targets or *.proj or *.csproj files where you will be using them. Its almost the same with NAnt scripts that terminate with .build extensions.

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" >
    <PropertyGroup>
        <TaskDirectory>Tasks</TaskDirectory>
        <BatchSql>Sql\script.sql</BatchSql>
       <ConnectionString>Server=localhost;Integrated Security=True</ConnectionString>      
    </PropertyGroup>

    <UsingTask AssemblyFile="$(TaskDirectory)\MSBuild.Community.Tasks.dll"
              TaskName="MSBuild.Community.Tasks.SqlServer.ExecuteDDL"/> 
</Project>

As you can see inside Sql.Scripts.Targets , I have added a block of PropertyGroup which declares the variables which I can use later in the script. I have referenced the tasks directory where the task DLL resides. Also, added the batch SQL path (can add multiple separated by ";") and the default connection string. UsingTask is just like any other using statements in your c# code that lets you include the reference of the task which you will be using in this or other files that is dependent on this.

Now, the interesting part. If you ever opened the c# project file by a notepad or any text editor, you must have seen this

<!-- To modify your build process, add your task inside one of the targets below and uncomment it. 
       Other similar extension points exist, see Microsoft.Common.targets.
  <Target Name="BeforeBuild">
  </Target>
  <Target Name="AfterBuild">
  </Target>
-->

As it says, you can add your custom tasks under it and before or after build, it will fire up your task to do the things you want it to do. So, our final touch is to include the target file and to add the ExecuteDDL task under AfterBuild target.

With this let's un-comment the above code and add our block there.

<Import Project="Sql.Scripts.Targets" />
<Target Name="AfterBuild">
   <ExecuteDDL ConnectionString="$(ConnectionString)" Files="$(BatchSql)" ContinueOnError="false" /> 
</Target>  

That's it, next time you build the code, right after it will run the necessary scripts to setup your database.

As you can see that any error occurs gives you a prompt message with VS task list window that also gives the user a clue what to do next. Finally, I have added a  hollow class library project with the scripts tied up, please download it here.

Hope that helps