Automating SQLServer from VB-Part-III
Google
Web www.mahipalreddy.com

Article on automating SQLServer database from VB - Part-III

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 Part-II of the article on SQLDMO, I looked at creating a databases, adding tables to the database,and adding columns to tables using SQLDMO.


In this part, 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. I also look at adding a Computed Column to the table

 

 

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 Tbl_Customers As SQLDMO.Table
	Dim Tbl_Orders As SQLDMO.Table

Now, having declared all the required variables, it's about time we connected to the server.
And this is how we do it.

	'connect using user name and password (sql server authentication)
	oSQLServer.Connect "IC6", "forApp", "mahipal"
	
	'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 declare a variable to hold the new column object and define it's attributes like name, datatype, allownulls etc.

	Dim MyNewCol As New SQLDMO.Column
	MyNewCol.Name = "Cust_Score"
	MyNewCol.Datatype = "smallint"
	MyNewCol.AllowNulls = False
	MyNewCol.DRIDefault.Text = "31"

Now, get the Customers table into the Tbl_Customers variable defined earlier, from the Northwind database

	Set Tbl_Customers = oSQLServer.Databases("Northwind").Tables("Customers")

Finally, add the newly created column to the Tbl_Customers table

	' Mark start of change unit
	Tbl_Customers.BeginAlter
	' Add the populated Column object to its containing collection
	Tbl_Customers.Columns.Add MyNewCol
	' Create the column by committing the unit of change
	Tbl_Customers.DoAlter


Now, we declare a variable to hold the new column object and define it's attributes like name, datatype, allownulls etc.

	Dim colStockValue As New SQLDMO.Column
	colStockValue.Name = "StockValue"
	colStockValue.IsComputed = True
	colStockValue.Datatype = "money"
	colStockValue.ComputedText = "UnitsInStock * UnitPrice"

Now, get the Orders table into the Tbl_Orders variable defined earlier, from the Northwind database

	Set Tbl_Orders = oSQLServer.Databases("Northwind").Tables("Orders")

Finally, add the newly created column to the Tbl_Customers table

	' Mark start of change unit
	Tbl_Orders.BeginAlter
	' Add the populated Column object to its containing collection
	Tbl_Orders.Columns.Add colStockValue
	' Create the column by committing the unit of change
	Tbl_Orders.DoAlter

This concludes Part-III of the article on SQLDMO. In Part-IV, I'll look at Performing Complete Database Backup, Differential database Backup, Backing Up a Database Filegroup, Backing Up a Database Transaction Log and Restoring a Database

Top

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