Click here to Skip to main content
Click here to Skip to main content

SMO - Manage your SQL Server!

, 31 May 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
A quick start tutorial on how to manage your Microsoft SQL Server 2000/2005 with C# and the Microsoft's SQL Server Management Objects (SMO)
Sample Image - SMODemo.jpg

Introduction

May I introduce you to SMO, the Framework provided by Microsoft to manage your Microsoft SQL Servers? This article is a quick start tutorial. SMO is a replacement of the SQL-DMO - it's now managed code and it supports the features of the SQL Server 2005. The intention of this article is to show what SMO is, explain some basic steps, and to make you hungry for more.

Advantages

  • .NET: Use your well known framework to develop your SQL Server applications.
  • OOP: It's much easier to browse the object hierarchy with IntelliSense than browsing hundreds of stored procedures.
  • Exceptions: SMO uses exceptions in an exemplary way.

Disadvantages

  • Requires .NET Framework to be installed on the client PC
  • Works only with Microsoft SQL Servers
  • Needs SMO to be installed

Note that most (or even all) features provided by the SMO are also available as stored procedures on the SQL Server. So you could say the SMO is a wrapper for the stored procedures on the SQL Server.

System Requirements

  • .NET Framework 2.0
  • SQL Server 2000 or 2005 (NOTE: SMO Supports features that are new in SQL Server 2005 and do not work with SQL Server 2000)
  • SMO needs to be installed

Skill Requirements

  • Basic C# knowledge + Windows Forms
  • Basic SQL Server knowledge
  • About 30 minutes of time

Adding a Reference to SMO

To use the SMO, you have to add it as a Reference to your Project. This screen shots have been made with Visual Studio 2005 Express Edition.

Solution Browser - > References -> Add Reference

Select SQL Server Components and Hit OK.

Getting Connected

The first thing we have to do is make a connection to our server.
Now you might be thinking "Hey, there is already a class existing to connect to a SQL Server - System.Data.SqlClient.SqlConnection", and you are all right - you can use this class to build your connection to the SQL Server.

Microsoft.SqlServer.Management.Smo.Server server;
///  <summary>
/// Initializes the field 'server'
/// </summary> 
void InitializeServer()
{
    // To Connect to our SQL Server - 
    // we Can use the Connection from the System.Data.SqlClient Namespace.
    SqlConnection sqlConnection = 
      new SqlConnection(@"Integrated Security=SSPI; Data Source=(local)\SQLEXPRESS");

    //build a "serverConnection" with the information of the "sqlConnection"
    Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = 
      new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);

    //The "serverConnection is used in the ctor of the Server.
    server = new Server(serverConnection);
}

Object Hierarchy

Once you have got a connection to your server - 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.....

//this Code adds all known Databases to a Listview

//clean up the listview first.
listView1.Clear();
listView1.Columns.Clear();

//building the Columns
listView1.Columns.Add("Name");
listView1.Columns.Add("# of Tables");
listView1.Columns.Add("Size");

//iterate 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());
}

This code shows how to enlist Backup Devices:

listView1.Clear();
listView1.Columns.Clear();

listView1.Columns.Add("Name");
listView1.Columns.Add("Location");

foreach (BackupDevice backupDevice in server.BackupDevices)
{
    ListViewItem item = listView1.Items.Add(backupDevice.Name);
    item.SubItems.Add(backupDevice.PhysicalLocation);
}

Create a New Database

Of course - we are not limited to getting information about our SQL Server - we can also create, drop and alter objects. Most SMO objects have 2 requirements - a valid (unique) Name and a valid Parent.

database.Name = dbName.Text;
database.Parent = server;
database.Create();

You see - SMO uses really compact code Smile | :) Now - let's create a backup device.

backupDevice.Parent = Server;
backupDevice.Name = "myBackupDevice";
backupDevice.PhysicalLocation = @"C:\myNewBackupDevice.bak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;
backupDevice.Create();

Scripting with T-SQL!

In some cases, you might want to have a T-SQL script of an operation. Let's take the example from above - we want a script for adding a Backup Device to our SQL Server.

backupDevice.Parent = Server;
backupDevice.Name = "myBackupDevice";
backupDevice.PhysicalLocation = @"C:\myNewBackupDevice.bak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;
StringCollection strings =  backupDevice.Script();
//results:
// strings [0] = "EXEC master.dbo.sp_addumpdevice  @devtype = N'disk',
// @logicalname = N'myBackupDevice', @physicalname = N'C:\myNewBackupDevice.bak'"

Doing a Backup

Finally, I want to show you how to do a backup of your database. Note that the class Backup doesn't represent a BackupDevice - it represents a "Backup Operation".

Backup backup = new Backup();
//we assume that there is a Logical Device with the Name "myBackupDevice"
backup.Devices.AddDevice("myBackupDevice", DeviceType.LogicalDevice);
backup.Database = "Master";
backup.SqlBackup(server);

Additional Features

The functional range of SMO is amazing!
SMO supports really everything you will need.
Indexes,
Constraints,
Relationships,
Permissions,
Stored Procedures,
Full Text Catalogues,
HTTP Protocol,
Triggers,
Mirroring,
Replication,
Asymmetric Encryption,
.
.
.

In short:
Everything you desire Smile | :)
And if you understand the basics of a specific feature, you won't have problems to implement it with SMO.

History

  • 04/11/06 - First version of article
  • 04/12/06 - Fixed wrong requirements (thx to akosows) - Added hint for SQL-DMO
  • 05/31/06 - Minor changes
Thank you for reading my article, comments and critiques are very welcome!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Thomas Haller
Software Developer (Senior)
Austria Austria
Thomas, living in austria, is a Softwaredeveloper focused on C# and MS Sql Server since 2004. He started software development at the age of 8 with Basic for Amiga 500, without great success, due his lack of english skills...
But the enthusiasm for software engeneering came back at the age of 15, and is still there Smile | :)
Nowadays, at the age of 30, Thomas is very fascinated about the possibilities of neural Networks, and is studying this at the university http://www.fernuni-hagen.de/ in addition to his regular work. (Blended Learning)

Comments and Discussions

 
GeneralMy vote of 3 PinmemberAFMatambo20-Jan-11 8:18 
GeneralPlease let me kow the procedure how to implement it Pinmembershavil6-May-10 22:10 
Generali am using EZManage SQL Pro to manage and backup all my sql server Pinmemberitayl27-May-09 4:13 
GeneralLicense of SMO Pinmemberlorecasas17-May-08 8:59 
GeneralRe: License of SMO Pinmemberalhambra-eidos6-Oct-08 22:35 
GeneralRe: License of SMO PinmemberLambin6-Apr-09 21:12 
GeneralServer for Backup Pinmemberlorecasas17-May-08 8:56 
GeneralDropping Stored Procs Pinmemberblacklocist18-Oct-07 9:40 
GeneralRe: Dropping Stored Procs Pinmemberblacklocist18-Oct-07 9:45 
GeneralRe: Dropping Stored Procs Pinmemberblacklocist18-Oct-07 10:58 
GeneralRe: Dropping Stored Procs PinmemberThomas Haller18-Oct-07 11:17 
GeneralRe: Dropping Stored Procs Pinmemberblacklocist19-Oct-07 5:33 
GeneralRe: Dropping Stored Procs PinmemberThomas Haller19-Oct-07 8:56 
GeneralError running demo PinmemberMarkChimes26-Feb-07 0:23 
GeneralRe: Error running demo PinmemberMarkChimes26-Feb-07 0:38 
GeneralFind Relation between two table Pinmemberatefeh mokhtary26-Nov-06 21:03 
GeneralSql Transaction Pinmembervalamas_salmat19-Nov-06 15:35 
GeneralProblems to create a DB [modified] PinmemberDark_leon12-Jun-06 0:16 
GeneralRe: Problems to create a DB PinmemberDark_leon12-Jun-06 0:47 
GeneralRequirements on System Pinmemberakosows11-Apr-06 23:25 
"
Requirements on System
 
.Net Framework 1.1
SQL Server 2000 or 2005 (NOTE: SMO Supports features that are new in SQL Server 2005 and do not work with SQL Server 2000)
SMO needs to be installed.
"
 
Are you sure ?
I tested and it's not working with .NET Framework 1.1,
I think that SMO is 2.0+ runtime.
 
Is it possible to download SMO for .NET Framework 1.1 ?
Where can I find it ?
 
Regards!
 
ADAM
 

GeneralRe: Requirements on System PinmemberThomas Haller12-Apr-06 4:14 
GeneralRe: Requirements on System Pinmemberalhambra-eidos6-Oct-08 22:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 31 May 2006
Article Copyright 2006 by Thomas Haller
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid