Automating SQLServer from VB-Part-II

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

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

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.

Terms and Conditions of Use
Copyright 2004 - 2006 Mahipal Padigela. All rights reserved.