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.