好吧,所以我放弃了,写了自己的宏,提前道歉,当我开始的时候,我的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中安装宏
-
您需要创建一个宏文件
上面的代码并调用它
上下文菜单VB
-
把它保存到
%userprofile%\documents\Visual Studio
2010年\项目\vsmacros80\mymacros
-
在VS2010中,转到“工具”>“宏”>
宏通过右键单击“我的宏”>“添加”>“添加现有项”来隐藏和加载宏。
-
有一些指示在
在宏顶部复制一些
程序集文件并添加一些
参考文献。跟着这些。
设置上下文菜单
-
工具>自定义…
-
勾选上下文菜单
-
转到“命令”选项卡
-
选择“上下文菜单”单选按钮
-
选择“项目和解决方案上下文”
下拉菜单中的“项目”
-
单击“添加命令”按钮
-
从类别中选择“宏”
列表
-
选择
'宏.mymacros.contextmenu.runonserver'
-
单击确定
-
单击“修改选择”
-
将名称更改为“继续…”
-
对“项目和解决方案”执行相同的操作
上下文菜单文件夹'
-
点击关闭
