VS2010 Macro / Add In用于Visual Studio 2010中的“凌un”和“凌un on”命令

Question

使许多开发人员感到非常烦恼的是，Microsoft删除了Visual Studio 2010中的“运行”和“运行”命令：

有关更多详细信息，请访问：http
:
//social.msdn.microsoft.com/Forums/en/vstsdb/thread/f374c604-a7eb-496d-a261-9374790cdbf9

有没有人看到或编写过填补空白并复制此功能的VS2010宏或扩展？

Answer 1

好的，所以我放弃了，并事先写了自己的宏，很抱歉，我刚开始时对VB.Net的知识几乎不存在。如果有些用法不正确或可以改进，我也很抱歉以前没有使用过几类。

在Visual
Studio中设置上下文菜单后，您只需右键单击脚本/脚本/文件夹，然后选择“运行于…”，脚本将全部在所选数据库上运行。这篇文章的底部提供了有关设置宏和创建上下文菜单的说明。

随时发布任何更正，我将尝试更新我的帖子。

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

在VS IDE中安装宏

• 您需要使用上面的代码创建一个宏文件，并将其命名为ContextMenu.vb
• 将其保存到％USERPROFILE％\ Documents \ Visual Studio 2010 \ Projects \ VSMacros80 \ MyMacros
• 在VS2010中，转到“工具”>“宏”>“ Macro IDE”，然后通过右键单击“ MyMacros”>“添加”>“添加现有项”来加载宏。
• 宏顶部有一些说明，用于复制一些程序集文件并添加一些引用。遵循这些。

设置上下文菜单

• 工具>自定义…
• 勾号上下文菜单
• 转到命令选项卡
• 选择“上下文菜单”单选按钮
• 选择“项目和解决方案上下文菜单| 下拉菜单中的“项目”
• 点击“添加命令”按钮
• 从类别列表中选择“宏”
• 选择“ Macros.MyMacros.ContextMenu.RunOnServer”
• 点击确定
• 单击修改选择
• 将名称更改为“ Run On …”
• 对“项目和解决方案上下文菜单| 文件夹’
• 点击关闭