Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
568 views
in Technique[技术] by (71.8m points)

sql - VS2010 Macro/Add In for "Run" and "Run On" commands in Visual Studio 2010

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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 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
        ' 
        txtData

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...