Visual Studio 2010 Database Deployment API - Part 2
Since posting the first code sample on how to leverage the Visual Studio Database Deployment API, I have had a number of folks contact me and ask how to do the same, but also comprehend the database references, options set in the project, refactor log, pre and post deployment scripts, etc. The short answer is that you include the deployment manifest in your use of the API. Including the manifest will use all the settings from your project and also load up referenced database models and artifacts.
Below I have updated the sample to use the manifest file to drive the options for the deployments. It assumes you are using integrated security so it can also pull the connection string from the manifest file. The sample basically drives the database project deployment engine from a console app.
using System;
using System.Diagnostics;
using System.IO;
using Microsoft.Data.Schema;
using Microsoft.Data.Schema.Sql;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema.Build;
using Microsoft.Build.Evaluation;
namespace DeployLocalModel
{
class Program
{
static void Main(string[] args)
{
string manifestFilePath = null;
ISchemaDeploymentController sdController = null;
Project projectManifest = null;
FileInfo manifestFile = null;
SchemaDeployment sdEngine = null;
SchemaDeploymentConstructor sdConstructor = null;
bool hasDeploymentChanges = false;
bool pastPredeploymentPayload = false;
try
{
manifestFilePath = @".\LocalModel.deploymanifest";
if (args.Length > 0)
{
manifestFilePath = args[0];
}
manifestFile = new FileInfo(manifestFilePath);
if (!manifestFile.Exists)
throw new Exception("Manfiest file does not exist!");
//Load manifest into MSBUILD Project so we can pull values from it
projectManifest = new Project(manifestFile.ToString());
/*Create a extension manager so we can load the SQL100 DSP
and utilize its implementation of the deployment engine*/
ExtensionManager extensionManager =
new ExtensionManager(typeof(Sql100DatabaseSchemaProvider).FullName);
// Create the deployment controller
sdConstructor =
extensionManager.DatabaseSchemaProvider.GetServiceConstructor<SchemaDeploymentConstructor>();
//Create a new error manager so we can capture the error messages
ErrorManager errorManager = new ErrorManager();
/*Attach error manager to the deployment constructor.
This will allow us to get errors from the deployment contributor later on */
sdConstructor.Errors = errorManager;
//Set up the deployment engine constructor of what we plan to deploy
sdConstructor.Setup(
new FileInfo(projectManifest.GetPropertyValue("SourceModel")),
projectManifest.GetPropertyValue("TargetConnectionString"));
//Create an instance of the deployment engine
sdEngine = sdConstructor.ConstructService();
//Load manifest so other artifacts and options are considerred
sdEngine.Configure(projectManifest, manifestFile.Directory);
//Do not deploy to dataabase, yet
sdEngine.SetDeployToDatabase(false);
//Execute the engine to create our deployment plan
sdEngine.Execute();
// Get access to the plan so we call walk through it and later execute it
DeploymentPlan deploymentPlan = sdEngine.Plan;
#region WalkTheDeploymentPlan
//Start with the first step in the plan and start iterating through it
DeploymentStep deploymentStep = deploymentPlan.Head;
while (deploymentStep != null)
{
#if DEBUG
//Send deployment step contents to output window
Debug.Print("Deploy Step {0} contains:\n\r {1}",
deploymentStep.GetType().FullName,
deploymentStep.Action());
#endif
/*Check and see if we are past the predeployment script so were can
determine if there are any incremental deployment changes*/
if (deploymentStep is EndPreDeploymentScriptStep)
pastPredeploymentPayload = true;
if (deploymentStep.Action() != "")
{
/*Test to see if we are deploying any changes,
if not we will short circuit later on.*/
if (deploymentStep is DeploymentScriptDomStep && pastPredeploymentPayload)
{
hasDeploymentChanges = true;
break;
}
}
deploymentStep = deploymentStep.Next;
}
#endregion WalkTheDeploymentPlan
if (hasDeploymentChanges)
{
//Create a controller se we can work with the deployment plan
sdController = sdEngine.CreateController();
// Setup our event handler so we can listen to events from deployment engine
sdController.DeploymentMessage
+= delegate(object sender, DeploymentContributorEventArgs deployArg)
{
Console.WriteLine("{0}", deployArg.Message.Message);
};
//Deploy the model to the target database using the plan
sdController.UpdateDatabase(deploymentPlan);
}
else
{
Console.WriteLine("No deployment changes to make!");
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
if (sdController != null)
{
sdController.Dispose();
}
Console.WriteLine("Execution complete. Strike any key to exit.");
Console.ReadKey();
}
}
}
}
If you want to start out with the sample already coded up you can download it from my SkyDrive.
Comments
- Anonymous
October 23, 2010
Hello,I am really interested in using this API as part of a Visual Studio setup project. I ran the block without any issues in a console app but I have been running into an exception when running it inside of a custom action. I have been unable to solve this problem and was wondering if you could point me in the right direction. Below is the exception and the stack trace:The target database schema provider could not be determined. Deployment cannot continue.Microsoft.Data.Schema.Build.DeploymentFailedExceptionStack Trace:at Microsoft.Data.Schema.Build.SchemaDeployment.FinishInitialize(String targetConnectionString, String targetDatabaseName)at Microsoft.Data.Schema.Build.SchemaDeployment.Initialize (FileInfo sourceDbSchemaFile, ErrorManager errors, String targetConnectionString, String targetDatabaseName)at Microsoft.Data.Schema.Build.SchemaDeploymentConstructor.ConstructServiceImplementation()at Microsoft.Data.Schema.SchemaDeploymentConstructor`1.ConstructService()Thanks for your help!PS I am running SQL Server Express 2008 - Anonymous
November 04, 2010
The comment has been removed - Anonymous
June 04, 2011
It would be very nice to have the "Generate DROP statements for objects that are in the target database..." option checked but ignore any user logins or roles. Is this possible at all? - Anonymous
November 17, 2011
Barclay, thanks for writing these two good articles! I work on a team that has been using the VSDBCMD.exe to do the database deployments on our client installation. This works well when it works. The issue that we run into is that there are any number of reasons why the database won't deploy. All of these reasons seem to come down to a memory issue. If the user restarts the computer and retries the deployment a lot of times this fixes the issue. However, we get enough of these errors that we have determined that we just cannot use the VSDBCMD anymore. I believe our problem is in the post data scripts where we load some of our tables with data. So, that has me looking into rolling my own deployment leveraging the api directly, which is why I have landed here at your blog post. I am having a difficult time finding the documentation on how to bring all the parts of the API together. One big piece that I am missing from your sample is where can I capture the logging information? I want to see what is happening as it runs as if I was in Visual Studio and deployed from there. What hooks do I need to put in place to get this information? Any guidance on additional documentation would be helpful as well. Thanks!-Patrick - Anonymous
November 27, 2011
@Patrick, you can receive messages through a delegate/event receiver similar to the sample I provided here: blogs.msdn.com/.../leveraging-the-visual-studio-2010-database-deployment-api.aspxI’m not sure rolling your own client over the API will solve the memory issues you are encountering as VSDBCMD.exe is just a thin veneer over the API, but may afford more flexibility in dealing with data as you describe.If this does not help, you can contact me here on specifics: blogs.msdn.com/.../contact.aspx - Anonymous
October 27, 2012
The comment has been removed - Anonymous
November 03, 2012
@Barclay,Just following up on a previous post of mine. This is great stuff!!!But I have struggling to get this to run without error on a client machine. I have installed SQL Server 2008 Express along with Management Objects and the native client.I still get an error - "Index (zero based) must be greater than or equal to zero and less than the size of the argument list".Any ideas on where I should be looking to resolve?Thanks in advance!!The Doctor's In - Anonymous
February 25, 2014
Can we implement Custom code analysis rules for SSDT as well?