This documentation is archived and is not being maintained.

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

Visual Studio 2005

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.

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

  1. Start Visual Studio if you have not already done so.

  2. On the File menu, point to New, and then click Project.

    The New Project dialog box appears.

  3. In the Project types list, expand the Database Projects node, and click Microsoft SQL Server.

  4. In the Templates list, click SQL Server 2005.

  5. In Name, type MyAdvWorks, and accept the default values for Location and Solution Name.

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

  1. On the View menu, click Schema View.

    Schema View appears if it was not already visible.

  2. Click MyAdvWorks in Schema View.

  3. On the Project menu, click Import Database Schema.

    NoteNote

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

    The Import Database Wizard appears.

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

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

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

    Macro Explorer appears.

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

    The Add Module dialog box appears.

  3. In Name, type BuildActionExample.

  4. Click Add.

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

    The Add Module dialog box appears.

  6. In Name, type ImportScriptsExample.

  7. Click Add.

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

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

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

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

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

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

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

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

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

  4. On the File menu, click Close and Return.

    Next you will run your macros to demonstrate the results.

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

  1. In Solution Explorer, expand the MyAdvWorks database project.

  2. Expand the Schema Objects folder.

  3. Expand the Tables folder.

  4. Expand the Triggers folder.

  5. In Solution Explorer, right-click any trigger, and click Properties.

    Note value of the Build Action property for the trigger that you chose.

  6. On the View menu, point to Other Windows, and click Command Window.

    The Command Window appears.

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

  8. Wait for the macro to finish.

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

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 AddScriptsInDirectory macro from Macro Explorer

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

    Macro Explorer appears.

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

  3. Type MyAdvWorks, and click OK.

    The Visual Studio Macros dialog box appears again, prompting you to "Type the script folder name."

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

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

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.

Show: