How to: Create, Alter, and Remove a Schema in Visual Basic .NET
This section describes how to create a schema and assign it to a database object and a user by using Microsoft Visual Basic .NET.
The code example demonstrates how to create a schema and grant permission to a user, and then creates a new table in the schema.
-
Start Visual Studio 2005.
-
From the File menu, select New Project. The New Project dialog box appears.
-
In the Project Types pane, select Visual Basic. In the Templates pane, select Console Application.
-
(Optional) In the Name box, type the name of the new application.
-
Click OK to load the Visual Basic console application template.
-
On the Project menu, select Add Reference item. The Add Reference dialog box appears. Select Browse and locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. Select the following files:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SmoEnum.dll
-
On the View menu, click Code.-Or-Select the Module1.vb window to display the code window.
-
In the code, before any declarations, type the following Imports statements to qualify the types in the SMO namespace:
Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.Common
-
Insert the code that follows this procedure into the main program.
-
Run and build the application.
'Connect to the local, default instance of SQL Server. Dim srv As Server srv = New Server 'Reference the AdventureWorks database. Dim db As Database db = srv.Databases("AdventureWorks") 'Define a Schema object variable by supplying the parent database and name arguments in the constructor. Dim sch As Schema sch = New Schema(db, "MySchema1") sch.Owner = "dbo" 'Create the schema on the instance of SQL Server. sch.Create() 'Define an ObjectPermissionSet that contains the Update and Select object permissions. Dim obperset As ObjectPermissionSet obperset = New ObjectPermissionSet() obperset.Add(ObjectPermission.Select) obperset.Add(ObjectPermission.Update) 'Grant the set of permissions on the schema to the guest account. sch.Grant(obperset, "guest") 'Define a Table object variable by supplying the parent database, name and schema arguments in the constructor. Dim tb As Table tb = New Table(db, "MyTable", "MySchema1") Dim mycol As Column mycol = New Column(tb, "Date", DataType.DateTime) tb.Columns.Add(mycol) tb.Create() 'Modify the owner of the schema and run the Alter method to make the change on the instance of SQL Server. sch.Owner = "guest" sch.Alter() 'Run the Drop method for the table and the schema to remove them. tb.Drop() sch.Drop()
