ReadyRoll's SQLCMD package format enables you to embed the deployment of your database within your application code. This can be useful if you don't have direct access to your customer's database environment, as with shrink-wrapped software or distributed application systems.
The db deployment can be performed within your application's "start" event or as part of the application installation process (e.g. using a custom action in WiX). ReadyRoll SQLCMD packages are smart enough to figure out whether any migration scripts need to be applied and run them, or simply exit if the database is already up-to-date. If any errors occur during deployment, then the deployment will be immediately halted and the transaction will be rolled back. Additionally, the database will be automatically created on the SQL Server on-premise or Azure SQL instance if it does not exist yet.
To embed the database deployment within your application, you will need to reference your ReadyRoll database project from your application project (e.g. from an ASP.NET website, Windows service or console application) and add ReadyRoll's output package artifact as an embedded resource. This includes the package file as a resource within your application assembly file, so you don't need to ship the package artifact separately. You can then include some code to execute the database deployment in a convenient part of your application.
Download the sample solution (C#): MyConsoleAppWithDb.zip
Database project setup
First the ReadyRoll database project needs to be configured to output a package file during build. In Visual Studio, right-click the database project in the Solution Explorer and select Project Properties.
On the Project Settings tab under Outputs, check the SQLCMD package (.sql file) option.
Perform a build of the solution.
In the output window, you'll notice that a package file is now being created as part of the build (in this case, a file called AdventureWorksAzure_Package.sql is being created):
Application project setup
These steps should be performed in the project that you wish to embed the database package and perform the deployment from.
Within your application project, add a reference to the ReadyRoll database project.
As no additional artifacts are needed from the database project, within the reference properties set Copy Local to False.
Next add a link to the package resource file by firstly unloading the application project.
And open the project file for editing.
Insert an embedded link at the end of the project file in the following format:
Save the file and reload the project.
Adding the SQLCMD runner
To run the package deployment script, we'll use the DBUtil open source library by Remus Rusanu.
Within the package manager console window, run the following to add the SQLCMD runner dependency to the project:
Please note that DbUtil does not currently support the following SQLCMD syntax:
If the full set of SQLCMD functionality is required, then we recommend using the PowerShell method to deploy your database. Unfortunately embedded deployment is not currently supported with this approach.
Apart from the DBUtil library, which currently includes a single assembly about 30kb in size, there are no other dependencies to ship with your application.
This means you can deploy your application to a customer without needing SqlCmd.exe or ReadyRoll itself to be installed. You don't even need to ship the above SQL file. as it will be embedded within your application assembly at build time.
Add the method to migrate the database (no changes required):
Add a call to the migrate method within the desired part of the application, substituting your own connection string and the name of the package artifact:
Now try starting the solution. The database should be deployed as the application starts.
This sample project contains a console application that deploys the package artifact from a database project as an embedded resource.
Download the sample solution/project: MyConsoleAppWithDb.zip