| Databases |
MicroStation® is a tool for designing, analysing, and presenting 3D models and 2D drawings. As well as the visual aspect of modelling, MicroStation also lets you annotate components of a model with alpha-numeric data. Those alpha-numeric data may be stored in the same file as the model, in which case we have an internal database. Alternatively, they may be stored in an external database. Each method of storage has its advantages and disadvantages.
Internal Databases (non-graphic data)
MicroStation provides a number of ways to store alpha-numeric data in a DGN file alongside your geometric model data. Some methods are there to support legacy data, others have been introduced more recently and offer a flexible and accessible way to store and retrieve data.
Alpha-numeric data may be associated in some way with a graphic element. A DGN file can store unassociated alpha-numeric data.
Here are some ways that MicroStation lets us store data in a DGN file …
| Name | Location |
|---|---|
| EC Schema Data | An implementation of XAttributes having a formal XML Schema definition |
| Item Types | A user-friendlier implementation of EC Schemas in MicroStation CONNECT |
| Application Element | DGN file: non-graphic DgnStore |
| User Data | Element user data linkage |
| XData | Element user data linkage |
| XML Fragments | Non-graphic DgnStore |
| XAttributes | Associated with an element, but stored separately, in a DGN model |
| Tag elements | Independent or associated with an element in a DGN model |
Application Element
A type 66, level 20 application element is a survivor from IGDS. It's a MicroStation idiom and has limitations — primarily space for data is limited by the MicroStation element size.
| Support Type | Availability | Comment |
|---|---|---|
| User Interface | No | |
| Python | Yes | |
| VBA | Yes | |
| .NET | Yes | Use COM (VBA) interface via InterOp |
| MDL (C) | Yes | |
| MicroStationAPI (C++) | No |
User Data
User Data is a MicroStation idiom. Data are appended to an element in a non-structured format. Or rather, the structure is dependent on the application that created the data. An element may have multiple user data linkages. The amount of data that can be attached to an element is limited.
There is no standard user interface for user data. Because the data are application-dependent, it's up to the application developer to provide a user interface if appropriate.
| Support Type | Availability | Comment |
|---|---|---|
| User Interface | No | |
| Python | Yes | |
| VBA | Yes | |
| .NET | No | |
| MDL (C) | Yes | |
| MicroStationAPI (C++) | Yes |
XData
XData is an AutoCAD™ idiom. It's been added to MicroStation explicitly to support data exchange. It's implemented as User Data. That, of course, might be a benefit in some circumstances. XData in MicroStation is subject to the same limitations as XData in AutoCAD and MicroStation User Data.
| Support Type | Availability | Comment |
|---|---|---|
| User Interface | No | |
| VBA | Yes | |
| .NET | Yes | Use COM (VBA) interface via InterOp |
| MDL (C) | Yes | |
| MicroStationAPI (C++) | No |
XML Fragments
A child of the V8 era in the 21st century, XML Fragments don't suffer the constraints of IGDS or DWG data. XML Fragments can be attached to an element or free-floating. As structured data, this approach could be very fruitful.
However, XML Fragments pre-date XAttributes and as such when you create an XML Fragment element the data actually gets stored as non-graphic DgnStore elements.
An example of XML Fragments is provided by Bentley's own product Bentley Map, which implements its XFM GIS data very effectively using XML Fragments.
| Support Type | Availability | Comment |
|---|---|---|
| User Interface | No | |
| Python | No | |
| VBA | No | |
| .NET | No | |
| MDL (C) | Yes | |
| MicroStationAPI (C++) | No |
Tag Elements
Tags can be can be attached to an element or free-floating. Tags are structured data, and are supported by MicroStation's user interface. Third party products, such as FlexiTable, can be used with tags.
| Support Type | Availability | Comment |
|---|---|---|
| User Interface | Yes | |
| Python | Yes | |
| VBA | Yes | |
| .NET | Yes | Use COM (VBA) interface via InterOp |
| MDL (C) | Yes | |
| MicroStationAPI (C++) | Yes |
Bentley Systems tell us that Tags are deprected in MicroStation CONNECT. They recommend that we use Item Types instead.
XAttributes
Brien Bastings is a senior staff member at Bentley Systems, Inc. He wrote …
The Bentley folks love XAttributes and that is the preferred way to store new variable size/parasite data on elements.
XAttributes are data that are stored on an element, not in an element.
An XAttribute is not part of the element's data (that is, visible through an MSElement).
By way of contrast, a User Data linkage is part of an element's data.
XAttributes were introduced with MicroStation V8i. They are stored in the non-model cache of a DGN file.
Here's a Be Communities article about XAttributes for MDL developers: Getting Started With XAttributes.
| Support Type | Availability | Comment |
|---|---|---|
| User Interface | No | |
| Python | Yes | |
| VBA | No | |
| .NET | No | |
| MDL (C) | No | |
| MicroStationAPI (C++) | Yes |
Item Types
Item Types arrived with MicroStation CONNECT. They are built on EC Schemas but are easier to use. They have a user interface to define and attach data to DGN elements.
MicroStation CONNECT introduced Reports. You can report on Item Types attached to DGN elements as well as other element data. For example, you can attach a suitable Item Type to several shape elements, then report their areas.
For an example, see AreaAnnotator CONNECT Edition.
EC Schema Data
Engineering Configuration (EC) Schema data is embedded XML data that conforms to a formal XML Schema definition. Bentley Systems developed the EC Schema concept to provide business data in i-Models.
Bentley Systems provide a number of iWare tools that enable you to work with i-Models and EC Schema data.
| Support Type | Availability | Comment |
|---|---|---|
| User Interface | Yes | |
| Python | Yes | |
| VBA | Yes | Use PropertyHandler |
| .NET | Yes | Use VBA COM InterOp |
| MDL (C) | No | |
| MicroStationAPI (C++) | Yes | XmlInstance Interface |
If you're writing C++, look for XML Instance API in the MicroStationAPI help document. When you install the SDK, look in the example projects folder to find XmlInstance.
External Databases
MicroStation provides a way to connect to relational databases such as Oracle™, Microsoft Access® and Microsoft SQL Server®.
| Support Type | Availability | Comment |
|---|---|---|
| User Interface | Yes | |
| Python | Yes | Use Python database libraries |
| VBA | Indirectly | Use Microsoft ActiveX Data Objects (ADO) |
| .NET | Yes | .NET provides excellent database connectivity |
| MDL (C) | Yes | Prefer to use C++ with MicroStation CONNECT |
| MicroStationAPI (C++) | No |
Supported Databases
Bentley Systems publish a list of databases supported by MicroStation.
ODBC and OLE-DB
Open Database Connectivity (ODBC) and Object Linking and Embedding for Databases (OLE-DB) are Microsoft technologies. We use either ODBC or OLE-DB to connect to a database on a Windows operating system.
Visit the Microsoft web sites to learn more about Open Database Connectivity (ODBC) and Object Linking and Embedding for Databases (OLE-DB) .
Creating a database connection for a 32-bit application such as MicroStation on a 64-bit operating system can be tricky. Allen Brown, a staffer at Bentley Systems and their database guru, has written a blog about 32 Bit Database Connectivity on a 64 Bit OS .
Database Linkages in MicroStation
Graphic objects in a 3D model are linked to corresponding rows in the database. Suppose, for example, that a model of a house contains a cell representing a door: the cell is linked to a database table that stores the manufacturer and cost of doors. If we attach a database linkage to all the doors and windows in our model of the house, we can run a report that extracts the cost of the door and other items. The report tells us the total cost of the components required to build the house.
|
When a relational database is linked to MicroStation, it must have certain components. Firstly, the MSCATALOG table must exist and must have columns EntityNum and TableName. For each table in your database that you want to link to MicroStation, that table must have a column named MSLINK that stores a 32-bit integer (Int 32). The name of that table must be present in MSCATALOG's TableName column, and should be assigned an Entity ID (table identity) stored in the EntityNum column.
MSCATALOG is documented in MicroStation help.
|
Here are answers to questions about MicroStation® Database Linkages that are posted from time to time on the Bentley Tech. Notes. The questions are posted by MDL and VBA developers as well as database developers.
Bentley Systems have a Technical Note about databases and MicroStation: Databases and MicroStation V8 FAQ
Q What is an MSLINK?
When you link a database to graphic elements in MicroStation, you create a correspondence between a row in a database table
and the element. The table that contains the row must have a column named MSLINK, having datatype 32-bit integer.
The element has a corresponding MSLINK 32-bit value, known as the database linkage or database attribute.
There is more information about MSLINK.
Q How does the database provided by MicroStation work?
A MicroStation does not provide a database. It provides a database server that communicates with an external relational database. Relational databases that work with MicroStation include Oracle Enterprise Server™, Microsoft SQL Server, and Microsoft Access™. For more information, visit the Bentley Systems web site.
There are some example files and databases delivered with MicroStation. The example database is located in …
X:\Program Files\Bentley\Program\MicroStation\database\odbc\examples\
The example files are located in …
X:\Program Files\Bentley\Workspace\Projects\Examples\Mapping\dgn
Where X is the drive where your MicroStation installation is located. This is usually the C drive.
Q I would like to know more about the link between objects and the database.
A Each object (graphic element) that is linked to a database has a database linkage attached to the element. Sometimes this is called the MSLINK in Bentley Systems' and other literature.
|
When you use MicroStation's Analyse tool (the i icon) on an element that has a linkage, the Attributes tab will reveal the linkage in Hexadecimal.
Each linkage comprises …
- a unique signature
- a
MSLINKvalue - an
Entity IDvalue
Header Files
The location of the C macros that define a database linkage depend on the verion of MicroStation you are using.
| MicroStation CONNECT | MicroStation V8i | |
|---|---|---|
| Folder | ..\SDK\include\Mstn\MdlApi | ..\mdl\include |
| File(s) | dbdefs.h and dbdefs.r.h | dbdefs.h |
For example, the ORACLE_ID linkage is defined thus …
#define ORACLE_ID 0x6091 // Oracle user data signature
|
In C or C++ the Entity ID and MSLINK are 32-bit integers; in VB or VBA these are Long datatypes.
The Entity ID is a reference to the database table that contains the MSLINK column.
The name of the table is found in the [mscatalog] table. Here's an SQL statement that
reveals a table name given its Entity ID …
SELECT tableName FROM mscatalog WHERE entityNum=20;
The value 20 is an example: it may not exist in your [mscatalog] table.
Examine the data in [mscatalog] and substitute an alternative.
You can list all the catalogy rows like this …
SELECT * FROM mscatalog ORDER BY tableName;
Suppose you have a table in your database named [myTable].
To link with a MicroStation element, there are two conditions …
-
[myTable]must appear in the[mscatalog]table -
[myTable]must have a column[mslink]
The entry in [mscatalog] defines your table's entity ID.
For example …
| Column | [tablename] | [entityid] |
| Data | myTable | 20 |
Your graphic element database linkage must have the entity ID
of 20 to link with your [myTable] table. The element's MSLINK
value determines the row in [myTable] that holds data about
that element.
Q How do I link an object to multiple tables?
A
An element may have multiple database linkages. That is,
there may be one or more [mslink]|[entity id] pairs of
information attached to an element from more than one table.
|
Each linkage will refer to a different table. For example, if
you have tables [myTable1] and [myTable2], then [mscatalog] might have
these entries …
| Column | [tablename] | [entityid] |
| Data | myTable1 | 20 |
| Data | myTable2 | 21 |
If your element is linked to both tables, the database linkage attached to the element could be …
| MSLINK | Entity ID |
|---|---|
1234 | 20 |
4567 | 21 |
The tables could have rows like this …
| MSLINK | Col2 | Col3 |
|---|---|---|
| … | ||
1234 | Data 1234 | MoreData 1234 |
| … |
| MSLINK | Col2 | Col3 |
|---|---|---|
| … | ||
4567 | Value 4567 | Another 4567 |
| … |
Q How do I use Visual Basic for Applications (VBA) to communicate between MicroStation and a database?
Q How do I communicate with a database using MicroStation VBA?
A MicroStation CONNECT and earlier versions of MicroStation include Visual Basic for Applications (VBA). MVBA includes functions for reading and creating database linkages on graphic elements.
MicroStation VBA does not, however, provide a mechanism to communicate with a database. Instead, you can use technology such as Microsoft's ActiveX Data Objects (ADO, also known as Microsoft Data Access Components) to connect to a database and exchange data. You can download the Microsoft Data Access Components libraries and SDK here …
The VBA project in this ZIP Archive demonstrates how Visual Basic can interrogate the linkage between MicroStation V8 and the delivered Access database mentioned above.
Note: VB.NET developers should use ADO.NET to communicate with a database.
Q Can I store my data (in my case, object name) without using an external database?
A Yes, through one of several technologies that are part of MicroStation …
Tag Elements
FlexiTable™ uses tags as a data source to create a schedule table that is displayed in MicroStation. However, it's a little more versatile than just a tag editor, because those data can be derived from many different data sources. FlexiTable also exports data in a number of ways.
Tag Elements are available through MicroStation's Tags toolbox. They are graphic objects that may be manipulated and printed like any other element. What's different is that there are built-in tools to query and report on tag data.
Tags Elements contain your data that are associated with
a host element. They are part of the DGN file and require no
external database or application. MicroStation's tag functionality includes
a reporting mechanism. You can save the data from tagged elements
to a CSV or XML file.
MicroStation lets you define a named Tag Set that contains a number of Tag definitions. This is analogous to defining the columns of a table in a database schema.
Some applications created by LA Solutions use Tag Elements to store data. For example, the freeware application AreaAnnotator™ records room areas in tags. Its strength is that it automatically updates its area tag value as you edit an area boundary.
Using FlexiTable™ in conjunction with AreaAnnotator lets you gather reports of annotated areas. FlexiTable can save a report in several formats, including HTML and Microsoft Excel®.
XML Fragments
XML Fragments are implemented with technology introduced publicly with MicroStation V8.1. An XML Fragment contains data embedded in an XML element. The XML element and its contained data are attached to a MicroStation graphic element as a data linkage.
Currently there are no user tools available for XML Fragments. The first Bentley Systems application to use XML Fragments is MicroStation GeoGraphics 2004 Edition, and subsequently Bentley Map. Bentley Map and MicroStation GeoGraphics provide GIS analysis, labelling, and reporting using XFM technology.
There is an XML Fragment API for MDL but not for MicroStation VBA. The MDL API provides full access not only to XML Fragments, but also to stand-alone XML data that is part of the DGN file but not associated with any particular graphic element.
