Walkthrough: Modify Database Projects by using the Visual Studio Automation Model

Article
06/26/2007

You can programmatically modify your database projects by using the extensibility support in Visual Studio. The database projects in Visual Studio Team Edition for Database Professionals support the Visual Studio automation model (also known as design-time extensibility or DTE) in a manner that is consistent with Visual C# and Visual Basic projects. For more information about this model, see Extending the Visual Studio Environment. In this walkthrough, you create Visual Studio macros that use the automation model to accomplish two tasks:

Toggle the build action of all the triggers in a database project. If the triggers are set to "Build," the macro changes them to "Not In Build." If the triggers are "Not In Build," the macro changes them to "Build."
Add all the script files in a directory to a folder in a database project. If the folder does not exist, it is created. Only script files that have specific extensions are added.

You could also perform these tasks in a Visual Studio add-in that is written in Visual C# or Visual Basic. For simplicity, this walkthrough uses macros.

In the following procedures, you will:

Create a database project that is organized by object type, and import the AdventureWorks database schema.
Launch Macro Explorer and create modules to contain the macros and supporting code.
Create a macro to toggle the build action for all triggers in a database project in the open solution.
Create a macro and supporting code to add scripts to your database project.
Run the ToggleTriggers macro from the Command Window.
Run the AddScriptsInDirectory macro from Macro Explorer.

Prerequisites

To complete this walkthrough, you must have installed Microsoft Visual Studio Team Edition for Database Professionals. This walkthrough assumes that you have installed a copy of the AdventureWorks sample database on a database server that is running Microsoft SQL Server 2005. You can substitute any other database project that is organized by object type. You must have one or more files that have the .sql extension in a directory to which you have access.

To create a database project

Start Visual Studio if you have not already done so.
On the File menu, point to New, and then click Project.

The New Project dialog box appears.
In the Project types list, expand the Database Projects node, and click Microsoft SQL Server.
In the Templates list, click SQL Server 2005.
In Name, type MyAdvWorks, and accept the default values for Location and Solution Name.
Select the Create directory for solution check box if it is not selected by default, and click OK.

A solution is created that contains the MyAdvWorks database project, which is empty.

Next you will start the Import Database Schema process, in which you specify a connection string to the source database.

To import the database schema from the existing AdventureWorks database

On the View menu, click Schema View.

Schema View appears if it was not already visible.
Click MyAdvWorks in Schema View.
On the Project menu, click Import Database Schema.

Note

You can also right-click MyAdvWorks and click Import Database Schema.

The Import Database Wizard appears.
In the Source database connection list, click the connection that corresponds to your existing AdventureWorks database. If you have not yet connected to that database, you must first create a connection to it. For more information, see How to: Create a Database Connection.
Click Finish.

As the schema is imported, project items that correspond to the objects that are in the database appear under the database project in Solution Explorer. Schema View shows the objects that are defined in the database project.

To start Macro Explorer and create modules

On the View menu, point to Other Windows, and click Macro Explorer.

Macro Explorer appears.
In Macro Explorer, right-click the MyMacros node, and click New module.

The Add Module dialog box appears.
In Name, type BuildActionExample.
Click Add.
In Macro Explorer, right-click the MyMacros node, and click New module.

The Add Module dialog box appears.
In Name, type ImportScriptsExample.
Click Add.

Next you create a macro to toggle the build action of all Data Manipulation Language (DML) triggers in a specified database.

Create the ToggleTriggers Macro

The ToggleTriggers macro takes one optional parameter, which is the name of the database project that contains the triggers to update. If you do not specify a project name, the macro will prompt for one. You could modify the macro to instead identify the type of each project in the solution and update all database projects. However, that approach is outside the scope of this walkthrough.

To create the ToggleTriggers macro

In Macro Explorer, right-click the BuildActionExample module, and click Edit.

The Microsoft Visual Studio Macros window appears. This window shows the contents of your BuildActionExample module.

Replace the contents of the module with the following VBScript code:

Imports System
Imports System.ComponentModel
Imports EnvDTE
Imports EnvDTE80
Imports System.Diagnostics

Public Module BuildActionExample

    ' Macro to toggle the BuildAction for all DML triggers
    ' in a database project.
    ' Before running this macro, you must:
    ' 1) Ensure that a solution file is open and saved to disk.
    ' 2) Pass in the name of a database project contained in the
    '    open solution in the dbProjName parameter. 
    Sub ToggleTriggers(Optional ByVal dbProjName As String = "")
        Dim project As Project

        ' if the database project name was not passed in, prompt the user for it.
        If (String.IsNullOrEmpty(dbProjName)) Then
            dbProjName = InputBox("Type the database project name.")
            If (String.IsNullOrEmpty(dbProjName)) Then
                Return
            End If
        End If

        ' Loop through each project until we find the one we want
        For Each project In DTE.Solution
            Dim projectItem As EnvDTE.ProjectItem
            'Look for a project whose name matches the parameter
            If (dbProjName.Equals(project.Name)) Then
                'Then loop through the project items, looking for
                'the Schema Objects folder.
                For Each projectItem In project.ProjectItems()
                    If (projectItem.Name = "Schema Objects") Then
                        ' loop through the subfolders and list the files, looking for the Tables sub-folder
                        Dim subItem As EnvDTE.ProjectItem
                        For Each subItem In projectItem.ProjectItems()
                            If (subItem.Name = "Tables") Then
                                ' loop through looking for the Triggers subfolder
                                Dim subsubItem As EnvDTE.ProjectItem
                                For Each subsubItem In subItem.ProjectItems()
                                    If (subsubItem.Name = "Triggers") Then
                                        ' okay, we're in the right folder, now set the build actions
                                        Dim triggerItem As EnvDTE.ProjectItem
                                        For Each triggerItem In subsubItem.ProjectItems()
                                            'MsgBox(" trigger: " + triggerItem.Name)
                                            Dim buildAction As EnvDTE.Property
                                            buildAction = triggerItem.Properties.Item("DBProjectBuildAction")

                                            ' here we toggle the build action. If it was NotInBuild(0),
                                            ' we set it to Build(1). If it was Build(1), then we set it
                                            ' to NotInBuild(0).
                                            If (buildAction.Value = 0) Then
                                                buildAction.Value = 1
                                            ElseIf (buildAction.Value = 1) Then
                                                buildAction.Value = 0
                                            End If
                                        Next
                                    End If
                                Next
                            End If
                        Next
                    End If
                Next
            End If
        Next
    End Sub
End Module

The macro iterates through the contents of the solution until it finds the database project whose name matches the name that you specified. After the macro identifies that project, it iterates over the project items, looking for the Solution Items folder. In the Solution Items folder, the macro looks for the Tables folder and within that, the macro looks for the Triggers folder. The macro then retrieves the value of the DBProjectBuildAction property for each trigger. If the value is 1 (Build), it is toggled to 0 (Not In Build). Similarly, if the value is 0, it is toggled to 1. Although the name of the property in the Properties window is Build Action, the underlying property name is DBProjectBuildAction.

In the macros window, open the File menu, and click Save MyMacros.

Next you create the macro that adds script files in a directory to the specified database project.

Create the AddScriptsInDirectory Macro

The AddScriptsInDirectory macro takes three parameters: the name of the database project to which you want to add the script files, the name of the folder in the database project where you want to add the scripts, and the path that contains the script files that the macro will import. If you do not specify these parameters when you run the macro, it will prompt you for them. If you do not specify the name of a project folder in response to the prompt, the files will be added to the Scripts folder.

This macro is more complex than the previous one. For simplicity, you build the AddScriptsInDirectory macro by creating the following two functions and two subroutines:

Function IsFileIncluded This function verifies whether the extension of a specified file name is in the list of extensions for files that should be treated as scripts and added to the database project.
Function GetOutputWindowPane This function returns the output window so that progress messages can be reported.
Sub AddScriptsInDirectory2 This subroutine takes a project folder and a path and adds all files whose extensions match the list of script extensions.
Sub AddScriptsInDirectory The entry routine for this macro, this subroutine processes the parameters that are passed to it, performs validation, and either creates the destination folder or finds it in the database project if the folder already exists. The subroutine then calls AddScriptsInDirectory2 to add files to that folder.

To create the AddScriptsInDirectory macro

In Macro Explorer, right-click the ImportScriptsExample module, and click Edit.

The Microsoft Visual Studio Macros window appears. This window shows the contents of your ImportScriptsExample module.

Replace the contents of the module with the following VBScript code:

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports System.Diagnostics

Public Module ImportScriptsExample
    ' A list of folder names, file names, and extensions that we want to add
    '  to the solution.
    Dim outputWindowPaneTitle As String = "Add scripts to a project folder report"
    Dim includedExtensions As New System.Collections.Specialized.StringCollection

    ' Function to filter out folder names, file names, and extensions that we do not 
    '  want to add to the solution.
    Function IsFileIncluded(ByVal filePath As String) As Boolean
        Dim extension As String
        Dim fileName As String

        extension = System.IO.Path.GetExtension(filePath)
        extension = extension.ToLower()

        fileName = System.IO.Path.GetFileName(filePath)
        fileName = fileName.ToLower()

        If (includedExtensions.Contains(extension)) Then
            Return True
        Else
            If (includedExtensions.Contains(fileName)) Then
                Return True
            Else
                Return False
            End If
        End If
    End Function

    ' This function retrieves the output window pane
    Function GetOutputWindowPane(ByVal Name As String, Optional ByVal show As Boolean = True) As OutputWindowPane
        Dim window As Window
        Dim outputWindow As OutputWindow
        Dim outputWindowPane As OutputWindowPane

        window = DTE.Windows.Item(EnvDTE.Constants.vsWindowKindOutput)
        If show Then window.Visible = True
        outputWindow = window.Object
        Try
            outputWindowPane = outputWindow.OutputWindowPanes.Item(Name)
        Catch e As System.Exception
            outputWindowPane = outputWindow.OutputWindowPanes.Add(Name)
        End Try
        outputWindowPane.Activate()
        Return outputWindowPane
    End Function

    ' Given a folder within the solution and a folder on disk, add all files whose extensions
    ' are on a list of "good" extensions to the folder in the solution.
    Sub AddScriptsInDirectory2(ByVal newScriptFolder As ProjectItem, ByVal startFolder As String)
        Dim files As String()
        Dim file As String
        Dim folder As String

        ' get a list of files in the specified folder
        files = System.IO.Directory.GetFiles(startFolder)

        ' get the output window pane so we can report status
        Dim outputWindowPane As EnvDTE.OutputWindowPane
        outputWindowPane = GetOutputWindowPane(outputWindowPaneTitle, True)

        ' Examine all the files within the folder.
        For Each file In files
            ' if this file's extension is one we want to include...
            If (IsFileIncluded(file)) Then
                ' try to add it to the folder
                Dim projItem As ProjectItem
                Try
                    projItem = newScriptFolder.ProjectItems().AddFromFile(file)
                    outputWindowPane.OutputString("The item """ + file + """ was added" + vbLf)

                    If (Not (projItem Is Nothing)) Then
                        If (Not (projItem.Document Is Nothing)) Then
                            projItem.Document.Close(vsSaveChanges.vsSaveChangesNo)
                        End If
                    End If
                Catch
                    ' if an error occurs, report the failure
                    outputWindowPane.OutputString("The item """ + file + """may have not been added to the solution." + vbLf)
                End Try
            End If
        Next
    End Sub

    ' creates a new subfolder within the Scripts folder in the specified database project
    ' then adds all files in the specified path to the newly created scripts sub-folder.
    Sub AddScriptsInDirectory(Optional ByVal dbProjName As String = "", Optional ByVal scriptFolderName As String = "", Optional ByVal startFolder As String = "")
        If (String.IsNullOrEmpty(dbProjName)) Then
            dbProjName = InputBox("Type the name of the database project to which you want the scripts to be imported.")
            If (String.IsNullOrEmpty(dbProjName)) Then
                Return
            End If
        End If

        If (String.IsNullOrEmpty(scriptFolderName)) Then
            scriptFolderName = InputBox("Type the script folder name.")
            If (String.IsNullOrEmpty(scriptFolderName)) Then
                scriptFolderName = "Scripts"
            End If
        End If

        If (String.IsNullOrEmpty(startFolder)) Then
            startFolder = InputBox("Type the folder path to import.")
            If (String.IsNullOrEmpty(startFolder)) Then
                Return
            End If
        End If

        If (System.IO.Directory.Exists(startFolder) = False) Then
            MsgBox("The specified folder could not be found.")
            Return
        End If

        GetOutputWindowPane(outputWindowPaneTitle, True).Clear()

        If System.IO.Directory.Exists(startFolder) = False Then
            Dim outputWindowPane As EnvDTE.OutputWindowPane
            outputWindowPane = GetOutputWindowPane(outputWindowPaneTitle, True)
            outputWindowPane.OutputString("The path entered could not be found" + vbLf)
            Exit Sub
        End If

        includedExtensions = New System.Collections.Specialized.StringCollection
        ' If you do not want a file with a particular extension or name
        '  to be added, then add that extension or name to this list:
        includedExtensions.Add(".sql")
        includedExtensions.Add(".tsql")

        Dim newScriptFolder As ProjectItem
        Dim project As Project

        ' now check to see if the desired folder in the project already exists
        For Each project In DTE.Solution
            Dim projectItem As EnvDTE.ProjectItem

            If (dbProjName.Equals(project.Name)) Then
                Dim found As Boolean
                found = False
                For Each projectItem In project.ProjectItems()
                    If (scriptFolderName.Equals(projectItem.Name)) Then
                        ' the desired folder already exists, save the projectItem that corresponds
                        ' to the folder.
                        found = True
                        newScriptFolder = projectItem
                    End If
                Next

                ' if the folder does not exist within the project, create it.
                If (Not found) Then
                    ' the folder does not already exist, so create it
                    newScriptFolder = project.ProjectItems().AddFolder(scriptFolderName, EnvDTE.Constants.vsProjectItemKindPhysicalFolder)
                End If
            End If
        Next

        ' now add the scripts in the folder to the project folder
        AddScriptsInDirectory2(newScriptFolder, startFolder)
    End Sub
End Module

In the macros window, open the File menu, and click Save MyMacros.
On the File menu, click Close and Return.

Next you will run your macros to demonstrate the results.

Run the ToggleTriggers Macro

If you run the macros with any solution other than the one that you created in this walkthrough, you must specify the name of the database project that is contained in your solution instead of MyAdvWorks.

To run the ToggleTriggers macro from the Command window

In Solution Explorer, expand the MyAdvWorks database project.
Expand the Schema Objects folder.
Expand the Tables folder.
Expand the Triggers folder.
In Solution Explorer, right-click any trigger, and click Properties.

Note value of the Build Action property for the trigger that you chose.
On the View menu, point to Other Windows, and click Command Window.

The Command Window appears.
In the Command window, type the following:
```
Macros.MyMacros.BuildActionExample.ToggleTriggers MyAdvWorks
```
MyAdvWorks is the name of the database project that will have the Build Action property of its triggers toggled.
Wait for the macro to finish.
When the macro finishes running, view the properties for the trigger from step 5.

The value of the Build Action property is the opposite of what it was before you ran the macro.

You can run the macro again to restore the values of the Build Action property to their original states.

Next you run the AddScriptsInDirectory macro from Macro Explorer.

Run the AddScriptsInDirectory Macro

To run the AddScriptsInDirectory macro from Macro Explorer

If Macro Explorer is not open, open the View menu, point to Other Windows, and click Macro Explorer.

Macro Explorer appears.
In Macro Explorer, right-click AddScriptsInDirectory (you might have to expand the ImportScriptsExample module to display the macro), and click Run.

The Visual Studio Macros dialog box appears, prompting you to "Type the name of the database project to which you want the scripts to be imported."
Type MyAdvWorks, and click OK.

The Visual Studio Macros dialog box appears again, prompting you to "Type the script folder name."
Click OK to accept the default behavior, which will add the scripts to the Scripts folder.

The Visual Studio Macros dialog box appears again, prompting you to "Type the folder path to import."
Type the path where you have script files as noted in the Prerequisites section earlier in this topic. For example, if you have scripts in C:\Temp, type C:\Temp, and then click OK.

The macro will run until any files that have the .sql or .tsql extension are added to the Scripts folder of the MyAdvWorks database project.

Next Steps

This walkthrough illustrates small samples of the tasks that you can perform when you use the Visual Studio automation model with your database projects. If you need more flexibility, you can use the automation model from a Visual Studio add-in.