Using the SMO API

Home
Advertise
Contact Us
Site Map

Using the SMO API...

The new SMO APIs can be used to interface with SQL Serer to perform a multitude of actions.

By: Brian Mains

Date: February 26, 2006

Download the code.

Printer Friendly Version

With the Microsoft SQL Server 2005 product, a new set of .NET API's are available. These API's can be used to interface with a SQL Server (called SMO) to perform a multitude of actions. For example, the APIs can retrieve all of the databases, tables, and columns that reside on a specific server, as well as create and manage them. Basically, the APIs can do what the front-end GUI can do, which are a lot of features. So, to illustrate, the code I created uses the SMO API to retrieve information about all of the databases, tables, and columns for the SQL Express database that is installed with Visual Studio .NET and Visual Web Developer.

To do this, you start with the ServerConnection and Server objects. The ServerConnection class establishes the connection to the server, which the Server class represents that server and all of its attributes. In the page I created, I used the Server class to iterate through all of the databases in the SQLExpress instance; however, it has more properties than just that.

ServerConnection conn = new ServerConnection(@".\sqlexpress");
Server server = new Server(conn);

foreach (Database db in server.Databases)
{
:TreeNode dbNode = new TreeNode(db.Name, "d:" + db.Name);
:tv.Nodes.Add(dbNode);
}

The Databases collection property exposes all the databases on the server, which the code sample iterates through each one, creates a TreeNode object to represent it, and adds it to a TreeView control. The Database class itself has a list of tables that belong to it, which is iterated and added to the TreeView control as such:

foreach (SMO.Table table in db.Tables)
{
:TreeNode tableNode = new TreeNode(table.Schema + '.' + table.Name, "t:" + table.Schema + '.' + table.Name);
:dbNode.ChildNodes.Add(tableNode);
}

Table name is put in the form of "<schema name>.<table name>z" to show distinction. You will also notice the "t:" (and "d:" before); this has a key use and is mentioned later. The last level of looping is the retrieval of columns, which are added to the proper table node.

foreach (Column column in table.Columns)
{
:TreeNode columnNode = new TreeNode(column.Name, "c:" + column.Name);
:tableNode.ChildNodes.Add(columnNode);
}

Pretty simple? It really is straightforward and easy to do. But obviously we want more than that; we want to see information about a database, table, or column that we select. How do we do this? Well, in the tables and columns node creation (previous two code samples), you will see the creation of a TreeNode, as mentioned above. The constructor is taking two arguments: the text and a key. The key is the key to finding out what the object is. So upon selection, the key has either a d:", "t:", or "c:" that we use to decipher the appropriate type. This is important because when the appropriate node is selected, we have to backtrack to get the parent nodes, reestablish the connection to the database, and reiterate through the collections. For example, when selecting a column, we need to get the name of the parent table and database, and then loop through all of the child collections to get the right Column object for the right Database. Remember that the TreeView control mimics the database structure exactly, so when getting the reference to the selected column, the Parent property contains the reference to the table name, and reference that node's Parent property to get the database name.

When the SelectedNodeChanged event of the TreeView control fires, does all of the above; it gets the parent node names, reestablishes the connection, and gets the appropriate SMO object. Below is how you get the right reference for the appropriate name through the collection.

Database db = server.Databases[dbNode.Text];
SMO.Table table = db.Tables[tableName];
Column column = table.Columns[node.Text];

Upon retracing this, we render some information about the selected object. I'll only show how this works for the database; you can see in the code sample how it works for all three. I'm referencing properties about the object directly to a label.

l.Text = "Name: " + db.Name + "<br>";
l.Text += "Owner: " + db.Owner + "<br>";
l.Text += "Version: " + db.Version.ToString() + "<br>";

When dealing with this, remember the natural tree order of Server > Database > Table > Column. You may ask: where is the schema? It is a property of the Table object (a string value). In our example, I appended it to the table name; however, it needs to be removed when referencing the name in the collection (db.Tables[name]).

That's what it takes to work with the SMO API; this example can also work with a SQL Server 2000 (I was surprised) or 2005 server, provided you have installed the APIs on the machine you are working on. Just change the ServerConnection path to the database name, and try it out.