Last edit: 2009-05-04 Graham Wideman

ModelRight

Sample ModelRight COM Automation

Contents

• COM Automation Introduction
• Automation using Excel as the Automation client
• Navigating and "dumping" a Database Model using COM Automation
  • Preparation
  • Sample Code
  • Notes

COM Automation Introduction

ModelRight supplies an apparatus whereby you can create code in your own development environment and use it to manipulate ModelRight model files.  This is the ModelRight DLL SCF.dll ("Script Framework"), which is installed as part of ModelRight, and registers a COM Automation interface that your code can call.

Automation using Excel as the Automation client

The sample code here is a VBA routine stored in an Excel spreadsheet VBA project. This makes a good demo automation environment for several reasons:

• Almost all customers will have ready access to Excel, and be able to try this code.
• It's directly representative of how to use SCF from any of the Microsoft Office applications, including Word, Excel, Access, Visio, Project.
• It's representative of how to use SCF from Visual Basic 6.
• It's only a small stretch to adapt this code for use with .NET languages (VB.NET, C#), Delphi, and many other languages.
• Although handling COM Automation from C++ is more laborious, this example at least shows the steps that SCF requires you to perform.

Navigating and "dumping" a Database Model using COM Automation

This is essentially a revision of the navigate-and-dump sample previously presented as a VB script.

Preparation

1. In Excel, create a new spreadsheet and save it with some memorable name
2. Open the VBA environment (Alt-F11), and create a new module (see Project Explorer, right-click on Microsoft Excel Objects within the current document node, and select Insert Module).  A fresh module editing window should open.
3. Establish a reference to the SCF Automation library. This will make SCF's objects and properties known to Excel (for this project). To do this: Tools > References... then browse down the list to "SCF 1.0 Type Library" and click the checkbox to select it.  Click OK.
   You can check now view the SCF library features using View > Object Browser, and select SCFLib in the combobox that lists available libraries. A list of available classes appears, and clicking those will show their methods and properties.
4. Now you can paste in the code below.
   Be sure to revise the FilePath string so that it points to a .wer file on your machine!
5. To run:
   -- Open the Debug output window: View > Immediate Window.
   -- Place the cursor  somewhere in the Dump_sakilla procedure
   -- Press the "Run sub" button on the toolbar (or Run > Run on the menu).
6. You should see a list of Tables and Columns print to the Immediate window.

Sample Code

Option Explicit

'-------------------------------------------------------
' Sample code showing how to access a ModelRight file
' Note: Use Tools > References to set a reference to "SCF 1.0 Type Library"
'-------------------------------------------------------

Const FilePath = "C:\Program Files\ModelRight\ModelRight3MySQL\Samples\sakilla51.wer"

'-----------------------------
Sub Dump_sakilla()
'-----------------------------
Dim Framework As SCFLib.ScriptFramework

Dim Model As SCFLib.MRModel
Dim ModelMRObject As SCFLib.MRObject
Dim Tables As SCFLib.MRObjectCollection
Dim Table As SCFLib.MRObject
Dim Columns As SCFLib.MRObjectCollection
Dim Column As SCFLib.MRObject
Dim TableNameProp As SCFLib.MRPropertyValue
Dim TableName As String

  Debug.Print "------- Start --------"
  Set Framework = CreateObject("SCF.ScriptFramework")
  Framework.Initialize
  Set Model = Framework.LoadModel(FilePath)
  Debug.Print "Model: " & Model.Name & "  Model ID: " & Model.ID
  
  Set ModelMRObject = Model.AsObject
  Debug.Print "ModelMRObject: " & ModelMRObject.Name

  Set Tables = ModelMRObject.Children("Table")
  
  Debug.Print "------ Tables and columns -------"
  For Each Table In Tables
    Set TableNameProp = Table.Property("Name")
    TableName = TableNameProp.AsString
    Debug.Print Table.TypeName & ": " & Table.Name
    Set Columns = Table.Children("Column")
    For Each Column In Columns
      Debug.Print "  " & TableName _
                      & "." & Column.Property("Name").AsString _
                      & " : " & Column.Property("Datatype").AsString
    Next
  Next
  
  Framework.CloseModel Model
  Debug.Print "------- Done --------"  
End Sub   

Notes

1. Dims and typed variables: Though not strictly necessary, this sample declares all variables (as required by "Option Explicit"), and gives them a type (uses the "As" clause). Once an object variable has a type, when you later type in the code the editor provides code-completion suggestions, which is highly convenient and reduces errors.
2. Initialize: When calling SCF from an automation client, it's necessary that the first operation with the new Framework object be Framework.Initialize.
3. Load Model and CloseModel: To obtain a model to work on, it must be loaded from a file, using LoadModel. If you change the model (use Transactions!) you can call SaveModel. When done, Framework needs to be tidied up with a call to CloseModel.
4. Output via Debug.Print: Whereas the VBScript sample sent output to the Document window, this sample sends it to the Immediate window using Debug.Print.  It could of course send output to the spreadsheet, or use Forms and provide a user interface, and so on.