Walkthrough: Extend Database Project Deployment to Modify the Deployment Plan
You can create deployment contributors to perform custom actions when you deploy a database project. You can create either a DeploymentPlanModifier or a DeploymentPlanExecutor. Use a DeploymentPlanModifier to change the plan before it is executed and a DeploymentPlanExecutor to perform operations while the plan is being executed. In this walkthrough, you create a DeploymentPlanModifier named SqlRestartableScriptContributor that adds IF statements to the batches in the deployment script to enable the script to be re-run until they are completed if an error occurs during execution.
In this walkthrough, you will accomplish the following major tasks:
Create the DeploymentPlanModifier type of deployment contributor
Install the deployment contributor
Test your deployment contributor
Prerequisites
You need the following components to complete this walkthrough:
Visual Studio 2010 Premium or Visual Studio 2010 Ultimate installed on your computer.
A database project that contains database objects
An instance of SQL Server to which you can deploy a database project
Note
This walkthrough is intended for users who are already familiar with the database features of Visual Studio. You are also expected to be familiar with basic Visual Studio concepts, such as how to create a class library and how to use the code editor to add code to a class.
Create a Deployment Contributor
To create a deployment contributor, you must perform the following tasks:
Create a class library project and add required references
Define a class named SqlRestartableScriptContributor that inherits from DeploymentPlanModifier
Override the OnExecute method
Add private helper methods
Build the resulting assembly
To create a class library project
Create a Visual C# or Visual Basic class library project named MyOtherDeploymentContributor.
In Solution Explorer, right-click the project and then click Add Reference.
Click the .NET tab.
Highlight the Microsoft.Data.Schema, Microsoft.Data.Schema.Sql, Microsoft.Data.Schema.ScriptDom, and Microsoft.Data.Schema.ScriptDom.Sql entries and click OK.
Next, start to add code to the class.
To define the SqlRestartableScriptContributor class
In the code editor, update the class1.cs file to match the following using statements:
using System; using System.Collections.Generic; using System.Text; using Microsoft.Data.Schema.Build; using Microsoft.Data.Schema.ScriptDom.Sql; using Microsoft.Data.Schema.SchemaModel; using System.Globalization; using Microsoft.Data.Schema.ScriptDom; using Microsoft.Data.Schema.Extensibility; using Microsoft.Data.Schema.Sql; using Microsoft.Data.Schema.Sql.Build; using Microsoft.Data.Schema.Sql.SchemaModel;Imports System Imports System.Collections.Generic Imports System.Text Imports Microsoft.Data.Schema.Build Imports Microsoft.Data.Schema.ScriptDom.Sql Imports Microsoft.Data.Schema.SchemaModel Imports System.Globalization Imports Microsoft.Data.Schema.ScriptDom Imports Microsoft.Data.Schema.Extensibility Imports Microsoft.Data.Schema.Sql Imports Microsoft.Data.Schema.Sql.Build Imports Microsoft.Data.Schema.Sql.SchemaModelUpdate the class definition to match the following example:
/// <summary> /// This deployment contributor modifies a deployment plan by adding if statements /// to the existing batches in order to make a deployment script able to be rerun to completion /// if an error is encountered during execution /// </summary> [DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))] class SqlRestartableScriptContributor : DeploymentPlanModifier { }''' <summary> ''' This deployment contributor modifies a deployment plan by adding if statements ''' to the existing batches in order to make a deployment script able to be rerun to completion ''' if an error is encountered during execution ''' </summary> <DatabaseSchemaProviderCompatibility(GetType(SqlDatabaseSchemaProvider))> _ Class SqlRestartableScriptContributor Inherits DeploymentPlanModifier End ClassNow you have defined your build contributor that inherits from DeploymentPlanModifier. You used the DatabaseSchemaProviderCompatibilityAttribute attribute to indicate that this contributor is compatible with any database schema provider that inherits from SqlDatabaseSchemaProvider.
Add the following member declarations:
private const string BatchIdColumnName = "BatchId"; private const string DescriptionColumnName = "Description"; private const string CompletedBatchesVariableName = "CompletedBatches"; private const string CompletedBatchesVariable = "$(CompletedBatches)"; private const string CompletedBatchesSqlCmd = @":setvar " + CompletedBatchesVariableName + " __completedBatches_{0}_{1}"; private const string TotalBatchCountSqlCmd = @":setvar TotalBatchCount {0}"; private const string CreateCompletedBatchesTable = @" if OBJECT_ID(N'tempdb.dbo." + CompletedBatchesVariable + @"', N'U') is null begin use tempdb create table [dbo].[$(CompletedBatches)] ( BatchId int primary key, Description nvarchar(300) ) use [$(DatabaseName)] end "; private const string DropCompletedBatchesTable = @"drop table [tempdb].[dbo].[" + CompletedBatchesVariable + "]";Private Const BatchIdColumnName As String = "BatchId" Private Const DescriptionColumnName As String = "Description" Private Const CompletedBatchesVariableName As String = "CompletedBatches" Private Const CompletedBatchesVariable As String = "$(CompletedBatches)" Private Const CompletedBatchesSqlCmd As String = ":setvar " & CompletedBatchesVariableName & " __completedBatches_{0}_{1}" Private Const TotalBatchCountSqlCmd As String = ":setvar TotalBatchCount {0}" Private Const CreateCompletedBatchesTable As String = vbCr & vbLf & "if OBJECT_ID(N'tempdb.dbo." & CompletedBatchesVariable & "', N'U') is null" & vbCr & vbLf & "begin" & vbCr & vbLf & vbTab & "use tempdb" & vbTab & vbCr & vbLf & vbTab & "create table [dbo].[$(CompletedBatches)]" & vbCr & vbLf & vbTab & "(" & vbCr & vbLf & vbTab & vbTab & "BatchId int primary key," & vbCr & vbLf & vbTab & vbTab & "Description nvarchar(300)" & vbCr & vbLf & vbTab & ")" & vbCr & vbLf & vbTab & "use [$(DatabaseName)]" & vbTab & vbCr & vbLf & "end" & vbCr & vbLf Private Const DropCompletedBatchesTable As String = "drop table [tempdb].[dbo].[" & CompletedBatchesVariable & "]"Next, you override the OnExecute method to add the code that you want to run when a database project is deployed.
To override OnExecute
Add the following method to your SqlRestartableScriptContributor class:
/// <summary> /// You override the OnExecute method to do the real work of the contributor. /// </summary> /// <param name="context"></param> protected override void OnExecute(DeploymentPlanContributorContext context) { // Replace this with the method body }''' <summary> ''' You override the OnExecute method to do the real work of the contributor. ''' </summary> ''' <param name="context"></param> Protected Overloads Overrides Sub OnExecute(ByVal context As DeploymentPlanContributorContext) ' Replace this with the method body End SubYou override the OnExecute method from the base class, DeploymentPlanContributor, which is the base class for both DeploymentPlanModifier and DeploymentPlanExecutor. The OnExecute method is passed a DeploymentPlanContributorContext object that provides access to any specified arguments, the source and target database model, build properties, and extension files. In this example, we get the deployment plan and the target database name.
Now add the beginnings of a body to the OnExecute method:
// Obtain the first step in the Plan from the provided context DeploymentStep nextStep = context.PlanHandle.Head; int batchId = 0; BeginPreDeploymentScriptStep beforePreDeploy = null; // Loop through all steps in the deployment plan while (nextStep != null) { // Increment the step pointer, saving both the current and next steps DeploymentStep currentStep = nextStep; nextStep = currentStep.Next; // Add additional step processing here } // if we found steps that required processing, set up a temporary table to track the work that you are doing if (beforePreDeploy != null) { // Add additional post-processing here } // Cleanup and drop the table DeploymentScriptStep dropStep = new DeploymentScriptStep(DropCompletedBatchesTable); base.AddAfter(context.PlanHandle, context.PlanHandle.Tail, dropStep);' Obtain the first step in the Plan from the provided context Dim nextStep As DeploymentStep = context.PlanHandle.Head Dim batchId As Integer = 0 Dim beforePreDeploy As BeginPreDeploymentScriptStep = Nothing ' Loop through all steps in the deployment plan While nextStep IsNot Nothing ' Increment the step pointer, saving both the current and next steps Dim currentStep As DeploymentStep = nextStep nextStep = currentStep.[Next] ' Add additional step processing here End While ' if we found steps that required processing, set up a temporary table to track the work that you are doing If beforePreDeploy IsNot Nothing Then ' Add additional post-processing here End If ' Cleanup and drop the table Dim dropStep As New DeploymentScriptStep(DropCompletedBatchesTable) MyBase.AddAfter(context.PlanHandle, context.PlanHandle.Tail, dropStep)In this code, we define a few local variables, and set up the loop that will handle processing of all the steps in the deployment plan. After the loop completes, we will have to do some post-processing, and then will drop the temporary table that we created during deployment to track progress as the plan executed. Key types here are: DeploymentStep and DeploymentScriptStep. A key method is AddAfter .
Now add the additional step processing, to replace the comment that reads "Add additional step processing here":
// Look for steps that mark the pre/post deployment scripts // These steps will always be in the deployment plan even if the // user's project does not have a pre/post deployment script if (currentStep is BeginPreDeploymentScriptStep) { // This step marks the begining of the predeployment script. // Save the step and move on. beforePreDeploy = (BeginPreDeploymentScriptStep)currentStep; continue; } if (currentStep is BeginPostDeploymentScriptStep) { // This is the step that marks the beginning of the post deployment script. // We do not continue processing after this point. break; } if (currentStep is SqlPrintStep) { // We do not need to put if statements around these continue; } // if we have not yet found the beginning of the pre-deployment script steps, // skip to the next step. if (beforePreDeploy == null) { // We only surround the "main" statement block with conditional // statements continue; } // Determine if this is a step that we need to surround with a conditional statement DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep ; if (domStep == null) { // This step is not a step that we know how to modify, // so skip to the next step. continue; } TSqlScript script = domStep.Script as TSqlScript; if (script == null) { // The script dom step does not have a script with batches - skip continue; } // Loop through all the batches in the script for this step. All the statements // in the batch will be enclosed in an if statement that will check the // table to ensure that the batch has not already been executed IModelElement element; string stepDescription; GetStepInfo(context, domStep, out stepDescription, out element); int batchCount = script.Batches.Count; for (int batchIndex = 0; batchIndex < batchCount; batchIndex++) { // Add batch processing here }' Look for steps that mark the pre/post deployment scripts ' These steps will always be in the deployment plan even if the ' user's project does not have a pre/post deployment script If TypeOf currentStep Is BeginPreDeploymentScriptStep Then ' This step marks the begining of the predeployment script. ' Save the step and move on. beforePreDeploy = DirectCast(currentStep, BeginPreDeploymentScriptStep) Continue While End If If TypeOf currentStep Is BeginPostDeploymentScriptStep Then ' This is the step that marks the beginning of the post deployment script. ' We do not continue processing after this point. Exit While End If If TypeOf currentStep Is SqlPrintStep Then ' We do not need to put if statements around these Continue While End If ' if we have not yet found the beginning of the pre-deployment script steps, ' skip to the next step. If beforePreDeploy Is Nothing Then ' We only surround the "main" statement block with conditional ' statements Continue While End If ' Determine if this is a step that we need to surround with a conditional statement Dim domStep As DeploymentScriptDomStep = TryCast(currentStep, DeploymentScriptDomStep) If domStep Is Nothing Then ' This step is not a step that we know how to modify, ' so skip to the next step. Continue While End If Dim script As TSqlScript = TryCast(domStep.Script, TSqlScript) If script Is Nothing Then ' The script dom step does not have a script with batches - skip Continue While End If ' Loop through all the batches in the script for this step. All the statements ' in the batch will be enclosed in an if statement that will check the ' table to ensure that the batch has not already been executed Dim element As IModelElement = Nothing Dim stepDescription As String = "" GetStepInfo(context, domStep, stepDescription, element) Dim batchCount As Integer = script.Batches.Count For batchIndex As Integer = 0 To batchCount - 1 ' Add batch processing here NextThe code comments explain the processing. At a high-level, this code looks for the steps that you care about, skipping others and stopping when you reach the beginning of the post-deployment steps. If the step contains statements that we must surround with conditionals, we will perform additional processing. Key types, methods, and properties include the following: BeginPreDeploymentScriptStep, BeginPostDeploymentScriptStep, IModelElement, TSqlScript, Script, DeploymentScriptDomStep, and SqlPrintStep.
Now, add the batch processing code by replacing the comment that reads "Add batch processing here":
// Create the if statement that will contain the batch's contents IfStatement ifBatchNotExecutedStatement = CreateIfNotExecutedStatement(batchId); BeginEndBlockStatement statementBlock = new BeginEndBlockStatement(); ifBatchNotExecutedStatement.ThenStatement = statementBlock; statementBlock.StatementList = new StatementList(); TSqlBatch batch = script.Batches[batchIndex]; int statementCount = batch.Statements.Count; // Loop through all statements in the batch, embedding those in an sp_execsql // statement that must be handled this way (schemas, stored procedures, // views, functions, and triggers). for (int statementIndex = 0; statementIndex < statementCount; statementIndex++) { // Add additional statement processing here } // Add an insert statement to track that all the statements in this // batch were executed. Turn on nocount to improve performance by // avoiding row inserted messages from the server string batchDescription = string.Format(CultureInfo.InvariantCulture, "{0} batch {1}", stepDescription, batchIndex); PredicateSetStatement noCountOff = new PredicateSetStatement(); noCountOff.IsOn = false; noCountOff.Options = SetOptions.NoCount; PredicateSetStatement noCountOn = new PredicateSetStatement(); noCountOn.IsOn = true; noCountOn.Options = SetOptions.NoCount; InsertStatement batchCompleteInsert = CreateBatchCompleteInsert(batchId, batchDescription); statementBlock.StatementList.Statements.Add(noCountOn); statementBlock.StatementList.Statements.Add(batchCompleteInsert); statementBlock.StatementList.Statements.Add(noCountOff); // Remove all the statements from the batch (they are now in the if block) and add the if statement // as the sole statement in the batch batch.Statements.Clear(); batch.Statements.Add(ifBatchNotExecutedStatement); // Next batch batchId++;' Create the if statement that will contain the batch's contents Dim ifBatchNotExecutedStatement As IfStatement = CreateIfNotExecutedStatement(batchId) Dim statementBlock As New BeginEndBlockStatement() ifBatchNotExecutedStatement.ThenStatement = statementBlock statementBlock.StatementList = New StatementList() Dim batch As TSqlBatch = script.Batches(batchIndex) Dim statementCount As Integer = batch.Statements.Count ' Loop through all statements in the batch, embedding those in an sp_execsql ' statement that must be handled this way (schemas, stored procedures, ' views, functions, and triggers). For statementIndex As Integer = 0 To statementCount - 1 ' Add additional statement processing here Next ' Add an insert statement to track that all the statements in this ' batch were executed. Turn on nocount to improve performance by ' avoiding row inserted messages from the server Dim batchDescription As String = String.Format(CultureInfo.InvariantCulture, "{0} batch {1}", stepDescription, batchIndex) Dim noCountOff As New PredicateSetStatement() noCountOff.IsOn = False noCountOff.Options = SetOptions.NoCount Dim noCountOn As New PredicateSetStatement() noCountOn.IsOn = True noCountOn.Options = SetOptions.NoCount Dim batchCompleteInsert As InsertStatement = CreateBatchCompleteInsert(batchId, batchDescription) statementBlock.StatementList.Statements.Add(noCountOn) statementBlock.StatementList.Statements.Add(batchCompleteInsert) statementBlock.StatementList.Statements.Add(noCountOff) ' Remove all the statements from the batch (they are now in the if block) and add the if statement ' as the sole statement in the batch batch.Statements.Clear() batch.Statements.Add(ifBatchNotExecutedStatement) ' Next batch batchId += 1This code creates an IF statement along with a BEGIN/END block. We then perform additional processing on the statements in the batch. Once that is complete, we add an INSERT statement to add information to the temporary table that tracks the progress of the script execution. Finally, update the batch, replacing the statements that used to be there with the new IF that contains those statements within it.
Key types, methods, and properties include:IfStatement, BeginEndBlockStatement, StatementList, TSqlBatch, PredicateSetStatement, SetOptions, and InsertStatement.
Now, add the body of the statement processing loop. Replace the comment that reads "Add additional statement processing here":
TSqlStatement smnt = batch.Statements[statementIndex]; if (IsStatementEscaped(element)) { // "escape" this statement by embedding it in a sp_executesql statement string statementScript = null; domStep.ScriptGenerator.GenerateScript(smnt, out statementScript); ExecuteStatement spExecuteSql = CreateExecuteSql(statementScript); smnt = spExecuteSql; } statementBlock.StatementList.Statements.Add(smnt);Dim smnt As TSqlStatement = batch.Statements(statementIndex) If IsStatementEscaped(element) Then ' "escape" this statement by embedding it in a sp_executesql statement Dim statementScript As String = Nothing domStep.ScriptGenerator.GenerateScript(smnt, statementScript) Dim spExecuteSql As ExecuteStatement = CreateExecuteSql(statementScript) smnt = spExecuteSql End If statementBlock.StatementList.Statements.Add(smnt)For each statement in the batch, if the statement is of a type that must be wrapped with an sp_executesql statement, modify the statement accordingly. The code then adds the statement to the statement list for the BEGIN/END block that you created. Key types, methods, and properties include TSqlStatement and ExecuteStatement.
Finally, add the post-processing section in place of the comment that reads "Add additional post-processing here":
// Declare a SqlCmd variables. // // CompletedBatches variable - defines the name of the table in tempdb that will track // all the completed batches. The temporary table's name has the target database name and // a guid embedded in it so that: // * Multiple deployment scripts targeting different DBs on the same server // * Failed deployments with old tables do not conflict with more recent deployments // // TotalBatchCount variable - the total number of batches surrounded by if statements. Using this // variable pre/post deployment scripts can also use the CompletedBatches table to make their // script rerunnable if there is an error during execution StringBuilder sqlcmdVars = new StringBuilder(); sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, CompletedBatchesSqlCmd, context.Options.TargetDatabaseName, Guid.NewGuid().ToString("D")); sqlcmdVars.AppendLine(); sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, TotalBatchCountSqlCmd, batchId); DeploymentScriptStep completedBatchesSetVarStep = new DeploymentScriptStep(sqlcmdVars.ToString()); base.AddBefore(context.PlanHandle, beforePreDeploy, completedBatchesSetVarStep); // Create the temporary table we will use to track the work that we are doing DeploymentScriptStep createStatusTableStep = new DeploymentScriptStep(CreateCompletedBatchesTable); base.AddBefore(context.PlanHandle, beforePreDeploy, createStatusTableStep);' Declare a SqlCmd variables. ' ' CompletedBatches variable - defines the name of the table in tempdb that will track ' all the completed batches. The temporary table's name has the target database name and ' a guid embedded in it so that: ' * Multiple deployment scripts targeting different DBs on the same server ' * Failed deployments with old tables do not conflict with more recent deployments ' ' TotalBatchCount variable - the total number of batches surrounded by if statements. Using this ' variable pre/post deployment scripts can also use the CompletedBatches table to make their ' script rerunnable if there is an error during execution Dim sqlcmdVars As New StringBuilder() sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, CompletedBatchesSqlCmd, context.Options.TargetDatabaseName, Guid.NewGuid().ToString("D")) sqlcmdVars.AppendLine() sqlcmdVars.AppendFormat(CultureInfo.InvariantCulture, TotalBatchCountSqlCmd, batchId) Dim completedBatchesSetVarStep As New DeploymentScriptStep(sqlcmdVars.ToString()) MyBase.AddBefore(context.PlanHandle, beforePreDeploy, completedBatchesSetVarStep) ' Create the temporary table we will use to track the work that we are doing Dim createStatusTableStep As New DeploymentScriptStep(CreateCompletedBatchesTable) MyBase.AddBefore(context.PlanHandle, beforePreDeploy, createStatusTableStep)If our processing found one or more steps that we surrounded with a conditional statement, we must add statements to the deployment script to define SQLCMD variables. The first variable, CompletedBatches, contains a unique name for the temporary table that the deployment script uses to keep track of which batches were successfully completed when the script was executed. The second variable, TotalBatchCount, contains the total number of batches in the deployment script.
Additional types, properties, and methods of interest include:
StringBuilder, DeploymentScriptStep, and AddBefore.
Next, you define the helper methods called by this method.
To add the CreateExecuteSql method
Add the following code to define the CreateExecuteSQL method to surround a provided statement with an EXEC sp_executesql statement:
/// <summary> /// The CreateExecuteSql method "wraps" the provided statement script in an "sp_executesql" statement /// Examples of statements that must be so wrapped include: stored procedures, views, and functions /// </summary> /// <param name="string"></param> /// <returns></returns> private static ExecuteStatement CreateExecuteSql(string statementScript) { // define a new Exec statement ExecuteStatement executeSp = new ExecuteStatement(); ExecutableProcedureReference spExecute = new ExecutableProcedureReference(); executeSp.ExecutableEntity = spExecute; // define the name of the procedure that you want to execute, in this case sp_executesql SchemaObjectName procName = new SchemaObjectName(); procName.Identifiers.Add(CreateIdentifier("sp_executesql", QuoteType.NotQuoted)); ProcedureReference procRef = new ProcedureReference(); procRef.Name = procName; spExecute.ProcedureReference = procRef; // add the script parameter, constructed from the provided statement script ExecuteParameter scriptParam = new ExecuteParameter(); spExecute.Parameters.Add(scriptParam); scriptParam.ParameterValue = CreateLiteral(statementScript, LiteralType.UnicodeStringLiteral); scriptParam.Variable = CreateLiteral("@stmt", LiteralType.Variable); return executeSp; }''' <summary> ''' The CreateExecuteSql method "wraps" the provided statement script in an "sp_executesql" statement ''' Examples of statements that must be so wrapped include: stored procedures, views, and functions ''' </summary> ''' <param name="statementScript"></param> ''' <returns></returns> Private Shared Function CreateExecuteSql(ByVal statementScript As String) As ExecuteStatement ' define a new Exec statement Dim executeSp As New ExecuteStatement() Dim spExecute As New ExecutableProcedureReference() executeSp.ExecutableEntity = spExecute ' define the name of the procedure that you want to execute, in this case sp_executesql Dim procName As New SchemaObjectName() procName.Identifiers.Add(CreateIdentifier("sp_executesql", QuoteType.NotQuoted)) Dim procRef As New ProcedureReference() procRef.Name = procName spExecute.ProcedureReference = procRef ' add the script parameter, constructed from the provided statement script Dim scriptParam As New ExecuteParameter() spExecute.Parameters.Add(scriptParam) scriptParam.ParameterValue = CreateLiteral(statementScript, LiteralType.UnicodeStringLiteral) scriptParam.Variable = CreateLiteral("@stmt", LiteralType.Variable) Return executeSp End FunctionKey types, methods, and properties include the following: ExecuteStatement, ExecutableProcedureReference, SchemaObjectName, ProcedureReference, and ExecuteParameter.
To add the CreateLiteral method
Add the following code to define the CreateLiteral method. This method creates a Literal object of the specified type from the provided string:
/// <summary> /// The CreateLiteral method creates a Literal object of the specified type from the provided string /// </summary> /// <param name="value"></param> /// <param name="type"></param> /// <returns></returns> private static Literal CreateLiteral(string value, LiteralType type) { Literal l = new Literal(); l.Value = value; l.LiteralType = type; return l; }''' <summary> ''' The CreateLiteral method creates a Literal object of the specified type from the provided string ''' </summary> ''' <param name="value"></param> ''' <param name="type"></param> ''' <returns></returns> Private Shared Function CreateLiteral(ByVal value As String, ByVal type As LiteralType) As Literal Dim l As New Literal() l.Value = value l.LiteralType = type Return l End FunctionKey types, methods, and properties include the following: Literal and LiteralType.
To add the CreateIdentifier method
Add the following code to define the CreateIdentifier method. This method creates an Identifier object that uses the specified type of quoting from the provided string:
/// <summary> /// The CreateIdentifier method returns a Identifier with the specified value and quoting type /// </summary> /// <param name="value"></param> /// <param name="quoteType"></param> /// <returns></returns> private static Identifier CreateIdentifier(string value, QuoteType quoteType) { Identifier id = new Identifier(); id.Value = value; id.QuoteType = quoteType; return id; }''' <summary> ''' The CreateIdentifier method returns a Identifier with the specified value and quoting type ''' </summary> ''' <param name="value"></param> ''' <param name="quoteType"></param> ''' <returns></returns> Private Shared Function CreateIdentifier(ByVal value As String, ByVal quoteType As QuoteType) As Identifier Dim id As New Identifier() id.Value = value id.QuoteType = quoteType Return id End FunctionKey types, methods, and properties include the following: Identifier and QuoteType.
To add the CreateCompletedBatchesName method
Add the following code to define the CreateCompletedBatchesName method. This method creates the name that will be inserted into the temporary table for a batch:
/// <summary> /// The CreateCompletedBatchesName method creates the name that will be inserted /// into the temporary table for a batch. /// </summary> /// <returns></returns> private static SchemaObjectName CreateCompletedBatchesName() { SchemaObjectName name = new SchemaObjectName(); name.Identifiers.Add(CreateIdentifier("tempdb", QuoteType.SquareBracket)); name.Identifiers.Add(CreateIdentifier("dbo", QuoteType.SquareBracket)); name.Identifiers.Add(CreateIdentifier(CompletedBatchesVariable, QuoteType.SquareBracket)); return name; }''' <summary> ''' The CreateCompletedBatchesName method creates the name that will be inserted ''' into the temporary table for a batch. ''' </summary> ''' <returns></returns> Private Shared Function CreateCompletedBatchesName() As SchemaObjectName Dim name As New SchemaObjectName() name.Identifiers.Add(CreateIdentifier("tempdb", QuoteType.SquareBracket)) name.Identifiers.Add(CreateIdentifier("dbo", QuoteType.SquareBracket)) name.Identifiers.Add(CreateIdentifier(CompletedBatchesVariable, QuoteType.SquareBracket)) Return name End FunctionKey types, methods, and properties include the following: SchemaObjectName.
To add the IsStatementEscaped method
Add the following code to define the IsStatementEscaped method. This method determines whether the type of model element requires the statement to be wrapped in an EXEC sp_executesql statement before it can be enclosed within an IF statement:
/// <summary> /// Helper method that determins whether the specified statement needs to /// be escaped /// </summary> /// <param name="smnt"></param> /// <returns></returns> private static bool IsStatementEscaped(IModelElement element) { return element is ISql90Schema || element is ISqlProcedure || element is ISqlView || element is ISqlFunction || element is ISqlTrigger; }''' <summary> ''' Helper method that determins whether the specified statement needs to ''' be escaped ''' </summary> ''' <param name="element"></param> ''' <returns></returns> Private Shared Function IsStatementEscaped(ByVal element As IModelElement) As Boolean Return TypeOf element Is ISql90Schema OrElse TypeOf element Is ISqlProcedure OrElse TypeOf element Is ISqlView OrElse TypeOf element Is ISqlFunction OrElse TypeOf element Is ISqlTrigger End FunctionKey types, methods, and properties include the following: IModelElement, ISql90Schema, ISqlProcedure, ISqlView, ISqlFunction, and ISqlTrigger.
To add the CreateBatchCompleteInsert method
Add the following code to define the CreateBatchCompleteInsert method. This method creates the INSERT statement that will be added to the deployment script to track progress of script execution:
/// <summary> /// Helper method that creates an INSERT statement to track a batch being completed /// </summary> /// <param name="batchId"></param> /// <param name="batchDescription"></param> /// <returns></returns> private static InsertStatement CreateBatchCompleteInsert(int batchId, string batchDescription) { InsertStatement insert = new InsertStatement(); SchemaObjectDataModificationTarget batchesCompleted = new SchemaObjectDataModificationTarget(); insert.Target = batchesCompleted; batchesCompleted.SchemaObject = CreateCompletedBatchesName(); // Build the columns inserted into Column batchIdColumn = new Column(); batchIdColumn.Identifiers.Add(CreateIdentifier(BatchIdColumnName, QuoteType.NotQuoted)); Column descriptionColumn = new Column(); descriptionColumn.Identifiers.Add(CreateIdentifier(DescriptionColumnName, QuoteType.NotQuoted)); insert.Columns.Add(batchIdColumn); insert.Columns.Add(descriptionColumn); // Build the values inserted ValuesInsertSource valueSource = new ValuesInsertSource(); insert.InsertSource = valueSource; RowValue values = new RowValue(); values.ColumnValues.Add(CreateLiteral(batchId.ToString(), LiteralType.Integer)); values.ColumnValues.Add(CreateLiteral(batchDescription, LiteralType.UnicodeStringLiteral)); valueSource.RowValues.Add(values); return insert; }''' <summary> ''' Helper method that creates an INSERT statement to track a batch being completed ''' </summary> ''' <param name="batchId"></param> ''' <param name="batchDescription"></param> ''' <returns></returns> Private Shared Function CreateBatchCompleteInsert(ByVal batchId As Integer, ByVal batchDescription As String) As InsertStatement Dim insert As New InsertStatement() Dim batchesCompleted As New SchemaObjectDataModificationTarget() insert.Target = batchesCompleted batchesCompleted.SchemaObject = CreateCompletedBatchesName() ' Build the columns inserted into Dim batchIdColumn As New Column() batchIdColumn.Identifiers.Add(CreateIdentifier(BatchIdColumnName, QuoteType.NotQuoted)) Dim descriptionColumn As New Column() descriptionColumn.Identifiers.Add(CreateIdentifier(DescriptionColumnName, QuoteType.NotQuoted)) insert.Columns.Add(batchIdColumn) insert.Columns.Add(descriptionColumn) ' Build the values inserted Dim valueSource As New ValuesInsertSource() insert.InsertSource = valueSource Dim values As New RowValue() values.ColumnValues.Add(CreateLiteral(batchId.ToString(), LiteralType.[Integer])) values.ColumnValues.Add(CreateLiteral(batchDescription, LiteralType.UnicodeStringLiteral)) valueSource.RowValues.Add(values) Return insert End FunctionKey types, methods, and properties include the following: InsertStatement, SchemaObjectDataModificationTarget, Column, ValuesInsertSource, and RowValue.
To add the CreateIfNotExecutedStatement method
Add the following code to define the CreateIfNotExecutedStatement method. This method generates an IF statement that checks to see if the temporary batches executes table indicates that this batch has already been executed:
/// <summary> /// This is a helper method that generates an if statement that checks the batches executed /// table to see if the current batch has been executed. The if statement will look like this /// /// if not exists(select 1 from [tempdb].[dbo].[$(CompletedBatches)] /// where BatchId = batchId) /// begin /// end /// </summary> /// <param name="batchId"></param> /// <returns></returns> private static IfStatement CreateIfNotExecutedStatement(int batchId) { // Create the exists/select statement ExistsPredicate existsExp = new ExistsPredicate(); Subquery subQuery = new Subquery(); existsExp.Subquery = subQuery; QuerySpecification select = new QuerySpecification(); subQuery.QueryExpression = select; select.SelectElements.Add(CreateLiteral("1", LiteralType.Integer)); SchemaObjectTableSource completedBatchesTable = new SchemaObjectTableSource(); select.FromClauses.Add(completedBatchesTable); completedBatchesTable.SchemaObject = CreateCompletedBatchesName(); WhereClause where = new WhereClause(); select.WhereClause = where; Column batchIdColumn = new Column(); batchIdColumn.Identifiers.Add(CreateIdentifier(BatchIdColumnName, QuoteType.SquareBracket)); Literal batchIdValue = CreateLiteral(batchId.ToString(), LiteralType.Integer); BinaryExpression stepsEqual = new BinaryExpression(); where.SearchCondition = stepsEqual; stepsEqual.BinaryExpressionType = BinaryExpressionType.Equals; stepsEqual.FirstExpression = batchIdColumn; stepsEqual.SecondExpression = batchIdValue; // Put together the rest of the statement IfStatement ifNotExists = new IfStatement(); UnaryExpression notExp = new UnaryExpression(); ifNotExists.Predicate = notExp; notExp.UnaryExpressionType = UnaryExpressionType.Not; notExp.Expression = existsExp; return ifNotExists; }''' <summary> ''' This is a helper method that generates an if statement that checks the batches executed ''' table to see if the current batch has been executed. The if statement will look like this ''' ''' if not exists(select 1 from [tempdb].[dbo].[$(CompletedBatches)] ''' where BatchId = batchId) ''' begin ''' end ''' </summary> ''' <param name="batchId"></param> ''' <returns></returns> Private Shared Function CreateIfNotExecutedStatement(ByVal batchId As Integer) As IfStatement ' Create the exists/select statement Dim existsExp As New ExistsPredicate() Dim subQuery As New Subquery() existsExp.Subquery = subQuery Dim [select] As New QuerySpecification() subQuery.QueryExpression = [select] [select].SelectElements.Add(CreateLiteral("1", LiteralType.[Integer])) Dim completedBatchesTable As New SchemaObjectTableSource() [select].FromClauses.Add(completedBatchesTable) completedBatchesTable.SchemaObject = CreateCompletedBatchesName() Dim where As New WhereClause() [select].WhereClause = where Dim batchIdColumn As New Column() batchIdColumn.Identifiers.Add(CreateIdentifier(BatchIdColumnName, QuoteType.SquareBracket)) Dim batchIdValue As Literal = CreateLiteral(batchId.ToString(), LiteralType.[Integer]) Dim stepsEqual As New BinaryExpression() where.SearchCondition = stepsEqual stepsEqual.BinaryExpressionType = BinaryExpressionType.Equals stepsEqual.FirstExpression = batchIdColumn stepsEqual.SecondExpression = batchIdValue ' Put together the rest of the statement Dim ifNotExists As New IfStatement() Dim notExp As New UnaryExpression() ifNotExists.Predicate = notExp notExp.UnaryExpressionType = UnaryExpressionType.[Not] notExp.Expression = existsExp Return ifNotExists End FunctionKey types, methods, and properties include the following: IfStatement, ExistsPredicate, Subquery, SchemaObjectTableSource, WhereClause, Column, Literal, BinaryExpression, and UnaryExpression
To add the GetStepInfo method
Add the following code to define the GetStepInfo method. This method:
/// <summary> /// Helper method that generates a useful description of the step. /// </summary> /// <param name="context"></param> /// <param name="domStep"></param> /// <param name="stepDescription"></param> /// <param name="element"></param> private static void GetStepInfo( DeploymentPlanContributorContext context, DeploymentScriptDomStep domStep, out string stepDescription, out IModelElement element) { element = null; CreateElementStep createStep = null; AlterElementStep alterStep = null; DropElementStep dropStep = null; // figure out what type of step we've got, and retrieve // either the source or target element. if ((createStep = domStep as CreateElementStep) != null) { element = createStep.SourceElement; } else if ((alterStep = domStep as AlterElementStep) != null) { element = alterStep.SourceElement; } else if ((dropStep = domStep as DropElementStep) != null) { element = dropStep.TargetElement; } // construct the step description by concatenating the type and the fully qualified // name of the associated element. string stepTypeName = domStep.GetType().Name; if (element != null) { string elementName = context.Source.DatabaseSchemaProvider.UserInteractionServices.GetElementName( element, Microsoft.Data.Schema.ElementNameStyle.FullyQualifiedName); stepDescription = string.Format(CultureInfo.InvariantCulture, "{0} {1}", stepTypeName, elementName); } else { // if the step has no associated element, just use the step type as the description stepDescription = stepTypeName; } }''' <summary> ''' Helper method that generates a useful description of the step. ''' </summary> ''' <param name="context"></param> ''' <param name="domStep"></param> ''' <param name="stepDescription"></param> ''' <param name="element"></param> Private Shared Sub GetStepInfo(ByVal context As DeploymentPlanContributorContext, ByVal domStep As DeploymentScriptDomStep, ByRef stepDescription As String, ByRef element As IModelElement) element = Nothing Dim createStep As CreateElementStep = Nothing Dim alterStep As AlterElementStep = Nothing Dim dropStep As DropElementStep = Nothing ' figure out what type of step we've got, and retrieve ' either the source or target element. If (InlineAssignHelper(createStep, TryCast(domStep, CreateElementStep))) IsNot Nothing Then element = createStep.SourceElement ElseIf (InlineAssignHelper(alterStep, TryCast(domStep, AlterElementStep))) IsNot Nothing Then element = alterStep.SourceElement ElseIf (InlineAssignHelper(dropStep, TryCast(domStep, DropElementStep))) IsNot Nothing Then element = dropStep.TargetElement End If ' construct the step description by concatenating the type and the fully qualified ' name of the associated element. Dim stepTypeName As String = domStep.[GetType]().Name If element IsNot Nothing Then Dim elementName As String = context.Source.DatabaseSchemaProvider.UserInteractionServices.GetElementName(element, Microsoft.Data.Schema.ElementNameStyle.FullyQualifiedName) stepDescription = String.Format(CultureInfo.InvariantCulture, "{0} {1}", stepTypeName, elementName) Else ' if the step has no associated element, just use the step type as the description stepDescription = stepTypeName End If End Sub Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, ByVal value As T) As T target = value Return value End FunctionTypes and methods of interest include the following: DeploymentPlanContributorContext, DeploymentScriptDomStep, IModelElement, CreateElementStep, AlterElementStep, and DropElementStep.
Save the changes to Class1.cs.
Next, you build the class library.
To sign and build the assembly
On the Project menu, click MyOtherDeploymentContributor Properties.
Click the Signing tab.
Click Sign the assembly.
In Choose a strong name key file, click <New>.
In the Create Strong Name Key dialog box, in Key file name, type MyRefKey.
(optional) You can specify a password for your strong name key file.
Click OK.
On the File menu, click Save All.
On the Build menu, click Build Solution.
Next, you must install and register the assembly so that it will be loaded when you deploy database projects.
Install a Deployment Contributor
To install a deployment contributor, you must perform the following tasks:
Copy the assembly and associated .pdb file to the Extensions folder
Create an Extensions.xml file to register the deployment contributor so that it is loaded when you deployment database projects
To install the MyOtherDeploymentContributor assembly
Create a folder named MyExtensions in the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions folder.
Copy your signed assembly (MyOtherDeploymentContributor.dll) to the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\MyExtensions folder.
Note
We recommend that you do not copy your XML files directly into the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions folder. If you use a subfolder instead, you will prevent accidental changes to the other files that are provided with Visual Studio.
Next, you must register your assembly, which is a type of feature extension, so that it will appear in Visual Studio.
To register the MyOtherDeploymentContributor assembly
On the View menu, click Other Windows, and then click Command Window to open the Command window.
In the Command window, type the following code. For FilePath, substitute the path and file name of your compiled .dll file. Include the quotation marks around the path and file name.
Note
By default, the path of your compiled .dll file is YourSolutionPath\bin\Debug or YourSolutionPath\bin\Release.
? System.Reflection.Assembly.LoadFrom(@"FilePath").FullName? System.Reflection.Assembly.LoadFrom("FilePath").FullNamePress Enter.
Copy the resultant line to the Clipboard. The line should resemble the following:
"GeneratorAssembly, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn"Open a plain-text editor, such as Notepad.
Important
On Windows Vista and Microsoft Windows Server 2008, open the editor as an administrator so that you can save the file to your Program Files folder.
Provide the following information, specifying your own assembly name, public key token, and extension type:
<?xml version="1.0" encoding="utf-8" ?> <extensions assembly="" version="1" xmlns="urn:Microsoft.Data.Schema.Extensions" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd"> <extension type="MyOtherDeploymentContributor.SqlRestartableScriptContributor" assembly="MyOtherDeploymentContributor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=<enter key here>" enabled="true" /> </extensions>You use this XML file to register the class that inherits from DeploymentPlanExecutor.
Save the file as MyOtherDeploymentContributor.extensions.xml in the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\MyExtensions folder.
Close Visual Studio.
Next, you will deploy a database project to test your contributor.
Test your Deployment Contributor
To test your deployment contributor, you must perform the following task:
- Deploy the database project by using MSBuild and providing the appropriate parameter
Because this deployment contributor is always enabled, you do not have to modify the database project to add any properties.
Deploy the Database Project
To deploy your database project and generate a deployment report
Open a Visual Studio Command Prompt. On the Start menu, click All Programs, click Microsoft Visual Studio 2010, click Visual Studio Tools, and click Visual Studio Command Prompt (2010).
At the command prompt, navigate to the folder that contains your database project.
At the command prompt, type the following command line:
MSBuild /t:Deploy MyDatabaseProject.dbprojReplace MyDatabaseProject with the name of the database project that you want to deploy.
Examine the resulting deployment script. Just before the section labeled "Pre-Deployment Script Template", you should see something that resembles the following Transact-SQL:
:setvar CompletedBatches __completedBatches_CompareProjectDB_cd1e348a-8f92-44e0-9a96-d25d65900fca :setvar TotalBatchCount 17 GO if OBJECT_ID(N'tempdb.dbo.$(CompletedBatches)', N'U') is null begin use tempdb create table [dbo].[$(CompletedBatches)] ( BatchId int primary key, Description nvarchar(300) ) use [$(DatabaseName)] endLater in the deployment script, around each batch, you see an IF statement that surrounds the original statement. For example, the following might appear for a CREATE SCHEMA statement:
IF NOT EXISTS (SELECT 1 FROM [tempdb].[dbo].[$(CompletedBatches)] WHERE [BatchId] = 0) BEGIN EXECUTE sp_executesql @stmt = N'CREATE SCHEMA [Sales] AUTHORIZATION [dbo]'; SET NOCOUNT ON; INSERT [tempdb].[dbo].[$(CompletedBatches)] (BatchId, Description) VALUES (0, N'CreateElementStep Sales batch 0'); SET NOCOUNT OFF; ENDNotice that CREATE SCHEMA is one of the statements that must be enclosed within an EXECUTE sp_executesql statement within the IF statement. Statements such as CREATE TABLE do not require the EXECUTE sp_executesql statement and will resemble the following example:
IF NOT EXISTS (SELECT 1 FROM [tempdb].[dbo].[$(CompletedBatches)] WHERE [BatchId] = 1) BEGIN CREATE TABLE [Sales].[Customer] ( [CustomerID] INT IDENTITY (1, 1) NOT NULL, [CustomerName] NVARCHAR (40) NOT NULL, [YTDOrders] INT NOT NULL, [YTDSales] INT NOT NULL ); SET NOCOUNT ON; INSERT [tempdb].[dbo].[$(CompletedBatches)] (BatchId, Description) VALUES (1, N'CreateElementStep Sales.Customer batch 0'); SET NOCOUNT OFF; ENDNote
If you deploy a database project that is identical to the target database, the resulting report will not be very meaningful. For more meaningful results, either deploy changes to a database or deploy a new database.
You can add, remove, or modify batches or statements in any deployment plan by using a DeploymentPlanModifier.
Next Steps
You can experiment with other types of modifications that you can make to deployment plans before they are executed. Some other types of modifications that you might want to make include the following: adding an extended property to all database objects that associate a version number with them, adding or removing additional diagnostic print statements or comments from deployment scripts, and so on.
See Also
Tasks
Walkthrough: Extend Database Project Build to Generate Model Statistics
Walkthrough: Extend Database Project Deployment to Analyze the Deployment Plan
Concepts
Customize Database Build and Deployment by Using Build and Deployment Contributors