SMO/SQL-Server (VB.NET) Part 1
Introduction
This article intent is to provide alternate methods to work with SQL-Server operations using (SMO) SQL-Server Management Objects which are designed for programmatic management of Microsoft SQL Server using .NET languages and PowerShell while the focus programming language is VB.NET.
Many of the operations which can be done with T-SQL can also be done in SMO. An advantage for SMO over T-SQL is an application may need to interface with custom libraries or hardware that does not operate in SSMS (SQL-Server Management Studio) or that a developer (or team of developers) may feel comfortable working with classes that SQL. No matter the following and which is to come in part 2 offer basic to advance code samples to create data objects, interrogate objects, servers, tables, columns, constraints and more.
Important note
Before continuing, the code samples presented are meant for SQL-Server and not SQL-Express edition.
Installation
SMO Installation for your project.
To install either open “Manage NuGet packages” in your Visual Studio solution, select “Browse” followed by entering SMO, select the following packages
Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended
Or use the following in the package management console.
Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 140.17283.0
SMO in the code samples notes
The SMO DLL files in this solution are for SQL-Server 2012, if using a earlier or newer version than the current DLL files need to be removed in place of the proper versions of the DLL files for your version of SQL-Server.
SMO Classes
Server class
The server class represents an instance of a SQL-Server. Create a new instance of this class with an empty constructor will default to the current SQL-Server installed. To target a specific server create an instance of the server class with a known server e.g. New Server(“Production-Web01”)
Create for default server
Dim srv = New Server
Create for a specific server
New Server("Production-Web01")
Once the server object has been created you can begin using it e.g. get the install path for SQL-Server, in this case for a specific server instance.
Public Function SqlServerInstallPath(pServerName As String) As String
Dim srv = New Server(pServerName)
Return srv.RootDirectory
End Function
If you want to get a list of available servers then the following code sample emulates available servers. By available meaning there may be more than you have permissions to see.
This is part of the included code samples.
Public Async Function GetServersAsync() As Task(Of List(Of String))
Dim serverNames As New List(Of String)
Await Task.Run(
Sub()
serverNames = SmoApplication.
EnumAvailableSqlServers(True).
AsEnumerable().
Select(Function(row) row.Field(Of String)("Name")).
ToList()
End Sub)
Return serverNames
End Function
Keeping with the server class, the following provides access to various properties including all it's databases.
Imports Microsoft.SqlServer.Management.Smo
Imports SMO_UtilityLibrary.Interfaces
Namespace Classes
Public Class ServerDetails
Implements IDetails
Public Property Name As String Implements IDetails.Name
Public Property Server() As Server
Public Property Databases() As DatabaseCollection
Public Property Exists As Boolean Implements IDetails.Exists
Public Property Exception() As Exception
End Class
End Namespace
Note the following method can be called with an optional parameter to include or exclude database objects.
Public Function GetServer(pServerName As String, Optional pLoadDatabases As Boolean = True) As ServerDetails
Dim serverDetails As New ServerDetails
Dim srv = New Server(pServerName)
Try
serverDetails.Name = pServerName
serverDetails.Server = srv
serverDetails.Exists = True
If pLoadDatabases Then
serverDetails.Databases = srv.Databases
End If
Catch ex As Exception
If ex.Message.Contains("Failed to connect to server") Then
serverDetails.Exists = False
serverDetails.Exception = ex
End If
End Try
Return serverDetails
End Function
To just return all databases for a specific server.
Public Function GetDatabases(pServerName As String) As List(Of Database)
Dim srvDetails = GetServer(pServerName, True)
Dim excludeDatabaseNames As String() = {"master", "model", "msdb"}
Dim result As New List(Of Database)
If srvDetails.Exists Then
If srvDetails.Databases IsNot Nothing Then
For Each db As Database In srvDetails.Databases
If Not excludeDatabaseNames.Contains(db.Name) Then
result.Add(db)
End If
Next
End If
End If
Return result
End Function
Note that when asking for all databases this will include system databases, the following excludes system databases.
Public Function GetAllDatabasesAndTables(pServerName As String, Optional pLoadDatabases As Boolean = True) As _
List(Of DatabaseAndTables)
Dim srvDetails = GetServer(pServerName, pLoadDatabases)
Dim excludeDatabaseNames As String() = {"master", "model", "msdb"}
Dim result = New List(Of DatabaseAndTables)
If srvDetails.Exists Then
If srvDetails.Databases IsNot Nothing Then
For Each db As Database In srvDetails.Databases
If Not excludeDatabaseNames.Contains(db.Name) Then
Dim item As New DatabaseAndTables With {.DatabaseName = db.Name, .TableNameList = New List(Of String)}
For Each tbl As Table In db.Tables
item.TableNameList.Add(tbl.Name)
Next
result.Add(item)
End If
Next
End If
End If
Return result
End Function
Server.DatabaseCollection
Using the DatabaseCollection this permits access to database details. For instance, to obtain table names for a specific database and return an instance of a custom class TableDetails.
Imports SMO_UtilityLibrary
Imports SMO_UtilityLibrary.Interfaces
Namespace Classes
Public Class TableDetails
Implements IDetails
Public Property ServerName() As String
Public Property DatabaseName() As String
''' <summary>
''' Indicates if the object is valid
''' </summary>
''' <returns></returns>
Public Property Exists As Boolean Implements IDetails.Exists
''' <summary>
''' Table name
''' </summary>
''' <returns></returns>
Public Property Name As String Implements IDetails.Name
''' <summary>
''' Table names
''' </summary>
''' <returns></returns>
Public Property NameList() As List(Of String)
''' <summary>
''' Check to see if there are tables in the database
''' </summary>
''' <returns></returns>
Public ReadOnly Property HasTables() As Boolean
Get
Return NameList.Count > 0
End Get
End Property
End Class
End Namespace
Method to obtain tables
Public Function TableNames(pDatabaseName As String) As TableDetails
Dim result As New TableDetails With {.Exists = False, .DatabaseName = pDatabaseName}
Dim srv = New Server()
Dim database = srv.Databases(pDatabaseName)
If database IsNot Nothing Then
result.ServerName = srv.Name
result.Exists = True
result.NameList = database.Tables.OfType(Of Table)().
Where(Function(tbl) (Not tbl.IsSystemObject)).
Select(Function(tbl) tbl.Name).ToList()
End If
Return result
End Function
Database class
The database class provides access to tables within a specific database. Suppose your application needs to determine if a specific table exists in a database. The following method provides this along with other information using the TableDetails class above.
Public Function TableExists(pServer As String, pDatabaseName As String, pTableName As String) As TableDetails
Dim result As New TableDetails With {.Exists = False}
Dim srv = New Server(pServer)
Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(tbl) tbl.Name = pDatabaseName)
If database IsNot Nothing Then
result.Exists = (database.Tables.OfType(Of Table)().
Where(Function(tbl) (Not tbl.IsSystemObject)).
FirstOrDefault(Function(tbl) tbl.Name = pTableName) IsNot Nothing)
End If
Return result
End Function
Find a table were the full name of the table is not known or perhaps a developer is looking for a similar table from another database.
Public Function GetTableByContainingToken(pServer As Server, pDatabaseName As String, pPartialTableName As String) As Table
Dim tblResult As Table = Nothing
Dim database = pServer.Databases.OfType(Of Database)().
FirstOrDefault(Function(tbl) tbl.Name = pDatabaseName)
If database IsNot Nothing Then
tblResult = database.Tables.OfType(Of Table)().
Where(Function(tbl) (Not tbl.IsSystemObject)).
Select(Function(tbl) tbl).
FirstOrDefault(Function(x) x.Name.Contains(pPartialTableName))
End If
Return tblResult
End Function
From the Database class there is access to columns in a table. The following method provides the ability to determine if a specific column exists in a specific table. Note the server object has no specified server which means it uses the default server.
Public Function ColumnExists(pDatabaseName As String, pTableName As String, pColumnName As String) As Boolean
Dim srv = New Server
Dim exists As Boolean = False
Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(db) db.Name = pDatabaseName)
If database IsNot Nothing Then
Dim table = database.Tables.OfType(Of Table)().FirstOrDefault(Function(tbl) tbl.Name = pTableName)
If table IsNot Nothing Then
exists = (table.Columns.OfType(Of Column)().FirstOrDefault(Function(col) col.Name = pColumnName) IsNot Nothing)
End If
End If
Return exists
End Function
If a column needs to be inspected the following method provides this capability using a special class built into the code sample can as with the other classes can be used in your projects.
Imports System.ComponentModel
Imports Microsoft.SqlServer.Management.Smo
Namespace Classes
Public Class ColumnDetails
''' <summary>
''' Column is a identify column
''' </summary>
<Category("Items"), Description("Indicates if the field is Identity")>
Public Property Identity() As Boolean
<Category("General"), Description("Column Name")>
Public Property Name() As String
''' <summary>
''' There are plenty of useful properties within DataType as an
''' example in the property SqlDataType or IsDate (which we know
''' there are multiple data types).
''' </summary>
<Category("Items"), Description("Describes the data type")>
Public Property DataType() As DataType
<Category("Items"), Description("Describes the sql data type")>
Public ReadOnly Property SqlDataType() As SqlDataType
Get
Return DataType.SqlDataType
End Get
End Property
'
' * I setup several properties for Dates to show that we can do this but
' * generally speaking we don't need to do all of them.
'
<Category("Items"), Description("Indicates if this field is a Date")>
Public ReadOnly Property IsDate() As Boolean
Get
Return DataType.SqlDataType = SqlDataType.Date
End Get
End Property
<Category("Items"), Description("Indicates if this field is a DateTime")>
Public ReadOnly Property IsDateTime() As Boolean
Get
Return DataType.SqlDataType = SqlDataType.DateTime
End Get
End Property
<Category("Items"), Description("Indicates if this field is a DateTime Offset")>
Public ReadOnly Property IsDateTimeOffset() As Boolean
Get
Return DataType.SqlDataType = SqlDataType.DateTimeOffset
End Get
End Property
<Category("Items"), Description("Indicates if this field is Nullable")>
Public Property Nullable() As Boolean
<CategoryAttribute("Items"), DescriptionAttribute("Indicates if field is in a primary key")>
Public Property InPrimaryKey() As Boolean
''' <summary>
''' get foreign keys
''' </summary>
<Category("Items"), Description("ForeignKeys DataTable")>
Public Property ForeignKeys() As DataTable
''' <summary>
''' Contains row data retrieved from EnumForeignKeys
''' which represent any foreign key definitions
''' </summary>
<Category("Items"), Description("ForeignKeys break down")>
Public Property ForeignKeysList() As List(Of ForeignKeysDetails)
Public Overrides Function ToString() As String
Return Name
End Function
End Class
End Namespace
Get column details using the class above for the return type using the default server.
Public Function GetColumnDetails(pDatabaseName As String, pTableName As String) As List(Of ColumnDetails)
Dim srv = New Server
Dim columnDetails = New List(Of ColumnDetails)()
Dim database = srv.Databases.OfType(Of Database)().
FirstOrDefault(Function(db) db.Name = pDatabaseName)
If database IsNot Nothing Then
Dim table = database.Tables.OfType(Of Table)().
FirstOrDefault(Function(tbl) tbl.Name = pTableName)
If table IsNot Nothing Then
columnDetails = table.Columns.OfType(Of Column)().
Select(Function(col) New ColumnDetails() With
{
.Identity = col.Identity,
.DataType = col.DataType,
.Name = col.Name,
.InPrimaryKey = col.InPrimaryKey,
.Nullable = col.Nullable
}
).ToList()
End If
End If
Return columnDetails
End Function
An overload of the above method to specify the server.
Public Function GetColumnDetails(pServer As String, pDatabaseName As String, pTableName As String) As List(Of ColumnDetails)
Dim srv = New Server(pServer)
Dim columnDetails = New List(Of ColumnDetails)()
Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(db) db.Name = pDatabaseName)
If database IsNot Nothing Then
Dim table = database.Tables.OfType(Of Table)().FirstOrDefault(Function(tbl) tbl.Name = pTableName)
If table IsNot Nothing Then
columnDetails = table.Columns.OfType(Of Column)().
Select(Function(col) New ColumnDetails() With
{
.Identity = col.Identity,
.DataType = col.DataType,
.Name = col.Name,
.InPrimaryKey = col.InPrimaryKey,
.Nullable = col.Nullable
}
).ToList()
End If
End If
Return columnDetails
End Function
The following provides access to foreign key details using the following class for a return type.
Public Function GetColumnDetails(pServer As String, pDatabaseName As String, pTableName As String) As List(Of ColumnDetails)
Dim srv = New Server(pServer)
Dim columnDetails = New List(Of ColumnDetails)()
Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(db) db.Name = pDatabaseName)
If database IsNot Nothing Then
Dim table = database.Tables.OfType(Of Table)().FirstOrDefault(Function(tbl) tbl.Name = pTableName)
If table IsNot Nothing Then
columnDetails = table.Columns.OfType(Of Column)().
Select(Function(col) New ColumnDetails() With
{
.Identity = col.Identity,
.DataType = col.DataType,
.Name = col.Name,
.InPrimaryKey = col.InPrimaryKey,
.Nullable = col.Nullable
}
).ToList()
End If
End If
Return columnDetails
End Function
Method to return foreign keys for a table in a database for the default server.
Public Function TableKeys(pDatabaseName As String, pTableName As String) As List(Of ForeignKeysDetails)
Dim srv = New Server()
Dim keyList = New List(Of ForeignKeysDetails)()
Dim database = srv.Databases.OfType(Of Database)().FirstOrDefault(Function(db) db.Name = pDatabaseName)
If database IsNot Nothing Then
Dim table = database.Tables.OfType(Of Table)().FirstOrDefault(Function(tbl) tbl.Name = pTableName)
If table IsNot Nothing Then
' ReSharper disable once LoopCanBeConvertedToQuery
For Each item As Column In table.Columns.OfType(Of Column)()
Dim fkds As List(Of ForeignKeysDetails) = item.EnumForeignKeys().
AsEnumerable().
Select(Function(row) New ForeignKeysDetails With
{
.TableSchema = row.Field(Of String)("Table_Schema"),
.TableName = row.Field(Of String)("Table_Name"),
.SchemaName = row.Field(Of String)("Name")
}
).ToList()
For Each ts As ForeignKeysDetails In fkds
keyList.Add(ts)
Next
Next
End If
End If
Return keyList
End Function
Copying an existing database operation
Not all developers have a development, test and production server. The next best thing is to duplicate a database. In the following method the name of an existing database is passed in as the first parameter and the second parameter is the name of a database to copy the information from the source database (parameter 1).
Public Function CopyDatabase(pOriginalDatabase As String, pNewDatabase As String) As Boolean
mHasException = False
Dim srv = New Server
Dim db As Database
Try
db = srv.Databases(pOriginalDatabase)
Dim dbCopy As Database
dbCopy = New Database(srv, pNewDatabase)
dbCopy.Create()
Dim trans As Transfer
trans = New Transfer(db)
trans.CopyAllTables = True
trans.Options.WithDependencies = True
trans.Options.ContinueScriptingOnError = True
trans.DestinationDatabase = pNewDatabase
trans.DestinationServer = srv.Name
trans.DestinationLoginSecure = True
trans.Options.DriAllKeys = True
trans.CopySchema = True
trans.TransferData()
Return True
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Function
Scripting tables in a database
SSMS provides the ability to script a database with or without data. With SMO using the Scripter class a developer can script out table(s) to a stream or text file for later use.
In the following method all tables for a specific database, in this case a modified version of Microsoft NorthWind database each table is scripted to one text file per table.
Public Function ScriptDatabaseTables() As List(Of String)
Dim fileNames As New List(Of String) From {"Tables for NorthWindAzure", ""}
' uses default server
Dim srv = New Server()
Dim scrp As New Scripter With {
.Server = srv
}
scrp.Options.ScriptData = True
scrp.Options.ScriptSchema = False
scrp.Options.ToFileOnly = True
Dim database = srv.Databases("NorthWindAzure")
Dim tables = database.Tables.OfType(Of Table)().
Where(Function(tbl) (Not tbl.IsSystemObject))
For Each table As Table In tables
scrp.Options.FileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, table.Name & ".txt")
fileNames.Add(Path.GetFileName(scrp.Options.FileName))
scrp.EnumScript(New SqlSmoObject() {table})
Next
Return fileNames
End Function
Events
There are operations where a developer needs to know when an operation has began and completed. The following demonstrates dropping and creation of a table with events where in the included code sample is called from a windows form which has events setup to monitor the drop and create of the table.
''' <summary>
''' This code sample shows how to create and drop a table with events.
''' If the database exists it is dropped, no prompting.
'''
''' On table create an event is raised indicating a table was created
''' while a message is shown when the table is dropped
''' </summary>
''' <remarks>
''' * Alternate is to use TSQL script to do the same work, here the advantage
''' for some is being able to inspect/alter properties when creating or after
''' creating.
''' * What can go wrong: There is a live connection on the database which would
''' cause the drop method to fail, in this case raise an exception which here
''' is remembered and sent back to the calling method within the form.
''' </remarks>
Public Function CreateAndDropTableWithEvents(pDatabaseName As String) As Boolean
mHasException = False
Dim dropResults = WhenDatabaseExistsDrop(pDatabaseName)
Dim srv = New Server()
If dropResults = DatabaseExistsResult.Dropped OrElse dropResults = DatabaseExistsResult.DropNotRequired Then
Dim db As New Database(srv, pDatabaseName)
'Define a Schema object variable by supplying the parent database and name arguments in the constructor.
'this is used in DemoTable below.
Dim schema As Schema
schema = New Schema(db, "kp")
schema.Owner = "dbo"
db.Create()
'Create the schema on the instance of SQL Server.
schema.Create()
Else
Return False
End If
Dim database = srv.Databases(pDatabaseName)
Dim databaseCreateEventSet As New DatabaseEventSet
databaseCreateEventSet.CreateTable = True
databaseCreateEventSet.DropTable = True
Dim serverCreateEventHandler As ServerEventHandler
serverCreateEventHandler = New ServerEventHandler(AddressOf CreateDropTableEventHandler)
'Subscribe to the first server event handler when a CreateTable event occurs.
database.Events.SubscribeToEvents(databaseCreateEventSet, serverCreateEventHandler)
database.Events.StartEvents()
'Create a table on the database.
'Create three most populate field types, primary key; integer, string field, date field
Dim tb As Table
tb = New Table(database, "DemoTable")
Dim primaryIdentifierColumn As New Column(tb, "ID", DataType.Int)
primaryIdentifierColumn.Identity = True
primaryIdentifierColumn.IdentitySeed = 1
primaryIdentifierColumn.Nullable = False
tb.Columns.Add(primaryIdentifierColumn)
Dim nameColumn As Column
nameColumn = New Column(tb, "Name", DataType.NChar(50))
nameColumn.Collation = "Latin1_General_CI_AS"
nameColumn.Nullable = True
tb.Columns.Add(nameColumn)
Dim joinDateColumn As New Column(tb, "JoinedDate", DataType.DateTime)
joinDateColumn.AddDefaultConstraint() ' you can specify constraint name here as well
joinDateColumn.DefaultConstraint.Text = "GETDATE()"
tb.Columns.Add(joinDateColumn)
' Add primary key index to the table
Dim primaryKeyIndex As New Index(tb, "PK_TestTableIdentifier")
primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey
primaryKeyIndex.IndexedColumns.Add(New IndexedColumn(primaryKeyIndex, "ID"))
tb.Indexes.Add(primaryKeyIndex)
tb.Schema = "kp"
tb.Create()
'
' Read script to insert serveral record from disk followed by performing the inserts/
'
Try
database.ExecuteNonQuery(File.ReadAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Scripts", "DemoTableRecord.txt")))
Catch ex As Exception
mHasException = True
mLastException = ex
database.Events.StopEvents()
End Try
Try
'Remove the table.
tb.Drop()
' drop database
database.Drop()
'Wait until the events have occured.
Dim dummy As Integer
For outer = 1 To 1000000000
dummy = outer * 2
Next
Catch ex As Exception
mHasException = True
mLastException = ex
Finally
'Stop event handling listening
database.Events.StopEvents()
End Try
Return True
End Function
Visual Studio solution
All SMO operations are housed in a class project while the implementation is in a Windows form. By separating SMO from the User Interface a developer can use the SMO functions in other types of projects.
Database for code sample
The database for this article must be created before running the example. To create the database, open CreateDatabaseTablePopulate.sql under the forms project beneath the folder Databasescript. Connect to the database via the connect button followed by executing the script.
Summary
In this article method have been presented to work with databases, tables, columns and keys along with scripting basics, copying databases and working with events. In the next part of this series working with backing up and restoring databases will be explored along with altering databases, tables, keys, indexes and more.
See also
How to: Create a shared library
Easy SQL Server Tool
Source code
https://github.com/karenpayneoregon/LearningSMObasicsWithVisualBasic