SQLDMO For C#

Many times I have had a need to get at SQL Server details in my applications. Until recently I had to use API calls and bastardized ADO calls to get the information I needed. Now we have SQLDMO (SQL Distributed Management Objects) . Although not widely known or used, SQLDMO provides a very powerful set of functionality to do just about anything with an SQL Server from code. For the purposes of this example I will show how to retrieve a list of SQL Servers on your local network, how to connect to one, and how to retrieve a list of tables, stored procedures, or views from a server.

The SQLDMO object comes from the SQLDMO.dll that ships with SQL Server 2000. The dll itself is a COM object and you must reference it from your .net project as such. The IDE will create the necessary COM wrappers needed to use the library. NOTE: IF YOU USE THE STATEMENT "using SQLDMO;" IN YOUR APP YOU MAY GET AN ERROR.

All of the operations performed in the example use one or more of the following objects:

There are a multitude of objects available for actions such as backups and restores, but for the purpose of this article I decided to keep it simple to ease you into the world of SQLDMO.

Listing the available SQL Servers on your network is quite simple. First you need a references SQLDMO.Application object. Next you set an instance of SQLDMO.NameList to the return value of the SQLDMO.Application.ListAvailableSQLServers() method. The SQLDMO.NameList if a COM collection of the server names.

Keep in mind, calling COM objects is a little funky until you get used to it, but the conventions are similar with all of them. Here is example code which fills a combo box name cboServers with a list of all available SQL Servers on the local network:

//get all available SQL Servers
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
for(int i=0;i<sqlServers.Count;i++)
{
    object srv = sqlServers.Item(i + 1);
    if(srv != null)
    {
        this.cboServers.Items.Add(srv);
    }
}
if(this.cboServers.Items.Count > 0)
    this.cboServers.SelectedIndex = 0;
else
    this.cboServers.Text = "<No available SQL Servers>";

As you can see, this is quite simple. Just remember that COM collections start at an index of 1, not 0.

Connecting to a server and getting a list of databases is also fairly simple. The following code will take the chosen SQL Server in the combo box, connect to it (with a user name and password in 2 text boxes), and then poulates another combo box with a list of databases on the server.

//get all available databases from an SQL Server
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);
foreach(SQLDMO.Database db in srv.Databases)
{
    if(db.Name!=null)
        this.cboDatabase.Items.Add(db.Name);
}

Getting a list of objects by type is also a breeze with this library. Again, you make a connection to the database, and then you loop through the object collection.

//Get all Stored procedures - tables are in the Tables collection, views are in the Views collection
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);
for(int i=0;i<srv.Databases.Count;i++)
{
    if(srv.Databases.Item(i+1,"dbo").Name == this.cboDatabase.SelectedItem.ToString())
    {
        SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");
        this.lstObjects.Items.Clear();
        for(int j=0;j<db.StoredProcedures.Count;j++)
        {
            this.lstObjects.Items.Add(db.StoredProcedures.Item(j+1,"dbo").Name);
        }
        break;
    }
}