The ADO Object Model - The ADOX Object Model
(Page 4 of 9 )
The ADOX library contains extensions for Data Definition Language (DDL) and security, providing a way to access schema creation and modification and for accessing security credentials for the schema. Many data stores allow access to schema and security information, but they often differ in the command language used. ADOX abstracts these provider-specific details into a common set of objects, allowing a single code set to be used regardless of provider-specific syntax.
The ADOX (for DDL and Security) object model observes more hierarchy than does the ADO object model, but this is because of the nature of the objects it contains. This means there is a parent object and several child objects, as this diagram for the ADOX object model illustrates:
The Catalog Object The Catalog object is a central repository, and is really just a container for all of the other objects. The Catalog object allows you to connect to a data store and examine the components comprising it. With the Catalog object, you can use the ActiveConnection property to specify the data store to which you want to connect, and then use the collections to obtain more detailed information. It’s also possible to obtain and set the owner of various objects without using the collections.
To use a catalog, you just point to an existing Connection object or a Connection string. For example:
Dim objCatalog As New ADOX.Catalog
Set objCatalog.ActiveConnection = objConn
or:
objCatalog.ActiveConnection = "Provider=SQLOLEDB; . . ."
The Table Object and the Tables Collection A Table object represents a single table, containing information such as the table name and the last change date, in a catalog. It is also a container for the Indexes, Keys, and Columns collections (described shortly).
The Tables collection contains Table objects—one for each table in the catalog. I provide an example of the Tables and Columns collections later in this section.
The Index Object and the Indexes Collection An Index object contains the details for a single index on a table. It identifies attributes such as the index name, whether the index is unique, whether the index allows nulls, and so on. Using the Index object’s Columns collection, you can see which columns comprise the index.
The Indexes collection contains all the Index objects for a particular Table. For example, the following code prints the indexes on a table and the columns that make up each index:
Dim objIndex As ADOX.Index
Dim objColumn As ADOX.Column
For Each objIndex In objTbl.Indexes
Print objIndex.Name
For Each objColumn In objIndex.Columns
Print objColumn.Name
Next
Next
The Key Object and the Keys Collection A Key object contains information regarding a table key. It identifies such items as its name, whether it is a primary or foreign key, the related table, and so on. Like the Index object, the Key object contains a collection of Column objects, identifying the columns that make up the key.
The Keys collection contains a list of keys for a table. For example, the following prints all the keys and columns for a table:
Dim objKey As ADOX.Key
Dim objColumn As ADOX.Column
For Each objKey In objTabl.Indexes
Print objKey.Name
For Each objColumn In objKey.Columns
Print objColumn.Name
Next
Next
The Column Object and the Columns Collection A Column object represents an individual column from a Table, Index, or Key object. In many respects it is similar to the ADO Field object, but rather than storing the details of a field, it holds the details of a stored column, such as its name, its data type, and so forth. For Key columns it contains details of the related columns, and for Index columns it contains details of the clustering and sorting. There is just one Column object, but what it contains depends upon its parent (Table, Index, or Key).
The Columns collection contains all the columns for a particular Table, Key, or Index object. For example, to see all the columns in a particular table, you would use this:
Dim objTbl As New ADOX.Table
Dim objCol As ADOX.Column
Set objTbl = objCatalog.Tables(0)
For Each objCol In objTbl.Columns
Print objCol.Name
Next
The syntax is very similar when looping through all of the Column objects associated with an Index or a Key object.
The Group Object and the Groups Collection A Group object identifies a security group. It contains a list of users in the catalog. Its main role is to allow the retrieval and setting of permission for a named group or for allowing access to the Users collection, which contains a list of all users belonging to this group.
The Groups collection contains a list of Groups that belong to a particular catalog, or a list of Groups that a particular user belongs to. For example, the following enumerates the Groups in a Catalog, and then the Users in a Group:
Dim objGroup As ADOX.Group
Dim objUser As ADOX.User
For Each objGroup In objCatalog.Groups
Print objGroup.Name
For Each objUser In objGroup.Users
Print objUser.Name
Next
Next
Not all providers support the Group object and Groups collection. At the moment, the Jet provider gives the most comprehensive support.
The User Object and the Users Collection A User object contains details of a single user of the data store and contains properties to retrieve or set the user name and the user’s unique ID, and methods to read and write permissions. The User object also contains a Groups collection, which is a list of groups to which the user belongs. Be careful about writing recursive procedures that traverse the Users and Groups collections, because they point to each other.
The Users collection contains a list of all users in a catalog. For an example, refer to the preceding description of the Group object.
Not all providers support the User object and Users collection. At the moment, the Jet provider gives the most comprehensive support.
The Procedure Object and the Procedures Collection A Procedure object identifies a stored procedure in a catalog. The Procedure object has very few properties, mainly because one of these properties represents an ADO Command object. The Procedure object identifies the details about the stored procedure (such as its name and when it was last modified), and the Command object identifies the internal details of the procedure (such as the SQL text).
The Procedures collection contains a Procedure object for each stored procedure in the Catalog. For example, to access a stored procedure and its SQL text, you could use the following code:
Dim objCmd As ADODB.Command
Set objCmd = objCat.Procedures(" proc _ name").Command Print objCmd.CommandText
This uses the Command property of the Procedure object to reference a standard ADO Command object and print the text (command string) of the Command.
The View Object and the Views Collection A View object identifies a single view in a Catalog, which is a set of records or a virtual table. Like the Procedure object, the View contains a Command object, allowing access to the view command.
The Views collection contains a list of View objects, one for each view in the catalog.
For example, to obtain all views in a catalog, you could do this:
Dim objView As ADOX.View
For Each objView In objCat.Views
Print objView.Name
Next
Like the Procedure object, the View object also supports an ADO Command as a property. So, to access the details of the command, you could use similar code:
Dim objCmd As ADODB.Command
Set objCmd = objCat.Views(" view _ name").Command
Print objCmd.CommandText
This object and collection is also provider-dependent.
The Property Object and the Properties Collection The Property object and Properties collection are identical to their ADODB equivalents.
 | This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today. Buy this book now! |
Next: ADOX Supported Features >>
More ASP.NET Articles
More By Apress Publishing
/ 15 
