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

Advertisements

One Response to SMO | Object Browsing

  1. macromedia says:

    Perfect!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: