Displaying items by tag: database builder

Enterprise Architect’s Database Builder provides interactive connectivity to a DBMS,  supporting both the initial creation of a database and the ongoing update of tables, views, triggers and procedures – all from within the design model.

This tutorial paper works through a simple database example (attached in .ZIP file) using the Database Builder to create tables within a new Database, then make modifications in both the model and the Database and use the compare functionality to update the Database and the model respectively.

As part of an EA add'in implementation, I had to import all Enterprise Architect table and column names in enumeration classes.

Having access to a database that stores a Sparx Systems Enterprise Architect project (e.g. MySQL, SQL Server, or even an EAP file via ODBC), there was a number of ways to populate enumeration classes with literal values using a small application in C# or another programming language. As Enterprise Architect 12 includes the Database Builder, an integrated tool to work on DB schemas, I took the opportunity to use it. Compared with the result of a C# application, this feature would let me capture the enumeration classes in my EA modelling project in addition to generating C# enumeration classes in my add'in solution.

This article only covers a small portion of Sparx EA Database Builder features: it illustrates how I managed to import Enterprise Architect table names as literal values in the EADBTableType enumeration class, and their column names as literal values in separate enumeration classes e.g. t_diagramColumnType, t_objectColumnType...

Import all tables in Enterprise Architect

- Open the menu Tools > Database Builder

- Right click and select New Data Model:

- Select the DBMS used, e.g. MySQL and confirm Ok

- Right click on MySQL > Load

- Right click on Connections > Add new DB connection

- Open the new DB connection and select ODBC based datatype

- Select an existing ODBC DSN (or create one) to the DB that stores the Enterprise Architect project where the schema needs to be imported

- Right click on MySQL > Import DB Schema from ODBC

- Click on Import and select all tables:

- All tables, columns, primary keys, foreign keys, indexes, etc. have been imported.

- Go back to the Database builder and open the Tables tabs: all tables are displayed. Selecting one of the tables provides access to all columns' details.

Generate the enumeration classes

The Database Builder let me achieved what I needed. Having all tables and columns stored in a single package, I wrote a simple script to perform the following:

- create an enumeration class named EADBTableType

- for each table:

    - create a literal value with the table name in EADBTableType e.g. t_object

    - create an enumeration class named after the table based on the following format: <table name>ColumnType e.g. t_attributeColumnType

    - for each column in the table:

       - create a literal value with the column name in the current table's enumeration class (e.g. Classifier in t_attributeColumnType)

Having executed this script, the following enumeration classes have been generated:

Transform enumeration classes to dotNet C# classes

The MDA model transformation can be used to generate C# enumeration classes for the above enums:

- Right click on the package that contains all enums > Advanced > Transform Package

- Select C# as the target, select the package, and click on Do Transform

- Having all C# enumeration classes created, and making sure the namespace is correct (e.g. create packages myaddin.common.enums), the code can be generated: right click on package > Code Engineering > Generate Source Code.

- The generated code can be used in Visual Studio IDE:

Enterprise Architect includes a powerful new database engineering toolset. The new Database Builder greatly enhances Enterprise Architect's existing data modeling capabilities – making it easier than ever to model, generate and synchronize database designs.

In this webinar you will learn how to:

• Model a database from scratch using predefined patterns
• Connect to live databases, run queries and compare differences
• Synchronize your live database schema based on model changes

For more information:

http://sparxsystems.com.au/resources/webinar/database-engineering/database-builder-intro/database-engineering-in-enterprise-architect.html

To view the entire webinar series:

http://www.sparxsystems.com/resources/webinar/webinar-series.html

Click the link below to register: