Adding a Column using SQL_DMO

,

Recently SqlServerCentral reader Judith

Loughran posted a question

in our DMO forum asking for some help in altering a table. Since I hate to miss

an opportunity to evangelize for DMO, here is a code sample showing how to add a

column to the categories table in the Pubs database. For those of you who may be

interested I have several other articles

on DMO with additional code samples posted here on the site.

One comment I frequently hear about DMO is "why use it? T-SQL can do

that!". Well, it's certainly true that there isn't much if anything that

you can do in DMO that you can't do using T-SQL and/or Enterprise Manager. DMO

will typically appeal to a developer more than a DBA since it is an "object

model". If you understand how classes, collections, and object hierarchies

work, using DMO is pretty straight forward.

A key concept about DMO that you need to learn is that you'll almost always

add an object (a class). This is a little different than you may be used to in

VB where the add method (such as ocolumns.add - add is the method) is

parameterized to save you the extra step of instantiating (creating) an object.

For example, here is how the add might look if built using parameters:

The only difference is where the work gets done. Using DMO you create the

object and pass it in as single parameter. Of course you can (and might) do this

in VB as well, but more likely you'll go with parameters - so what does VB do

with the parameters? If you use the Class Wizard you'll get code a lot like

this:

Public Function Add(ColumnName As String, ColumnLength As Integer, AllowNulls As Boolean, DataType As String, Columns As Columns, Optional sKey As String) As Column

Dim objNewMember As Column

'create a new object

Set objNewMember = New Column

'set the properties passed into the method

If IsObject(ColumnName) Then

Set objNewMember.ColumnName = ColumnName

Else

objNewMember.ColumnName = ColumnName

End If

With objNewMember

.ColumnLength = ColumnLength

.AllowNulls = AllowNulls

.DataType = DataType

End With

Set objNewMember.Columns = Columns

If Len(sKey) = 0 Then

mCol.Add objNewMember

Else

mCol.Add objNewMember, sKey

End If

'return the object created

Set Add = objNewMember

Set objNewMember = Nothing

End Function

No magic, we're just creating the object "inside" the add method

and then adding it to the columns collection. If you wanted to pass an object,

it might look like this instead:

Public Function Add(oColumn As Column)

mCol.Add oColumn

End Function

Now that's a very quick intro to objects in VB. Probably too quick! The thing

I hope you'll take away from this is that it doesn't matter if you're adding a

column to a table, a login to a server, or a user to a database - each time

you'll dim and create the object, set whichever properties you need, then pass

that object into the appropriate add method.

And finally, here is the sample code for adding a varchar column:

Dim oServer As SQLDMO.SQLServer

Dim oTable As SQLDMO.Table

Dim oColumn As SQLDMO.Column

'this logs into the local server using NT auth

Set oServer = New SQLDMO.SQLServer

With oServer

.LoginSecure = True

.Connect

End With

'we don't need a reference to the database, we can access the table

directly

Set oTable = oServer.Databases("Pubs").Tables("Categories")

'create a column object with the appropriate properties. For data types

that have a

'user defined length you have to set the length property too. For this

example Im

'allowing nulls since the table should already have data and not allowing

nulls

'would cause the alter to fail!

Set oColumn = New SQLDMO.Column

With oColumn

.Name = "SSC_COLUMN_TEST"

.Datatype = "varchar"

.Length = 500

.AllowNulls = True

End With

'apply the change

With oTable

.BeginAlter

.Columns.Add oColumn

.DoAlter

End With

'clean up

oServer.DisConnect

Set oServer = Nothing

As always I'd enjoy receiving comments and questions about the article --

please click the 'Your Opinion' tab below to enter your thoughts.

Rate

Share

Share

Rate