How to use multiple database types in a project

Say you started your project with a single database type, e.g. SQL Server, and your application now also needs to support Oracle. LLBLGen Pro supports multiple database types per project, which means you can map a single entity model to multiple database types. To migrate your project with a single database type (e.g. SQL Server) to a project with multiple database types (e.g. SQL Server and Oracle), we'll use the build-in model-first technology to quickly build a schema from our existing project.

Be aware that the designer can only create tables, not views nor stored procedures. If you use views and/or stored procedures in one database, you have to migrate these manually to the other database and then include them by using the Sync Relational Model Data tab.

The steps to take are described below. For this tutorial, we'll migrate the project from a SQL Server only project to a project which uses SQL Server and Oracle.

  1. First load your LLBLGen Pro project into the designer.
  2. Make sure the project is without errors, by checking the Errors and Warnings pane in the designer.
  3. Add a storage for relational model data for Oracle.
  4. Open Project Settings and inspect the Model First Development settings and verify the settings are set to the values you want them to be.
  5. If you're using boolean types or other types in the SQL Server project, and you're using the LLBLGen Pro Runtime Framework, you can setup type conversion definitions. If you're setting up type conversion definitions, be sure that in Project Settings, under General the setting Auto assign type converter to field mapping is checked.
  6. Open the Sync Relational Model Data Tab to sync the model with the containers. Click 'Perform Tasks...' to sync the entity model with the empty Oracle meta data container. This will auto-create schema and tables for each entity in the Oracle relational model data storage container.
  7. Verify the creation of the meta-data by re-validating the project. It might be the auto-mapper couldn't create oracle fields because it couldn't find a destination type. Open the entity the field is in and on the Field mappings tab, create the table field manually with the buttons in the lower right corner.
  8. If the project validates correctly, export the Oracle DDL SQL script. This script can then be used to create the actual schema in the Oracle database.
  9. To migrate existing generated code easily, first generate code in an empty folder. This shows you the folder structure which will be used for the project. The folder structure is a little bit different to keep the database specific files separate from each other. Before you actually generate new code into your existing code base's folder, create a folder in your existing code base folder for the database which was already present in the project, in our example 'SQL Server' and move the existing database specific files to that folder. This will make sure that when generating code, LLBLGen Pro will update existing VS.NET project files and existing code files are updated as-is.

When generating code, LLBLGen Pro will make sure the database specific elements are stored in a folder per database type, to keep them separated. See step 9 for migrating existing code prior to generating code.