Selectively Running Database Project Post Deploy SQL Scripts using SQLCMD

Posted by & filed under Development, Microsoft, SQL Server, TFS.

We’ve been playing with our TFS builds recently now have a system in place where our nightly build will drop and recreate the project’s databases based on the schemas in the database projects. As part of the publish, MSBuild will generate a script file containing either the entire schema (nightly build) or any schema changes found in running a comparison (QA environment and higher).

The Problem

One issue we had was that we wanted a post deploy script to run in any data required for testing the nightly environment (users, reference data, even a few test customer records). Fine, database projects handle this, simply mark the script file as Build: Post Deploy Script and the script will run after the schema changes have been run in. Only one problem, this post deploy script will run regardless of environment. On our QA environment, we already had the data we needed there, since we weren’t dropping the database, just running in the schema changes.

The Solution

I found a stack overflow article here – http://stackoverflow.com/q/7151021/503734 – which talks about changing the sqlproj file to copy certain files into a location that the rest of the build is looking for to run as a post deploy script. A bit messy. Here’s how I eventually handled it…

Add a SQLCMD Variable

Right click on the sqlproj in your solution and go to properties. You’ll see a SQLCMD Variable tab. Add a variable, something like $(EXECUTEPOSTDEPLOY).

Project Properties SQLCMD Variables

 

Add a Condition to your Post Deploy Script

Check the $(EXECUTEPOSTDEPLOY) variable, and if it’s not equal to ‘True’, SET NOEXEC ON. This basically turns of execution of scripts for that connection. You may want to SET NOEXEC OFF again at the end if you have other scripts to run.

PostDeployScript

 

Set the SQLCMD Variable depending on configuration

In your publish profile for your required configuration, set the SQLCMD Variable to either true or false (depending on whether you want to run the script or not).

Publish Settings

 

That’s It!

Run your build (with publish enabled, obviously!) and it will either run or not run depending on your setup. You can set other SQLCMD Variables and play around with the conditions to allow the script to run. You could also SET NOEXEC on or off at various points to selectively run parts of the script (would probably be better to split into seperate scripts if this is the case though).

Hope this helps.

Using packages in NuGet without commiting binaries to SCM using NuGet PowerTools

Posted by & filed under Microsoft, NuGet, Visual Studio.

As you can probably see from previous posts, I’ve been using NuGet a lot lately and I definately see it as the way forward for managing dependencies. We’ve been using a private NuGet server to publish common assemblies build via our continuous integration server, and allows us to push updates out to projects.

The Problem

The issue we had though is that NuGet creates a “Packages” folder and stores the assemblies in there for all of the packages the projects use. This means binaries would likely get checked into source control, and using a system like Git for SCM this means the repository will get bloated really quickly. We needed a solution that would allow us to reference the packages and for Visual Studio to call NuGet to grab the versions of the packages it needed to build. The CI server would also need to do this from the command line or an MSBuild target.

NuGet PowerTools

NuGet PowerTools is the solution! You’ll need to use the Package Manager withing Visual Studio (requires you to install Powershell 2.0 if using Windows Vista and below) and run the following…

PM> Install-Package NuGetPowerTools
Successfully installed 'NuGetPowerTools 0.28'.

## Constraining Upgrades To Allowed Versions
By default, when running the `Update-Package` command on a package (or updating the package using dialog),  it will be updated to the latest version in the feed. With the new support for updating all packages, there may be cases in which you want to lock a package to a specific version range. For example, you may know in advance that your application will only work with version 2.* of a package, but not 3.0 and above. In order to prevent accidentally updating the package to 3, NuGet supports constraining the range of versions that packages can be upgraded to by hand editing the `packages.config` file using the `allowedVersions` attribute.

For example, the following example shows how to lock the `SomePackage` package the version range 2.0 - 3.0 (exclusive). The `allowedVersions` attribute accepts values using the [version range format](../Reference/Version-Range-Specification).   

Currently, locking a package to a specific version range requires hand-edited the packages.config file.

PM> Enable-PackageRestore
Attempting to resolve dependency 'NuGet.CommandLine (≥ 1.4)'.
Successfully installed 'NuGet.CommandLine 1.4.20615.182'.
Successfully installed 'NuGet.Build 0.16'.

Copying nuget.exe and msbuild scripts to D:CodeStarterAppsMvc3Application.nuget
Successfully uninstalled 'NuGet.Build 0.16'.
Successfully uninstalled 'NuGet.CommandLine 1.4.20615.182'.

Don't forget to commit the .nuget folder
Updated 'Mvc3Application' to use 'NuGet.targets'
Enabled package restore for Mvc3Application

This will remove all of the packages from your packages directory (except for the powertools package itself) and creates a .nuget directory (and as it states above, make sure you check this in). If you check out your project files now (in something like notepad), you’ll see an extra MSBuild target is added to the project…

<Import Project="$(SolutionDir).nugetNuGet.targets" />

This references the targets in the .nuget directory, along with some command line cleverness that uses nuget.exe (also in the .nuget directory). You’ll now find everytime you build your solution, the packages directory will repopulate itself (but you don’t need to check these folders in).

Hope this helps!

ASP.NET MVC4 Mobile Views

Posted by & filed under .NET 4.5, Microsoft, MVC4.

Whilst Scott Hanselman created a few mobile MVC views a while back, .NET MVC4 brings built in support for Mobile phones and tablets making it even easier to target these devices. Built on JQuery Mobile, these mobile views incorporate everything you’d expect such as collapsible panels and  client side validation.

To start, simply select MVC4 Web Application, and select the Mobile Application template.

You’ll see the project includes new jquery.mobile js and css files. Views use the HTML5 data- attributes on the divs to specify the usage and allow the JQuery Mobile template to function correctly. Other than that, this is a pretty standard MVC application.

Run the project and you’ll see there’s a boilerplate .net web application template but optimised for mobile devices, with the usual “About”, “Contact” and Login buttons.

The login form contains the usual controls, and contains client side validation.

You can download the MVC4 developer preview here. Keep it coming Microsoft! 🙂