Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

SMO Tutorial 1 of n - Programming data storage objects

Rate me:
Please Sign up or sign in to vote.
4.90/5 (90 votes)
13 Nov 2010CPOL9 min read 158.3K   134   52
In this first part of the tutorial, I will show you how to create data storage objects (databases, tables, Views ...) using SMO.

Table of contents

Introduction

When I was preparing for exam 70-433, I found that one objective was about Server Management Objects. When I passed the exam, I decided to continue my investigation of SMO. I picked up a lot of knowledge and it really made me so excited. Now I want to share my knowledge with others.

Before I will show examples of how to use SMO for various problems, I think it is appropriate to describe what Server Management Objects are. (People who are familiar with SMO can jump to the next section.)

Background

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. All functions available in SQL Server Management Studio are available in SMO, but SMO includes several more features than Management Studio. SMO is a descendent of SQL Server Distributed Management Objects (SQL-DMO). SMO is easier to use than SQL-DMO and adds functionality to support the new features in SQL Server 2005.

SMO is compatible with SQL Server 2000 and SQL Server 7.0. This allows developers to create applications for various SQL Server versions. But there is one limitation. SMO can't be used for SQL Servers with compatibility level 60 or 65.

Connecting to SQL Server

When you want to connect to SQL Server 2005 (or later version), you can user SQL Server SMO. To get started with SMO, first you must add references in Visual Studio. In the Add Reference window, select:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Mircorost.SqlServer.ConnectionInfo

When the references are added, you must add two using statements for these namespaces:

C#
Using Microsoft.SqlServer.Management.Common;
Using Microsoft.SqlServer.Management.Smo;

Now you can start working with SMO. Before doing restore and backup tasks, you must connect to the server. This connection is provided by the ServerConnection object. It lets you connect to the server and assign that connection to the Server object. The Server object represents an instance of SQL Server. In SMO programming, the Server object determines the connection to a physical SQL Server installation.

C#
ServerConnection conn = new ServerConnection("INSTANCE", 
                            "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Console.WriteLine("Server: " + srv.Name);
    Console.WriteLine("Edition: " + srv.Information.Edition);
    conn.Disconnect();
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

This example shows you how to connect to SQL Server using a user name and password. Alternatively, you can use Windows Integrated Authentication:

C#
ServerConnection conn = new ServerConnection();
conn.ServerInstance = "INSTANCE";
try
{
    Server srv = new Server(conn);
    Console.WriteLine("Server: " + srv.Name);
    Console.WriteLine("Edition: " + srv.Information.Edition);
    conn.Disconnect();
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

For disconnecting from SQL Server, we use the function Disconnect() of the ServerConnection class. The ServerConnection object is represented by Server.ConnectionContext, and alternatively you can disconnect from SQL Server by calling:

C#
Server.ConnectionContext.Disconnect();

Working with databases

Enumerating databases, filegroups, and files

The Database property of the Server object represents a collection of Database objects. Using this collection, you can enumerate the databases on SQL Server.

C#
Server srv = new Server(conn);
foreach (Database db in srv.Databases)
{
    Console.WriteLine(db.Name);
    foreach (FileGroup fg in db.FileGroups)
    {
        Console.WriteLine("   " + fg.Name);
        foreach (DataFile df in fg.Files)
        {
            Console.WriteLine("      " + df.Name + " " + df.FileName);
        }
    }
}

Enumerating database properties

Database properties are represented by the Properties property of a Database object. Properties is a collection of Property objects. The following sample demonstrates how to get database properties:

C#
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
foreach (Property prop in database.Properties)
{
    Console.WriteLine(prop.Name + " " + prop.Value);
}

Creating databases

With SMO, you can create databases. When you want to create a database, you must create the Database object. This example demonstrates how to create a database named MyNewDatabase and create a data file called MyNewDatabase.mdf (in the primary filegroup) and a log file named MyNewDatabase.log.

C#
Database database = new Database(srv, "MyNewDatabase");
database.FileGroups.Add(new FileGroup(database, "PRIMARY"));
DataFile dtPrimary = new DataFile(database.FileGroups["PRIMARY"], 
         "PriValue", @"E:\Data\MyNewDatabase\MyNewDatabase.mdf");
dtPrimary.Size = 77.0 * 1024.0;
dtPrimary.GrowthType = FileGrowthType.KB;
dtPrimary.Growth = 1.0 * 1024.0;
database.FileGroups["PRIMARY"].Files.Add(dtPrimary);

LogFile logFile = new LogFile(database, "Log", 
        @"E:\Data\MyNewDatabase\MyNewDatabase.ldf");
logFile.Size = 7.0 * 1024.0;
logFile.GrowthType = FileGrowthType.Percent;
logFile.Growth = 10.0;
 
database.LogFiles.Add(logFile);
database.Create();
database.Refresh();

SMO allows you to set the Growth of a database and other properties. More about properties can be found on MSDN. When you want to drop a database, just call the Drop() method of the Database object.

Dababase backup

SMO allows you to backup databases very easily. For backup operations, you must create an instance of the Backup class and then assign the Action property to BackupActionType.Database. Now you have to add a device you want to backup to. In many cases, it is file. You can backup not only to file, but to tape, logical drive, pipe, and virtual device.

C#
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.Database = database.Name;
backup.Devices.AddDevice(@"E:\Data\Backup\AW.bak", DeviceType.File);
backup.PercentCompleteNotification = 10;
backup.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
backup.SqlBackup(srv);

SMO allows you to monitor the progress of a backup operation being performed. You can easily implement this feature. The first thing you must do is create an event handler with the PercentCompleteEventArgs parameter. This parameter includes the Percent property that contains the percent complete value. This value is an integer between 0 and 100.

C#
static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
{
    Console.WriteLine(e.Percent);
}

Performing a log backup operation is similar to a database backup. Just set the Action property to Log instead of Database.

Dababase restore

SMO allows you to perform database restore easily. A database restore operation is performed by the Restore class which is in the Microsoft.SqlServer.Management.Smo.Restore namespace. Before running any restore, you must provide a database name and a valid backup file. Then you must set the Action property. To restore a database, set it to RestoreActionType.Database. To restore a log, just set it to RestoreActionType.Log. During restore, you can monitor the progress of the restoring operation. This could be done the same way as in the case of database backup.

C#
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
Backup backup = new Backup();
Restore restore = new Restore();
restore.Action = RestoreActionType.Database;
restore.Devices.AddDevice(@"E:\Data\Backup\AW.bak", DeviceType.File);
restore.Database = database.Name;
restore.PercentCompleteNotification = 10;
restore.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
restore.SqlRestore(srv);

Database tables

In SMO, tables are represented by the Table object. In SMO objects hierarchy, the Table object is below the Database object.

Enumerating tables and columns

This example demonstrates how to enumerate all the tables and columns of the AdventureWorks database.

C#
Server srv = new Server(conn);
Database db = srv.Databases["AdventureWorks"];

foreach (Table table in db.Tables)
{
    Console.WriteLine(" " + table.Name);
    foreach (Column col in table.Columns)
    {
        Console.WriteLine("  " + col.Name + " " + col.DataType.Name);
    }
}

Creating and removing tables

The following example shows how to create a database table. It creates MyFirstSMOTable in the MyNewDatabase database. The table contains two columns (ID and Name) and a Primary Key index on the first column.

C#
Database myNewDatabase = srv.Databases["myNewDatabase"];

Table myFirstSMOTable = new Table(myNewDatabase, "MyFirstSMOTable ");

DataType dt = new DataType(SqlDataType.Int);
Column idColumn = new Column(myFirstSMOTable, "ID", dt);
idColumn.Nullable = false;
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
myFirstSMOTable.Columns.Add(idColumn);

dt = new DataType(SqlDataType.VarChar, 50);
Column nameColumn = new Column(myFirstSMOTable, "Name", dt);
myFirstSMOTable.Columns.Add(nameColumn);

myFirstSMOTable.Create();

Index primaryKey = new Index(myFirstSMOTable, "PK_ID");
IndexedColumn indexedColumn = new IndexedColumn(primaryKey, "ID");
primaryKey.IndexedColumns.Add(indexedColumn);
primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKey.Create();

In this example, the Table object is instantiated and two parameters are passed into the constructor of this class. The first parameter is a database represented by the myNewDatabase object of the Database class. The second parameter is the name of the table to be created. The columns of the table are created by instantiating the Column object. Before creating a column object, it is important to define the data type of the newly created column. The data type is represented by the DataType object. Next, this DataType object is passed into the constructor of the Column object. The constructor of the Column object has two other parameters: table and name of column.

In this example, two columns are created and a Primary Key index. For creating Primary Key index, serves the Index object with two parameters in the constructor. First is the table and the second, the ID name of the index. The IndexedColumn object is important to be created with two parameters in the constructor (index and name of column).

Checking table integrity

The following example demonstrates how to check the integrity of all the tables and their indexes in the database. In this example, we call the method CheckIntegrity() with a single parameter. This parameter specifies the RepairType.

C#
Server srv = new Server(conn);
Database database = srv.Databases["AdventureWorks"];
StringCollection sc = database.CheckTables(RepairType.None);
foreach (object o in sc)
{
    Console.WriteLine(o.ToString());
}

Views

In SQL Server Management objects, Views are represented by a View object.

Enumerating Views

C#
Server srv = new Server(conn);
Database db = srv.Databases["AdventureWorks"];
foreach (View view in db.Views)
{
    /*
     *  SOME CODE
     */ 
}

Creating and removing Views

In the following example, a view is created in text mode. In this case, the TextHeader property must be set. This property gets or sets the header part of the string that defines a View. The body of a View is represented by the TextBody property. Other properties that need mention are IsSchemaBound which gets or sets the value that specifies whether a schema is bound to the View, and IsEncrypted that specifies whether a View is encrypted. When a View is created and IsEncrypted is true, the definition of the View is stored in an obfuscated format. When IsSchemaBound is true, Views and tables participating in a View clause cannot be dropped unless that View is dropped or changed so that it no longer has schema binding.

C#
View myview = new View(myNewDatabase, "My_SMO_View");
myview.TextHeader = "CREATE VIEW [My_SMO_View] AS";
myview.TextBody = "SELECT ID, NAME FROM MyFirstSMOTable"; 
myview.Create();

This example shows how to create a View in MyNewDatabse. The newly created View is named My_SMO_View and it selects all the rows from the table MyFirstTable.

Stored Procedures

The StoredProcedure object represents SQL Server Stored Procedures.

Enumerating Stored Procedures

Stored Procedures are represented by the StoredProcedures property of the Database object. The following example demonstrates how to enumerate Stored Procedures of a selected database (in this case, AdventureWorks).

C#
Server srv = new Server(conn);
Database db = srv.Databases["AdventureWorks"];
foreach (StoredProcedure sp in db.StoredProcedures)
{
    /*
     *  SOME CODE
     */ 
}

Creating a Stored Procedure

When you want to create a Stored Procedure, the TextBody property must be set to the T-SQL script that defines the Stored Procedure. Sometimes developers create a Stored Procedure with parameters. Parameters of the Stored Procedure are represented by the Parameters property of the StoredProcedure object. The Parameters property is a collection of StoredProcedureParameter objects. All parameters require the "@" prefix in the Stored Procedure parameter name.

C#
Database myNewDatabase = srv.Databases["myNewDatabase"];
StoredProcedure sp = new StoredProcedure(myNewDatabase, "spRowByID_Get");
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
StoredProcedureParameter param;
param = new StoredProcedureParameter(sp, "@ID", DataType.Int);
sp.Parameters.Add(param);
string spBody = "SELECT * FROM MyFirstSMOTable WHERE ID=@ID";
sp.TextBody = spBody;
sp.Create();

In this example, a Stored Procedure named spRowByID_Get was created with an input parameter @ID. When you want to alter an existing Stored Procedure, just create a StoredProcedure object, set all the properties you need to change, and call the Alter() method.

C#
Database myNewDatabase = srv.Databases["myNewDatabase"];
StoredProcedure sp = myNewDatabase.StoredProcedures["spRowByID_Get"];
/*
 *  stored procedure modifications
 */ 
sp.Alter();

When you want to drop an existing Stored Procedure, just call the Drop() method of the StoredProcedure object.

Triggers

Before talking about the code part, it is important to tell something about triggers. Triggers are a special kind of Stored Procedure that respond to special events. SQL Server offers two types of Triggers: Data Definition Language (DDL) Triggers and Data Manipulation Language (DML) Triggers.

DDL Triggers fire in response to a change in the structure of a database (when CREATE, ALTER, or DROP statements are executed).

Creating a DML Trigger

DML Triggers fire in response to changing of data (when INSERT, UPDATE, or DETELE statements are executes). DML Triggers are represented by a Trigger object. One of the main properties of the Trigger object is TextBody. This property represents code that runs when a Trigger is fired. The type of Trigger is set by the Insert, Update, or Delete properties.

C#
Database myNewDatabase = srv.Databases["myNewDatabase"];
Table myFirstSMOTable = myNewDatabase.Tables["myFirstSMOTable"];
Trigger trigger = new Trigger(myFirstSMOTable, "SMOTrigger");
trigger.TextMode = false;
trigger.Insert = true;
trigger.Update = true;
trigger.Delete = false;
trigger.InsertOrder = 
  Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.First;
trigger.TextBody = " RAISERROR('MESSAGE',16,10) "; ;
trigger.ImplementationType = ImplementationType.TransactSql;
trigger.Create();

In this example, a Trigger named SMOTrigger is created. This trigger fires when an Insert or Update statement is executed. DML Triggers are bound to a table. In this case, the trigger is bound with MyFirstSMOTable and when one of the mentioned statements (INSERT or UPDATE) occurs, the Trigger fires.

Creating a DDL Trigger

Another type of Triggers is DDL Triggers. In SMO, this type is represented by two objects: ServerDdlTrigger and DatabaseDdlTrigger. Here is an example of DatabaseDdlTrigger:

C#
Database myNewDatabase = srv.Databases["myNewDatabase"];
DatabaseDdlTrigger databaseTrigger = 
  new DatabaseDdlTrigger(myNewDatabase,"SMODatabaseTrigger");
databaseTrigger.TextHeader = 
  "CREATE TRIGGER SMODatabaseTrigger ON DATABASE FOR DROP_TABLE AS";
databaseTrigger.TextBody = 
  "PRINT 'You can not delete table!' ; ROLLBACK "; ;
databaseTrigger.ImplementationType = ImplementationType.TransactSql;
databaseTrigger.ExecutionContext = DatabaseDdlTriggerExecutionContext.Caller;
databaseTrigger.Create();

In this example, a DDL trigger named SMODatabaseTrigger in the database MyNewDatabase is created. This Trigger fires when someone tries to execute a Drop table statement.

Schemas

In SQL Server 2005, Microsoft introduced the concept of database schemas as opposed to object owners. A schema is a container of objects distinct from the users who created those objects.

Schema in SMO is represented by the Schema object. The Schema object represents an ownership context for database objects.

Enumerating schemas

You can enumerate all schemas of a database using the Schemas property of the Database object. The Schemas property is an ordinary collection of Schema objects.

C#
Database myNewDatabase = srv.Databases["myNewDatabase"];
foreach (Schema schema in myNewDatabase.Schemas)
{
    Console.WriteLine(schema.Name);
}

Creating schemas

The following example demonstrates how to create a new database table named MySecondSMOTable in the newly created NewSchema schema:

C#
Database myNewDatabase = srv.Databases["myNewDatabase"];

Schema newSchema = new Schema(myNewDatabase, "NewSchema");
newSchema.Owner = "dbo";
newSchema.Create();

Table mySecondSMOTable = new Table(myNewDatabase, 
      "MySecondSMOTable","NewSchema");

DataType dt = new DataType(SqlDataType.Int);
Column idColumn = new Column(mySecondSMOTable, "ID", dt);
idColumn.Nullable = false;
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
mySecondSMOTable.Columns.Add(idColumn);
dt = new DataType(SqlDataType.VarChar, 50);
Column nameColumn = new Column(mySecondSMOTable, "Name", dt);
mySecondSMOTable.Columns.Add(nameColumn);
mySecondSMOTable.Create();

History

  • 12 Nov. 2010 - Article created.
  • 13 Nov. 2010 - Content posted.

License

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


Written By
Architect Marwin Cassovia Soft
Slovakia Slovakia
My name is Robert Kanasz and I have been working with ASP.NET, WinForms and C# for several years.
MCSD - Web Applications
MCSE - Data Platform
MCPD - ASP.NET Developer 3.5
- Web Developer 4
MCITP - Database Administrator 2008
- Database Developer 2008
MCSA - SQL Server 2012
MCTS - .NET Framework 3.5, ASP.NET Applications
- SQL Server 2008, Database Development
- SQL Server 2008, Implementation and Maintenance
- .NET Framework 4, Data Access
- .NET Framework 4, Service Communication Applications
- .NET Framework 4, Web Applications
MS - Programming in HTML5 with JavaScript and CSS3 Specialist

Open source projects: DBScripter - Library for scripting SQL Server database objects


Please, do not forget vote

Comments and Discussions

 
QuestionExcellent article Pin
xmaster123_212-Mar-13 23:55
xmaster123_212-Mar-13 23:55 
Very interesting article. Smile | :)
AnswerRe: Excellent article Pin
Kanasz Robert13-Mar-13 3:15
professionalKanasz Robert13-Mar-13 3:15 
QuestionNice Pin
strucker_luc18-Nov-12 3:13
strucker_luc18-Nov-12 3:13 
AnswerRe: Nice Pin
Kanasz Robert18-Nov-12 3:22
professionalKanasz Robert18-Nov-12 3:22 
GeneralMy vote of 5 Pin
Iftikhar Akram8-Nov-12 23:48
Iftikhar Akram8-Nov-12 23:48 
GeneralRe: My vote of 5 Pin
Kanasz Robert9-Nov-12 2:02
professionalKanasz Robert9-Nov-12 2:02 
QuestionInteresting article and very helpful Pin
kr1234564-Nov-12 3:54
kr1234564-Nov-12 3:54 
AnswerRe: Interesting article and very helpful Pin
Kanasz Robert4-Nov-12 4:05
professionalKanasz Robert4-Nov-12 4:05 
Questiongood Pin
superdevX151-Nov-12 6:49
superdevX151-Nov-12 6:49 
AnswerRe: good Pin
Kanasz Robert1-Nov-12 6:56
professionalKanasz Robert1-Nov-12 6:56 
Questiongot my 5 Pin
hakon12331-Oct-12 5:28
hakon12331-Oct-12 5:28 
AnswerRe: got my 5 Pin
Kanasz Robert31-Oct-12 5:37
professionalKanasz Robert31-Oct-12 5:37 
QuestionExcellent Pin
memlon mulas29-Oct-12 5:13
memlon mulas29-Oct-12 5:13 
AnswerRe: Excellent Pin
Kanasz Robert29-Oct-12 5:19
professionalKanasz Robert29-Oct-12 5:19 
Questiongood and well written article Pin
jackhoal27-Oct-12 3:54
jackhoal27-Oct-12 3:54 
AnswerRe: good and well written article Pin
Kanasz Robert27-Oct-12 4:00
professionalKanasz Robert27-Oct-12 4:00 
QuestionExcellent Pin
robkaan27-Oct-12 3:25
robkaan27-Oct-12 3:25 
AnswerRe: Excellent Pin
Kanasz Robert27-Oct-12 3:31
professionalKanasz Robert27-Oct-12 3:31 
Questionthanks Pin
windevvv21-Oct-12 6:46
windevvv21-Oct-12 6:46 
AnswerRe: thanks Pin
Kanasz Robert21-Oct-12 7:01
professionalKanasz Robert21-Oct-12 7:01 
QuestionGood Pin
kaslaninovic2-Oct-12 22:45
kaslaninovic2-Oct-12 22:45 
AnswerRe: Good Pin
Kanasz Robert3-Oct-12 6:52
professionalKanasz Robert3-Oct-12 6:52 
Questiongood one Pin
developer88123-Sep-12 2:59
developer88123-Sep-12 2:59 
AnswerRe: good one Pin
Kanasz Robert23-Sep-12 23:09
professionalKanasz Robert23-Sep-12 23:09 
QuestionExcellent Pin
bikerius19-Sep-12 2:00
bikerius19-Sep-12 2:00 

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

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