Export (0) Print
Expand All

Walkthrough: Extend Database Project Deployment to Analyze 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 DeploymentPlanExecutor named DeploymentUpdateReportContributor that creates a report about the actions that are performed when you deploy a database project. Because this build contributor accepts a parameter to control whether the report is generated, you must perform an additional required step.

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 DeploymentUpdateReportContributor that inherits from DeploymentPlanExecutor

  • Override the OnPopulateArguments and OnExecute methods.

  • Add a private helper class

  • Build the resulting assembly

To create a class library project

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

  2. In Solution Explorer, right-click the References folder and click Add Reference.

  3. Click the .NET tab.

  4. Highlight the Microsoft.Data.Schema and Microsoft.Data.Schema.Sql entries and click OK.

    Next, start to add code to the class.

To define the DeploymentUpdateReportContributor class

  1. In the code editor, update the class1.cs file to match the following using or Imports statements:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using Microsoft.Data.Schema.Build;
    using Microsoft.Data.Schema.Extensibility;
    using Microsoft.Data.Schema.Sql;
    using System.IO;
    using Microsoft.Data.Schema.SchemaModel;
    using System.Xml;
    using Microsoft.Data.Schema;
    using Microsoft.Data.Schema.Sql.Build;
    

    Imports System
    Imports System.Collections.Generic
    Imports System.Text
    Imports Microsoft.Data.Schema.Build
    Imports Microsoft.Data.Schema.Extensibility
    Imports Microsoft.Data.Schema.Sql
    Imports System.IO
    Imports Microsoft.Data.Schema.SchemaModel
    Imports System.Xml
    Imports Microsoft.Data.Schema
    Imports Microsoft.Data.Schema.Sql.Build
    
  2. Update the class definition to match the following:

        [DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
        class DeploymentUpdateReportContributor : DeploymentPlanExecutor
        {
        }
    

    ''' <summary>
    ''' The DeploymentUpdateReportContributor class demonstrates
    ''' how you can create a class that inherits DeploymentPlanExecutor
    ''' to perform actions when you execute the deployment plan
    ''' for a database project.
    ''' </summary>
    <DatabaseSchemaProviderCompatibility(GetType(SqlDatabaseSchemaProvider))>
    Public Class DeploymentUpdateReportContributor
        Inherits DeploymentPlanExecutor
    End Class
    

    Now you have defined your build contributor and used the attribute to indicate that this contributor is compatible with any database schema provider that inherits from SqlDatabaseSchemaProvider.

  3. Next, add the following member that you will use to enable this provider to accept a command-line parameter:

            private const string GenerateUpdateReport = "GenerateUpdateReport";
    
    

        Dim GenerateUpdateReport As String = "GenerateUpdateReport"
    

    This member enables the user to specify whether the report should be generated by using the GenerateUpdateReport option.

    Next, you override the OnPopulateArguments method to build the list of arguments to pass to the deployment contributor.

To override OnPopulateArguments

  • Add the following override method to the DeploymentUpdateReportContributor class:

        /// <summary>
        /// Override the OnPopulateArgument method to build a list of arguments from the input
        /// configuration information.
        /// </summary>
            protected override IList<ContributorArgumentConfiguration> OnPopulateArguments()
            {
                List<ContributorArgumentConfiguration> args = new List<ContributorArgumentConfiguration>();
    
                // Generate reports when in debug configuration
                args.Add(new ContributorArgumentConfiguration( GenerateUpdateReport, "true", "'$(Configuration)' == 'Debug'"));
                return args;
            }
    

        ''' <summary>
        ''' Override the OnPopulateArgument method to build a list of arguments from the input
        ''' configuration information.
        ''' </summary>
        Protected Overloads Overrides Function OnPopulateArguments() As IList(Of ContributorArgumentConfiguration)
            Dim args As New List(Of ContributorArgumentConfiguration)()
    
            ' Generate reports when in debug configuration 
            args.Add(New ContributorArgumentConfiguration(GenerateUpdateReport, "true", "'$(Configuration)' == 'Debug'"))
            Return args
        End Function
    
    

    You build a ContributorArgumentConfiguration object, and added it to the arguments list. By default, the report is generated when you generate a debug build.

    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 DeploymentUpdateReportContributor class:

        /// <summary>
        /// Override the OnExecute method to perform actions when you execute the deployment plan for
        /// a database project.
        /// </summary>
            protected override void OnExecute(DeploymentPlanContributorContext context)
            {
                // determine whether the user specified a report is to be generated
                bool generateReport = false;
                string generateReportValue;
                if (context.Arguments.TryGetValue(GenerateUpdateReport, out generateReportValue) == false)
                {
                    // couldn't find the GenerateUpdateReport argument, so do not generate
                    generateReport = false;
                }
                else
                {
                    // GenerateUpdateReport argument was specified, try to parse the value
                    if (bool.TryParse(generateReportValue, out generateReport))
                    {
                        // if we end up here, the value for the argument was not valid.
                        // default is false, so do nothing.
                    }
                }
    
                if (generateReport == false)
                {
                    // if user does not want to generate a report, we are done
                    return;
                }
    
                // We will output to the same directory where the deployment script
                // is output or to the current directory
                string reportPrefix = context.Options.TargetDatabaseName;
                string reportPath;
                if (string.IsNullOrEmpty(context.DeploymentScriptPath))
                {
                    reportPath = Environment.CurrentDirectory;
                }
                else
                {
                    reportPath = Path.GetDirectoryName(context.DeploymentScriptPath);
                }
                FileInfo summaryReportFile = new FileInfo(Path.Combine(reportPath, reportPrefix + ".summary.xml"));
                FileInfo detailsReportFile = new FileInfo(Path.Combine(reportPath, reportPrefix + ".details.xml"));
    
                // Generate the reports by using the helper class DeploymentReportWriter
                DeploymentReportWriter writer = new DeploymentReportWriter(context);
                writer.WriteReport(summaryReportFile);
                writer.IncludeScripts = true;
                writer.WriteReport(detailsReportFile);
    
                string msg = "Deployment reports ->"
                    + Environment.NewLine + summaryReportFile.FullName
                    + Environment.NewLine + detailsReportFile.FullName;
    
                DataSchemaError reportMsg = new DataSchemaError(msg, ErrorSeverity.Message);
                base.PublishMessage(reportMsg);
            }
    

        ''' <summary>
        ''' Override the OnExecute method to perform actions when you execute the deployment plan for
        ''' a database project.
        ''' </summary>
        Protected Overloads Overrides Sub OnExecute(ByVal context As DeploymentPlanContributorContext)
            ' output the names and values for any provided arguments 
            For Each arg As KeyValuePair(Of String, String) In context.Arguments
                Dim argMsg As New DataSchemaError((arg.Key & "=") + arg.Value, ErrorSeverity.Message)
                Me.PublishMessage(argMsg)
            Next
            ' determine whether the user specified a report is to be generated 
            Dim generateReport As Boolean = False
            Dim generateReportValue As String
            If context.Arguments.TryGetValue(GenerateUpdateReport, generateReportValue) = False Then
                ' couldn't find the GenerateUpdateReport argument, so do not generate 
                generateReport = False
            Else
                ' GenerateUpdateReport argument was specified, try to parse the value 
                If Boolean.TryParse(generateReportValue, generateReport) Then
                    ' if we end up here, the value for the argument was not valid. 
                    ' default is false, so do nothing. 
                End If
            End If
    
            If generateReport = False Then
                ' if user does not want to generate a report, we are done 
                Exit Sub
            End If
    
            ' We will output to the same directory where the deployment script 
            ' is output or to the current directory 
            Dim reportPrefix As String = context.Options.TargetDatabaseName
            Dim reportPath As String
            If String.IsNullOrEmpty(context.DeploymentScriptPath) Then
                reportPath = Environment.CurrentDirectory
            Else
                reportPath = Path.GetDirectoryName(context.DeploymentScriptPath)
            End If
            Dim summaryReportFile As New FileInfo(Path.Combine(reportPath, reportPrefix & ".summary.xml"))
            Dim detailsReportFile As New FileInfo(Path.Combine(reportPath, reportPrefix & ".details.xml"))
    
            ' Generate the reports by using the helper class DeploymentReportWriter 
            Dim writer As New DeploymentReportWriter(context)
            writer.WriteReport(summaryReportFile)
            writer.IncludeScripts = True
            writer.WriteReport(detailsReportFile)
    
            Dim msg As String = ("Deployment reports ->" & Environment.NewLine) + summaryReportFile.FullName + Environment.NewLine + detailsReportFile.FullName
    
            Dim reportMsg As New DataSchemaError(msg, ErrorSeverity.Message)
            MyBase.PublishMessage(reportMsg)
        End Sub
    

    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 model, and then call helper functions to output information about the model. The method is also passed an ErrorManager to use to report any errors that occur.

    Additional types and methods of interest include: DataSchemaModel, ModelStore, ModelComparisonResult , DatabaseSchemaProvider , DeploymentPlanHandle , and SchemaDeploymentOptions.

    Next, you define the helper class that dig into the details of the deployment plan.

To add the helper class that generates the report body

  • First, add the skeletons of the helper class and its methods by adding the following code:

            /// <summary>
            /// This class is used to generate a deployment
            /// report. 
            /// </summary>
            private class DeploymentReportWriter
            {
                /// <summary>
                /// The constructor accepts the same context info
                /// that was passed to the OnExecute method of the
                /// deployment contributor.
                /// </summary>
                public DeploymentReportWriter(DeploymentPlanContributorContext context)
                {
               }
                /// <summary>
                /// Property indicating whether script bodies
                /// should be included in the report.
                /// </summary>
                public bool IncludeScripts { get; set; }
    
                /// <summary>
                /// Drives the report generation, opening files, 
                /// writing the beginning and ending report elements,
                /// and calling helper methods to report on the
                /// plan operations.
                /// </summary>
                internal void WriteReport(FileInfo reportFile)
                {
                }
    
                /// <summary>
                /// Writes details for the various operation types
                /// that could be contained in the deployment plan.
                /// Optionally writes script bodies, depending on
                /// the value of the IncludeScripts property.
                /// </summary>
                private void ReportPlanOperations(XmlWriter xmlw)
                {
                }
    
                /// <summary>
                /// Returns the category of the specified element
                /// in the source model
                /// </summary>
                private string GetElementCategory(IModelElement element)
                {
                }
    
                /// <summary>
                /// Returns the name of the specified element
                /// in the source model
                /// </summary>
                private string GetElementName(IModelElement element)
                {
                }
            }
    

    ''' <summary>
    ''' This class is used to generate a deployment
    ''' report. 
    ''' </summary>
    Private Class DeploymentReportWriter
    
        Public Sub New(ByVal context As DeploymentPlanContributorContext)
        End Sub
    
        Private _includeScripts As Boolean
        ''' <summary>
        ''' Property indicating whether script bodies
        ''' should be included in the report.
        ''' </summary>
        Public Property IncludeScripts() As Boolean
            Get
                IncludeScripts = _includeScripts
            End Get
            Set(ByVal value As Boolean)
                _includeScripts = value
            End Set
        End Property
    
    
        ''' <summary> 
        ''' Drives the report generation, opening files, 
        ''' writing the beginning and ending report elements, 
        ''' and calling helper methods to report on the 
        ''' plan operations. 
        ''' </summary> 
        Friend Sub WriteReport(ByVal reportFile As FileInfo)
        End Sub
    
        ''' <summary> 
        ''' Writes details for the various operation types 
        ''' that could be contained in the deployment plan. 
        ''' Optionally writes script bodies, depending on 
        ''' the value of the IncludeScripts property. 
        ''' </summary> 
        Private Sub ReportPlanOperations(ByVal xmlw As XmlWriter)
        End Sub
        ''' <summary>
        ''' Returns the category of the specified element
        ''' in the source model
        ''' </summary> 
        Private Function GetElementCategory(ByVal element As IModelElement) As String
            Return ""
        End Function
    
        ''' <summary>
        ''' Returns the name of the specified element
        ''' in the source model
        ''' </summary>
        Private Function GetElementName(ByVal element As IModelElement) As String
            Return ""
        End Function
    End Class
    
  • Save the changes to Class1.cs.

    Next, you add the class members and method bodies..

To add the class members

  • In the code editor, add the following code to the DeploymentReportWriter class:

                readonly DataSchemaModel _sourceModel;
                readonly ModelComparisonResult _diff;
                readonly DeploymentStep _planHead;
    

            ReadOnly _sourceModel As DataSchemaModel
            ReadOnly _diff As ModelComparisonResult
            ReadOnly _planHead As DeploymentStep
    

    Types of interest include the following: DataSchemaModel , ModelComparisonResult , and DeploymentStep .

    Next, you add the body to the class constructor.

To add the method body to the constructor

  • Add the following code as the body of the constructor:

                    if (context == null)
                    {
                        throw new ArgumentNullException("context");
                    }
    
                    // save the source model, source/target differences,
                    // and the beginning of the deployment plan.
                    _sourceModel = context.Source;
                    _diff = context.ComparisonResult;
                    _planHead = context.PlanHandle.Head;
    

                If context Is Nothing Then
                    Throw New ArgumentNullException("context")
                End If
    
                ' save the source model, source/target differences, 
                ' and the beginning of the deployment plan. 
                _sourceModel = context.Source
                _diff = context.ComparisonResult
                _planHead = context.PlanHandle.Head
    

    Next, you add the method body to the WriteReport method.

To add the method body to the WriteReport method

  • Add the following code as the body of the WriteReport method:

                    // Assumes that we have a valid report file
                    if (reportFile == null)
                    {
                        throw new ArgumentNullException("reportFile");
                    }
    
                    // set up the XML writer
                    XmlWriterSettings xmlws = new XmlWriterSettings();
                    // Indentation makes it a bit more readable
                    xmlws.Indent = true;
                    FileStream fs = new FileStream(reportFile.FullName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite);
                    XmlWriter xmlw = XmlWriter.Create(fs, xmlws);
    
                    try
                    {
                        xmlw.WriteStartDocument(true);
                        xmlw.WriteStartElement("DeploymentReport");
    
                        // Summary report of the operations that
                        // are contained in the plan.
                        ReportPlanOperations(xmlw);
    
                        // You could add a method call here
                        // to produce a detailed listing of the 
                        // differences between the source and
                        // target model.
                        xmlw.WriteEndElement();
                        xmlw.WriteEndDocument();
                        xmlw.Flush();
                        fs.Flush();
                    }
                    finally
                    {
                        xmlw.Close();
                        fs.Dispose();
                    }
    

                ' Assumes that we have a valid report file 
                If reportFile Is Nothing Then
                    Throw New ArgumentNullException("reportFile")
                End If
    
                ' set up the XML writer 
                Dim xmlws As New XmlWriterSettings()
                ' Indentation makes it a bit more readable 
                xmlws.Indent = True
                Dim fs As New FileStream(reportFile.FullName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite)
                Dim xmlw As XmlWriter = XmlWriter.Create(fs, xmlws)
    
                Try
                    xmlw.WriteStartDocument(True)
                    xmlw.WriteStartElement("DeploymentReport")
    
                    ' Summary report of the operations that 
                    ' are contained in the plan. 
                    ReportPlanOperations(xmlw)
    
                    ' You could add a method call here 
                    ' to produce a detailed listing of the 
                    ' differences between the source and 
                    ' target model. 
                    xmlw.WriteEndElement()
                    xmlw.WriteEndDocument()
                    xmlw.Flush()
                    fs.Flush()
                Finally
                    xmlw.Close()
                    fs.Dispose()
                End Try
    

    Types of interest are XmlWriter and XmlWriterSettings.

    Next, you add the body to the ReportPlanOperations method.

To add the method body to the ReportPlanOperations method

  • Add the following code as the body of the ReportPlanOperations method:

                    // write the node to indicate the start
                    // of the list of operations.
                    xmlw.WriteStartElement("Operations");
    
                    // Loop through the steps in the plan,
                    // starting at the beginning.
                    DeploymentStep currentStep = _planHead;
                    while (currentStep != null)
                    {
                        // Report the type of step
                        xmlw.WriteStartElement(currentStep.GetType().Name);
    
                        // based on the type of step, report
                        // the relevant information.
                        // Note that this procedure only handles 
                        // a subset of all step types.
                        if (currentStep is SqlRenameStep)
                        {
                            SqlRenameStep renameStep = (SqlRenameStep)currentStep;
                            xmlw.WriteAttributeString("OriginalName", renameStep.OldName);
                            xmlw.WriteAttributeString("NewName", renameStep.NewName);
                            xmlw.WriteAttributeString("Category", GetElementCategory(renameStep.RenamedElement));
                        }
                        else if (currentStep is SqlMoveSchemaStep)
                        {
                            SqlMoveSchemaStep moveStep = (SqlMoveSchemaStep)currentStep;
                            xmlw.WriteAttributeString("OrignalName", moveStep.PreviousName);
                            xmlw.WriteAttributeString("NewSchema", moveStep.NewSchema);
                            xmlw.WriteAttributeString("Category", GetElementCategory(moveStep.MovedElement));
                        }
                        else if (currentStep is SqlTableMigrationStep)
                        {
                            SqlTableMigrationStep dmStep = (SqlTableMigrationStep)currentStep;
                            xmlw.WriteAttributeString("Name", GetElementName(dmStep.SourceTable));
                            xmlw.WriteAttributeString("Category", GetElementCategory(dmStep.SourceElement));
                        }
                        else if (currentStep is CreateElementStep)
                        {
                            CreateElementStep createStep = (CreateElementStep)currentStep;
                            xmlw.WriteAttributeString("Name", GetElementName(createStep.SourceElement));
                            xmlw.WriteAttributeString("Category", GetElementCategory(createStep.SourceElement));
                        }
                        else if (currentStep is AlterElementStep)
                        {
                            AlterElementStep alterStep = (AlterElementStep)currentStep;
                            xmlw.WriteAttributeString("Name", GetElementName(alterStep.SourceElement));
                            xmlw.WriteAttributeString("Category", GetElementCategory(alterStep.SourceElement));
                        }
                        else if (currentStep is DropElementStep)
                        {
                            DropElementStep dropStep = (DropElementStep)currentStep;
                            xmlw.WriteAttributeString("Name", GetElementName(dropStep.TargetElement));
                            xmlw.WriteAttributeString("Category", GetElementCategory(dropStep.TargetElement));
                        }
    
                        // If the script bodies are to be included,
                        // add them to the report.
                        if (this.IncludeScripts)
                        {
                            string tsqlBody = currentStep.Action();
                            if (string.IsNullOrEmpty(tsqlBody) == false)
                            {
                                xmlw.WriteCData(tsqlBody);
                            }
                        }
    
                        // close off the current step
                        xmlw.WriteEndElement();
                        currentStep = currentStep.Next;
                    }
                    xmlw.WriteEndElement();
    

                ' write the node to indicate the start 
                ' of the list of operations. 
                xmlw.WriteStartElement("Operations")
    
                ' Loop through the steps in the plan, 
                ' starting at the beginning. 
                Dim currentStep As DeploymentStep = _planHead
                While currentStep IsNot Nothing
                    ' Report the type of step 
                    xmlw.WriteStartElement(currentStep.[GetType]().Name)
    
                    ' based on the type of step, report 
                    ' the relevant information. 
                    If TypeOf currentStep Is SqlRenameStep Then
                        Dim renameStep As SqlRenameStep = DirectCast(currentStep, SqlRenameStep)
                        xmlw.WriteAttributeString("OriginalName", renameStep.OldName)
                        xmlw.WriteAttributeString("NewName", renameStep.NewName)
                        xmlw.WriteAttributeString("Category", GetElementCategory(renameStep.RenamedElement))
                    ElseIf TypeOf currentStep Is SqlMoveSchemaStep Then
                        Dim moveStep As SqlMoveSchemaStep = DirectCast(currentStep, SqlMoveSchemaStep)
                        xmlw.WriteAttributeString("OrignalName", moveStep.PreviousName)
                        xmlw.WriteAttributeString("NewSchema", moveStep.NewSchema)
                        xmlw.WriteAttributeString("Category", GetElementCategory(moveStep.MovedElement))
                    ElseIf TypeOf currentStep Is SqlTableMigrationStep Then
                        Dim dmStep As SqlTableMigrationStep = DirectCast(currentStep, SqlTableMigrationStep)
                        xmlw.WriteAttributeString("Name", GetElementName(dmStep.SourceTable))
                        xmlw.WriteAttributeString("Category", GetElementCategory(dmStep.SourceElement))
                    ElseIf TypeOf currentStep Is CreateElementStep Then
                        Dim createStep As CreateElementStep = DirectCast(currentStep, CreateElementStep)
                        xmlw.WriteAttributeString("Name", GetElementName(createStep.SourceElement))
                        xmlw.WriteAttributeString("Category", GetElementCategory(createStep.SourceElement))
                    ElseIf TypeOf currentStep Is AlterElementStep Then
                        Dim alterStep As AlterElementStep = DirectCast(currentStep, AlterElementStep)
                        xmlw.WriteAttributeString("Name", GetElementName(alterStep.SourceElement))
                        xmlw.WriteAttributeString("Category", GetElementCategory(alterStep.SourceElement))
                    ElseIf TypeOf currentStep Is DropElementStep Then
                        Dim dropStep As DropElementStep = DirectCast(currentStep, DropElementStep)
                        xmlw.WriteAttributeString("Name", GetElementName(dropStep.TargetElement))
                        xmlw.WriteAttributeString("Category", GetElementCategory(dropStep.TargetElement))
                    End If
    
                    ' If the script bodies are to be included, 
                    ' add them to the report. 
                    If Me.IncludeScripts Then
                        Dim tsqlBody As String = currentStep.Action()
                        If String.IsNullOrEmpty(tsqlBody) = False Then
                            xmlw.WriteCData(tsqlBody)
                        End If
                    End If
    
                    ' close off the current step 
                    xmlw.WriteEndElement()
                    currentStep = currentStep.[Next]
                End While
                xmlw.WriteEndElement()
    

    Types of interest include the following: DeploymentStep , SqlRenameStep , SqlMoveSchemaStep , SqlTableMigrationStep , CreateElementStep , AlterElementStep , and DropElementStep . The following step types are additional step types that are not shown in this example: BeginPostDeploymentScriptStep , BeginPreDeploymentScriptStep , DeploymentScriptDomStep , DeploymentScriptStep , EndPostDeploymentScriptStep , and EndPreDeploymentScriptStep . You could also look for the SQL Server-specific steps: SqlBeginAltersStep , SqlBeginDropsStep , SqlBeginPreservationStep , SqlBeginTransactionStep , SqlEndAltersStep , SqlEndDropsStep , SqlEndPreservationStep , SqlEndTransactionStep , SqlFinalizeDatabaseAccessStep , SqlMoveSchemaStep , SqlPrintStep , SqlRenameStep , and SqlTableMigrationStep .

    Next, you add the body of the GetElementCategory method.

To add the method body to the GetElementCategory method

  • Add the following code as the body of the GetElementCategory method:

                    return _sourceModel.DatabaseSchemaProvider.UserInteractionServices.GetElementTypeDescription(
                        element.ElementClass);
    

                Return _sourceModel.DatabaseSchemaProvider.UserInteractionServices.GetElementTypeDescription(element.ElementClass)
    

    Types and methods of interest include the following: DataSchemaModel , DatabaseSchemaProvider , UserInteractionServices , and GetElementTypeDescription .

    Next, you add the body of the GetElementName method.

To add the method body to the GetElementName method

  • Add the following code as the body of the GetElementName method:

                    return _sourceModel.DatabaseSchemaProvider.UserInteractionServices.GetElementName(
                        element, 
                        ElementNameStyle.FullyQualifiedName);
    

                Return _sourceModel.DatabaseSchemaProvider.UserInteractionServices.GetElementName(element, ElementNameStyle.FullyQualifiedName)
    

    Types and methods of interest include the following: DataSchemaModel , DatabaseSchemaProvider , UserInteractionServices , GetElementName , and ElementNameStyle .

    Save the changes to the class. Next, you build the class library.

To sign and build the assembly

  1. On the Project menu, click MyDeploymentContributor 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 MyDeploymentContributor assembly

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

  2. Copy your signed assembly (MyDeploymentContributor.dll) and the associated .pdb file (MyDeploymentContributor.pdb) 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 Premium.

    Next, you must register your assembly, which is a type of feature extension, so that it will appear in Visual Studio Premium.

To register the MyDeploymentContributor 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:

    "MyDeploymentContributor, 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="MyDeploymentContributor.DeploymentUpdateReportContributor" 
    assembly="MyDeploymentContributor, 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 MyDeploymentContributor.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 tasks:

  • Add a property to the .dbproj file that you plan to deploy

  • Deploy the database project by using MSBuild and providing the appropriate parameter

Add Properties to the Database Project (.dbproj) File

If you want to use this deployment contributor from MSBuild, you must modify the database project to enable users to pass a parameter through MSBuild. To update your database project, open it in the editor of your choice and add the following statements to the .dbproj file between the last </ItemGroup> node in the file and the final </Project> node:

  <ItemGroup>
    <DeploymentContributorArgument Include="GenerateUpdateReport=$(GenerateUpdateReport)" />
  </ItemGroup>

After you have updated the .dbproj file, you can use MSBuild to pass in the parameters for command-line builds.

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:Rebuild MyDatabaseProject.dbproj /p:OutDir=.\
    

    Replace MyDatabaseProject with the name of the database project that you want to build. If you had changed the project after you last built it, you could use /t:Build instead of /t:Rebuild.

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

    MSBuild /t:Deploy MyDatabaseProject.dbproj /p:GenerateUpdateReport=true
    

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

    Output such as the following appears:

Microsoft (R) Build Engine Version 4.0.20817.0
[Microsoft .NET Framework, Version 4.0.20817.0]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 8/26/2009 3:12:43 PM.
Project "C:\Users\UserName\Documents\Visual Studio 2010\Projects\MyDatabaseProject\Dep
TestToo\MyDatabaseProject.dbproj" on node 1 (Deploy target(s)).
DspDeploy:
  GenerateUpdateReport=true

  Deployment reports ->
  C:\Users\UserName\Documents\Visual Studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyTargetDatabase.summary.xml
  C:\Users\UserName\Documents\Visual Studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyTargetDatabase.details.xml

  Deployment script generated to:
  C:\Users\UserName\Documents\Visual Studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyDatabaseProject.sql

Done Building Project "C:\Users\UserName\Documents\Visual Studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\MyDatabaseProject.dbproj" (Deploy target(s)).


Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:04.02
  1. Open MyTargetDatabase.summary.xml and examine the contents.

    The file resembles the following example that shows a new database deployment:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<DeploymentReport>
  <Operations>
    <DeploymentScriptStep />
    <DeploymentScriptDomStep />
    <DeploymentScriptStep />
    <DeploymentScriptDomStep />
    <DeploymentScriptStep />
    <DeploymentScriptStep />
    <DeploymentScriptStep />
    <DeploymentScriptStep />
    <DeploymentScriptDomStep />
    <DeploymentScriptDomStep />
    <DeploymentScriptDomStep />
    <DeploymentScriptDomStep />
    <DeploymentScriptStep />
    <DeploymentScriptDomStep />
    <BeginPreDeploymentScriptStep />
    <DeploymentScriptStep />
    <EndPreDeploymentScriptStep />
    <SqlBeginPreservationStep />
    <SqlEndPreservationStep />
    <SqlBeginDropsStep />
    <SqlEndDropsStep />
    <SqlBeginAltersStep />
    <SqlPrintStep />
    <CreateElementStep Name="Sales" Category="Schema" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.Customer" Category="Table" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.PK_Customer_CustID" Category="Primary Key" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.Orders" Category="Table" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.PK_Orders_OrderID" Category="Primary Key" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.Def_Customer_YTDOrders" Category="Default Constraint" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.Def_Customer_YTDSales" Category="Default Constraint" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.Def_Orders_OrderDate" Category="Default Constraint" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.Def_Orders_Status" Category="Default Constraint" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.FK_Orders_Customer_CustID" Category="Foreign Key" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.CK_Orders_FilledDate" Category="Check Constraint" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.CK_Orders_OrderDate" Category="Check Constraint" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.uspCancelOrder" Category="Procedure" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.uspFillOrder" Category="Procedure" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.uspNewCustomer" Category="Procedure" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.uspPlaceNewOrder" Category="Procedure" />
    <SqlPrintStep />
    <CreateElementStep Name="Sales.uspShowOrderDetails" Category="Procedure" />
    <SqlEndAltersStep />
    <DeploymentScriptStep />
    <BeginPostDeploymentScriptStep />
    <DeploymentScriptStep />
    <EndPostDeploymentScriptStep />
    <DeploymentScriptDomStep />
    <DeploymentScriptDomStep />
    <DeploymentScriptDomStep />
  </Operations>
</DeploymentReport>
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.

  1. Open MyTargetDatabase.details.xml and examine the contents.

    A small section of the details file shows the entries and script that create the Sales schema, that print a message about creating a table, and that create the table:

    <CreateElementStep Name="Sales" Category="Schema"><![CDATA[CREATE SCHEMA [Sales]
    AUTHORIZATION [dbo];

]]></CreateElementStep>
    <SqlPrintStep><![CDATA[PRINT N'Creating [Sales].[Customer]...';

]]></SqlPrintStep>
    <CreateElementStep Name="Sales.Customer" Category="Table"><![CDATA[CREATE TABLE [Sales].[Customer] (
    [CustomerID]   INT           IDENTITY (1, 1) NOT NULL,
    [CustomerName] NVARCHAR (40) NOT NULL,
    [YTDOrders]    INT           NOT NULL,
    [YTDSales]     INT           NOT NULL
);

]]></CreateElementStep>

By analyzing the deployment plan as it is executed, you can report on any information that is contained in the deployment and can take additional actions based on the steps in that plan.

You could create additional tools to perform processing of the output XML files. This is just one example of a DeploymentPlanExecutor. You could also create a DeploymentPlanModifier to change a deployment plan before it is executed.

Community Additions

ADD
Show:
© 2014 Microsoft