What are the Sql Server Management Objects?

Baldi
Posted by in .NET Framework category on for Beginner level | Views : 7799 red flag

What are the Sql Server Management Objects? It is a .NET library from Microsoft which allows you to access and manage all objects of the Microsoft SQL Server. It allows you to create, alter and drop objects like views, tables, jobs from your .NET application in a easy way.


 Download source code for What are the Sql Server Management Objects?

Introduction

SQL Server Management Objects (also called SMO) is a .NET library which allows you to access and manage all objects of the Microsoft SQL Server. Such a library also exists for Analysis Services (AMO) and SQL Server Replication (RMO). Why do I write about it? I did a project which used SMO a lot, so it is a sort of brain dump for me. It hope this article will save you the time I spent at the beginning of my project.  

SMO supports SQL Server 2000, 2005 and 2008. The required Dll's are in assembly folder of your SQL Server Version (they are not registered in the GAC) directory. To execute the code samples, you must have a SQL Server installed on your computer, which allows you to login with integrated security. Otherwise you will have to change server connection information.

The Server object

The library has the same hierarchical arrangement as you see it in the SQL Server Management Studio. At the top of this hierarchy is the Server object.

private Server Connect()
{
var connection = new ServerConnection();
connection.ServerInstance = "localhost";
connection.LoginSecure = true;
connection.Connect();
return new Server(connection);
}

The server object object contains all information about the SQL Server, a list with all database objects, a list with all roles, a reference to the Job Server and so on. You get a new Server object with a ServerConnection. The ServerConnection can also be instantiated with a SqlConnection (and also provides more overloads). So the ServerConnection contains the similar information than the connection string normally contains.

Database, Tables, Views and other DB objects

The sample below lists all all databases of the server, all tables and views which the databases contains. Don't execute this code on a server which contains a lot of databases, it could take some minutes!

public void PrintDatabases()
{
var server = Connect();

foreach (Database db in server.Databases)
{
Console.WriteLine("Database: {0}", db.Name);

foreach (Schema schema in db.Schemas)
{
printTables(schema, db);
}

printViews(db);
}

server.ConnectionContext.Disconnect();
}

private void printTables(Schema schema, Database db)
{
foreach (Table table in db.Tables)
{
if (schema.Name.Equals(table.Schema))
{
Console.WriteLine(" Table: {0}.{1}", table.Schema, table.Name);
printColumns(table.Columns);
}
}
}

private void printColumns(ColumnCollection columns)
{
foreach (Column c in columns)
{
Console.WriteLine(" Column: {0}", c.Name);
}
}

private void printViews(Database db)
{
foreach (View view in db.Views)
{
Console.WriteLine(" View: {0}", view.Name);
printColumns(view.Columns);
printIndexes(view.Indexes);
}
}

Also here you see the hierarchical structure. The table object contains the columns but also the indexes which the table contains. The index of each collection allows to to access every object with the name. So if I want to access the table "Contact" with the schema "Person" in the database "AdventureWorks" it would look like this.

public Table GetContact()
{
return server.Databases["AdventureWorks"].Tables["Contact", "Person"];
}

More about SMO

A lot more examples provides MSDN separated in programming specific tasks. There you will also find a great overview of the object model. My next post will show how you can modify objects which lives in the SQL Server.

Page copy protected against web site content infringement by Copyscape

About the Author

Baldi
Full Name: Mattia Baldinger
Member Level: Starter
Member Status: Member
Member Since: 5/25/2010 7:56:55 AM
Country: Switzerland
http://www.mbaldinger.com
http://www.mbaldinger.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)