440 Microsoft SQL Server 2005 Developer’s Guide
In this chapter, you learn how you can manage SQL Server programmatically
from VB.NET by taking advantage of SQL Management Objects (SMO).
Like its predecessor, Distributed Management Objects (SQL-DMO), SMO
enables you to develop custom SQL Server management applications that you can
tailor to your environment. Using SMO with VB.NET or any other .Net language,
you can create custom SQL Server management interfaces that let you perform
all the functions SQL Server’s Management Studio provides. In fact, SMO is
the foundation for SQL Server’s Management Studio. Using SMO, you can list
databases and tables; add logins; control replication; import and export data; and
perform backups, restores, and many other administrative tasks. SMO opens up
SQL Server to a number of custom programs that can both display and manipulate
SQL Server and all of its databases and objects.
In this chapter, you get an overview of SMO, as well as a look at its underlying
architecture. Then, you see how to use SMO from VB.NET. In this section, you
see how to add the SMO object library to the Visual Basic Integrated Development
Environment (IDE). You also see how to perform some common tasks with SMO.
Finally, this chapter finishes by presenting a sample SQL Server management utility
that’s built using VB.NET and SMO.
Using SMO
To get programmatic access to management functions of other database platforms,
you might need to master low-level networking and system interfaces—if it’s
available at all. However, SMO provides a .NET framework solution that makes
SQL Server’s database management functions easy to access. The hierarchy for the
SMO objects used in the .NET framework is discussed in the next section of this
chapter. SQL Server’s SMO functions can be used by a programming language that
is supported by the Common Language Runtime (CLR), such as Visual Basic.NET
and Visual C#.NET.
To use SMO from VB.NET, follow these basic steps:
1. Add a reference to the SMO assemblies and then import the namespaces that
are required so that your program can recognize the SMO types.
2. Create an instance of the Server object.
3. Establish a connection to the instance of the Server object to SQL Server.
4. Use the Server object.
5. Disconnect from SQL Server.
Chapter 12: Developing with SMO 441
The following section of the chapter walks you through the basic steps needed
to build a project using SMO. The project presented is a Winforms project built
in Visual Basic, but you can follow these steps to build an ASP project or even
a command-line project.
Adding SMO Objects to Visual Studio
Before you can begin to use the SMO objects in Visual Basic’s development
environment, you need to incorporate the SMO assemblies into your Visual Basic
project. The files that provide the basic support for SMO are copied to your client
system when you first install the SQL Server client. However, you still need to set
a reference to them in Visual Studio’s development environment to enable their use
from your applications. To add the SMO references to your Visual Studio project,
you must select the Add Reference option from the Project menu. This action displays
the References dialog box you can see in Figure 12-1.
Select the .NET tab and scroll through the References dialog box until you see the
SMO assemblies: Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer
.Smo, Microsoft.SqlServer.SmoEnum, and Microsoft.SqlServer.SqlEnum. Selecting
these items and then clicking OK adds the references to Visual Basic’s Interactive
Figure 12-1 Adding references to SMO
442 Microsoft SQL Server 2005 Developer’s Guide
Development Environment (IDE). To see the SMO properties and methods, you
must use Visual Basic’s Object Browser, shown in Figure 12-2.
Creating the Server Object
Before you can use any of the SMO methods, you must first specify an import
directive for the Microsoft.SqlServer.Management.Smo Namespace in your project.
The Microsoft.SqlServer.Management.Smo Namespace contains all of the related
SQL Server connection and data access classes. To add an import directive for the
Microsoft.SqlServer.Management.Smo to a VB.NET project, you would add the
following code to the declaration section of your source file:
Imports Microsoft.SqlServer.Management.Smo
Next, you must create an instance of the Server object, which is the most basic
object in the SMO set. You can create an instance of the Server object and establish
a connection to the SQL Server instance in three different ways: explicitly setting
Figure 12-2 Viewing an SMO assembly from the Object Browser
Chapter 12: Developing with SMO 443
the connection information through the Server object properties, passing the
SQL Server instance name to the Server object constructor function, or using the
ServerConnection object to provide the connection information.
To explicitly set the connection information through the Server object properties,
you simply declare the Server object variable using the default constructor. If you do
not set any of the Server object’s properties, the Server object attempts to connect
to the local instance of SQL Server with the default connection settings. To connect
to a remote or named instance of SQL Server, set the name property and any other
properties that affect the connection settings, such as authentication mode, logins,
and passwords, as shown here:
Dim oSQLServer As New Server()
oSQLServer.ConnectionContext.LoginSecure = false
oSQLServer.ConnectionContext.Login = "username"
oSQLServer.ConnectionContext.Password = "password"
To pass the SQL Server instance name to the Server object, you first declare the
Server object variable and pass the SQL Server instance name as a string parameter
in the constructor, as shown here:
Dim oSQLServer As Server = New Server("SQL2005")
To use the ServerConnection object, you need to specify an import directive for
the Microsoft.SqlServer.Management.Common namespace. The import directive for
Microsoft.SqlServer.Management.Common is added to the declaration section of
your source file:
Imports Microsoft.SqlServer.Management.Common
In order to use the ServerConnection object to provide the connection information to
the Server object, you declare a ServerConnection object variable and set the connection
information, such as the SQL Server instance name and the authentication mode into its
properties. You then pass the ServerConnection object as a parameter to the Server object
constructor. Here is an example of this method:
Dim oServerConn As ServerConnection = New ServerConnection()
oServerConn.ServerInstance = "SQL2005"
oServerConn.LoginSecure = True
Dim oSQLServer As New Server(oServerConn)
