Some Uses of SMO Objects

Let's perform a few simple operations on SMO objects to demonstrate their multiple uses. Most objects support Create, Alter, and Drop operations, so in this section you will see an example of each operation.

To use SMO in projects, the user needs to include a few namespace references:

• Visual Basic .NET

  • Imports Microsoft.SqlServer.Management.SMO

  • Imports Microsoft.SqlServer.Management.Common

• C# .NET

  • using Microsoft.SqlServer.Management.Smo

  • using Microsoft.SqlServer.Management.Common

The user also needs to reference the following few assemblies in his project:

• Microsoft.SqlServer.Smo.dll

• Microsoft.SqlServer.SmoEnum.dll

• Microsoft.SqlServer.SqlEnum.dll

• Microsoft.SqlServer.ConnectionInfo.dll

In general you can find these assemblies in two places on your computer. First they are located in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies. That is the source from which you need to reference them. The second place you can find them is the Global Assembly Cache (GAC) under some cryptic directory similar to this one:

C:\Windows\Assembly\GAC_MSIL\Microsoft.SqlServer.SMO\9.0.242.0__898
45dcd8080cc91.

The SDK directory should be used at design time and GAC is used at run time. The SMO redistribution module mentioned earlier handles registration of deployed SMO assemblies with GAC automatically so your programs will be able to locate them when they are running.

Let's dive directly into SMO code through different usage scenarios. Throughout this chapter most of the code samples are provided in two mainstream .NET languages: Visual Basic and C#. Only when syntax is identical or nearly identical will one of the languages be omitted for the sake of brevity.

Create a Simple Login Using SQL Authentication

The first part of the following code snippets is used to a create server object and establish a connection common to all samples, and therefore is omitted from here on. Please make sure to supply the correct serverName value, that is, the name of the server operation you want to be performed.

Visual Basic .NET

Dim serverInstance As Server
serverInstance = New Server(serverName)
serverInstance.ConnectionContext.NonPooledConnection = True
serverInstance.ConnectionContext.LoginSecure = True

C# .NET

Server serverInstance = new Server(serverName);
serverInstance.ConnectionContext.NonPooledConnection = true;
serverInstance.ConnectionContext.LoginSecure = true;

With the server object created, now you can create a login object on the server. Make sure loginPassword satisfies security requirements of SQL Server Database Engine in terms of complexity.

Visual Basic .NET

Dim l As Login
l = New Login(serverInstance, loginName)
l.LoginType = LoginType.SqlLogin
l.DefaultDatabase = "AdventureWorks"
l.Create(loginPassword)

C# .NET

Login l = new Login(serverInstance, loginName);
l.LoginType = LoginType.SqlLogin ;
l.DefaultDatabase = "AdventureWorks";
l.Create(loginPassword);

Alter an Existing Table by Changing the Data Type of One of Its Columns to Small Integer

It would have been possible to perform navigation to the column object in one line, but it is split here for the sake of simplicity.

Visual Basic .NET

Dim db As Database
Dim t As Table
Dim c As Column
db = serverInstance.Databases(databaseName)
t = db.Tables(tableName, tableSchema)
c = t.Columns(columnName)
c.DataType = DataType.SmallInt
c.Alter()

C# .NET

Database db = serverInstance.Databases[databaseName];
Table t = db.Tables[tableName, tableSchema];
Column c = t.Columns[columnName];
c.DataType = DataType.SmallInt;
c.Alter();

Drop Full Text Index on View

Suppose a view has a full text index defined and you want to remove it.

Visual Basic .NET

Dim fti As FullTextIndex
fti = serverInstance.Databases(databaseName).Views(viewName).FullTextIndex;
fti.Drop();

C# .NET

Full Text Index fti = serverInstance.Databases [databaseName].Views[viewName].FullTextIndex;
fti.Drop();

What happened behind the scenes as the code ran? SMO generated proper DDL statements to perform the desired operations on the Database Engine instance and submitted them to the server for immediate execution. Should an error occur during any of these operations, SMO throws an exception containing a specific SQL Server message with an error number and a brief description of why it failed. However, you do not always want to execute DDL immediately against the server. Sometimes it is desirable to obtain a T-SQL script for examination and later execute it in a different environment. SMO provides the functionality to accomplish this in Capture Mode. SQL Server Management Studio leverages this feature from all management dialogs when you choose to generate a script.