Export (0) Print
Expand All

Walkthrough: Extend Database Project Deployment to Modify the Deployment Plan

Visual Studio 2010

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:

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

NoteNote

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.

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

  1. Create a Visual C# or Visual Basic class library project named MyOtherDeploymentContributor.

  2. In Solution Explorer, right-click the project and then click Add Reference.

  3. Click the .NET tab.

  4. 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

  1. 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.SchemaModel
    
  2. Update 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 Class
    

    Now 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.

  3. 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

  1. 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 Sub
    

    You 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.

  2. 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 .

  3. 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
                Next
    

    The 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.

  4. 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 += 1
    

    This 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.

  5. 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.

  6. 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 Function
    
    

    Key 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 Function
    
    

    Key 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 Function
    

    Key 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 Function
    

    Key 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 Function
    

    Key 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 Function
    

    Key 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 Function
    

    Key types, methods, and properties include the following: IfStatement, ExistsPredicate, Subquery, SchemaObjectTableSource, WhereClause, Column, Literal, BinaryExpression, and UnaryExpression

To add the GetStepInfo method

  1. 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 Function
    

    Types and methods of interest include the following: DeploymentPlanContributorContext, DeploymentScriptDomStep, IModelElement, CreateElementStep, AlterElementStep, and DropElementStep.

  2. Save the changes to Class1.cs.

    Next, you build the class library.

To sign and build the assembly

  1. On the Project menu, click MyOtherDeploymentContributor Properties.

  2. Click the Signing tab.

  3. Click Sign the assembly.

  4. In Choose a strong name key file, click <New>.

  5. In the Create Strong Name Key dialog box, in Key file name, type MyRefKey.

  6. (optional) You can specify a password for your strong name key file.

  7. Click OK.

  8. On the File menu, click Save All.

  9. 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.

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

  1. Create a folder named MyExtensions in the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions folder.

  2. Copy your signed assembly (MyOtherDeploymentContributor.dll) to the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\MyExtensions folder.

    NoteNote

    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

  1. On the View menu, click Other Windows, and then click Command Window to open the Command window.

  2. 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.

    NoteNote

    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").FullName
    

  3. Press Enter.

  4. Copy the resultant line to the Clipboard. The line should resemble the following:

    "GeneratorAssembly, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn"
    
  5. Open a plain-text editor, such as Notepad.

    Important noteImportant

    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.

  6. 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.

  7. Save the file as MyOtherDeploymentContributor.extensions.xml in the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\MyExtensions folder.

  8. Close Visual Studio.

    Next, you will deploy a database project to test your 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

  1. 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).

  2. At the command prompt, navigate to the folder that contains your database project.

  3. At the command prompt, type the following command line:

    MSBuild /t:Deploy MyDatabaseProject.dbproj
    

    Replace MyDatabaseProject with the name of the database project that you want to deploy.

  4. 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)]
    end
    

    Later 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;
        END
    

    Notice 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;
        END
    
    NoteNote

    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.

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.

Community Additions

ADD
Show:
© 2014 Microsoft