Using LocalDB in Visual Studio Online
- Create a batch file that Creates a named instance of LocalDB and starts it up
- This batch file also Publishes your database (the dacpac)
- Save this batch file in your Solution
- Modify your Build Definition to tell it to use this batch file as a Pre Test Script
- Queue a Build
It will probably take you a few tried to get the paths and relative paths for your build server correct, but hopefully, I'll cover most of the gotcha so you don't have the same head scratching moments (or hours) that I had.
I assume that
- You use LocalDB for your tests on your local machine
- You have a Database Project in your solution
- You Publish your LocalDB (if needed) and then run your tests
- You have a Post Deployment script that seeds your database with any data your tests will need
- You have your tests working on your local machine against this LocalDB instance
Batch File
The CI build servers in Visual Studio Online come pre-installed with SqlLocalDB. This is a utility that allows you to configure LocalDB and spin it up. If the instance is already there and configured and started there is no harm in running these commands again. However, in Visual Studio Online, your CI build servers are freshly installed and spun up VMs, so you’re always starting from a “fresh” installation so you’re not technically running these scripts “again”.
Please take Note of the Paths
- SqlLocalDB is installed in the Program Files directory
- SqlPackage is installed in Program Files (x86) directory
"%ProgramFiles%\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe" create "ProjectsV12" 12.0 -s
"%ProgramFiles(x86)%\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\SqlPackage.exe" /Action:Publish /SourceFile:"%TF_BUILD_BINARIESDIRECTORY%\OoblxDb.dacpac" /Profile:"%TF_BUILD_SOURCESDIRECTORY%\Dev\vNext\Ooblx.Web\Database\OoblxDb\OoblxDb.publish.xml"
CreateLocalDBInstance.bat
The first line in the batch file above - SqlLocalDB Utility - SqlLocalDB.exe can be found in the Program Files folder (and not the Program Files (x86) folder). The rest of the command line creates a localDB instance named “ProjectsV12” with an instace version of “12.0” and starts it. More information on SqlLocalDB Utility can be found here SqlLocalDB Utility
It is important to note that I have "ProjectsV12" as the instance name because the connection string I'm using has it in there. Here is what my connection string looks like
connectionString="Data Source=(localdb)\ProjectsV12;Initial Catalog=Ooblx;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False"
The Connection String I use in my Unit Test Project
The second line in the batch file - This is where you'll probably get tripped up. Essentially we need to Publish our database and in order to do so, there are a few pre-requisites:
- You will typically have a Database Project (of course) that produces your database and seeds it with any data you'll need in order to run your tests
- The output of this Database Project is a *.dacpac. You should double check the name of this dacpac file
- You don't have to, but it is best to have a Publish Profile - a publish.xml file in order to keep the Publishing step simple