Sep 13

Written by: Steve Gray
9/13/2010 5:13 PM 

SQL SMO is a set of objects that can be used to manipulate SQL Server objects (servers, databases, tables… and so on) from VB. Probably C# also, but that’s not a real programming language, is it?

Anyway, here is the class that I copy into projects that need SQL SMO. It’s a good start.

Imports Microsoft.SqlServer.Management.Smo
Imports System.Data

Public Class SQLSMO

    Function getServers() As DataTable
        'enumerate available servers
        Try
            'returns a datatable with these columns:
            'name
            'server
            'instance
            'isclustered
            'version
            'islocal

            Return SmoApplication.EnumAvailableSqlServers(False)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try


    End Function
    Function isValidServer(ByVal strServer As String) As Boolean
        'validates that a specific server is available
        Try
            Dim server As Microsoft.SqlServer.Management.Smo.Server
            server = New Server(strServer)

            'retrieve a property to see if there is an error
            Dim strVersion As String
            strVersion = server.Information.Version.ToString
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Function getDatabases(ByVal strServer As String) As DataTable
        'enumerate all the databases for a server
        Dim b As Boolean
        b = False
        Try
            Dim svr As Server = New Server(strServer)

            Dim dt As New DataTable
            With dt.Columns
                .Add("Name")
                .Add("selected")
                dt.Columns("selected").DataType = System.Type.GetType("System.Boolean")
            End With

            For Each db As Microsoft.SqlServer.Management.Smo.Database In svr.Databases()
                Dim dr As DataRow = dt.NewRow
                dr("Name") = db.Name
                dr("Selected") = 0
                dt.Rows.Add(dr)
            Next
            Return dt

        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try

    End Function
    Sub runQuery(ByVal strServer As String, ByVal strDB As String, ByVal strText As String)
        'run a query againsts a server/database
        Try

            'objDB.ExecuteNonQuery(allText)
            'Dim db As Microsoft.SqlServer.Management.Smo.Database()

            Dim SMOServer As Server = New Server(strServer)
            Dim db As Database = SMOServer.Databases(strDB)
            db.ExecuteNonQuery(strText)

        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try


    End Sub

End Class