NConstruct - Intelligent Software Factory

Oracle and NConstruct Author: Sebastijan Pistotnik, member of NConstruct Developers Team
Release date: February, 2008

Oracle and NConstruct Application Builder

1 Installation of Oracle Database 10g Express Edition

- Download and install Oracle Database 10g Express Edition for Microsoft Windows
- Download and install Oracle Database 10g Express Client

You can find installation files on the following URL:

http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html

During the Oracle Database 10g Express Edition for Microsoft Windows installation, a wizard process will ask you for a password for the SYSTEM database account. For the purpose of our example, let's enter:

Password: nconstruct

So, if you want to access the Database Home Page (which will be described later in this first article) after the installation is complete, you will use the following login data:

Username: system
Password: nconstruct

But first, let us clarify some facts about Oracle Database 10g:

1.1 What is Oracle Database 10g Express Client?

Oracle Client is a software that lets you enable remote access to Oracle Database XE Server. If you want to enable remote access to Oracle Database XE Server, install Oracle Database XE Client onto each client computer.

Once you install Oracle Instant Client, you can reference databases you create with editing the TNSNAMES.ora file with the connection information which enables Oracle client software connection with the database.

1.2 What is Oracle TNSNAMES.ora?

The TNSNAMES.ora file is a plain-text file used by Oracle to determine the information needed to connect to a database. You’ll typically find it within the "Oracle Home / NETWORK\ADMIN" directory. Every connection includes the server name (or IP address), the port where the Oracle Listener is running, and the name of the database in view.

Oracle Database 10g Express Edition comes with a default Oracle Database named XE. The TNS entry that appears in the TNSNAMES.ora file is already there (You can check it out in your "Oracle Home / NETWORK\ADMIN" directory):

2 Creating a schema on Oracle database XE

One of the differences between SQL Server and Oracle database is that SQL Server use a notion of multiple databases (Northwind, Pubs, MyDatabase, etc.), while Oracle has a single database (Xe), but multiple tablespaces (i.e. system, users, etc.), multiple schemas/users (i.e. pistotnik, hr, etc). The schema name is the same as your Oracle username. Oracle Database automatically creates a schema when you create a user.

Note:
There is also a great tutorial to start on the following URL:
http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm

2.1 What to do first

2.1.1 Log in to a server

Run Oracle server in a browser with the following URL: http://127.0.0.1:8080/apex

Users access Oracle Database 10g Express Edition through database user accounts. The installation process creates an account named SYSTEM. This account is considered an administrator account because it has DBA access.

So log in as the default administrator user:

Username: system
Password: password_you_entered_in_oracle_installation_process

In our example this would be:

Username: system
Password: nconstruct

2.1.2 Creating a database user

Create a database user (schema) with a username pistotnik and password pistotnik (you do not need to grant him a DBA User privilege):

Click on Administration> Manage Database Users links and click the Create button. You can leave all the default selected options.

Alternative:
You can unlock an existing user hr by following the links: Administration>Manage Database Users, click on hr user and select Unlock item in a combo box.

Log out from an application.

Log in again as:

Username: pistotnik
Password: pistotnik

2.1.3 Loading a sample database script

Click SQL>SQL Scripts>Upload
You can load a sample database script from:
Oracle Home \product\10.2.0\server\demo\schema\human_resources\ hr_cre.sql

In some real installation case, this could be:
C:\oraclexe\app\oracle\product\10.2.0\server\demo\schema\human_resources\hr_cre.sql

Enter a name of a script, for example hrcre.
When the script is uploaded, select it and click the Run button.
Note: If you are using Firefox browser, the content of a SQL file might not be shown, so it might be better to use IE browser.

Now you have a sample database tables installed, actually a database named pistotnik.
You can view your created tables by clicking:

Object Browser>Browse>Tables

The result is shown in the Figure 1.

Figure 1

2.1.4 Loading sample data

Click:
SQL>SQL Scripts>Upload

You can load a sample database script from the path:
Oracle Home \product\10.2.0\server\demo\schema\human_resources\ hr_popul.sql

In our example this would be:
C:\oraclexe\app\oracle\product\10.2.0\server\demo\schema\human_resources\hr_popul.sql

Enter a name of a script, for example hrpopul.
When the script is uploaded, click onselect it and click on the Run button.
Note: If you are using Firefox browser, the content of a SQL file might not be shown, so it might be better to use IE browser.

3 Connection Settings

After running NConstruct application builder and selecting Oracle 10g database type in a Database type selection step, you need to define database connection parameters in an Application settings step in the Connection Settings group (Figure 2).

Figure 2

Let's assume you are using Xe database on Oracle 10g and you created a user named pistotnik and password pistotnik.
To connect as pistotnik / pistotnik using the TNS Alias, a valid input appears as follows:

Server/Database instance name: Xe
User ID: pistotnik
Password: pistotnik

You can obviate the need for .net searching for the tnsnames.ora file, however. Simply instead of entering an alias, enter a Value of an alias that you find in your TNSNAMES.ora file.

For example: If you TNSNAMES.ora file has an alias ORCL defined as:

ORCL = 
	(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 89.212.135.14)(PORT = 1521)) 
		(CONNECT_DATA = (SERVER = DEDICATED) 
		(SERVICE_NAME = orcl) ) )

Then the Value of an alias (ORCL) that you must enter (just copy it) is:

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 89.212.135.14)(PORT = 1521)) 
	(CONNECT_DATA = (SERVER = DEDICATED) 
	(SERVICE_NAME = orcl) ) )

4 Namespace definition

By clicking the Next button, the NConstruct system tables will be created and after selecting the desired graphical user interface, you should get the following screen shown in the Figure 3.

Figure 3

And your database should look like the one in the Figure 4.

Figure 4

5 Object creation and management

The Object creation and management step looks like the one in the Figure 5.

Figure 5

Object Management

5.1 The Oracle naming convention to .NET c# source code issue

As you can see, tables in the Oracle database server are written with upper case letters. If you want to use more readable form for the composed names (composed of more words), the underscore sign ("_") used as a separator is a common way for solving this issue. For example: A table which contains data for history of jobs is named JOB_HISTORY.

Why the Oracle is using such naming convention is another problem. You do not have to stick with it, but then you will have other problems, like using PL/SQL Developer, which is an Integrated Development Environment that is specifically targeted for the development of stored program units for Oracle Databases.
As this is a fact, and, on the other side, most modern OO developers don't want to watch uppercase names in their code, we created methods for parsing such names to more convenient programming names.

For example: NConstruct translates a table named JOB_HISTORY to an entity class name JobHistoryEntity.

The same stands for the column naming.
For example: a database column named START_DATE is transformed to a property named StartDate.

5.2 Oracle Sequences to Object model mapping problem

First, let us emphasize, that if a database is designed properly and according to good recommendations and naming conventions, you shouldn't have problems at all. But since that is not always a case, let's explain the two main conventions.

- When a primary key of a table is not assigned or composed key, it should have a sequence defined.
- The sequence name must assembled by the following conventions:

The sequence name must be:

[Table name]_SEQ.

Example:

Table: DEPARTMENTS
Sequence name: DEPARTMENTS_SEQ

The problem with a sequence is that in the Oracle database, there is no meta information of which sequence belongs to which table. You can find all sequence names in an all_sequences system table only. A sequence isn't linked to data in a table in any way, even if that's where it originally came from.

You can also check the following discussion on the Microsoft forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1710876&SiteID=1

So, for example, let's check how a mapping looks like for DEPARTMENTS database table:

That's mainly all about it. The application can be successfully started and a Windows client application should look like the screen in the Figure 6. A Web application should look like the screen in the Figure 7.

Figure 6

Figure 7