SQL Server Managerment Objects (SMO) Explained with Example

Assumptions:
1..NET2.0/4.0 and vs 2005/2008/2010/above is installed on system. 
 Code Download: Click Here to download entire solution
2. Knowledge .Net Basics with C#and basic SQL Server
Note : I am using Microsoft vs2013 Express for windows application development.You can use visual studio any version starting from 2005

---

Introduction:

 I am going to start series of articles which will guide you how to use SMO Object model in .net world and Let us end up with creating one successful tool like query analyzer of our own using SMO(SqlServer Management Object). 

Before implementation it is necessary to understand certain concepts of SMO Object model and capabilities of this model

What is SMO ?  SMO(SqlServer Management Object) name itself indicated that Object model is responsible for managing Sql Server Programmatically like you can create Database Objects dynamically and perform lots common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more..

• How to use Smo Object Model in .NET
• What Tasks You can perform using SMO(SqlServer Management Object)
• Create Sql Query Analyzer Like Tool

Before we will discuss anything about SMO,let me clear you one thing ,When ever you are going to use SMO Object Model be sure your Program includes Reference to Microsoft.SqlServer.Management.Smo namespace. Actually there are two ways to do this. The first one is by using SQL-DMO (Distributed Management Objects) which is a set classic(old way) classes that were used to programmatically manipulate and interact with SQL Servers, usually it was used before .Net World. With time and arrival of great .NET CLR Platform, lots of new changes were made to SQL-DMO Object model, which was named as SMO[Server Management Objects]sure,name itself indicates object model is used to manipulate SqlServer programmatically .So we can say SQL Server Management objects (SMO) exposes the functionality of SQL Server database and replication management. SMO can be used to automate repetitive tasks or commonly performed administrative tasks. The SQL SMO is implemented as a .NET assembly and the model extends and replaces the SQL Server Distributed Management object (DMO) of the earlier versions.
There are certain features that all Server Management objects share such as running methods, setting properties and manipulating collections. Specific tasks can be programmed using SMO objects. These include complex subjects that are required by programs with specialized functions such as backing up, monitoring statistics, replication, managing instance objects and setting configuration options.

SETUP YOUR DEVELOPMENT ENVIRONMENT: 

1. Open visual studio or visual express and add references of below listed namespaces to your project as shown below fig
   

 Let us Start Implementation

1. open new windows form  and drag and drop required controls on form as shown below
   
   
   

Once you are done with designing this UI , you can start writing down your code using SMO Object model with your language of choice .

First we need to write code on Form load to establish connection with sql sever to perform operation on objects like database table view,procedure... - accessing databases is very simple. Most of the SMO objects are stored in a Parent/Child Collection ownership.
A Server has got a collection of databases (The databases Parent is the Server), a database has got a collection of Tables, a Table has got a collection of Columns.....

The following code allow you to connect to SQL Server with the SQL

//Server authentification :
erverConnection conn;
Server sqlserver;

conn = new ServerConnection();
conn.ServerInstance = "My Server";
 conn.Login = "My login";

 conn.Password = "My Password";
 conn.LoginSecure =true;
 conn.Connect();
 sqlserver = new Server(conn);

//this Code adds all known Databases to a Listview of UI shown above

listView1.Clear();

listView1.Columns.Clear();
//building the Columns
listView1.Columns.Add("Name");
listView1.Columns.Add("# of Tables");
listView1.Columns.Add("Size");
//loop over all Databases
foreach( Database db in server.Databases )
{
    //add the Data to the listview.
    ListViewItem item = listView1.Items.Add(db.Name);
    item.SubItems.Add( db.Tables.Count.ToString() );
    item.SubItems.Add(db.Size.ToString());

}

Let us list of SQL servers available on the network

public void GetServers()
{ 
// Get a list of SQL servers available on the network 
DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false);
foreach (DatRow row in dtServers.Rows) 
{ string sqlServerName = row["Server"].ToString(); 
if  (row["Instance"] != null && row["Instance"].ToString().Length > 0) 
sqlServerName += @"\" + row["Instance" ].ToString();
 } 
}           

Let us Retrieve list of databases

public  List<string>GetDatabaseNameList()
 { 
List<string>dbList = new  List<string>();
foreach(Database db in server.Databases)
dbList.Add(db.Name); 
return dbList; 
}       

Let us Retrieve list of tables within database  using SMO

public void List<string>GetTableNameList(Database db) 
{ 
List<string> tableList =new  List<string>(); 
foreach (Table table in db.Tables) 
tableList.Add(table.Name);
return tableList; 
}        

Let us Retrieve list of StoredProcedures using SMO

public void List<string> GetStoredProcedureNameList(Database db) 
{ 
List<string> storedProcedureNameList = new List<string>(); 
foreach (StoredProcedure storedProcedure in db.StoredProcedures)
 storedProcedureNameList.Add(storedProcedure.Name); 
return storedProcedureNameList;
}; 

Let us Retrieve list of views within database  using SMO

public void List<string>GetViewNameList(Database db) 
{ 
List<string> viewNameList = new  List<string>(); 
foreach (View view in db.Views)
 viewNameList.Add(view.Name);
 return viewNameList;
 }          

Let us Retrieve Column Names of Table using SMO

public void List<string>GetColumnNameList(Table table) 

{ 
List<string> columnList = new List<string>(); 
 foreach (Column column in table.Columns)
 columnList.Add(column.Name); 
return columnList; 
}          

Let us Retrieve User Names using SMO

public void List<string>GetUserNameList(Database db)
 { 
List<string>userNameList = new  List<string>(); 
 foreach (User user in db.Users) 
userNameList.Add(user.Name); 
return userNameList;

 }

Next we will use other functionalities like database backup,restore...