Chapter 1 - Generating C# .NET, Database and Persistent Library

DB Visual ARCHITECT (DB-VA) can generate C# .NET code, export database schema (DDL) to database and create the persistent library based on your design in the class diagram and entity relationship diagram. DB-VA will generate a high performance O/R Mapping (ORM) layer library that is readily for you to code and build. The ORM library basically intends to takes most of the relational to object-oriented mapping burden off your shoulder. With generated ORM code and library, you can take the plain C# objects to use in the application and tell the ORM layer to persist the object for you (e.g. ObjectDAO.save(myObject);). This chapter gives you an introduction to DB-VA, describe how to configure database, generate database and C# .NET code step by step.

In this chapter:

• Introduction
• Configuring Database
• Generating Database
• Generating C# .NET Code

Introduction

DB Visual ARCHITECT (DB-VA) provides an easy-to-use environment bridging between object model, data model and relational database. You can use visual modeling for both logical data design and physical data design. It also automates the mapping between object model and data model.

In this chapter, we assume that you know how to use the class diagram and entity relationship diagram to design the model (please refer to the Designer Guide for the design with class diagram and entity relationship diagram in details). Class Diagram and Entity Relationship Diagram will be used in this chapter to demonstrate how to use the -VA to export database schema (DDL) to database and generate C# persistent code.

Figure 1.1 - Mapping between classes and entities

Configuring Database

DB-VA covers most of the databases in the market. You can check the latest supported databases version from http://www.visual-paradigm.com/product/dbva/

1. Please open flight.vpp or draw the class diagram above and synchronize to ERD.
2. From the menu, select Tools > Object-Relational Mapping (ORM) > Database Configuration... to open the Database Configuration dialog box.

Figure 1.2 - select Database Configuration

3. Select .NET in Language in drop down menu, select a database and enter database settings. We will use MySQL database in this example.

Figure 1.3 - Database Configuration dialog

4. Enter database setting

For Driver, select a .NET Driver. It contains the default Driver Class and Dialect. You can click the drop down button to modify its Driver Class and Dialect.

Figure 1.4 - The driver configuration

For Driver and Adapter Driver file, you can click the Driver button to select Download Driver and Adapter, Download, Update, or Default Driver. DB-VA will help you to download the most up-to-date driver and adapter driver according to the Driver field information. You also can select Browse...to select a driver and adapter driver file in your computer.

Figure 1.5 - The download button

After downloaded the driver file, <<MySQL Connector/Net 1.0.6>> shown on the Driver file indicates that the .NET driver file is downloaded with the specified version number by DB-VA.

For the Connection String, It provides the Connection String template for different databases. You need to fill in the information for Connection String to connect database.

The original template for MySQL Connection URL:

Server=<host_name>;Database=<database_name>;User ID=<username>;Password=<password>; CharSet=<charset>

The modified template for MySQL Connection URL:

Server=localhost;Database=control;User ID=root;
5. Test the database connection by clicking the Test Connection button

Figure 1.6 - Test Connection button

If success to connect with database the Connection Successful dialog box will show, otherwise the Connection Exception dialog box will appear.

Figure 1.7 - The connection successful/failure message

6. Select a database to be the default database which is the default database connection for generating code and database.
   • Right click on database and select Set as Default

   Figure 1.8 - Set the database as default

DB-VA allows you to change the default database anytime, which means you can change to use any database when you are developing application. And you do not need to worry about the database-specific details because DB-VA will take care of them for you. You only need to configure the target database as default.

Generating Database

Now you can export the database schema from the Entity Relationship Diagram to the default database.

1. From the menu bar, select Tools > Object-Relational Mapping (ORM) > Generate Database... to open the Database Code Generation dialog box.

Figure 1.9 - To Generate Database

2. The dialog box shows the previously configured default database setting.

Figure 1.10 - Database Code Generation dialog

3. Select Generate Database option which specifies the action for the database. Since this is the first time you export database schema, so you can select the Create Database option. DB-VA allows you to select Create Database, Update Database, Drop and Create Database and Drop Database.

Figure 1.11 - Generate Database options

4. Select Export to database option allows altering the database immediately after you click the OK button.

Figure 1.12 - Export to database option

5. Select Generate DDL allows the generation of DDL file.

Figure 1.13 - Generate DDL option

6. If you used some reserved word (e.g. Order) in your database design, you can choose the Quote SQL Identifier option to avoid the naming problem in your design with the target database. Auto -only quote the detected reserved word. Yes -quote all table or column names. No - don��t quote any word(s)

Figure 1.14 - Quote SQL Identifier options

7. Click Database Options button to reconfigure the database settings before generating database.
8. Click OK on the dialog box then DB-VA will export the database schema to the default database and generate the DDL file to the output path.

Figure 1.15 - Generate ORM Code/Database dialog

9. Check the tables created in the MySQL database.

Figure 1.16 - The tables generate in database

10. Read the generated DDL file
create table Flight (ID int not null auto_increment, departureTime date, departingAirport varchar(255), arrivingAirport varchar(255), PlaneID int, primary key (ID)) type=InnoDB;

create table Plane (ID int not null auto_increment, PlaneType varchar(255), maxSpeed double not null, maxDistance double not null, primary key (ID)) type=InnoDB;

alter table Flight add index FK_Flight_1115 (PlaneID), add constraint FK_Flight_1115 foreign key (PlaneID) references Plane (ID);

Generating C# .NET Code

Now you can export the database schema from the Entity Relationship Diagram to the default database.

1. From the menu bar, select Tools > Object-Relational Mapping (ORM) > Generate Database... to open the Database Code Generation dialog box.

Figure 1.17 - To generate code

2. The Database Code Generation dialog box for C#:

Figure 1.18 - Database Code Generation dialog

• Output Path

Specify the location of C# persistent code generation.

• Error Handling

Select the way to handle errors. The possible errors include PersistentException, ADOException.

• Return false/null - It returns false/null in the method to terminate its execution.
• Throw PersistentException - It throws a PersistentException which will be handled by the caller.

Figure 1.19 - Error Handling options

• Exception Handling
  • Do not Show - It hides the error message.
  • Print to Error Stream -It prints the error message to the error stream.
  • Print to log4net - It prints the error message to the log4net library.

  Figure 1.20 - exception Handling options

• Lazy Collection Initialization

Check this option to avoid the associated objects from being loaded when the main object is loaded. Unchecking this option will result in the loading of associated objects when the main object is loaded. If you enabled (checked) the lazy collection initialization, associated objects (1 to many) will not be loaded until you access it (e.g. getFlight(0)). Enabling this option usually reduce more then 80% of the database loading.

• Association Handling

Select the type of association handling to be used, either Smart or Standard.

• Smart - With smart association handling, when you update one end of a bi-directional association, the generated persistent code is able to update the other end automatically. Besides, you do not need to cast the retrieved object(s) into its corresponding persistence class when retrieving object(s) from the collection.
• Standard - With standard association handling, you must update both ends of a bi-directional association manually to maintain its consistency. Besides, casting of object(s) to its corresponding persistence class is required when retrieving object(s) from the collection.

Figure 1.21 - Association Handling options

• Persistent API
Select the type of persistent code to be generated, Static Methods, Factory Class, DAO or POJO.

• Static Method - Client can create, retrieve, and persist the PersistentObject directly.
• Factory Class - Create FactoryObject class for client to create and retrieve PersistentObject. Client still can persist the PersistentObject directly.
• DAO - Client uses the PersistentObjectDAO class to create, retrieve and persist PersistentObject.
• POJO - Client uses the PersistentManager to retrieve and persist PersistentObject

Figure 1.22 - Persistenet API options

• Create Criteria

You can check the option for Generate Criteria to generate the criteria class for each ORM Persistable class. The Criteria is used for querying the database in an object-oriented way (please refer to chapter 9 for more details about the criteria)

• Sample

Sample files, including C# application sample and C# project file for Visual Studio .NET 2003 are available for generation. The generated sample files guide you through the usage of the C# persistence class. You can check the options to generate the sample files for reference.

You need to select to generate the sample and check create C# project because you will modify the sample to execute the generated C# persistence class in Visual Studio .NET 2003.

• C# Assembly name

Specify the name of the assembly for the .NET application which holds the assembly metadata.

• Compile to DLL

By checking the option of Compile to DLL, DB-VA will generate DLL files which can be referenced by .NET projects of language other than C# source.

• Advance Setting
  • Default Order Collection Type - Select the type of ordered collection to be used in handling the multiple cardinality relationship, either List or Map.
  • Default Un-Order Collection Type - Select the type of un-ordered collection to be used in handling the multiple cardinality relationship, either Set or Bag.
  • Override toString Method - Select the way that you want to override the toString method of the object. There are three options provided to override the toString method as follows:
    • ID Only - the toString method returns the value of the primary key of the object as string.
    • All Properties - the toString method returns a string with the pattern "Entity[<column1_name>=<column1_value><column2_name>=<column2_value>...]".
    • No - the toString method will not be overridden.

    Figure 1.23 - Advance Setting dialog

3. Click OK to generate the C# persistent code to the Output Path.
   • bin folder contains the generated code DLL.
   • src folder contains the source code of C#.
   • lib folder contains the persistent library.

   Figure 1.24 - The generated output

Visual Paradigm International Limited Website: www.visual-paradigm.com E-mail: support@visual-paradigm.com