|
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
Create and add a Column object
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
Adding a Computed Column
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
|
