Posted by: Cirilo Meggiolaro | 12/28/2008

Tip of the day #75 – Exploring the Microsoft.SqlServer.Management.Smo namespace

Let’s make a quick overview about the Microsoft.SqlServer.Management.Smo namespace available under the Microsoft.SqlServer.Smo.dll assembly.

The idea behind the Smo namespace is to allow access to core objects from the Sql Server engine and some administrative tasks such as backup and restore of databases. The following tasks are just a sample of what may be executed from the Smo namespace classes:

  • Connect to an instance of SQL Server;
  • View the database catalog and all the objects under it;
  • View and modify SQL Server settings and configuration options;
  • View and modify SQL Server engine objects;
  • Perform schema tasks on an instance of SQL Server like add and or remove columns and table objects;
  • Script databases dependencies;
  • Back up and restore databases;
  • Transfer database schemas and data.

The Smo namespace contains hundreds of classes, interfaces, structures and enumerators. To get a list of all of them, click here!

Important: The permissions you need to perform these tasks are the same that you need on the database side and are set up on the database level.

Main Classes

Two classes are the start points for every action performed against a Sql Server database: Server and Database.

Server class

The Server class represents a Sql Server instance. The following constructor overloads are available:

public Server();
public Server(ServerConnection serverConnection);
public Server(string name);

Database class

The Database class represents a database from a specific Sql Server instance. The following constructor overloads are available:

public Database();
public Database(Server server, string name);

How to…

In this example we are going to retrieve information about a specific database using both Server and Database classes. Although the core objects are under the Microsoft.SqlServer.Smo.dll assembly, some objects types are split between several assemblies.

  • The first task you need to perform is to add the following assemblies referenced to your project:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll

  • The following namespaces are necessary in your class:

using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

  • Create an instance of the Server class passing the instance name as parameter;
  • If you have a valid Sql Server instance created, it’s time to retrieve the database object using the databases collection property available under the Server class;
  • Once we have a valid instance of the database, the task is just a matter to access the properties and display information on the console window;

The final code must be similar to the following:

/// Creates a server class instance.
Server svr = new Server(@”MYPC\SQLEXPRESS”);

if ((svr != null) && (svr.Databases.Count > 0))
{
    /// Retrieves the database by the name.
    Database db = svr.Databases[“MyDatabaseName”];

    if (db != null)
    {
        Console.WriteLine(“Database information:”);
        Console.WriteLine();

        /// Displays database information.
        Console.WriteLine(“Database id: {0}”, db.ID);
        Console.WriteLine(“Database name: {0}”, db.Name);
        Console.WriteLine(“Database owner: {0}”, db.Owner);
        Console.WriteLine(“Database status: {0}”, db.Status);
        Console.WriteLine(“Database state: {0}”, db.State);
    }
}

Along this week I am going to post some code snippets that demonstrate how to use the Microsoft.SqlServer.Management.Smo namespace and its classes.

Stay tuned!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: