cdbexplorer.html

《VB6数据库开发指南》所有的例程的源码

  
  With mtvw.Nodes
    ' refresh collection
    mdb.QueryDefs(strQueryDefName).Parameters.Refresh
    For Each param In _
      mdb.QueryDefs(strQueryDefName).Parameters
      strParameterName = param.Name
      .Add strQueryDefName & "Parameters", tvwChild, _
        strQueryDefName & "Parameters" & strParameterName, _
        strParameterName
    Next  ' Parameter
  End With

End Sub
</pre>

<h3>Populating the Fields Collection of a Relation Object</h3>
The TVGetRelationFields procedure populates the nodes for the Fields 
collection of a Relation object. Although most Relation objects have 
only a single field, it is possible for a multiple field Relation object
to exist, so an iterative approach is required.
<pre>
Private Sub TVGetRelationFields(strRelationName As String)

  Dim fld As Field
  Dim strFieldName As String
  
  With mtvw.Nodes
    ' refresh collection
    mdb.Relations(strRelationName).Fields.Refresh
    For Each fld In mdb.Relations(strRelationName).Fields
      strFieldName = fld.Name
      .Add _
        "Relations" & strRelationName & "Fields", _
        tvwChild, _
        "Relations" & strRelationName & _
          "Fields" & strFieldName, _
        strFieldName
    Next  ' Field
  End With
        
End Sub
</pre>

<h3>Determining the Current Depth in the Tree</h3>
When a node is expanded, it must be determined where the selected node 
exists in the tree. The TVGetNodeType procedure finds the type of node 
selected. It takes a Node object as a parameter and finds the position 
in the tree by working its way back up through parent nodes until it 
finds the root node. At that point it can determine how far down in the 
tree it is by the number of parent nodes. Rather than working with a 
numeric depth counter, the dbExpNodeType enumeration in the Declarations 
section abstracts the depth into an object level, as follows:
<ul>
	<li>
		Database Object
		<br>
		This is the root node.
	</li>
	<li>
		Root Collections
		<br>
		The TableDefs, QueryDefs, and Relations collections.
	</li>
	<li>
		Root Objects
		<br>
		These are the nodes that are direct children of the root collection nodes. 
		They are either TableDef objects, QueryDef objects, or Relation objects.
	</li>
	<li>
		Object Collections
		<br>
		The are the collections that exist as children of Root Objects. TableDef 
		objects contain the Fields and Indexes collections, QueryDef objects contain
		the Fields and Parameters collections, and Relation objects contains the 
		Fields collection.
	</li>
	<li>
		Objects
		<br>
		This, perhaps, might be better named Child Objects, because these are the 
		objects that are the child of the Object Collections. They are the individual
		Field, Index, and Parameter objects.
	</li>
</ul>
Note that in the context of this procedure, a node will not be aware of the type of 
object it represents, only its relative depth in the tree.
<pre>
Private Function TVGetNodeType(nd As Node) As Long
' Determine where we are in the database
' Can be one of the following:
'   the database
'   one of the root collections:
'     TableDefs, QueryDefs, Relations
'   an object in one of the root collections:
'     TableDef, QueryDef, or Relation
'   one of the object collections:
'     Fields, Indexes, Parameters
'   bottom level object: Field, Index, Parameter
' This works by running an accumlator to determine the "depth"
' in the tree. The depth is represented by the dbExpNodeType
' enumeration in the header section

  Dim lngDepth As Long
  Dim ndParent As Node
  Dim ndRoot As Node

  Set ndRoot = nd.Root
  
  If nd = ndRoot Then
    ' root node
    lngDepth = 0
  Else
    lngDepth = 1
    Set ndParent = nd.Parent
    Do
      If ndParent = ndRoot Then
        ' done
        Exit Do
      Else
        ' move up one level
        lngDepth = lngDepth + 1
        Set ndParent = ndParent.Parent
      End If
    Loop
  End If

  TVGetNodeType = lngDepth

End Function
</pre>

<h3>Initializing the ListView Control</h3>
Because the implementation of the list in this class is highly simplified, only 
two routines are required to implement all of the listview functionality. The first 
of these is the LVInit procedure. In report view, the ListView requires that you setup 
the ColumnHeaders collection. Since the list will only contain list of Properties collections, 
two column headers are added: one for the property name and one for its value.
<pre>
Private Sub LVInit()
' Initialize the ListView

  Static blnFirstTime As Boolean

  ' set view
  mlvw.View = lvwReport
  
  If Not blnFirstTime Then
    ' setup ColumnHeaders
    With mlvw.ColumnHeaders
      .Clear
      .Add , "Property", "Property"
      .Add , "Value", "Value"
    End With
    mlvw.ColumnHeaders("Value").Width = _
      mlvw.Width - mlvw.ColumnHeaders("Property").Width
    blnFirstTime = True
  End If

End Sub
</pre>

<h3>Populating the Property List</h3>
To populate the list of properties for an object, the LVListProperties procedure
iterates the collection and adds the property name and value as ListItem objects 
in the ListView control. The error handler in the procedure traps errors that are 
generated for procedures that cannot be read in this context.
<pre>
Private Sub LVListProperties(obj As Object)
' List the properties of an object
' Note: Since every DAO object except for the collections
'   have a properties collection this procedure can be
'   completely generic
'   The error handler traps the collections error
'   This module could be extended to list the members of
'   collections instead of just trapping the error
On Error GoTo ProcError

  Dim prop As Property
  Dim strPropertyName As String
  Dim li As ListItem

  With mlvw
    .ListItems.Clear
    For Each prop In obj.Properties
      strPropertyName = prop.Name
      Set li = mlvw.ListItems.Add _
        (, strPropertyName, strPropertyName)
      li.SubItems(1) = CStr(prop.Value)
    Next  ' Property
  End With
  
  Exit Sub

ProcError:
  Select Case Err.Number
    Case 3251, 3219, 3267
      ' property can't be read in this manner
      ' continue
      Resume Next
    Case 438
      ' object doesn't support this property or method
      ' this is a collection object
      Exit Sub
    Case Else
      ' something else, raise it again
      Err.Raise _
        Err.Number, _
        Err.Source, _
        Err.Description, _
        Err.HelpFile, _
        Err.HelpContext
  End Select

End Sub
</pre>

<h3>Getting a Database Object Based on a Node</h3>
This procedure is the real key to having a class module handle the 
TreeView control. It takes a Node object as a parameter, and based on the 
properties of the node and its position in the tree determines what database
object is associated with the node. It then returns a reference to that object
so that the object can be used in other code, such as populating the property 
list or child collections.
<p>
The procedure works by first calling TVGetNodeType to determine the depth in the 
tree. It then examines parent nodes from its own position upward until it can 
determine what type of object the node represents. For example, at the Object level, 
a node could be a TableDef field or index, a QueryDef field or parameter, or a 
Relation field. While the TVGetNodeType can determine the depth, only the properties 
of a node and its parent nodes can determine the type of object.
<p>
Althought the procedure looks long and complex, it is really nothing more than a lengthy 
Select Case block, based on the return value of the TVGetNodeType function. As nodes 
get deeper in the tree, the nesting in the Select Case statement gets correspondingly deep.
<p>
This could also be implemented using a number or shorter procedures, but that would do little
to simplify the structure. It might also be possible to obtain the same result using a 
recursive function, but that would make the procedure significantly more obscure.
<pre>
Private Function GetDAOObjectFromNode _
  (nd As Node) As Object
' Refresh the property list for the node provided

  Dim lNodeType As dbExpNodeType
  Dim obj As Object
    
  lNodeType = TVGetNodeType(nd)
  
  Select Case lNodeType
    Case ntDatabase
      Set obj = mdb
    Case ntRootCollection
      ' find out which collection
      Select Case nd.Text
        Case "TableDefs"
          Set obj = mdb.TableDefs
        Case "QueryDefs"
          Set obj = mdb.QueryDefs
        Case "Relations"
          Set obj = mdb.Relations
      End Select
    Case ntRootObject
      ' before we can list the object
      ' properties, we need to know the
      ' collection it belongs to, which
      ' can be found by examining the Text
      ' of the parent node
      Select Case nd.Parent.Text
        Case "TableDefs"
          Set obj = mdb.TableDefs(nd.Text)
        Case "QueryDefs"
          Set obj = mdb.QueryDefs(nd.Text)
        Case "Relations"
          Set obj = mdb.Relations(nd.Text)
      End Select
    Case ntObjectCollection
      ' same as ntRootObject, except this time we
      ' need to go to the "grandparent"
      Select Case nd.Parent.Parent.Text
        Case "TableDefs"
          Select Case nd.Text
            Case "Fields"
              Set obj = _
                mdb.TableDefs(nd.Parent.Text).Fields
            Case "Indexes"
              Set obj = _
                mdb.TableDefs(nd.Parent.Text).Indexes
          End Select
        Case "QueryDefs"
          Select Case nd.Text
            Case "Fields"
              Set obj = _
                mdb.QueryDefs(nd.Parent.Text).Fields
            Case "Parameters"
              Set obj = _
                mdb.QueryDefs(nd.Parent.Text).Parameters
          End Select
        Case "Relations"
          ' the only collection is fields
          Set obj = mdb.Relations(nd.Parent.Text).Fields
      End Select
    Case ntObject
      ' get "great-grandparent" collection
      Select Case nd.Parent.Parent.Parent.Text
        Case "TableDefs"
          ' return a field or index object
          Select Case nd.Parent.Text
            Case "Fields"
              Set obj = _
                mdb.TableDefs(nd.Parent.Parent.Text). _
                Fields(nd.Text)
            Case "Indexes"
              Set obj = _
                mdb.TableDefs(nd.Parent.Parent.Text). _
                Indexes(nd.Text)
          End Select
        Case "QueryDefs"
          ' return an index or parameter object
          Select Case nd.Parent.Text
            Case "Fields"
              Set obj = _
                mdb.QueryDefs(nd.Parent.Parent.Text). _
                Fields(nd.Text)
            Case "Parameters"
              Set obj = _
                mdb.QueryDefs(nd.Parent.Parent.Text). _
                  Parameters(nd.Text)
          End Select
        Case "Relations"
          ' return a field object
          Set obj = _
            mdb.Relations(nd.Parent.Parent.Text). _
            Fields(nd.Text)
      End Select
  End Select

  Set GetDAOObjectFromNode = obj

End Function
</pre>

<hr>

<h2>Public Methods</h2>

The public interface for the CDBExplorer class is very simple. 
Three procedures: ExploreDatabase, ListProperties, and ExpandNode handle all of the 
functionality required to implement a read-only, hierarchical view of a database.

<h3>Initializing the CDBExplorer Class with ExploreDatabase</h3>

The ExploreDatabase method sets up the initial view of the database in the tree and list 
panes. It takes as parameters a database name, a treeview control reference, and a 
listview control reference. These are retained in their respective module level object 
variables and used throughout the class. The procedure then initializes the tree and list 
using the TVInit and LVInit procedures and populates the list with the properties of the 
root database object.
<pre>
Public Sub ExploreDatabase( _
  strDBName As String, tvw As TreeView, lvw As ListView)

  Set mdb = DBEngine(0).OpenDatabase(strDBName)
  Set mtvw = tvw
  Set mlvw = lvw

  TVInit
  LVInit

  ' list database properties
  LVListProperties mdb

End Sub
</pre>
<h3>Getting a Property List with ListProperties</h3>
This three statement procedure calls two core routines in the class to populate the list. 
The Explorer form passes a Node object to the procedure from the NodeClick event of its 
treeview object. This procedure then passes that Node object on to GetDAOObjectFromNode