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

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

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

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

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

imports Systemimports EnvDTEimports EnvDTE80imports EnvDTE90imports EnvDTE90aimports EnvDTE100imports System.IOimports System.Data.SqlClientimports Microsoft.SqlServer.Management.Smoimports Microsoft.SqlServer.Management.Commonimports System.Collections.Genericimports System.Windows.FormsPublic Module ContextMenu    ' Copy the following files from: C:Program FilesMicrosoft SQL Server100SDKAssemblies    ' to: C:Program Files (x86)Microsoft Visual Studio 10.0Common7IDEPublicAssemblies    '    ' 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 (pre-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 SubEnd 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 usedPublic 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 PropertyEnd Class

在VS IDE中安装宏

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

设置上下文菜单

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