SMO | Object Browsing

Assalamu Alaykum..

  • What is SMO ?

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

  • What ‘ll we discuss in SMO ?

There are a lot of topics we ‘ll discuss isA about SMO, and the first one is how to connect to SQL Server using both Windows and SQL Server Authentications, and how to browse SQL objects (e.g: Databases, Tables, Views,  Columns, Functions, Triggers, Stored Procedures) using C#.

  • First Topic: Object Browsing

-Connect to SQL Server using both Windows and SQL Server Authentications.

ConnectToServer

Browse SQL Server Objects.

ObjectBrowser

Now we ‘ll dicuss the code rapidly cuz it’s well commented and easy to understand..

-How to Connect to SQL Server:

smo_server = new Server(ServerName);
if (!WindowsAuth)
{
      smo_server.ConnectionContext.LoginSecure = false;
      smo_server.ConnectionContext.Login = UserName;
      smo_server.ConnectionContext.Password = Password;
}

We declare an object of type Microsoft.SqlServer.Management.Smo.Server with constructor which takes Server Name as parameter. The server Authentication initially is set to Windows Authentication by default. To set it as SQL Server Authentication, set the Boolean property LoginSecure to false, and set User Name and Password.

-How to Browse SQL Server Objects:

//Enum all available databases with its tables and columns..
TreeNode Databases = new TreeNode("Databases");
foreach (Database Db in smo_server.Databases)
{
    TreeNode databasenode = new TreeNode(Db.Name, 1, 1);

    //Add Tables
    TreeNode Tablesnode = new TreeNode("Tables");
    //Add tables to database
    databasenode.Nodes.Add(Tablesnode);
    Tablesnode.Nodes.Add("expanding...");

    //Add View
    TreeNode Viewsnode = new TreeNode("Views");
    //Add views to database
    databasenode.Nodes.Add(Viewsnode);
    Viewsnode.Nodes.Add("expanding...");

    ...

    //Add database to database collection
    Databases.Nodes.Add(databasenode);
}
treeViewObjects.Nodes.Add(Databases);

Get all available databases by iterating on the collection  smo_server.Databases and add them to the TreeView..

if (CurrentNode.Text == "Tables")
{
   CurrentNode.Nodes.Clear();
   DatabaseName = CurrentNode.Parent.Text;

   foreach (Table table in smo_server.Databases[DatabaseName].Tables)
   {
      //Add table
      Tablenode = new TreeNode(table.Name);
      CurrentNode.Nodes.Add(Tablenode);
      //Add columns to table
      foreach (Column column in table.Columns)
      {
          Tablenode.Nodes.Add(column.Name);
      }
   }
}

Get all tables of a selected database by iterating on the collection smo_server.Databases[DatabaseName].Tables..

And So on…, The same for views, functions, stored procedures, …etc.

Download Source Code