Integrating a Visio Solution with a Database

3 4

Integrating a Microsoft Visio solution with a database requires planning to synchronize the drawings with the database. It's important to decide which database should be used, what should be changed and how, and when the changes should occur.

For example, you might develop a solution in which each master corresponds to a numbered part in a manufacturer's catalog. By storing a part number as a custom property of each master, it is easy to look up part information in a version of the parts catalog stored as a database. However, the correspondence between the database and drawings created from the solution is not necessarily one-to-one, because there might be 20 instances of a master in a drawing, all with the same part number. Deleting a shape in the diagram shouldn't delete that part from the database, but updating the database should identify components used in the drawing that are no longer available.

Alternatively, to check a drawing for correctness, a solution developer might build an external model of the components and their interconnections, and store the external model in a data repository or temporarily in memory. Because the model must represent both the inventory of the parts and their interconnections, it might be composed of multiple linked tables. In this model, each component in the drawing would need a unique identifier in addition to its part number, so that identical components in the drawing can be distinguished from each other in the model. It would make sense to synchronize the external model with the drawing in real time so that, as the user adds component shapes to the drawing, the solution could add a suitable node to the model, and behave similarly if the user deleted a shape or a connection.

Deleting a shape, however, will generally do more than simply delete a record in the table that records all the components; the table that records connections between components will have to be modified as well. In general, an action in the drawing corresponds to a transaction that must be performed on the database, not just a simple insertion or deletion of a record.

Similarly, in a department organization solution, deleting an employee from one manager's organization chart would not justify deleting the employee's record in the company's human resources database until the new organization has been created and approved by management (and perhaps not even then). Instead, the organization charts would be synchronized with a central data model that represents the reorganization as it is being designed. After designing the interactions between a solution and a database, a solution can make changes by:

• Handling ShapeAdded, ShapesDeleted, ConnectionsAdded, ConnectionsDeleted, TextChanged, and other Visio events and updating the database as the user changes the drawing.
• Handling the DocumentOpened and BeforeDocumentClose events and performing batch updates or otherwise synchronizing the database when the user opens and closes the drawing.
• Creating an external program that queries the Visio drawing to extract the data when needed—for example, when the user requests it.
• Storing all the solution's data in an external database and using data associated with Visio shapes, such as a custom property, as a key attribute to access records in the external database.

The Database Wizard provided with Microsoft Visio can add user-defined cells and link custom property cells to database fields for simple solutions or for prototyping more complex solutions. You can use the DAO (Data Access Object) library provided by Microsoft to access databases through ODBC (Open Database Connectivity) or use the Jet database engine. Or, you might have the Visio solution call an Automation server that actually updates and synchronizes the database, which provides more control over the integrity of the database.