VS2010 Macro/Add In for “Run” and “Run On” command

问题:

Much to annoyance of many developers Microsoft have removed the "Run" and "Run On" commands in Visual Studio 2010:

More details at: http://social.msdn.microsoft.com/Forums/en/vstsdb/thread/f374c604-a7eb-496d-a261-9374790cdbf9

Has anyone seen or written a VS2010 Macro or Extension that fills the gaps and replicates this functionality?

回答1:

OK so I gave up and wrote my own macro, apologies in advance my VB.Net knowledge was close to non-existent when I started. There were also a few classes I hadn't used before so apologies if there are some uses which are not correct or could be improved.

Once you've set up the context menus in Visual Studio you should just able to right click on a script/scripts/folder and select "Run On..." and the scripts will all be run on the selected database. There are instructions for setting up the macro and creating the context menus at the bottom of this post.

Feel free to post any corrections and I'll try and update my post.

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports EnvDTE90a
Imports EnvDTE100
Imports System.IO
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Generic

Imports System.Windows.Forms

Public Module ContextMenu

    ' Copy the following files from: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\
    ' to: C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PublicAssemblies
    '
    ' Microsoft.SqlServer.Smo.dll
    ' Microsoft.SqlServer.ConnectionInfo.dll
    ' Microsoft.SqlServerManagement.Sdk.Sfc.dll
    '
    ' Add a reference to them in your Macros project
    '
    ' Add a references to
    '   System.Data.dll
    '   System.Drawing.dll

    Dim WithEvents _connection As SqlConnection
    Dim _lastError As String
    Dim _fileNames As New Collection
    Dim _errorCount As Int16
    Dim _databaseOutputPane As OutputWindowPane

    Public Sub RunOnServer()
        Dim serverName As String
        Dim databaseName As String
        Dim fileName As String
        Dim scriptError As String

        'On Error GoTo ErrHandler

        'Rest global variables since macro last run!
        _errorCount = 0
        _fileNames.Clear()
        _lastError = String.Empty

        Try
            _databaseOutputPane = GetDatabaseOutputPane()

            GetAllSelectedFiles()
            If Not ScriptsSelectedOk() Then Exit Sub
            If Not SelectServer(serverName, databaseName) Then Exit Sub
            If Not ValidateDatabaseConnection(serverName, databaseName) Then Exit Sub

            WriteToOutputWindow(Environment.NewLine)
            WriteToOutputWindow(String.Format("------ Running SQL scripts on '{0}' ------", serverName))

            'Iterating through all selected items (for some reason the list is upside down)
            For i = _fileNames.Count To 1 Step -1

                fileName = _fileNames(i)

                If Path.GetExtension(fileName) = ".sql" Then
                    Try
                        scriptError = RunScript(serverName, databaseName, fileName)

                        If Len(scriptError) > 0 Then
                            If (ResumeRunningScripts(_lastError, fileName) = False) Then
                                Exit For
                            End If
                        End If

                    Catch ex As Exception
                        ' Any unexpected errors not caught by the Sub Connection_InfoMessage()
                        Dim message As String = String.Format("Do you wish to continue" + Environment.NewLine + "Error running script {0} {1} {2}", fileName, Str$(ex.Message), ex.StackTrace)
                        Dim result = MsgBox(message, MsgBoxStyle.YesNo, "Error Running Sql Script")

                        If result <> MsgBoxResult.Yes Then
                            Exit For
                        End If
                    End Try
                End If
            Next

            DisplayResults()

        Catch ex As Exception
            Dim message As String = "An unexpected error occurred." + Environment.NewLine + ex.Message + vbCrLf + ex.StackTrace
            MessageBox.Show(message, "Run On...", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Function GetConnectionString(ByVal serverName As String, ByVal databaseName As String)
        Return String.Format("Database={0};Server={1};Trusted_Connection=True", databaseName, serverName)
    End Function

    Private Function RunScript(ByVal serverName As String, ByVal databaseName As String, ByVal fileName As String) As String
        Dim connectionString As String = GetConnectionString(serverName, databaseName)
        Dim script As String
        Dim server As Server

        WriteToOutputWindow(fileName)

        Try
            Using reader As New StreamReader(fileName)
                script = reader.ReadToEnd()
            End Using

            _lastError = vbNullString

            _connection = New SqlConnection(connectionString)
            ' Any errors fire the event Connection_InfoMessage
            _connection.FireInfoMessageEventOnUserErrors = True

            server = New Server(New ServerConnection(_connection))
            Dim rowsAffected As Integer = server.ConnectionContext.ExecuteNonQuery(script)

        Finally
            server.ConnectionContext.Disconnect()
            _connection.Close()
            '_connection.ClearAllPools()   
        End Try

        Return _lastError
    End Function

    Private Sub Connection_InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs) Handles _connection.InfoMessage

        Dim err As SqlError

        For Each err In e.Errors
            _errorCount = _errorCount + 1

            Dim errorMessage As String = String.Format("Msg {0}, Level {1}, State {2}, Line {3}" + Environment.NewLine + "{4}", _
                                            err.Number, err.Class, err.State, err.LineNumber, err.Message)

            _lastError = _lastError & Environment.NewLine & errorMessage

            WriteToOutputWindow(vbCr)
            WriteToOutputWindow(errorMessage)
        Next

    End Sub

    Private Function ResumeRunningScripts(ByVal ErrorMessage As String, ByVal fileName As String) As Boolean
        Dim result As MsgBoxResult

        Dim message As String = String.Format("An error occured running the script '{0}', see output window for further details.{1}Do you wish to continue?", Path.GetFileName(fileName), Environment.NewLine)
        result = MsgBox(message, MsgBoxStyle.YesNo, "Error Running Sql Script")

        Return (result = MsgBoxResult.Yes)

    End Function

    Private Function SelectServer(ByRef serverName, ByRef databaseName) As Boolean
        Dim serverList As String() = New String() {"DEVDB", "STAGEDB", "LIVEDB"}

        Dim frm As New Form
        Dim cboServers As New ComboBox
        Dim lblServer As New Label
        Dim btnOk As New Button
        Dim btnCancel As New Button
        Dim lblDatabase As New Label
        Dim txtDatabase As New TextBox
        ' 
        ' cboServers
        ' 
        cboServers.FormattingEnabled = True
        cboServers.Items.AddRange(serverList)
        cboServers.Location = New System.Drawing.Point(99, 13)
        cboServers.Size = New System.Drawing.Size(189, 21)
        cboServers.TabIndex = 0
        cboServers.Name = "cboServers"
        cboServers.SelectedIndex = 0
        ' 
        ' lblServer
        ' 
        lblServer.AutoSize = True
        lblServer.Location = New System.Drawing.Point(12, 16)
        lblServer.Name = "lblServer"
        lblServer.Size = New System.Drawing.Size(70, 13)
        lblServer.Text = "Server name:"
        '
        ' btnOk
        '
        btnOk.DialogResult = DialogResult.OK
        btnOk.Location = New System.Drawing.Point(132, 69)
        btnOk.Size = New System.Drawing.Size(75, 23)
        btnOk.TabIndex = 3
        btnOk.Text = "OK"
        '
        ' btnCancel
        '
        btnCancel.DialogResult = DialogResult.Cancel
        btnCancel.Size = New System.Drawing.Size(75, 23)
        btnCancel.Location = New System.Drawing.Point(212, 69)
        btnCancel.TabIndex = 4
        btnCancel.Text = "Cancel"
        ' 
        ' lblDatabase
        ' 
        lblDatabase.AutoSize = True
        lblDatabase.Location = New System.Drawing.Point(12, 46)
        lblDatabase.Size = New System.Drawing.Size(70, 13)
        lblDatabase.Text = "Database:"
        ' 
        ' txtDatabase
        ' 
        txtDatabase.Location = New System.Drawing.Point(99, 43)
        txtDatabase.Size = New System.Drawing.Size(189, 20)
        txtDatabase.Text = "MyDatabaseName"
        txtDatabase.TabIndex = 2
        '
        ' frm
        '
        frm.AutoScaleDimensions = New System.Drawing.SizeF(6.0F, 13.0F)
        frm.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        frm.Text = "Select Server"
        frm.Size = New System.Drawing.Size(299, 128)
        frm.AcceptButton = btnOk
        frm.CancelButton = btnCancel
        frm.FormBorderStyle = FormBorderStyle.FixedDialog
        frm.StartPosition = FormStartPosition.CenterParent
        frm.Controls.Add(btnCancel)
        frm.Controls.Add(btnOk)
        frm.Controls.Add(lblServer)
        frm.Controls.Add(cboServers)
        frm.Controls.Add(txtDatabase)
        frm.Controls.Add(lblDatabase)

        Dim winptr As New WinWrapper()
        Dim result As String

        ' Launch server/database dialog

        Try
            If frm.ShowDialog(winptr) = DialogResult.OK Then
                If Not cboServers.SelectedItem Is Nothing Then
                    serverName = cboServers.SelectedItem
                Else
                    serverName = cboServers.Text
                End If
                databaseName = txtDatabase.Text
            Else
                serverName = vbNullString
                databaseName = vbNullString
            End If

            SelectServer = (Len(serverName & databaseName) > 0)

        Catch ex As Exception
            frm.Close()
        Finally
            winptr = Nothing
        End Try

    End Function

    Public Function CreateServerForm(ByVal serverList As String()) As Form
        Dim frm As New Form
        Dim cboServers As New ComboBox
        Dim lblServer As New Label
        Dim btnOk As New Button
        Dim btnCancel As New Button
        Dim lblDatabase As New Label
        Dim txtDatabase As New TextBox
        ' 
        ' cboServers
        ' 
        cboServers.FormattingEnabled = True
        cboServers.Items.AddRange(serverList)
        cboServers.Location = New System.Drawing.Point(99, 13)
        cboServers.Size = New System.Drawing.Size(189, 21)
        cboServers.TabIndex = 0
        cboServers.SelectedIndex = 0
        ' 
        ' lblServer
        ' 
        lblServer.AutoSize = True
        lblServer.Location = New System.Drawing.Point(12, 16)
        lblServer.Name = "lblServer"
        lblServer.Size = New System.Drawing.Size(70, 13)
        lblServer.Text = "Server name:"
        '
        ' btnOk
        '
        btnOk.DialogResult = DialogResult.OK
        btnOk.Location = New System.Drawing.Point(132, 69)
        btnOk.Size = New System.Drawing.Size(75, 23)
        btnOk.TabIndex = 3
        btnOk.Text = "OK"
        '
        ' btnCancel
        '
        btnCancel.DialogResult = DialogResult.Cancel
        btnCancel.Size = New System.Drawing.Size(75, 23)
        btnCancel.Location = New System.Drawing.Point(212, 69)
        btnCancel.TabIndex = 4
        btnCancel.Text = "Cancel"
        ' 
        ' lblDatabase
        ' 
        lblDatabase.AutoSize = True
        lblDatabase.Location = New System.Drawing.Point(12, 46)
        lblDatabase.Size = New System.Drawing.Size(70, 13)
        lblDatabase.Text = "Database:"
        ' 
        ' txtDatabase
        ' 
        txtDatabase.Location = New System.Drawing.Point(99, 43)
        txtDatabase.Size = New System.Drawing.Size(189, 20)
        txtDatabase.Text = "MyDatabaseName"
        txtDatabase.TabIndex = 2
        '
        ' frm
        '
        frm.AutoScaleDimensions = New System.Drawing.SizeF(6.0F, 13.0F)
        frm.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        frm.Text = "Select Server"
        frm.Size = New System.Drawing.Size(299, 128)
        frm.AcceptButton = btnOk
        frm.CancelButton = btnCancel
        frm.FormBorderStyle = FormBorderStyle.FixedDialog
        frm.StartPosition = FormStartPosition.CenterParent
        frm.Controls.Add(btnCancel)
        frm.Controls.Add(btnOk)
        frm.Controls.Add(lblServer)
        frm.Controls.Add(cboServers)
        frm.Controls.Add(txtDatabase)
        frm.Controls.Add(lblDatabase)

        Return frm

    End Function

    Private Function ValidateDatabaseConnection(ByVal serverName As String, ByVal databaseName As String)
        Dim connectionString As String = GetConnectionString(serverName, databaseName)

        Try
            Using conn = New SqlConnection(connectionString)
                conn.Open()
            End Using
            Return True
        Catch ex As Exception
            MessageBox.Show(String.Format("Unable to connection to the database '{0}' on server '{1}'", databaseName, serverName), "Run On...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        End Try

    End Function

    Private Function ScriptsSelectedOk() As Boolean
        Dim fileExtension As String
        Dim fileName As String

        ScriptsSelectedOk = True

        'Iterating through all selected items
        For Each fileName In _fileNames
            fileExtension = Path.GetExtension(fileName).ToLower()

            If (fileExtension <> ".sql") Then
                ScriptsSelectedOk = False
            End If
        Next

        If Not ScriptsSelectedOk Then
            Dim result As DialogResult = MsgBox("Non-SQL scripts have been selected, do you wish to continue?", MsgBoxStyle.YesNo, "Run On...")
            ScriptsSelectedOk = (result = DialogResult.Yes)
        End If

    End Function

    Private Sub DisplayResults()

        WriteToOutputWindow(vbCr)
        WriteToOutputWindow(String.Format("Running scripts complete -- {0} errors", _errorCount))

    End Sub

    Private Function GetDatabaseOutputPane() As OutputWindowPane
        Dim win As Window = DTE.Windows.Item(EnvDTE.Constants.vsWindowKindOutput)
        Dim ow As OutputWindow = win.Object
        Dim owPane As OutputWindowPane

        For i As Integer = 1 To ow.OutputWindowPanes.Count
            If (ow.OutputWindowPanes.Item(i).Name = "Database Output") Then
                owPane = ow.OutputWindowPanes.Item(i)
                owPane.Activate()
                Return owPane
            End If
        Next
    End Function

    Private Sub WriteToOutputWindow(ByVal message As String)
        _databaseOutputPane.OutputString(message)
        _databaseOutputPane.OutputString(vbLf)
    End Sub

    Private Sub GetAllSelectedFiles()

        Dim selectedItem As EnvDTE.ProjectItem
        Dim solutionExplorer As UIHierarchy

        solutionExplorer = DTE.Windows.Item(Constants.vsext_wk_SProjectWindow).Object()

        'Iterating through all selected items
        For Each item In solutionExplorer.SelectedItems
            'Get the item
            selectedItem = CType(item.Object, EnvDTE.ProjectItem)

            If (selectedItem.ProjectItems Is Nothing OrElse selectedItem.ProjectItems.Count = 0) Then
                ' Single File
                Dim fileName As Object = selectedItem.FileNames(1)
                _fileNames.Add(fileName)
            Else
                ' Folder
                NavigateProjectItems(selectedItem.ProjectItems)
            End If
        Next

    End Sub

    Private Sub NavigateProjectItems(ByVal colProjectItems As ProjectItems)

        Dim objProjectItem As EnvDTE.ProjectItem
        Dim sMsg As String

        If Not (colProjectItems Is Nothing) Then
            For Each objProjectItem In colProjectItems

                If Not (objProjectItem.SubProject Is Nothing) Then
                    ' We navigate recursively because it can be:
                    ' - An Enterprise project in VS.NET 2002/2003
                    ' - A solution folder in VS 2005
                    NavigateProject(objProjectItem.SubProject)
                Else
                    Dim fileName As Object = objProjectItem.FileNames(1)
                    ' Exclude folders
                    If Right(fileName, 1) <> "\" Then
                        _fileNames.Add(fileName)
                    End If

                    ' We navigate recursively because it can be:
                    ' - An folder inside a project
                    ' - A project item with nested project items (code-behind files, etc.)
                    NavigateProjectItems(objProjectItem.ProjectItems)
                End If

            Next

        End If

    End Sub

    Private Sub NavigateProject(ByVal objProject As Project)

        Dim sMsg As String
        Dim objParentProjectItem As ProjectItem

        Try
            objParentProjectItem = objProject.ParentProjectItem
        Catch
        End Try

        NavigateProjectItems(objProject.ProjectItems)

    End Sub

End Module

'' This class is used to set the proper parent to any UI that you may display from within a macro.
''  See the AddClassicComRef macro for an example of how this is used
Public Class WinWrapper
    Implements System.Windows.Forms.IWin32Window

    Overridable ReadOnly Property Handle() As System.IntPtr Implements System.Windows.Forms.IWin32Window.Handle
        Get
            Dim iptr As New System.IntPtr(DTE.MainWindow.HWnd)
            Return iptr
        End Get
    End Property
End Class

Install the macro in VS IDE

• You need to create a macro file with the above code and call it ContextMenu.vb
• Save it to %USERPROFILE%\Documents\Visual Studio 2010\Projects\VSMacros80\MyMacros
• In VS2010 go to Tools > Macros > Macros IDE and load the macro by right clicking MyMacros > Add > Add Existing Item
• There are some instructions at the top of the Macro to copy some assembly files and add some references. Follow these.

Set up the Context Menus

• Tools > Customize...
• Tick Context Menus
• Go to Commands tab
• Select 'Context menu' radio button
• Select 'Project and Solution Context Menu | Item' from the drop down
• Click 'Add Command' Button
• Select 'Macros' from the Categories list
• Select 'Macros.MyMacros.ContextMenu.RunOnServer'
• Click OK
• Click Modify selection
• Change the name to 'Run On...'
• Do the same for 'Project and Solution Context Menu | Folder'
• Click Close

回答2:

I created a project in VS2008 that will merge your .sql files for you into 1 file from multiple directories ... feel free to use: http://www.filesavr.com/V9MSV0VUE77Z0WG