How To Work Database First

LLBLGen Pro supports both Database First and Model First development. This article describes briefly which functionality of the designer is available to you when you want to work Database First as well as how the typical Database First work flow looks like.

Work flow of Database First

The schematic workflow of Database First development looks like the following:

Database First workflow

Schematic workflow of Database First

The flow of data is from the Database Schema Elements inside an RDBMS, e.g. SQL Server, to the Abstract Entity Model and Relational Model Data in a Relational Model Data storage inside LLBLGen Pro. The Database Schema Elements, e.g. a catalog with a schema and a set of tables, are edited by an external tool in the RDBMS.

Two processes are used to retrieve the definitions of these Database Schema Elements: Add Relational data from a Database and Sync Relational Model Data. Both result in Relational Model Data which is used as mapping targets for the elements in the Abstract Entity Model.

Let's break this down into smaller steps, from creating a project to creating a set of entities and syncing the relational model data with a database.

  1. Create a project in the LLBLGen Pro designer and choose a target framework
  2. After you have examined and eventually adjusted the Project Settings and have clicked OK, Right-Click the node 'Relational Model Data' in the Project Explorer or Catalog Explorer and select Add Relational Model data from a Database from the context menu
  3. The Relational Model Data Retrieval Wizard is started. Complete the wizard's steps to obtain the relational model data from your database. Check at least 1 table with some fields.
  4. You now have a Relational Model Data storage with Relational Model Data in the project. Go to the Catalog Explorer and examine what's been retrieved by LLBLGen Pro
  5. In Catalog Explorer, Right-click the catalog node, or schema node and select Reverse Engineer Tables to Entity Definitions... from the context menu.
  6. The Reverse Engineering Element Editor is opened. Examine the names pre-generated and check all checkboxes in front of the elements in the grid. Then click the Add to Project button.
  7. The Project Explorer now shows one or more entity definitions. You now have reverse-engineered tables, their fields, primary key constraints and FK constraints to an Abstract Entity Model with entity definitions, fields, identifying field sets and relationships. Open one or more entities in their editor by right-clicking them in Project Explorer and select Edit...
  8. Make a change to the database schema in your RDBMS, e.g. remove a field of a table which is mapped by an entity in your project.
  9. Select Project -> Sync Relational Model Data from the main menu or click the Sync button button in the toolbar.
  10. The Sync Relational Model Data tab is opened. Make sure the sync sources for your schema elements are set to Database and click Perform Tasks. This will bring up the Relational Model Data Retrieval Wizard. Complete the wizard's steps again to obtain the relational model data from your database.
  11. LLBLGen Pro will now migrate your Abstract Entity Model and will show the log of the actions performed afterwards. If you for example removed a field in the database, or renamed a field in the database, it will show you that it for example has left a field unmapped as there's  no target field found, or has mapped the entity field on a different field now (the renamed field).
  12. Generate code by pressing F7 or by selecting Project -> Generate Source-code from the main menu.

Steps 8-12 are the typical work cycle from then on: you alter the database, sync the relational model data, check the changes made to the model, generate code again and you start again by altering the database....

Tools and functionality available to you

The designer contains a lot of functionality, both visible and hidden beneath the surface, which help you work with an Abstract Entity Model using a Database First workflow. The following list of features are mainly used for and designed for working Database First.

  • Adding Relational Model Data from a Database. The initial retrieval of relational model data from a RDBMS
  • Syncing Relational Model Data. The process of synchronizing the relational model data in the Relational Model Data storage with a source (in this case, an external database schema) and migrating the Abstract Entity Model to that new set of relational model data.
  • Reverse-engineering Abstract Entity Model elements from Relational Model Data through the Reverse Engineering Element Editor. See the various How to Add / Edit topics (please use the ToC at the left), how to reverse engineer entities, typed views and the like from relational model data.
  • Catalog Explorer with its context menus on various nodes to for example exclude a set of elements (like a set of tables) or to rename a schema or table.
  • Designer Preferences and Project Settings, especially the reverse engineering and naming construction sections.
  • On the Field Mappings tab of the Entity Editor or other project element editors the following utilities:
    • Sync selected... button. This button makes it easier to sync a model field's type specification with its mapped target field, e.g. if you changed the mapping
    • Reverse-engineer unmapped target fields button. This button can be helpful if you first decided to have less entity fields than there were target fields in the mapped target, but later on you decided you want to have additional target fields mapped in your entity. Instead of manually defining the fields in the entity first and then map them onto the fields in the mapped target, you can also use this button to easily reverse-engineer a subset of the remaining unmapped fields in the mapped target.
  • Type Conversion Definitions. To auto-assign type converters during the synchronization of the Relational Model Data, it's required that you define a type conversion definition, so the designer can use that conversion definition to decide which field mapping should get a type converter assigned. Be sure to set the AutoAssignTypeConverterToFieldMapping Project Setting to true (default).

Additionally, the rich editing system of the designer for the Abstract Entity Model elements is at your disposal.

Mixing Database First with Model First

It's ok to mix a Database First workflow with a Model First workflow. In the Sync Relational Model Data tab, you can specify which elements are sourced from which source, e.g. source the tables from the Abstract Entity Model and views / stored procedures from a database. You can do that by setting the sync sources in the Sync Relational Model Data tab of the involved schemas to Mixed.