External Database Reference Manager

Issue

You want to connect a table of an external database to your AutoCAD Plant 3D or AutoCAD P&ID project.

Overview

The AutoCAD Plant 3D and P&ID project database model is an extensible model which can be configured to reference data tables of an external database using either a Virtual Mapping or an Existing Mapping:

Virtual Mapping

The column in the external database table does not become a property in the project hierarchy; it will appear as a new property, and can have a name that's different from the external database column name.

Existing Mapping

The column in the external database is mapped to an existing property in the project table.

Database connection

The project database interacts with external databases through the OLE DB .NET provider, and can connect to most database systems with an installation of the corresponding OLE DB providers on the client system. Additional external table columns can be referenced from the project table by joining the project table with an external database table. Rows in the project and external database tables are bound by the specification of a foreign key column) for the project table, and a primary key (column) for the external database table.

This document describes a sample database extension where columns from an external database are added to the project database.

Description of the external database

External database: autodesk.accdb

External table:       Table1

Key column:          Field1

Extension column:  Field2, Field3, Field4

Solution

Creating a Data Source

• Invoke the External Database Reference Manager dialog with the 'PLANTXDBMANAGER' command.
• Create a data source named "autodesk_datasource" by clicking the "Add" button in the "Data Sources" tab. Multiple data sources can be defined.
• Click the "Define..." button to specify the connection properties in the "Data Link Properties" dialog. In this example, we're using the "Microsoft Office 12.0 Access Database Engine" to connect to an Microsoft Access database at <path>\autodesk.accdb.

Create Property Mappings

Once the data source is created, switch to the "Property Mapping" tab to define a property mapping. Toggle the "Hide Unused" button to show all available classes in the project hierarchy.

Select the "EngineeringItems" table (i.e. the table that you want to extend), and click the "Map Virtual Property" button to create a new property mapping set. Multiple property mapping sets can be defined. Alternatively, you can also click the "Map Existing Property" button to create mappings to existing columns in the project table.

Select "autodesk_datasource" as the Referenced data source, and "Table1" as the Referenced table [see BLUE box in the snapshot below]. Once the referenced table is selected, map the "ModelNumber" column in the project database to the “Field1” column in the external database as shown [see RED box in snapshot below].

The last thing to do in this step is to specify the columns from the external database table and how they will display when extending the “EngineeringItems” table [see ORANGE box in snapshot below].

If you're creating mappings to existing project table columns, specify the columns from the external database table and the columns in the project table that they should map to [see ORANGE box in the snapshot below].

Turn on and Apply

Check the “Use” checkbox in the General group box [see RED oval two snapshots above]. This turns on the reference, so that it is used. To finish and exit the dialog box, click the “Apply” button followed by the “Close” button.

Test

Open the Data Manager and scroll to the right. Notice that three new columns now exist for the “EngineeringItems” table. If you type in “Info1” for the “ModelNumber” column of a record, “FieldName2” through “FieldName4” are automatically updated.

If you've created mappings to existing project columns, after you've typed in "Info1" for the "ModelNumber" column of a record, select and right-click the "Engineering Items" node in the class tree and select the "Reload Mapped Properties..." menu to populate the mapped property values in the selected table.