 |
Database »
Database »
General
Intermediate
SMO - Manage your SQL Server!By Thomas HallerA quick start tutorial on how to manage your Microsoft SQL Server 2000/2005 with C# and the Microsoft's SQL Server Management Objects (SMO) |
C#, Windows, .NET, Visual Studio, DBA, Dev
|
||||||||||
|
Advanced Search Add to IE Search |
|
|
|
||||||||||||||||
May I introduce you to SMO, the Framework provided by Microsoft to manage your MS SQL Servers? This Article is a quick start tutorial. SMO is a replacement of the SQL-DMO - it's now managed code and it now supports the features of the SQL Server 2005 now. 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: Its much easier to browse the object hierarchy with IntelliSense than browsing the 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.
.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.
Basic C# knowledge + Windows Forms
Basic SQL Server knowledge
About 30 minutes of time
To use the SMO, you have to add it as a Reference to your Project. This Screenshots has been made with Visual Studio 2005 Express Edition.
The first thing we have to do is to 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);
}
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 a all known Databases to a Listview
//clean up the listview first.
listView1.Clear();
listView1.Columns.Clear();
//building the Coloumns
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 enlisting 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);
}
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 :-) Now - lets Create a Backup Device.
backupDevice.Parent = Server;
backupDevice.Name = "myBackupDevice";
backupDevice.PhysicalLocation = @"C:\myNewBackupDevice.bak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;
backupDevice.Create();
In some cases you might want to have a T-SQL Script of a 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'"
Backup doesn't represent a BackupDevice - it represents a "Backup Operation". Backup backup = new Backup();
//we asume that there is a Logical Device with the Name "myBackupDevice"
backup.Devices.AddDevice("myBackupDevice", DeviceType.LogicalDevice);
backup.Database = "Master";
backup.SqlBackup(server);
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 :)
And if you understand the basics of a specific feature, you won't have problems to implement it with SMO.
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 31 May 2006 Editor: |
Copyright 2006 by Thomas Haller Everything else Copyright © CodeProject, 1999-2009 Web21 | Advertise on the Code Project |
