Introduction To SQL Server Management Object

Introduction

SQL Server Management Objects (SMOs) were introduced by Microsoft to manage SQL Server objects in C#. This is the managed code replacement of DMOs.
 
Advantages  

1. Uses the .NET Framework to manage SQL objects
2. Since it is managed code, no wrapper class is required

Limitations

1. The SMO must be installed

Procedure to create a SMO Project

1. Click on "References" then "Add Reference". Then click "Browse" then locate the SMO assemblies in the "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\" folder, and then select the following files. These are the minimum files that are required to build an SMO application:
   
   
   

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.SqlEnum.dll 

Connecting to Server

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

try

            {

                cbo.Items.Clear();

                ServerConnection conn = new ServerConnection();

                conn.ServerInstance = cboServer.Text;

                conn.LoginSecure = false; //using sqlserver auth

                conn.Login = txtLogin.Text;

                conn.Password = txtPassword.Text;

                Server srv = new Server(conn);

//get all the database list on that server

                foreach (Database db in srv.Databases)

                {

                    cbo.Items.Add(db.Name);

                }

            }

            catch (Exception err)

            {

Conn.ConnectionContext.Disconnect();



Connect to Database

private void btnConnect_Click(object sender, EventArgs e)

        {

Server server1 = null;  

ServerConnection conn = new ServerConnection();

            if (rbSQLServerAuthentication1.Checked == true)

            {

               

                conn.ServerInstance = cboServer1.Text;

                conn.LoginSecure = false;

                conn.Login = txtUser1.Text;

                conn.Password = txtPassword1.Text;              

                server1 = new Server(conn);

            }

            else

            {              

                conn.ServerInstance = cboServer1.Text;

                server1 = new Server(conn);

Retrieving databases, tables, SPs, UDFs and Properties

//List down all the databases on the server
foreach (Database myDatabase in myServer.Databases)
{
Console.WriteLine(myDatabase.Name);
}
Database myAdventureWorks = myServer.Databases["AdventureWorks"];
//List down all the tables of AdventureWorks
foreach (Table myTable in myAdventureWorks.Tables)
{
Console.WriteLine(myTable.Name);
}
//List down all the Stored Procedures of AdventureWorks
foreach (StoredProcedure myStoredProcedure in myAdventureWorks.StoredProcedures)
{
Console.WriteLine(myStoredProcedure.Name);
}
//List down all the user-defined function of AdventureWorks
foreach (UserDefinedFunction myUserDefinedFunction in myAdventureWorks.UserDefinedFunctions)
{
Console.WriteLine(myUserDefinedFunction.Name);
}
//List down all the properties and its values of [HumanResources].[Employee] table
foreach (Property myTableProperty in myServer.Databases["AdventureWorks"].Tables["Employee",
"HumanResources"].Properties)
{
Console.WriteLine(myTableProperty.Name + " : " + myTableProperty.Value);
}

Summary

SMOs are a powerful feature. Using this you can build many Database applications. Databases, drop databases and backup databases can be created. They support all the database objects.

SMOs support really everything you will need.

Indexes,
Constraints,
Relationships,
Permissions,
Stored Procedures,
Full Text Catalogues,
HTTP Protocol,
Triggers,
Mirroring,
Replication. and so on.