Deployment Contributor Sample: LiteScript
In Visual Studio 2010 we have enabled a number of new extensibility points as detailed in my earlier post. In this post we will take a look at creating a deployment contributor. This deployment contributor is a deployment executor.
Essentially what the sample contributor does is walks through the deployment plan and creates an additional deployment script with a _lite.sql extension that already has the SQLCMD variables preprocessed and contains only the body of the deployment script. This can be handy if integrating deployment scripts into other processes or APIs where SQLCMD is not support or statement execution is limited to only the immediate database. For example, you internet hosting service only allows you to submit a SQL script through their deployment tool and the script can only execute in the context of your user database. This additional _lite.sql script is not executed against the target. It is simply produced in addition to the original deployment script.
The following is the implementation of our Lite Script Deployment Plan Executor. You may want to customize this adding or removing additional deployment steps from the new _lite.sql script. To use the sample, download the sample from my SkyDrive and build it. You will then need to register the extension as you do the other samples on MSDN. This sample is already keyed using the key in the project. Simply build the project and move LiteScriptContributor.dll, LiteScriptContributor.pdb, and LiteScriptContributor.Extensions.xml to your %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\ directory. The next time you load Visual Studio 2010 the contributor will load and start producing the additional script when deployed.
Special thanks to Jamie Laflen for providing the sample SQL Managed Batch Parser Wrapper.
Here is what that looks like in the output window when deploying your project:
------ Build started: Project: MyDatabase, Configuration: Debug Any CPU ------
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
Writing model to MyDatabase.dbschema...
MyDatabase -> C:\Work\Demo\MyDatabase\MyDatabase\sql\debug\MyDatabase.dbschema
------ Deploy started: Project: MyDatabase, Configuration: Debug Any CPU ------
Lite Deployment SqlCmd substituted script generated to:
C:\Work\Demo\MyDatabase\MyDatabase\sql\debug\MyDatabase_lite.sql
Deployment script generated to:
C:\Work\Demo\MyDatabase\MyDatabase\sql\debug\MyDatabase.sql
The deployment script was generated, but was not deployed. You can change the deploy action on the Deploy tab of the project properties.
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========
LiteScriptContributor.cs
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Data.Schema.Build;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema.Sql;
using System.IO;
using System.Globalization;
using System.Diagnostics;
using ManagedBatchParser;
namespace LiteScriptContributor
{
/// <summary>
/// This deployment contributor generates a deployment script that contains
/// only the body of the deployment script with the SQLCMD variables pre-processed .
/// </summary>
[DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
class LiteScriptContributor : DeploymentPlanExecutor
{
protected override void OnExecute(DeploymentPlanContributorContext context)
{
string scriptName = null;
string scriptFullPath = null;
string parserScript = null;
StreamWriter sw = null;
BatchParserWrapper parser = null;
bool writeLiteScript = false;
try
{
scriptName = string.Format(CultureInfo.CurrentCulture, "{0}_lite.sql", context.Options.TargetDatabaseName);
if (string.IsNullOrEmpty(context.DeploymentScriptPath))
{
// Default script to current directory
scriptFullPath = Path.Combine(Environment.CurrentDirectory, scriptName);
}
else
{
scriptFullPath = Path.Combine(Path.GetDirectoryName(context.DeploymentScriptPath), scriptName);
}
//Open a file to write on new deployment script to
sw = new StreamWriter(scriptFullPath, false, Encoding.UTF8);
//Create an instance of our managed batch parser wrapper the we will use later on
parser = new BatchParserWrapper();
//Start with the top of the deployment plan, the head step.
DeploymentStep currentStep = context.PlanHandle.Head;
while (currentStep != null)
{
/*For our Lite Script we only care about what is between the pre and post
*deployment steps in the deployment plan*/
if (currentStep is BeginPreDeploymentScriptStep)
{
writeLiteScript = true;
}
else
{
if (currentStep is EndPostDeploymentScriptStep)
{
writeLiteScript = false;
}
}
//Get T-SQL from deployment step and process the SQLCMD vars
string sqlcmdScript = currentStep.Action();
parserScript = parser.ProcessSqlCmdScript(sqlcmdScript);
//Write out the deployment step to our new file if it meets our criteria
if (writeLiteScript)
{
if (string.IsNullOrEmpty(sqlcmdScript) == false)
{
sw.Write(parserScript);
}
}
currentStep = currentStep.Next;
}
//Send a message back to the deployment engine that will be displayed in the VS output window
string scriptCompleteMsg =
string.Format(CultureInfo.CurrentCulture,
"Lite Deployment SqlCmd substituted script generated to:{0}{1}",
Environment.NewLine, scriptFullPath);
SqlServerError scriptCompleteError =
new SqlServerError(scriptCompleteMsg,
string.Empty,
Microsoft.Data.Schema.ErrorSeverity.Message);
base.PublishMessage(scriptCompleteError);
}
catch (Exception ex)
{
Debug.Write(ex.ToString());
throw;
}
finally
{
if (sw != null)
{
sw.Dispose();
}
if (parser != null)
{
parser.Dispose();
}
}
}
}
}
If you want to start out with the sample already coded up you can download it from my SkyDrive.
Comments
- Anonymous
June 15, 2010
Many thanks for this article. I think this extension will be very usable. It would be great to have such function as a standard option on the Deploy tab of db project in VS ... - Anonymous
August 22, 2011
nice oneis it posible to create script with if exists then drop then create with the help of data base projectmeans i have one sp test if i change this spthen database project generate script contains alter testinsted of this i want drop and then create test is it posiblehow i can debug your project