A downloadable file for this article is available here.
The entire demonstration solution has been developed using SQL Server 2005 Enterprise Edition and Visual Studio 2005 Professional Edition on Windows Server 2003 Standard Edition. Note that I didn’t really test the solution on any of other versions/editions of similar suites of Microsoft products.
What the hell are “DMO” and “SMO”?
SQL Distributed Management Objects (SQL-DMO) is a collection of objects encapsulating Microsoft® SQL Server™'s database and replication management. SQL-DMO is a dual interface COM, in-process server implemented as a dynamic-link library (DLL). SQL-DMO encapsulates SQL Server components, presenting the attributes of the component piece to you as the properties of an object instance. We can alter the properties of the instance, or use object methods to automate SQL Server administration, including replication. Now, this whole issue is becoming as an old story.
The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model. Microsoft defines SMO as follows: “SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications.” In fact, we can consider SMO to be further enhanced than DMO. The most wonderful feature of SMO is that it is implemented as a .NET assembly.
If you would like to work with DMO (or SQL-DMO), we need to work with some of the COM interoperability issues together with .NET (which is a bit complicated in certain cases). Coming to the case of SMO, we can straight away integrate it into any .NET based applications. SMO is also compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, which makes it easy for you to manage a multi-version environment. In fact, the SMO model supersedes and replaces SQL-DMO. If you are interested in working with SQL-DMO, you can look at another article of mine dedicated to it at http://www.aspfree.com/c/a/VB.NET/Simple-BackupRestore-Utility-With-SQLDMO- Using-VBNET/1/
To work with SMO, we should learn about the hierarchy of the SMO Object Model. Within the huge hierarchy of the SMO Object model, “server” objects stays at the top. The “server” object generally needs to connect “SQL Server” and from there on, we can retrieve or manage SQL Server database information very easily.
The following sections shall help you to retrieve SQL Server database information using SMO together with Visual Basic 2005. Let’s start now.
Actually, it is a very simple task to connect any SQL Server database (7.0 onwards) using SMO. We just need to refer certain SMO assemblies, and that should work fine for us. You can proceed with the following steps to start with Visual Studio 2005.
Open Programs -> Microsoft Visual Studio 2005 -> Microsoft Visual Studio 2005.
Go to File -> New -> Project.
Within the New Project dialog, select “Visual Basic” as Project Type and “Windows Application” as Template.
Provide an appropriate Project Name (something like “SMOSample01”) and Solution Name (along with location) and click OK. A Solution is created with a WinForm named “Form1”.
Go to Solution Explorer, right click on the project and go to “Add Reference” (as shown in Fig 1).
Within the “Add Reference” dialog, search and select all of the following assemblies:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum
Finally, click OK.
If you go to Project->Properties->References, it should look like the following image (Fig 2):
In some situations, you may also need to refer the following assemblies (depending upon your requirements):
After completing all the steps in the previous section, create a layout with only a button (“btnConnect”) and multi-lined text box as shown in the following inage (Fig 3):
Now, it is the time to write some code! Let us switch to the code window (by pressing F7) and modify as shown below:
Imports Microsoft.SqlServer.Management.Smo
Public Class Form1
Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
That’s it. You are finished. Execute the solution by pressing F5. Once you click on the “Connect” button, it should connect to the default instance of SQL Server and retrieve information as shown in the following image (Fig 4):
Let us go through some of the important statements within the above code snippet. The first statement from the code is the following:
Imports Microsoft.SqlServer.Management.Smo
We need the above statement to “import” all classes and other declarations to work with our form. In fact, we need to consider that the “Microsoft.SqlServer.Management.Smo” namespace is the heart of SMO.
Dim svr As Server = New Server()
The above statement creates a new object “svr” based on the class “Microsoft.SqlServerManagement.Smo.Server”. When the object (“svr”) gets created, it automatically tries to connect to the default instance of SQL Server on the OS where our SMO application is running. It tries to connect with “Windows Authentication” by default (also called “Secured Connectivity”).
If you wanted to connect to another instance, you can modify the above statement as follows:
Dim svr As Server = New Server(“192.168.1.2\SQL2K5”)
The above statement tries to connect to the instance named “SQL2K5” existing at “192.168.1.2” (still based on “Windows Authentication”).
If you would like to connect by providing SQL Server authentication, you may need to modify your code as shown here:
Dim svr As Server = New Server(“192.168.1.2\SQL2K5”) svr.ConnectionContext.LoginSecure = false
Once you connect to a SQL Server instance, you can retrieve information of that instance with the following properties existing in “Microsoft.SqlServer.Management.Smo.Server” class.
Name
InstanceName
Information.Product
Information.VersionString
Information.Edition
Information.Language
Since the above properties are self-explanatory, I shall leave them without any explanation. I don’t want to stop here. We shall further extend our solution to get more important information from the same database instance. Let us proceed to the next section.
The previous code just introduced you to connecting to an instance using SMO. The information that we retrieved in the previous code is not of much practical use. Let us further extend the same solution with one more “windows form” which retrieves all “database” related information present in a particular SQL Server database instance.
Don’t forget to add a new form (such as “form2”) to your existing solution and modify your project properties so that it will be the “start up form” during execution. The layout of the form will be very similar to that of the one I designed in the previous section.
Open a code window (by pressing F7) and modify the code in such a way that it looks something this:
Imports Microsoft.SqlServer.Management.Smo
Public Class Form2
Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click
“Microsoft.SqlServer.Management.Smo.Databases” is a collection; it contains the list of all databases existing within the connected instance. You can retrieve each of those using a loop. For every iteration, the loop fetches a particular “database” into “db.” We extract information about that “database” using its properties and finally add the same to the textbox.
Summary
In this article, I touched only the basics of SMO available in Microsoft SQL Server 2005. You can explore further by referring to SQL Server 2005 books online at Microsoft's website. You can also develop your own web-based SQL Server database management tool using ASP.NET 2.0 without changing the code at all. In fact, it all works with .NET.
You can extend the possibilities even further to Smart Clients such as Smart Phone, Pocket PC, Tablet PC, and so on. If you are familiar with WMI, you can extend the possibilities to that level as well.
My forthcoming articles (related to this subject) will focus on in-depth discussion along with developing some small practical utilities (which may be essential in certain situations). So, do not forget to check this site frequently :)
Any comments, suggestions, ideas, improvements, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
MS SQL Server 
/ 14 
ASP.NET Export to Excel and Word using VB.NET and C#
