Chapter 6 - Generating Object-Oriented .NET Source from Relational Database

Chapter 6 - Generating Object-Oriented .NET Source from Relational Database

With DB Visual ARCHITECH (DB-VA), you can easily reverse relational database schema to Object-Oriented .NET source. This feature can help user to develop a new application for existing data in relational database. And you do not need to write SQL to insert, query, update or delete records in the database. In this chapter, we will focus on how to use wizards in DB-VA to reverse the relational database schema to Object-Oriented .NET source.

In this chapter:

Introduction
Creating Sample Data
Generating Code from Database Wizard
Configuring Database
Selecting Tables
Configuring Generated Classes Details
Specifying Code Generation Details
Using Generated Sample

Introduction

In this chapter, we will use a MySQL database. You need to import schema to the MySQL database. This schema is used for simulating an existing database schema that you will reverse in DB-VA into Class Diagram and Entity Relationship Diagram. And you will generate Object-Oriented .NET source from the reversed model. Before you start to reverse the schema, you should make sure you have the MySQL 5.0 Community Edition installed.

The MySQL 5.0 Community Edition can be downloaded on http://dev.mysql.com/downloads/mysql/5.0.html .

Creating Sample Data

Open the Command Prompt, Log on MySql database and create database called “store”.

mysql –u root

create database store;

exit;

Use Command Prompt to change to the directory that contains the sample schema. The following is content of the sample schema (store.ddl).

create table contacts (TOrderIndex int not null unique, contact varchar(255), TOrderID int not null, primary key (TOrderIndex, TOrderID)) type=InnoDB;

create table customer (ID varchar(255) not null unique, name varchar(255), discount double, primary key (ID)) type=InnoDB;

create table orderline (ID int not null auto_increment unique, quantity int not null, OrderID int not null, primary key (ID)) type=InnoDB;

create table product (ID int not null auto_increment unique, name varchar(255), OrderLineID int not null, primary key (ID)) type=InnoDB;

create table torder (ID int not null auto_increment unique, OrderDate date, CustomerID varchar(255) not null, primary key (ID)) type=InnoDB;

alter table contacts add index FK_Contacts_7690 (TOrderID), add constraint FK_Contacts_7690 foreign key (TOrderID) references torder (ID);

alter table orderline add index FK_OrderLine_9672 (OrderID), add constraint FK_OrderLine_9672 foreign key (OrderID) references torder (ID);

alter table product add index FK_Product_5274 (OrderLineID), add constraint FK_Product_5274 foreign key (OrderLineID) references orderline (ID);

alter table torder add index FK_TOrder_4869 (CustomerID), add constraint FK_TOrder_4869 foreign key (CustomerID) references customer (ID);

Type the following command to import the schema to the store database.

mysql –u root store < store.ddl
Log on MySql database and list the tables in the store database.

mysql -u root store

show tables;

Generating Code from Database Wizard

New Project in DB-VA called “Store”.
From the menu, select Tools > Object-Relational Mapping (ORM) > Wizard… to open the Wizard.
Select C# in Language and select Generate Code from Database on the Wizard Welcome page and then click Next >.

Configuring Database

In this step, you need to select the database and enter the database information, then DB-VA will use this information to get the database schema to reverse.

Select MySQL (MySQL Connector/Net 1.0) for Driver option.
Download or browse the suitable Driver and Adapter file for your selected driver.

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

After downloaded the driver file, <<MySQL Connector/Net 1.0.6>> will be shown on Driver file and <<MySQL Connector/J 3.1.10>> will be shown on Adapter file.

Fill in the Connection String information of your database.

For Connection String, the Connection URL template for different databases is shown, enter the information for connecting the database.

The default Connection URL template for MySQL is:

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

A sample Connection URL for MySQL is:

Server=localhost;Database=store;User ID=root;

Click Test Connection to test the database connection.

If success to connect with database the Connection Successful dialog box will show, otherwise the Connection Exception dialog will show.
Click Next > to select tables.

Selecting Table

DB-VA uses your previously configured database settings to connect to the database. You can select the database tables which you want to generate persistent classes for. In this example, we will select all the database tables to reverse. You can deselect tables by using the list of buttons between the list of Available Tables and Selected Tables.

Add Selected
Add the selected table from Available Tables to Selected Tables.
Remove Selected
Remove the selected table from Selected Tables to Available Tables.
Add All
Add all tables from Available Tables to Selected Tables.
Remove All
Remove all tables from Selected Tables to Available Tables.

Configuring Generated Class Details

After selecting tables, you will be directed to the Class Details Configuration pane. In this pane, you can define the class details for generating code. DB-VA generates persistent classes based on the information you defined here. You can edit the class details by double-clicking the field. The following is a sample of modifying the Customer class name, association role name and attribute, etc…

Type the package name called “store”. A package will be created to store the generated persistent code. If the package name is not defined, you will be prompted by a dialog box warning you the classes will be generated in the default package.
Change Customer class name to Buyer. You can edit the class name which will be used as the name of the generated persistent code for a corresponding table.
Change the first character of the Buyer class’s Association Role Name from upper case to lower case. You can edit the role name for a reference in the class.
Modify the Buyer class’s Attribute from ID to custID.

Click Custom Code Style button to open Custom Code Style Setting dialog box. You can modify the prefix or suffix of the Class, Attribute and Role Name.

For Type, select the type of Class details - either Class, Attribute or Role Name (PK), that you want to apply code style.

For Prefix/Suffix, select either Prefix or Suffix to be added or removed.

For Add/Remove option, select the option for the action of code style to be applied.

For the textbox, enter the word for either prefix or suffix.

For Scope, select the scope of the code style to be applied to, either All or Selected.

The table below shows the result of applying code styles.

Code Style	Before Applying	After Applying
Add Prefix (E.g. pre_)	Item	pre_Item
Remove Prefix (E.g. pre_)	pre_Item	Item
Add Suffix (E.g. _suf)	Item	Item_suf
Remove (E.g. _suf)	Item_suf	Item

Specifying Code Generation Details

This is the final step to specify .NET code generation details, you can select the location of the code generation, C# Assembly Name, etc… according to your requirements.

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.
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.
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 loading the associated objects when the main object is loaded. If you enabled (checked) lazy collection initialization, associated objects (1 to many) will not be loaded until you access them (e.g. getFlight(0)). Enabling this option can usually reduce more then 80% of the database loading.
Output Path
Specify the location of C# persistent code generation.
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.
Persistent API
Select the type of persistent code to be generated, either 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 the PersistentObject.
- POJO - Client uses the PersistentManager to retrieve and persist the PersistentObject.
Create Criteria
You can check the Generate Criteria option to generate the criteria class for each ORM Persistable class. The Criteria is used for querying the database in 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 generate the sample and check the create C# project option so that you can modify the sample to execute the generated C# persistence class in Visual Studio .NET 2003.

You have to select All Properties of Override toString Method when you execute the list data sample so that you can read the persistent object information.

Click Finish, the Generate ORM Code/Database dialog box appears showing the progress of code generation. Click Close when the generation is complete.

A class diagram and an entity relationship diagram will be generated automatically and added to your project. The generated persistent C# code and the required resources will be generated to the specified output path.

The Class Diagram:

The ER Diagram:

Generated C# code:

Using the Generated Sample

You have selected to generate the sample for the persistent code, so you can modify the sample code slightly to test and execute the .NET code. If you have entered the package name for the generated Java code, the sample code will be generated in the ormsamples package. The ormsamples package contains the following files:

Class File	Function
CreateStoreData.cs	Create persistent objects and save objects to database.
CreateStoreDatabaseSchema.cs	Export the schema to database.
DeleteStoreData.cs	Delete persistent objects from the database.
DropStoreDatabaseSchema.cs	Remove the schema in the database.
ListStoreData.cs	List all the persistent objects in database.
RetrieveAndUpdateStoreData.cs	Get the persistent object from the database and modify the object attributes.

We will demonstrate how to modify the CreateStoreData.cs to create the persistent objects and relationships from the generated C# code and save the persistent object to database.

The Original CreateStoreData.cs file, CreateData() method:

private void CreateData() {

PersistentTransaction t = store.StorePersistentManager.Instance().GetSession().BeginTransaction();

try {

store.Contacts lstoreContacts = store.ContactsFactory.CreateContacts();

// Initialize the properties of the persistent object

lstoreContacts.Save();

store.Buyer lstoreBuyer = store.BuyerFactory.CreateBuyer();

// Initialize the properties of the persistent object

lstoreBuyer.Save();

store.Orderline lstoreOrderline = store.OrderlineFactory.CreateOrderline();

// Initialize the properties of the persistent object

lstoreOrderline.Save();

store.Product lstoreProduct = store.ProductFactory.CreateProduct();

// Initialize the properties of the persistent object

lstoreProduct.Save();

store.Torder lstoreTorder = store.TorderFactory.CreateTorder();

// Initialize the properties of the persistent object

lstoreTorder.Save();

t.Commit();

}

catch(Exception e) {

t.RollBack();

Console.WriteLine(e);

}

The modified CreateStoreData.cs file, CreateData() method:

public class CreateStoreData {

private void CreateData() {

PersistentTransaction t = store.StorePersistentManager.Instance().GetSession().BeginTransaction();

try {

//create persistent object instance

//create Contacts

Console.WriteLine("Create persistent objects.");

store.Contacts lstoreContacts = store.ContactsFactory.CreateContacts();

lstoreContacts.Contact = "contact : 12345678";

lstoreContacts.TOrderIndex = 1;

//create Buyer

store.Buyer lstoreBuyer = store.BuyerFactory.CreateBuyer();

lstoreBuyer.Discount = 0.9;

lstoreBuyer.Name = "Judy";

lstoreBuyer.CustID = "judy";

//create Torder

store.Torder lstoreTorder = store.TorderFactory.CreateTorder();

lstoreTorder.OrderDate = DateTime.Now;

//create Orderline

store.Orderline lstoreOrderline = store.OrderlineFactory.CreateOrderline();

lstoreOrderline.Quantity = 10000;

//create Product

store.Product lstoreProduct = store.ProductFactory.CreateProduct();

lstoreProduct.Name = "Chocolate";

//create relationship

Console.WriteLine("Create the relationships between persistent objects.");

lstoreTorder.Customer = lstoreBuyer;

lstoreContacts.TOrder = lstoreTorder;

lstoreOrderline.Order = lstoreTorder;

lstoreProduct.OrderLine = lstoreOrderline;

//save the persistent objects

Console.WriteLine("Save the persistent objects.");

lstoreBuyer.Save();

t.Commit();

}

catch(Exception e) {

t.RollBack();

Console.WriteLine(e);

}

Uncomment the line CreateStoreData.Main(args); in SampleCenterControl.cs and modify it to execute the generated sample. The persistent objects will be created in database.　 You can execute ListStoreData.java to show the information of all the created persistent objects.

The ListStoreData.cs file, listTestData() method:

public void ListData() {

System.Console.WriteLine("Listing Contacts...");

store.Contacts[] lstoreContactss = store.ContactsFactory.ListContactsByQuery(null, null);

int length = Math.Min(lstoreContactss.Length, ROW_COUNT);

for (int i = 0; i < length; i++) {

System.Console.WriteLine(lstoreContactss[i]);

}

System.Console.WriteLine(length + " record(s) retrieved.");

System.Console.WriteLine("Listing Buyer...");

store.Buyer[] lstoreBuyers = store.BuyerFactory.ListBuyerByQuery(null, null);

length = Math.Min(lstoreBuyers.Length, ROW_COUNT);

for (int i = 0; i < length; i++) {

System.Console.WriteLine(lstoreBuyers[i]);

}

System.Console.WriteLine(length + " record(s) retrieved.");

System.Console.WriteLine("Listing Orderline...");

store.Orderline[] lstoreOrderlines = store.OrderlineFactory.ListOrderlineByQuery(null, null);

length = Math.Min(lstoreOrderlines.Length, ROW_COUNT);

for (int i = 0; i < length; i++) {

System.Console.WriteLine(lstoreOrderlines[i]);

}

System.Console.WriteLine(length + " record(s) retrieved.");

System.Console.WriteLine("Listing Product...");

store.Product[] lstoreProducts = store.ProductFactory.ListProductByQuery(null, null);

length = Math.Min(lstoreProducts.Length, ROW_COUNT);

for (int i = 0; i < length; i++) {

System.Console.WriteLine(lstoreProducts[i]);

}

System.Console.WriteLine(length + " record(s) retrieved.");

System.Console.WriteLine("Listing Torder...");

store.Torder[] lstoreTorders = store.TorderFactory.ListTorderByQuery(null, null);

length = Math.Min(lstoreTorders.Length, ROW_COUNT);

for (int i = 0; i < length; i++) {

System.Console.WriteLine(lstoreTorders[i]);

}

System.Console.WriteLine(length + " record(s) retrieved.");

}

Uncomment the ListStoreData.Main(args); in SampleCenterControl.cs and execute the sample.

The result of execute ListStoreData.cs:

Listing Contacts...

Contacts[ TOrderIndex=1 Contacts=contact : 12345678 TOrder.Persist_ID=1 ]

1 record(s) retrieved.

Listing Buyer...

Buyer[ CustID=judy Name=Judy Discount=0.9 torder.size=1 ]

1 record(s) retrieved.

Listing Orderline...

Orderline[ ID=1 Quantity=10000 Order.Persist_ID=1 Product.size=1 ]

1 record(s) retrieved.

Listing Product...

Product[ ID=1 Name=Chocolate OrderLine.Persist_ID=1 ]

1 record(s) retrieved.

Listing Torder...

Torder[ ID=1 OrderDate=2006/1/3 上午 12:00:00 Customer.Persist_ID=judy Contacts.

size=1 Orderline.size=1 ]

1 record(s) retrieved.

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