.png)
How to: Create, Alter, and Remove a Table in Visual Basic .NET
This section describes how to create, alter, and remove a table in Visual Basic .NET.
The code example creates a table that has several columns with different types and purposes. The code also provides examples of how to create an identity field, how to create a primary key, and how to alter table properties.
-
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 Table object variable by supplying the parent database and table name in the constructor. Dim tb As Table tb = New Table(db, "Test_Table") 'Add various columns to the table. Dim col1 As Column col1 = New Column(tb, "Name", DataType.NChar(50)) col1.Collation = "Latin1_General_CI_AS" col1.Nullable = True tb.Columns.Add(col1) Dim col2 As Column col2 = New Column(tb, "ID", DataType.Int) col2.Identity = True col2.IdentitySeed = 1 col2.IdentityIncrement = 1 tb.Columns.Add(col2) Dim col3 As Column col3 = New Column(tb, "Value", DataType.Real) tb.Columns.Add(col3) Dim col4 As Column col4 = New Column(tb, "Date", DataType.DateTime) col4.Nullable = False tb.Columns.Add(col4) 'Create the table on the instance of SQL Server. tb.Create() 'Add another column. Dim col5 As Column col5 = New Column(tb, "ExpiryDate", DataType.DateTime) col5.Nullable = False tb.Columns.Add(col5) 'Run the Alter method to make the change on the instance of SQL Server. tb.Alter() 'Remove the table from the database. tb.Drop()
