How to: Create, Alter, and Remove a Rule in Visual Basic .NET
The section describes how to create, alter, and remove a rule in Visual Basic .NET.
The code sample shows how to create a rule, attach it to a column, modify properties of the Rule object, detach it from the column, and then drop it.
Creating, Altering, and Removing a Rule
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.CommonInsert the code that follows this procedure into the main program.
Run and build the application.
Example
'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")
'Declare a Table object variable and reference the Product table.
Dim tb As Table
tb = db.Tables("Product", "Production")
'Define a Rule object variable by supplying the parent database, name and schema in the constructor.
'Note that the full namespace must be given for the Rule type to differentiate it from other Rule types.
Dim ru As Microsoft.SqlServer.Management.Smo.Rule
ru = New Rule(db, "TestRule", "Production")
'Set the TextHeader and TextBody properties to define the rule.
ru.TextHeader = "CREATE RULE [Production].[TestRule] AS"
ru.TextBody = "@value BETWEEN GETDATE() AND DATEADD(year,4,GETDATE())"
'Create the rule on the instance of SQL Server.
ru.Create()
'Bind the rule to a column in the Product table by supplying the table, schema, and
'column as arguments in the BindToColumn method.
ru.BindToColumn("Product", "SellEndDate", "Production")
'Unbind from the column before removing the rule from the database.
ru.UnbindFromColumn("Product", "SellEndDate", "Production")
ru.Drop()
The Dim statement for the Rule object is specified with the full assembly path to avoid ambiguity with a Rule object in the System.Data assembly.
See Also
Concepts
Creating, Altering, and Removing Rules