Monday, April 13, 2009

Start with SMO

Getting Connected
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;
///
/// Initializes the field 'server'
///

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 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);
}
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 :-) Now - lets 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 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'"
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 asume 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 :)
And if you understand the basics of a specific feature, you won't have problems to implement it with SMO.

No comments:

Post a Comment