Building First SMO (SQL Server Management Object) Application
By : Kasim Wirama, MCDBA, MCITP
With limitless idea exposed by classes in SMO, you could build full-fledged administrative tool which is similar or even more complete than SQL Server Management Studio (SSMS). Here I show important steps to build SMO application. These steps will apply from simplest until most complex SMO application.
To get started to build first application with SMO, let’s fire up Visual Studio 2008. Choose any C#/VB.NET project type. For example in this posting, I choose C# windows form application. Add Project Reference and pick up 4 DLLs, i.e. Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended, Microsoft.SqlServer.Management.Sdk.Sfc.
Switch on code behind of Form1 user interface and put 2 SMO-related namespace here :
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
On Form1 user interface, draw 3 textbox and 1 button, name them txtServer, txtUserName, txtPassword and btnLogin. Double click on btnLogin so that Visual Studio will automatically create “click” event handler for the button. Inside event handler, you specify the code below :
ServerConnection conn = new ServerConnection();
conn.ServerInstance = txtServer.txt;
conn.LoginSecure = false;
conn.Login = txtUserName.txt;
conn.Password = txtPassword.txt;
try{
Server localServer = new Server(conn);
Messagebox.Show (“connected”);
}
catch (SmoException exSMO){
MessageBox.Show (exSMO.ToString());
}
This code above just simply logins into specified SQL Server instance with supplied user name and password, and that’s SQL Server authentication mode. ServerConnection object (conn variable) represents instance of SQL Server and assign connection to instance of Server object (localServer variable). If connection is made with SQL Server authentication mode, you need to set LoginSecure property is false otherwise it is true (Windows authentication mode). For windows authentication mode, it is not necessary to supply value to Login and Password property.
Next code is “Server localServer = new Server (conn);”. It makes connection and if successfully authenticated, conn object will assign connection state into Server instance and pop up message “connected” to end-user. If failed to connect, then the code pass to SmoException.
That’s the simple SMO example for getting started. With important initial steps to setup SMO solution, you have basic knowledge to get ready to build more complex SMO application.