|
Automating SQLServer from VB (using Microsoft SQLDMO Obect Lirary) - Part-II - by Mahipal Padigela
The article looks at automating Microsoft SQL server from visual basic using SQL Distributed Management Objects
(SQLDMO object library).
In part-I of the article on SQLDMO, I explained about connecting to Sqlserver, getting a list of all the available databases, tables and fields.
In this Part, I'll look at creating a databases, adding tables to the database,and adding columns to tables using SQLDMO.
The first thing you need to do before getting started is to add a reference to Microsoft SQLDMO Obect Lirary from VB.
(goto Project-->References-->Microsoft SQLDMO Object Library)
'Declaring Object Variables
Dim oSQLServer As New SQLDMO.SQLServer
Dim oDatabase As SQLDMO.Database
Dim oTable As SQLDMO.Table
Dim oColumn As SQLDMO.Column
Dim oDBFile As SQLDMO.DBFile
Dim oFileGroup As SQLDMO.FileGroup
Now, having declared all the required variables, it's about time we connected to the server.
And this is how we do it.
'EITHER connect using user name and password (sql server authentication)
oSQLServer.Connect "IC6", "forApp", "mahipal"
' OR
'connect using a direct secure connection (Windows authentication)
oSQLServer.LoginSecure = True
oSQLServer.Connect "(local)" 'replace with your server name if not a local installation
Create and add Database to SQLServer
Now, we set the object variables to hold the database, primary data file and transaction log file
'set object variables
Set oDatabase = New SQLDMO.Database
Set oDBFileData = New SQLDMO.DBFile
Set oLogFile = New SQLDMO.LogFile
Give the database you want to create some name like... 'northwind'
oDatabase.Name = "Northwind"
Now, define the Primary datafile attributes
' Define the PRIMARY data file.
oDBFileData.Name = "NorthData1"
oDBFileData.PhysicalName = "c:\program files\microsoft sql server\mssql\data\northwnd.mdf"
oDBFileData.PrimaryFile = True
Specify the file growth in chunks of fixed size and add the file to the database Filegroup
' Specify file growth in chunks of fixed size for all data files.
oDBFileData.FileGrowthType = SQLDMOGrowth_MB
oDBFileData.FileGrowth = 1
'Add file to the database Filegroup
oDatabase.FileGroups("PRIMARY").DBFiles.Add oDBFileData
Define the database transaction log attributes and add it to the database Logfiles
' Define the database transaction log.
oLogFile.Name = "NorthLog1"
oLogFile.PhysicalName = "c:\program files\microsoft sql server\mssql\data\northwnd.ldf"
oDatabase.TransactionLog.LogFiles.Add oLogFile
And now, add the database defined to the SQLserver
oSQLServer.Databases.Add oDatabase
Create a Table and add fields to it
Declare object variables to hold a table object and two column objects we are about to add to the newly created database
Dim tableCategories As New SQLDMO.Table
Dim colCategoryID As New SQLDMO.Column
Dim colCategoryName As New SQLDMO.Column
Now, populate the Column objects to define the table columns
'First column
colCategoryID.Name = "CategoryID"
colCategoryID.Datatype = "int"
colCategoryID.Identity = True
colCategoryID.IdentityIncrement = 1
colCategoryID.IdentitySeed = 1
colCategoryID.AllowNulls = False
'Second column
colCategoryName.Name = "CategoryName"
colCategoryName.Datatype = "varchar"
colCategoryName.Length = 15
colCategoryName.AllowNulls = False
Name the table, then set desired properties to control eventual table construction.
tableCategories.Name = "Categories"
tableCategories.FileGroup = "PRIMARY"
tableCategories.TextFileGroup = "fgNorthwindTxtImg"
Now, add populated Column objects to the Columns collection of the Table object.
tableCategories.Columns.Add colCategoryID
tableCategories.Columns.Add colCategoryName
And finally, create the table by adding the Table object to its containing collection
oDatabase.Tables.Add tableCategories
This concludes Part-II of the article on SQLDMO.
In Part-III , I'll look at altering an existing Table by adding a new column and populating
the existing rows in the table with a default value. The article also looks at adding a Computed Column.
|
