10.1. Programming SMO Instance Classes for Administering Data Storage Objects

The following SQL Server objects are considered to store data. They are used to identify the SMO instance classes that administer SQL Server data storage objects.

• SQL Server instances

• Databases

• Schemas

• Tables

• Views

• Columns

• Indexes

• Foreign keys

• Check constraints

• Rules

• Stored procedures, extended stored procedures, and numbered stored procedures

• DML and DDL triggers

• User-defined aggregates, user-defined functions, and user-defined types

• Data types

• System data types

Figure 10-1 shows the relationship between SMO instance classes used to administer the preceding list of SQL Server objects. A reference to these classes is included in the "SMO Instance Classes for Administering Data Storage Objects Reference" section, later in this chapter.

The following subsections show how to programmatically use SMO instance classes for data storage. The examples in this section are all built using Visual Studio 2005. You need a reference to the following assemblies to compile and run the examples:

• Microsoft.SqlServer.ConnectionInfo

• Microsoft.SqlServer.Smo

Additional assembly references for examples will be indicated where required.

10.1.1. Connecting to and Disconnecting from SQL Server

This example demonstrates how to instantiate an SMO instance class and iterate through a collection. The example connects to the local SQL Server instance, lists the databases on the instance, and disconnects from the instance:

    using System;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server(  );

            DatabaseCollection dbs = server.Databases;

            foreach (Database db in dbs)
                Console.WriteLine(db.Name);

            server.ConnectionContext.Disconnect(  );

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey(  );
        }
    }

Figure 10-1. SMO instance classes for data storage hierarchy

Results are shown in Figure 10-2.

The Server class is the top-level class in the SMO hierarchy, and represents a SQL Server instance. The Server instance is used to access the collection of Database objects in the DatabaseCollection object and enumerate the names of the databases on the server. The SMO classes used to manage SQL Server databases are described later in the chapter, in the section "SMO Instance Classes for Administering Data Storage Objects Reference."

Figure 10-2. Results for listing server databases example

The ServerConnection object (represented by the Server.ConnectionContext property) contains the information needed to connect to and disconnect from a SQL Server instance. You can reuse a ServerConnection object, which is helpful if the connection information that it contains is extensive. You do not need to call the Connect( ) method on the ServerConnection object. SMO will automatically open and close a connection to the server as required. The Disconnect( ) method of the ServerConnection class explicitly disconnects the connection instead of simply allowing it to disconnect automatically when the connection goes out of scope.

The Server object constructor has three overloads. The default constructor (used in the preceding example) automatically tries to connect to the default SQL Server instance with default connection settings.

The second overload specifies the SQL Server instance name as a constructor argument, as shown in the following example:

    Server server = new Server("localhost");

The third overload creates the Server object by using a ServerConnection object, as shown in the following example:

    ServerConnection sc = new ServerConnection(  );
    sc.ServerInstance = "localhost";
    Server server = new Server(sc);

10.1.2. Navigating the Server Hierarchy

This example iterates over all databases on the local SQL Server instance, listing the tables and columns in each:

    using System;
    using System.Data;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");

            foreach (Database db in server.Databases)
            {
                Console.WriteLine("DATABASE: " + db.Name);
                foreach (Table t in db.Tables)
                {
                    Console.WriteLine("  TABLE: " + t.Name);
                    Console.WriteLine("    COLUMNS:");
                    foreach (Column c in t.Columns)
                        Console.WriteLine("    " + c.Name);
                }
                Console.WriteLine(  );
            }

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey(  );
        }
    }

Partial results are shown in Figure 10-3.

Figure 10-3. Partial results for navigating server hierarchy example

The Database object exposes a collection of Table objects representing the tables in the database. Each Table object in turn exposes a collection of Column objects representing the columns in the table. Accessing the other collections of data storage objects is similar to accessing the Table and Column collections. Figure 10-1, earlier in the chapter, shows the class hierarchy, which will help you understand the relationships among the classes, and the "SMO Instance Classes for Administering Data Storage Objects Reference" section, later in this chapter, provides more detailed information.

10.1.3. Enumerating Database Properties

This example enumerates the properties of the AdventureWorks database on the local SQL Server instance, using the Database.Properties collection:

    using System;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");

            DatabaseCollection dbs = server.Databases;
            Database db = dbs["AdventureWorks"];
            foreach (Property p in db.Properties)
                Console.WriteLine(p.Name + ": " + p.Value);

            Console.WriteLine(Environment.NewLine + "Press any key to continue.");
            Console.ReadKey(  );
        }
    }

Results are shown in Figure 10-4.

10.1.4. Enumerating Database Objects

This example enumerates all objects in the AdventureWorks database:

    using System;
    using System.Data;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");

            Database db = server.Databases["AdventureWorks"];

            using (DataTable dt = db.EnumObjects(  ))
            {
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                        Console.WriteLine(dt.Columns[i].ColumnName +
                            ": " + row[i]);
                    Console.WriteLine(  );
                }
            }

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey(  );
        }
    }

Figure 10-4. Results for enumerating database properties example

Partial results are shown in Figure 10-5.

Figure 10-5. Results for enumerating database example

The EnumObjects( ) method of the Database class returns a DataTable with the columns described in Table 10-1.

Table 10-1. DataTable columns returned by Database.EnumObjects( )

Column	Description
DatabaseObjectTypes	A value from the DatabaseObjectTypes enumeration identifying the object type
Schema	The schema to which the database object belongs
Name	The name of the database object
Urn	The Uniform Resource Name (URN) for the database object

The EnumObjects( ) method has three overloads. Besides the no-argument version that you've just seen, there are two that let you specify the type of object to enumerate as a value from the DatabaseObjectTypes enumeration. The following are the prototypes for the EnumObjects( ) methods:

    Database.EnumObjects(  )
    Database.EnumObjects(DatabaseObjectTypes)
    Database.EnumObjects(DatabaseObjectTypes, SortOrder)

where:

DatabaseObjectTypes

Enumeration of database object typesfor example, All, DatabaseRole, Schema, Table, and View. If not specified, the default is All. For a complete list, see Microsoft SQL Server 2005 Books Online.

SortOrder

Enumeration of sort ordersName, Schema, Type, and Urn.

For example, the following line of code returns information about tables in the database:

    db.EnumObjects(DatabaseObjectTypes.Table)

DatabaseObjectTypes enumeration values can be logically ORed to return a table containing multiple object types.

10.1.5. Creating a Database Object

This example demonstrates how to create new SMO instance objects . It creates a table named SmoTestTable in the ProgrammingSqlServer2005 database. The table contains two columns, with a primary key index on the first. You need to add a reference to Microsoft.SqlServer.SqlEnum to compile and execute this example.

    using System;
    using System.Data;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");
            Database db = server.Databases["ProgrammingSqlServer2005"];

            // create a table
            Table t = new Table(db, "SmoTestTable");

            DataType dt = new DataType(SqlDataType.Int);
            Column c = new Column(t, "ID", dt);
            c.Nullable = false;
            t.Columns.Add(c);

            dt = new DataType(SqlDataType.VarChar, 100);
            c = new Column(t, "Name", dt);
            t.Columns.Add(c);

            t.Create(  );

            // create a primary key index on the table
            Index i = new Index(t, "PK");
            IndexedColumn ic = new IndexedColumn(i, "ID");
            i.IndexedColumns.Add(ic);
            i.IndexKeyType = IndexKeyType.DriPrimaryKey;
            i.Create(  );

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey(  );
        }
    }

The process for creating SMO instance objects typically follows the pattern in this example:

In this example, a new table is created by instantiating a Table object, passing in arguments for the database in which the table is created and the name of the table. Columns are added to the table by instantiating a Column object, passing in arguments for the table to which you are adding them, the name of the column, and the data type as a DataType instance. Finally, the table is created in the database by calling the Create( ) method of the Table object.

The primary key index is created by instantiating an Index, passing in arguments for the table in which the index is created and the name of the index. A column is added as part of the index by instantiating an IndexedColumn object, passing in arguments for the index to which it is added, and the name of the column. Next, you specify the type of index you want by setting the IndexKeyType property to a value from the IndexKeyType enumerationDriPrimaryKey, DriUniqueKey, or None. Finally, you create the index by calling the Create( ) method of the Index object.

The Alter( ) method of SMO instance objects is used to update the object properties with new values. The Drop( ) method of SMO instance objects is used to drop the object.

10.1.6. Checking Database Table Integrity

This example checks the integrity of all tables and their indexes in AdventureWorks and writes the errors (if any) to the console:

    using System;
    using System.Collections.Specialized;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");

            DatabaseCollection dbs = server.Databases;
            Database db = dbs["AdventureWorks"];

            StringCollection sc = db.CheckTables(RepairType 
.None);

            foreach (object o in sc)
                Console.WriteLine("{0}", o);

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey(  );
        }
    }

There is no output from this example unless you run it against a corrupted database, in which case the errors are output to the console.

The CheckTables( ) method of the Database class tests the database pages implementing storage for all tables and indexes defined on tables. The CheckTables( ) method takes a single argumenta value from the RepairType enumeration described in Table 10-2.

Table 10-2. RepairType enumeration

Value	Description
AllowDataLoss	Attempt to repair all data pages even if data is lost.
Fast	Repair data pages in fast mode. This option is for backward compatibility.
None	Do not repair data pages.
Rebuild	Repair data pages by rebuilding them.

The CheckTables( ) method is implemented using the T-SQL DBCC CHECKDB statement. The StringCollection returned from the method is a collection of error messages returned by DBCC CHECKDB. The database must be in single-user mode to use any of the three repair options (other than None).

10.1.7. Transacting SMO Operations

SMO commands can be wrapped in transactions so that multiple commands either succeed or fail as a group. This example transacts a set of SMO commands that creates a table named SmoTestTable in the ProgrammingSqlServer2005 database. If the user enters a Y at the prompt, the transaction commits and the table is created. Otherwise, the transaction rolls back.

    using System;
    using System.Data;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");
            server.ConnectionContext.BeginTransaction(  );

            Database db = server.Databases["ProgrammingSqlServer2005"];

            // create a table
            Table t = new Table(db, "SmoTestTable2");

            DataType dt = new DataType(SqlDataType.Int);
            Column c = new Column(t, "ID", dt);
            c.Nullable = false;
            t.Columns.Add(c);

            dt = new DataType(SqlDataType.VarChar, 100);
            c = new Column(t, "Name", dt);
            t.Columns.Add(c);

            t.Create(  );

            Console.WriteLine("Commit new table (Y/N)?");
            ConsoleKeyInfo cki = Console.ReadKey(  );

            if ((char)cki.Key == 'Y')
            {
                server.ConnectionContext.CommitTransaction(  );
                Console.WriteLine(Environment.NewLine + "Table created.");
            }
            else
            {
                server.ConnectionContext.RollBackTransaction(  );
                Console.WriteLine(Environment.NewLine + "Table not created.");
            }

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey(  );
        }
    }

The Server class exposes a ServerConnection object through its ConnectionContext property. The ServerConnection object lets you programmatically interrogate and manage the connection with the SQL Server instance. ServerConnection also provides support for transactions through its BeginTransaction( ), CommitTransaction( ), and RollbackTransaction( ) methods.

10.1.8. Capture Mode

SMO applications can capture and record T-SQL statements that are equivalent to the operations performed by the SMO statements. This example captures the T-SQL commands generated by SMO programming and outputs them to the console window:

    using System;
    using System.Data;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");
            server.ConnectionContext.SqlExecutionModes 
 = SqlExecutionModes.CaptureSql;
            server.ConnectionContext.Connect(  );

            Database db = server.Databases["ProgrammingSqlServer2005"];

            // create a table
            Table t = new Table(db, "SmoTestTable");

            DataType dt = new DataType(SqlDataType.Int);
            Column c = new Column(t, "ID", dt);
            c.Nullable = false;
            t.Columns.Add(c);

            dt = new DataType(SqlDataType.VarChar, 100);
            c = new Column(t, "Name", dt);
            t.Columns.Add(c);

            t.Create(  );

            // create a primary key index on the table
            Index i = new Index(t, "PK");
            IndexedColumn ic = new IndexedColumn(i, "ID");
            i.IndexedColumns.Add(ic);
            i.IndexKeyType = IndexKeyType.DriPrimaryKey;
            i.Create(  );

            // output the captured T-SQL
            foreach (string s in server.ConnectionContext.CapturedSql.Text)
                Console.WriteLine(s);

            Console.WriteLine(Environment.NewLine + "Press any key to continue.");
            Console.ReadKey(  );
        }
    }

Results are shown in Figure 10-6.

This example uses the same table creation code as the example in the "Creating a Database Object" section. The generated T-SQL code is captured and output. It is not sent to the server and it is not executed.

The mode is controlled by the SqlExecutionModes property of the Server object ConnectionContext. It takes a value from the SqlExecutionModes enumeration that specifies whether the code is executed (ExecuteSql), captured (CaptureSql), or both (ExecuteAndCaptureSql).

Figure 10-6. Results for capturing T-SQL example

Captured SQL is written to the CapturedSql object (exposed as a property of the Server object's ConnectionContext) and accessed through the CapturedSql.Text property, which exposes it as a StringCollection object.

10.1.9. Event Notification

Events let you monitor the SQL Server Database Engine. Event monitoring can be set up for the following SMO instance classes, some of which are not described until Chapter 11:

Server

Database

Schema

Table

View

Index

Stored procedure

DML and DDL triggers

User-defined functions

User-defined types

Synonyms

Logins

Users

SQL assemblies

Application roles

Certificates

Partition functions and schemes

Subscribing to events for the different SMO instance classes follows a similar pattern:

This example shows how to configure a database-event handler and subscribe to it. You need to add a reference to Microsoft.SqlServer.SqlEnum to compile and execute this example.

    using System;
    using System.Data;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server(  );
            Database db = server.Databases["ProgrammingSqlServer2005"];

            DatabaseEventSet des = new DatabaseEventSet(  );
            des.CreateTable = true;
            des.DropTable = true;
            ServerEventHandler seh = new ServerEventHandler(OnDatabaseEvent);
            db.Events.SubscribeToEvents(des, seh);

            db.Events.StartEvents(  );

            // create a table with a single column
            Table t = new Table(db, "SmoTestTable3");

            DataType dt = new DataType(SqlDataType.Int);
            Column c = new Column(t, "ID", dt);
            c.Nullable = false;
            t.Columns.Add(c);

            t.Create(  );

            // drop the table
            t.Drop(  );

            db.Events.StopEvents(  );

            Console.WriteLine(Environment.NewLine + "Press any key to continue.");
            Console.ReadKey(  );
        }

        protected static void OnDatabaseEvent(object sender, ServerEventArgs e)
        {
            if (e.EventType.ToString(  ) == "CreateTable")
                Console.WriteLine("A table named " +
                    e.Properties["ObjectName"].Value + " was created.");
            else if (e.EventType.ToString(  ) == "DropTable")
                Console.WriteLine("A table named " +
                    e.Properties["ObjectName"].Value + " was dropped.");
        }
    }

Results are shown in Figure 10-7.

Figure 10-7. Results for database event example

The SubscribeToEvents( ) method of the DatabaseEvents object (exposed through the Events property of the Database class) takes two argumentsa DatabaseEventSet object that specifies the events to monitor, and a ServerEventHandler delegate that handles the database events with the OnDatabaseEvent( ) method.

Alternatively, the DatabaseEventSet object can be created from the sum of the corresponding properties in the DatabaseEvent class, as shown in the following code:

    DatabaseEventSet des = DatabaseEvent.CreateTable + DatabaseEvent.DropTable;

10.1.10. Handling Exceptions

This example shows how to catch and handle an SMO exception. An exception is raised when an attempt is made to create the database AdventureWorks, which already exists.

    using System;
    using System.Data;

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

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");

            Database db = new Database(server, "AdventureWorks");
            try
            {
                db.Create(  );
                Console.WriteLine(Environment.NewLine +
                    "Database created. Press any key to continue.");
            }
            catch (SmoException 
 ex)
            {
                Console.WriteLine(ex.SmoExceptionType 
.ToString(  ));
                Console.WriteLine(ex.Message);
                if (ex.InnerException != null)
                    Console.WriteLine(ex.InnerException.Message);
                Console.WriteLine(Environment.NewLine + "Press any key to continue.");
            }

            Console.ReadKey(  );
        }
    }

Results are shown in Figure 10-8.

Figure 10-8. Results for handling a database exception example

The SmoException class in the Microsoft.SqlServer.Management.Smo namespace represents an exception raised during a SMO operation. The SmoException class inherits from the Exception class and has additional public properties, listed in Table 10-3.

Table 10-3. SmoException class additional public properties

Property	Description
HelpLink	The help link for more information from Microsoft about the SMO exception.
ProductName	The name of the product that caused the SMO exception.
SmoExceptionType	The type of SMO exception. This is a value from the SmoExceptionType enumeration.

The SmoExceptionType enumeration values are described in Table 10-4.

Table 10-4. SmoExceptionType enumeration

Value	Description
CollectionNotAvailableException	Attempting to retrieve a collection that is not available.
FailedOperationException	An operation fails.
InternalEnumeratorException	An error occurs during an enumeration operation.
InternalSmoErrorException	An internal SMO exception occurs.
InvalidConfigurationFileEnumeratorException	An invalid configuration file is encountered.
InvalidPropertyUsageEnumeratorException	An object property is accessed incorrectly.
InvalidQueryExpressionEnumeratorException	An invalid query expression is encountered.
InvalidSmoOperationException	An invalid SMO operation is called.
InvalidVersionEnumeratorException	The version is not valid.
InvalidVersionSmoOperationException	An invalid version of an SMO operation is called.
MissingObjectException	An object is missing.
PropertyCannotBeRetrievedException	A property cannot be retrieved.
PropertyNotSetException	A property value has not been set and is required to be set.
PropertyReadOnlyException	Attempt to set a read-only property.
PropertyTypeMismatchException	Attempt to set a property with a value having an incorrect data type.
PropertyWriteException	An error occurs updating the value of a property.
ServiceRequestException	An error occurs during a service request.
SmoException	An SMO exception occurs.
UnknownPropertyEnumeratorException	An unknown property enumerator is encountered.
UnknownPropertyException	An unknown property is requested.
UnknownTypeEnumeratorException	An unknown type enumerator is encountered.
UnsupportedFeatureException	An unsupported feature is requested.
UnsupportedObjectNameException	An object name is not supported.
UnsupportedVersionException	An unsupported version of SQL Server is encountered.
WrongPropertyValueException	A property is assigned an incorrect value.

The exception classes derived from the SmoException class have the same names as the SMO exception types listed in Table 10-4. For example, there is a class named CollectionNotAvailableException that is derived from the SmoException class.