Using SQL Server Management Objects in your projects

When I want to test methods that build databases at runtime, I use the SQL Server Management Objects (SMO). SMO is a collection of .Net objects that allow us to do things that SQl Server Management studio application can do, for example:
  1. create/delete database
  2. list databases on the sever
  3. create/delete tables
  4. list tables in a database
  5. create/delete columns
  6. list columns in a table
  7. get sql server version
Of course, you can use SMO in your .Net projects or in PowerShell scripts.
To get this functionality available in your projects, you must:
  1. Download and install SQL Server System CLR Types wich is required by SQL Server Management Objects: you can download it from  http://www.microsoft.com/download/en/details.aspx?id=29065 (search in the same page “Microsoft® System CLR Types for Microsoft® SQL Server® 2012″ and choose either x86 or x64 package).
  2. Download and install SQL Server Management Objects from  http://www.microsoft.com/download/en/details.aspx?id=29065 (search in the same page “Microsoft® SQL Server® 2012 Shared Management Objects” and choose either x86 or x64 package).
The installations will put the dlls under C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies. 
SMO 2012 is compatible with SQL Server 2005 and later versions, so these packages work fin for sql server 2005, SQl Server 2008, SQL Server 2008 R2, and SQL Server 2012.
Note that Visual studio 2010 will not show these dlls in “Add Reference” dialog, I don’t know why!, so you can add reference to these dlls in your projects by selecting them directly from its location.

You need to reference these dlls:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll

Here are some examples of how using SMO objects:

Connect to server:

// Create server instance representing the
// SQL sever instance
Server server = new Server(@"WINXP\SQLEXPRESS2008R2");
// Connect to server using windows authentication
server.ConnectionContext.LoginSecure = true;
server.ConnectionContext.Connect();
You can get the name of SQL Server from the properties of the server node in SQL Server Manaemenet Studio:


Create database

// Create a database named 'MyDatabase'
Database db = new Database(server, "MyDatabase");
db.Create();

The above code creates a database named ‘MyDatabase’:


Delete database

To delete a database from the server, you have to ways to do it,
either:
// Delete 'MyDatabase'
server.Databases["MyDatabase"].Drop();

or

// Kill 'MyDatabase'
server.KillDatabase("MyDatabase");
The last way forces database deletion by closing all open connection to it. This can be useful when you do some work with the database and directly after that you want to delete it (in unit test for example). If you use the Drop method in this situation it will fails saying that the database is in use.

Create Table with columns

The following code shows how to create a table with three columns. The first one is the identity column which is the primary key index:

// Get the database instance
Database myDatabase = server.Databases["MyDatabase"];
// Create a table instance named 'MyTable'
Table myTable = new Table(myDatabase,"MyTable");
// Create [id] column
Column id = new Column(myTable, "id", DataType.BigInt);
id.Identity = true;
// Add [id] column to the table
myTable.Columns.Add(id);
// Create another column [name]
Column name = new Column(myTable, "name", DataType.VarChar(125));
name.Nullable = true;
// Add [name] column to the table
myTable.Columns.Add(name);

// Create an index
Index pkIndex = new Index(myTable, "PK_MyTable");
// Set it as primary
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
// Create an indexed column for the column [id]
// and add it to the primary indexd columns
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, "id"));
// Add the primary key index to the table's indexes
myTable.Indexes.Add(pkIndex);

// Create the table on the instance SQL server
myTable.Create();

This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>