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. Tip To treat objects polymorphically and check at runtime whether a given object supports Create, Alter, or Drop operations, you can check whether the object implements the ICreatable, IAlterable, or IDroppable interface and act on it correspondingly. To use SMO in projects, the user needs to include a few namespace references:
The user also needs to reference the following few assemblies in his project:
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 AuthenticationThe 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 .NETDim serverInstance As Server serverInstance = New Server(serverName) serverInstance.ConnectionContext.NonPooledConnection = True serverInstance.ConnectionContext.LoginSecure = True C# .NETServer 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 .NETDim l As Login l = New Login(serverInstance, loginName) l.LoginType = LoginType.SqlLogin l.DefaultDatabase = "AdventureWorks" l.Create(loginPassword) C# .NETLogin 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 IntegerIt 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 .NETDim 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# .NETDatabase 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 ViewSuppose a view has a full text index defined and you want to remove it. Visual Basic .NETDim fti As FullTextIndex fti = serverInstance.Databases(databaseName).Views(viewName).FullTextIndex; fti.Drop(); C# .NETFull 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. |