逐步解說：擴充資料庫專案部署以修改部署計劃

發行項
08/15/2011

您可以建立部署參與者，以在部署資料庫專案時執行自訂動作。您可以建立 DeploymentPlanModifier 或 DeploymentPlanExecutor。 DeploymentPlanModifier 可用於在執行計劃之前變更計劃，DeploymentPlanExecutor 則可用於在正在執行計劃時執行作業。在這個逐步解說中，您會建立名為 SqlRestartableScriptContributor 的 DeploymentPlanModifier，它會將 IF 陳述式加入至部署指令碼中的批次，讓指令碼在遇到錯誤時能夠重新執行到完成為止。

在這個逐步解說中，您將完成下列主要工作：

建立 DeploymentPlanModifier 型別的部署參與者
安裝部署參與者
測試部署參與者

必要條件

您需要下列元件才能完成此逐步解說：

電腦上已安裝 Visual Studio 2010 Premium 或 Visual Studio 2010 Ultimate。
包含資料庫物件的資料庫專案
可以對其部署資料庫專案的 SQL Server 執行個體

注意事項
此逐步解說適合已經熟悉 Visual Studio 資料庫功能的使用者使用。您也必須熟悉基本的 Visual Studio 概念，例如如何建立類別庫，以及如何使用程式碼編輯器，將程式碼加入至類別。

建立部署參與者

若要建立部署參與者，您必須執行下列工作：

建立類別庫專案並加入必要的參考
定義名為 SqlRestartableScriptContributor 的類別，該類別繼承自 DeploymentPlanModifier
覆寫 OnExecute 方法
加入私用 Helper 方法
建置產生的組件

若要建立類別庫專案

建立名為 MyOtherDeploymentContributor 的 Visual C# 或 Visual Basic 類別庫專案。
在 [方案總管] 中，以滑鼠右鍵按一下專案，然後按一下 [加入參考]。
按一下 [.NET] 索引標籤。
選取 [Microsoft.Data.Schema]、[Microsoft.Data.Schema.Sql]、[Microsoft.Data.Schema.ScriptDom] 和 [Microsoft.Data.Schema.ScriptDom.Sql] 項目，然後按一下 [確定]。

接下來，開始將程式碼加入至類別。

若要定義 SqlRestartableScriptContributor 類別

在程式碼編輯器中，更新 class1.cs 檔，使其符合下列 using 陳述式：

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

更新類別定義使其符合下列範例：

        /// <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

您現在已定義繼承自 DeploymentPlanModifier 的建置參與者。您已使用 DatabaseSchemaProviderCompatibilityAttribute 屬性表示這個參與者與所有繼承自 SqlDatabaseSchemaProvider 的資料庫結構描述提供者都相容。

加入下列成員宣告：

        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 & "]"

接下來，您會覆寫 OnExecute 方法，以便加入要在部署資料庫專案時執行的程式碼。

若要覆寫 OnExecute

將下列方法加入至 SqlRestartableScriptContributor 類別：

        /// <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

您會覆寫來自基底類別 DeploymentPlanContributor 的 OnExecute 方法，這個基底類別是 DeploymentPlanModifier 和 DeploymentPlanExecutor 的基底類別。 OnExecute 方法接受 DeploymentPlanContributorContext 物件，這個物件可用來存取任何指定的引數、來源和目標資料庫模型、建置屬性和擴充檔。在這個範例中，我們會取得部署計劃和目標資料庫名稱。

現在，將主體的開頭加入至 OnExecute 方法：

        // 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)

在這個程式碼中，我們會定義幾個區域變數，並且設定迴圈來處理部署計劃中的所有步驟。在迴圈完成之後，我們必須執行一些後置處理動作，然後捨棄我們在部署期間為了追蹤計劃進度而建立的暫存資料表。這裡的關鍵型別是：DeploymentStep 和 DeploymentScriptStep。關鍵的方法是 AddAfter。

現在，加入其他步驟處理，以取代顯示 "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

程式碼註解會說明所做的處理。概略而言，這個程式碼會尋找您在意的步驟，而略過其他步驟，並在您到達後置部署步驟的開頭時停止。如果步驟包含必須以條件式包住的陳述式，我們就會執行額外的處理。關鍵的型別、方法和屬性包括：BeginPreDeploymentScriptStep、BeginPostDeploymentScriptStep、IModelElement、TSqlScript、Script、DeploymentScriptDomStep 和 SqlPrintStep。

現在，加入批次處理程式碼，以取代顯示 "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

這個程式碼會同時建立 BEGIN/END 區塊和 IF 陳述式。然後我們會對批次中的陳述式執行額外的處理。完成這個動作之後，我們就會加入 INSERT 陳述式，將資訊插入至用於追蹤指令碼執行狀況的暫存資料表。最後，使用包含這些陳述式的新 IF 陳述式，取代原有的陳述式，藉以更新批次。

關鍵的型別、方法和屬性包括：IfStatement、BeginEndBlockStatement、StatementList、TSqlBatch、PredicateSetStatement、SetOptions 和 InsertStatement。

現在，加入用於處理迴圈的陳述式的主體。取代顯示 "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)

批次中如果有陳述式屬於必須以 sp_executesql 陳述式包住的型別，請適當修改陳述式。然後程式碼就會針對您剛建立的 BEGIN/END 區塊，將陳述式加入至陳述式清單。關鍵的型別、方法和屬性包括：TSqlStatement 和 ExecuteStatement。

最後，加入後置處理區段，以取代顯示 "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)

如果處理過程中發現一個或多個以條件陳述式包住的步驟，我們必須將陳述式加入至部署指令碼以定義 SQLCMD 變數。第一個變數 CompletedBatches 包含暫存資料表的唯一名稱，部署指令碼會使用這個資料表來追蹤當指令碼執行時，有哪些批次已成功完成。第二個變數 TotalBatchCount 包含部署指令碼中的批次總數。

其他要注意的型別、屬性和方法包括：

StringBuilder、DeploymentScriptStep 和 AddBefore。

接下來，您會定義這個方法呼叫的 Helper 方法。

若要加入 CreateExecuteSql 方法

加入下列程式碼，將 CreateExecuteSQL 方法定義成以 EXEC sp_executesql 陳述式包住所提供的陳述式：

        /// <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

關鍵的型別、方法和屬性包括：ExecuteStatement、ExecutableProcedureReference、SchemaObjectName、ProcedureReference 和 ExecuteParameter。

若要加入 CreateLiteral 方法。

加入下列程式碼以定義 CreateLiteral 方法。這個方法會從提供的字串建立所指定型別的 Literal 物件。

        /// <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

關鍵的型別、方法和屬性包括：Literal 和 LiteralType。

若要加入 CreateIdentifier 方法

加入下列程式碼以定義 CreateIdentifier 方法。這個方法會建立 Identifier 物件，該物件使用來自提供字串的指定引用型別。

        /// <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

關鍵的型別、方法和屬性包括：Identifier 和 QuoteType。

若要加入 CreateCompletedBatchesName 方法

加入下列程式碼以定義 CreateCompletedBatchesName 方法。這個方法會建立要插入至暫存資料表中的批次名稱。

        /// <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

關鍵的型別、方法和屬性包括：SchemaObjectName。

若要加入 IsStatementEscaped 方法

加入下列程式碼以定義 IsStatementEscaped 方法。這個方法會判斷模型項目的型別是否要求陳述式必須先以 EXEC sp_executesql 陳述式包住，然後才包在 IF 陳述式中。

        /// <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

關鍵的型別、方法和屬性包括：IModelElement、ISql90Schema、ISqlProcedure、ISqlView、ISqlFunction 和 ISqlTrigger。

若要加入 CreateBatchCompleteInsert 方法

加入下列程式碼以定義 CreateBatchCompleteInsert 方法。這個方法會建立 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 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

關鍵的型別、方法和屬性包括：InsertStatement、SchemaObjectDataModificationTarget、Column、ValuesInsertSource 和 RowValue。

若要加入 CreateIfNotExecutedStatement 方法

加入下列程式碼以定義 CreateIfNotExecutedStatement 方法。這個方法會產生 IF 陳述式，該陳述式會檢查暫存批次執行資料表是否表示該批次已執行：

        /// <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

關鍵的型別、方法和屬性包括：IfStatement、ExistsPredicate、Subquery、SchemaObjectTableSource、WhereClause、Column、Literal、BinaryExpression 和 UnaryExpression。

若要加入 GetStepInfo 方法

加入下列程式碼以定義 GetStepInfo 方法。這個方法可以：

        /// <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

要注意的型別和方法包括：DeploymentPlanContributorContext、DeploymentScriptDomStep、IModelElement、CreateElementStep、AlterElementStep 和 DropElementStep。

將這些變更儲存至 Class1.cs。

接下來，您會建置類別庫。

若要簽署和建置組件

按一下 [專案] 功能表上的 [MyOtherDeploymentContributor 屬性]。
按一下 [簽署] 索引標籤。
按一下 [簽署組件]。
在 [選擇強式名稱金鑰檔] 中，按一下 [<新增>]。
在 [建立強式名稱金鑰] 對話方塊的 [金鑰檔名稱] 中，輸入 MyRefKey。
(選擇性) 您可以為強式名稱金鑰檔指定密碼。
按一下 [確定]。
在 [檔案] 功能表上按一下 [全部儲存]。
在 [建置] 功能表上，按一下 [建置方案]。

接下來，您必須安裝並註冊組件，以便在部署資料庫專案時可以載入它。

安裝部署參與者

若要安裝部署參與者，您必須執行下列工作：

將組件和相關聯的 .pdb 檔案複製到 Extensions 資料夾
建立 Extensions.xml 檔來註冊部署參與者，以在您部署資料庫專案時載入該部署參與者

若要安裝 MyOtherDeploymentContributor 組件

在 %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions 資料夾中建立名為 MyExtensions 的資料夾。

將已簽署的組件 (MyOtherDeploymentContributor.dll) 複製到 %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\MyExtensions 資料夾。

注意事項
建議您不要直接將 XML 檔案複製到 %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions 資料夾中。如果改用子資料夾，可以防止不小心變更 Visual Studio 隨附的其他檔案。

接下來，您必須註冊組件 (一種「擴充功能」(Feature Extension) 類型)，以便讓它出現在 Visual Studio 中。

若要註冊 MyOtherDeploymentContributor 組件

按一下 [檢視] 功能表上的 [其他視窗]，然後按一下 [命令視窗] 開啟 [命令視窗]。
在 [命令] 視窗中輸入下列程式碼。將 FilePath 替代為已編譯之 .dll 檔案的路徑和檔案名稱。請在路徑和檔案名稱周圍加上引號。

注意事項

根據預設，已編譯之 .dll 檔案的路徑為 <您的方案路徑>\bin\Debug 或 <您的方案路徑>\bin\Release。
```
? System.Reflection.Assembly.LoadFrom(@"FilePath").FullName
```
```
? System.Reflection.Assembly.LoadFrom("FilePath").FullName
```
按 Enter。
將產生的程式碼行複製到剪貼簿中。此程式碼行應該與下列程式碼相似：
```
"GeneratorAssembly, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn"
```
開啟純文字編輯器，如 [記事本]。

重要事項

在 Windows Vista 和 Microsoft Windows Server 2008 上，以系統管理員身分開啟編輯器，以便將檔案儲存至 Program Files 資料夾。

注意事項
根據預設，已編譯之 .dll 檔案的路徑為 <您的方案路徑>\bin\Debug 或 <您的方案路徑>\bin\Release。

重要事項
在 Windows Vista 和 Microsoft Windows Server 2008 上，以系統管理員身分開啟編輯器，以便將檔案儲存至 Program Files 資料夾。

提供下列資訊，並指定您自己的組件名稱、公開金鑰語彙基元和副檔名類型：

<?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>

您可使用此 XML 檔案註冊繼承自 DeploymentPlanExecutor 的類別。

將此檔案另存為 %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\MyExtensions 資料夾中的 MyOtherDeploymentContributor.extensions.xml。
關閉 Visual Studio。

接下來，您會部署資料庫專案以測試參與者。

測試部署參與者

若要測試部署參與者，您必須執行下列工作：

使用 MSBuild 並提供適當參數來部署資料庫專案

因為這個部署參與者永遠都是啟用狀態，所以您不需要修改資料庫專案來加入任何屬性。

部署資料庫專案

若要部署您的資料庫專案並產生部署報告

開啟 Visual Studio 命令提示字元。依序按一下 [開始] 功能表、[所有程式]、[Microsoft Visual Studio 2010]、[Visual Studio Tools]，然後按一下 [Visual Studio 命令提示字元 (2010)]。
在命令提示字元中，巡覽至包含資料庫專案的資料夾。
在命令提示字元中，輸入下列命令列：
```
MSBuild /t:Deploy MyDatabaseProject.dbproj
```
以您要部署的資料庫專案名稱取代 MyDatabaseProject。

檢查產生的部署指令碼。就在標示 "Pre-Deployment Script Template" 的區段前，您應該會看見類似如下的 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

在部署指令碼較後段的部分，您會在每個批次外面看見包住原始陳述式的 IF 陳述式。例如，CREATE SCHEMA 陳述式可能會變成像下面這樣：

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

請注意，CREATE SCHEMA 是 IF 陳述式內必須以 EXECUTE sp_executesql 陳述式包住的陳述式之一。 CREATE TABLE 之類的陳述式則不需要 EXECUTE sp_executesql，因此看起來會如下列範例所示：

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

注意事項
如果您部署的資料庫專案與目標資料庫完全相同，產生的報告就沒有什麼意義。如需更有意義的結果，請對資料庫部署變更或是部署新的資料庫。

您可以使用 DeploymentPlanModifier，在任何部署計劃中加入、移除或修改批次/陳述式。

後續步驟

在執行其他類型修改前，您可以先試驗是否可對部署計劃進行那些修改。您可能需要進行的其他類型修改包括：將擴充屬性加入至所有資料庫物件 (該物件具有相關聯的版本號碼)、在部署指令碼中加入或移除其他的診斷列印陳述式/註解等等。

請參閱

概念

擴充 Visual Studio 的資料庫功能

其他資源

使用建置和部署參與者自訂資料庫建置和部署

逐步解說：擴充資料庫專案組建以產生模型統計資料

逐步解說：擴充資料庫專案部署以分析部署計劃

共用方式為

逐步解說：擴充資料庫專案部署以修改部署計劃

必要條件

建立部署參與者

若要建立類別庫專案

若要定義 SqlRestartableScriptContributor 類別

若要覆寫 OnExecute

若要加入 CreateExecuteSql 方法

若要加入 CreateLiteral 方法。

若要加入 CreateIdentifier 方法

若要加入 CreateCompletedBatchesName 方法

若要加入 IsStatementEscaped 方法

若要加入 CreateBatchCompleteInsert 方法

若要加入 CreateIfNotExecutedStatement 方法

若要加入 GetStepInfo 方法

若要簽署和建置組件

安裝部署參與者

若要安裝 MyOtherDeploymentContributor 組件

若要註冊 MyOtherDeploymentContributor 組件

測試部署參與者

部署資料庫專案

若要部署您的資料庫專案並產生部署報告

後續步驟

請參閱

概念

其他資源

其他資源