Friday, September 12, 2008

IBM DB2 databases with Enterprise Core Objects and ASP.NET

Original Source click here

Level: Intermediate

Bob Swart (drbob@chello.nl), Author, Trainer, Consultant and Webmaster, Bob Swart Training & Consultancy

26 Jan 2006

Use Borland Delphi 2005 and the Enterprise Core Objects (ECO) to import an IBM DB2® Universal Database™ (DB2 UDB) database and create a UML object model for it, which is the basis for an ASP.NET® Web application. Following up on the previous ECO article (which produced a WinForms application), this article provides examples that use IBM DB2 UDB as the basis for the EcoSpace, present the UML model of the Employee and Department classes with associations, and generate ASP.NET Web forms.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


Using Enterprise Core Objects II

Last time, we created an ECO WinForms Application in Delphi 2005, but this time, we'll make an ECO ASP.NET Web Application.

Start Delphi 2005, select File > New > Other, and from the Delphi for .NET Projects, double-click the ECO ASP.NET Web Application icon, as shown in Figure 1.


Figure 1. Delphi 2005 Object Repository
Delphi 2005 Object Repository

This starts the ECO ASP.NET Web Application wizard, where you only have to specify the name of the project, like ECO2DB2ASP in this case (see Figure 2). Optionally, you can specify the location, although by default the name of the project will be used as the name of the new ASP.NET virtual directory in IIS as well.


Figure 2. New ECO ASP.NET Web Application
New ECO ASP.NET Web Application

Click OK to create the ECO ASP.NET Web Application, which consists of a number of generated source files.

Before you continue, compile and save the project, close the project and reopen it again. This will ensure that the ECO components that are already placed in the different forms and designers start with the correct value (the type of the EcoSpace for example), which is only known after compiling the project.

After you've reopened the project, you have to perform three steps: design your UML model, create and specify the (empty) IBM DB2 UDB database to store the UML model, and design the ASP.NET Web user interface for the model.

Designing the UML model

Like last time, I will focus on the EMPLOYEE and DEPARTMENT tables in the IBM DB2 UDB SAMPLE database, and recreate them as Employee and Department classes in the ECO UML model.

Click the Model View tab in the Project Manager, and open the CoreClasses node. Then double-click the CoreClasses child node, which will start the UML Designer. Here you can use UML design elements to draw your design for the Employee and Department classes, like we did last time for the WinForms example (so this will be a bit shorter and quicker than in the previous article).

The IBM DB2 UDB SAMPLE database contains an EMPLOYEE table with an EMPNO field that is used as identifying field, both for itself (as key field, although it's not specified to be the key field), and for the DEPARTMENT table which uses it for the MGRNO field. The EMPLOYEE table also has a WORKDEPT field, which can be used to map with the DEPTNO field in the DEPARTMENT table. And finally, the DEPARTMENT table has a ADMRDEPT field which is a circular link to itself (identifying departments that manage other departments). These key and foreign key fields are useless in UML object models, so we will not add them to the class designs, but will implement the implicit functionality that they stand for using explicit associations.

Designing the Employee class

Right-click the designer area of the CoreClasses designer and use the pop-up menu to add a new ECO Class. Name it Employee.

The IBM DB2 UDB SAMPLE database used the EMPNO field as unique value to determine the employee, which was used by the DEPARTMENT table as well, but you can skip this field (just like the WORKDEPT field), since ECO itself will add unique index fields to identify class instances.

Right-click the Employee class and use the pop-up menu to add attributes. Add the FirstName attribute of type String, the MidInit attribute of type WideChar, the LastName attribute of type string, the PhoneNo attribute of type String, the HireDate attribute of type DateTime, the Job attribute of type String, the EdLevel attribute of type Integer, the Sex attribute of type WideChar, the Birthdate attribute of type DateTime, and the Salary, Bonus, and Comm attributes of type Currency.

Designing the Department class

Right-click on the designer area and add a new ECO Class called Department. You can skip the DEPTNO field (just like the EMPNO field), so start with the DeptName field of type String. The MGRNO field is actually a link back to the EMPLOYEE table, and we'll deal with that in a minute when adding associated, so skip that field as well. Same with the ADMRDEPT field, which is actually a link from the EMPLOYEE table back to itself. The only field you need to add is the LOCATION field of type String.

Adding Association Relations

With the Employee and Department classes designed, you must now design the associations between them (which were implicitly defined using the EMPNO and WORKDEPT fields of the EMPLOYEE table, and the DEPTNO, MGRNO, and ADMRDEPT fields of the DEPARTMENT table). The WORKDEPT field of the EMPLOYEE table obviously pointed to the DEPARTMENT table, using the DEPTNO field to link to. This can be replaced by an association from Employee to Department. In the Tool Palette, select the Association element, then click on the Employee class, hold down the mouse button, and drag to the Department class. This will draw an association between Employee and Department. Use the Object Inspector to set the name of the new association to Employment.

An has two ends: one at the Employee side and one at the Department side, both set to 0..1 multiplicity by default. Based on the fact that the original WORKDEPT field in the EMPLOYEE table pointed to the DEPTNO field in the DEPARTMENT table, you can assume that an Employee can link to only one Department (or none, since WORKDEPT is a nullable field), so the multiplicity can remain 0..1. For the Department, it means that zero or more Employees can be linked to it. So at the Employee side, the multiplicity must change from 0..1 to 0..*.

Now you must add the link back from the Department class to the Employee class, implementing the MGRNO field. This time the multiplicity must be set so that a Department is managed by 0 or 1 Employees (MGRNO is nullable), and an Employee can in fact manage zero of more Departments.

Only one relationship left: replacing the ADMRDEPT field, which is between the Department class and itself (one Department is managed by another Department, which in turn can manage multiple Departments). This is done by an association between Department and itself. The final UML model can be seen in Figure 3.


Figure 3. Employee-Department UML model
Employee-Department UML model

Although you no longer have the EMPNO and WORKDEPT fields (in the EMPLOYEE table) nor the DEPTNO, MGRNO, and ADMRDEPT fields (in the DEPARTMENT table), the new UML model can store the same information through the explicit association relations.



Back to top


Creating an empty IBM DB2 ECO Database

Normally, you would need an empty database as a container to store the EcoSpace. Delphi 2005 comes with a CD-ROM that contains the IBM DB2 UDB Developer edition. You can use the Control Center (found in the IBM DB2 General Administrator Tools), and in the Control Center use the treeview to browse your system, DB2 instances and databases. Create a new database using the wizard, which will give you a 7-step wizard where you only have to specify the name of the new empty database (something like ECO2ASP).

The SQL DDL which is used to create the database is as follows:

CREATE DATABASE ECO2 ON C: USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM;

After the empty IBM DB2 UDB ECO2 database has been created, you need to go to the Data Explorer and add a new DB2 Connection to the ECO2 database. Make sure to specify the right databasename (ECO2) and a valid username and password to connect to it.



Back to top


Configuring the EcoPersistenceMapperProvider

With the UML model ready and the empty IBM DB2 UDB ECO2 database created, it's time to make the model persistent. Last time, in a single-user WinForm application, you could place the PersistenceMapperBdp component in the EcoSpace unit. However, this time you're building a multi-user ASP.NET Web Forms application, which means the application must be multi-user aware, sharing the model in different threads. This is handled by a special EcoPersistenceMapperProvider unit.

In the Project Manager, double-click the EcoPersistenceMapperProvider.pas node, and then click the design tab so you can place the required components. First, place a PersistenceMapperBdp component on the EcoPersistenceMapperProvider design area, and click on the DB2 Setup link at the bottom of the Object Inspector.

Then drag the BDP Connection component from the Data Explorer (the one pointing to the empty IBM DB2 UDB ECO2 database) and place it next to the PersistenceMapperBdp component. If you do it in this order, then all the necessary properties will be linked automatically (that is, the PersistenceMapper property of the EcoPersistemceMapperProvider will be pointing to the PersistenceMapperBdp component, and the Connection property of the PersistenceMapperBdp will be pointing to the BdpConnection component).

There's one property you must set manually, and that's the SyncActive property of the PersistenceMapperBdp component. Set this to True.

You must now generate the required tables for the EcoSpace and add them to the empty IBM DB2 UDB ECO2 database. To do this, click the leftmost button at the bottom of the EcoPersistenceMapperProvider designer, which says Generate Schema. After you click the button, the application is recompiled, and a dialog box displays which tables can (optionally) be deleted, which tables must be dropped and recreated, and which new tables will be created in the database, as shown in Figure 4.


Figure 4. Generate Schema
Generate Schema

After you click the OK button, the seven new tables are added to the IBM DB2 UDB ECO2 database. Apart from the EMPLOYEE and DEPARTMENT tables, you get five ECO specific tables that are needed for the EcoSpace management itself.

There is only one step left: building a user interface on top of the EcoSpace, this time using ASP.NET Web Forms instead of .NET WinForms.



Back to top


Building the ASP.NET user interface

The ASP.NET user interface is built in the WebForm1.aspx file. To open this unit in the HMTL designer, double-click the WebForm1.aspx node in the Project Manager. Where the WinForm (from last time) had five components already present in the non-visual component area of the WinForms Designer, the ASP.NET Web Forms Designer only shows one component: the rhRoot Reference Handle to the root of the EcoSpace (and all object instances inside it).

You need to add an additional component from the Enterprise Core Objects category, namely an ExpressionHandler. An ExpressionHandle is linked to either a ReferenceHandle (like rhRoot) or another ExpressionHandle. You can use it to evaluate an OCL (Object Constraint Language) expression. In this case, the ExpressionHandle will be called ehEmployee and connected with its RootHandle property to rhRoot.
In the Expression property, you can use the OCL Expression Editor again to build the Employee.allInstances expression, just like last time.

What's different this time is that you also need to set the AddExternalId property of the ehEmployee ExpressionHandler to True (you'll use the ExternalId field in a minute).

Now, place a DataGrid component on the Web Form, and assign its DataSource to the ehEmployee ExpressionHandler. Also set the DataKeyField property of the DataGrid component to ExternalId (this is important), so it will use the internal ECO "key" as an ID for external use (so we can identify which item in the DataGrid is the current one, for example).

The result is that the DataGrid will show the metadata -- the names and types of the attributes of the Employee class, including the ExternalId field (an internal field added by ECO that you don't really need to show).

In order to make sure the ExternalId field is not shown, click the Property Builder verb at the bottom of the Object Inspector. This will start the DataGrid Properties dialog box. Here, you can specify which fields you want to see, and which to hide.

The left side of this dialog box shows five categories. You need the second one, for the columns. In this page, you can select the columns that you want to see (move them from the listbox on the left to the right), for example the FirstName, MidInit, LastName, and PhoneNo but none of the other fields (see Figure 5).


Figure 5. DataGrid Properties
DataGrid Properties

There's one more thing you need to do before closing the DataGrid Properties editor: at the top of the Columns page is the checkbox for the Create columns automatically at run time option. If you don't uncheck that option, then you get your selected columns as well as all normal columns (some of them twice). So uncheck that option to make sure you only get the columns you've selected. That's a feature of Microsoft's ASP.NET DataGrid, by the way, not specifically in combination with ECO or DB2.

After you've unchecked this option, you can click OK to close the DataGrid Properties dialog box.

Now, at the bottom of the Object Inspector there is another verb called Auto Format which you can use to give the DataGrid a quick look. If you click the Auto Format verb, you can select a layout for the DataGrid, like ColorFul 3 (you can see the result in one of the next screenshots).

Viewing the first result in the browser

You can almost view the first result in a browser. There's one thing missing: the ability to add new Employee objects to the EcoSpace. For this, you need a Button control, placed somewhere next to the DataGrid, with the Text property set to New Employee and the following implementation for its Click event handler:


Listing 1. New Employee Object

procedure TWebForm1.Button1_Click(sender: System.Object; e: System.EventArgs);
var
NewEmployee: Employee;
begin
NewEmployee := Employee.Create(EcoSpace);
NewEmployee.Firstname := 'Robert';
NewEmployee.MidInit := 'E';
NewEmployee.Lastname := 'Swart';
NewEmployee.PhoneNo := '42';
UpdateDatabase;
DataBind;
end;

This example creates a new instance of an Employee while passing the EcoSpace as an argument to the constructor, in order to make sure that the Employee is living in our EcoSpace. It also assigns some initial values to the FirstName, MidInit, LastName, and PhoneNo attributes, although it's generally OK to leave these empty for new objects. You can edit them later and enter the correct values.

The call to UpdateDatabase ensures that the underlying database is updated with the contents of the modified EcoSpace. The call to DataBind makes sure that the ASP.NET controls are updated (specifically that the DataGrid displays the new result of the ehEmployee ExpressionHandler).

If you compile and run the application, however, you'll quickly notice that you can enter new Employees, but there is no way to modify them in any way, or to delete them for that matter. And although I am a hard worker, it doesn't seem very sensible to have an EcoSpace with Robert E. Swart as the only Employee available.

Working with the ECO Data in the Browser

So, you need to add the ability to edit the contents of the DataGrid. Select the DataGrid, and click the Property Builder verb again to start the DataGrid Properties dialog box (like in Figure 5).

Go to the Columns page again. Apart from regular columns, you also need to have Buttons.

In the Available columns listbox, scroll down to the Button Column. Select the Edit, Update, Cancel, and Delete buttons, and click on the arrow to add them to the Selected columns listbox. You can customize the buttons, for example changing their type to PushButton instead of LinkButton. When you're finished, close the dialog box.

Now you need to assign the events that the DataGrid fires for the Edit, Update, Cancel, and Delete commands. Go to the Events tab of the Object Inspector, and open up the drop-down combobox for the EditCommand event handler. It displays a number of options (which were generated by the ECO wizards when you created the ASP.NET ECO project). For the EditCommand, connect the event handler to the DataGrid_EditCommand method. Do the same thing for the UpdateCommand (to DataGrid_UpdateCommand), the CancelCommand, and the DeleteCommand.

You can now run the ASP.NET ECO 2 application and use the button next to the DataGrid to create new Employee objects, and the buttons in the DataGrid to edit, update, cancel, or delete the Employee objects again (as shown in Figure 6).


Figure 6. ECO Employees in ASP.NET
ECO Employees in ASP.NET

There is one problem: the DataGrid doesn't contain all attributes of the Employee class (otherwise the DataGrid would be too wide for the screen). So we cannot edit all fields. Come to think of it, wouldn't it be nicer to be able to select an Employee in the DataGrid and click on a Button or Link to go to a detail page where you can edit all fields? A bit like the AutoForms in the WinForms version of the ECO project.

And while you're at it, you could add the ability to add and link to Department objects as well.

Creating hyperlink details links

Go back to Delphi 2005, go to the HTML designer, select the DataGrid, click the Property Builder link again, and add another new colulmn: this time a HyperLink column. Set the Text property to Details, the URL field to the ExternalId, and the URL format property to:
EmployeeDetails.aspx?RootId={0}

This will make sure that for a given row, the Details link will point to the EmployeeDetails.aspx page (which you will need to create), passing as RootId the value of the ExternalId internal key field. Neat, huh?

Adding an employee details page

Now, select File > New - Other, go to the ASP.NET Files category in the Object Repository, and add an ECO ASP.NET Page to the project. Make sure you don't add a regular ASP.NET Page, but an ECO ASP.NET Page. Only the latter has the code to support ECO (hooking into the DataGrid's events, and retrieving the RootId value as starting point for the rhRoot Reference Handle, pointing to the right Employee class instance).

Now you need to save the new ECO ASP.NET Page in EmployeeDetails.aspx (since that's the pagename that you used in the URL format property of the Details Hyperlink).

In the code editor for the EmployeeDetails.pas file, add the CoreClassesUnit to the uses clause, so that the new ECO ASP.NET Page knows about the class types in the EcoSpace. Then, go to the Design view again, and click on the rhRoot and set the EcoSpaceType to ECO2DB2ASPEcoSpace.TECO2DB2ASPEcoSpace (just open up the drop-down combobox for the EcoSpaceType property and select the only choice available).

Set the StaticValueType to Employee (this you will have to enter manually), to indicate that the element that the rhRoot points to is an Employee, so that you get valid design-time support.

Now, place an ExpressionHandler component next to the RhRoot, call it ehEmployee, set its RootHandle property to rhRoot, the AddExternalId property to True, and the Expression property to self (which will be the current Employee class instance that was selected in the previous ASP.NET page).

Now, instead of adding a DataGrid, which will again be too wide if you add all columns to it, you should instead add individual ASP.NET TextBox controls to bind to the attributes of the Employee.

Place a TextBox control on the ECO ASP.NET Web Form, and click on the ellipsis (...) for the DataBinding property. This will open a dialog box that you can use to connect the Text property to the ehEmployee[0]'s FirstName field, as shown in Figure 7.


Figure 7. Simple DataBinding to ECO Expression Handle
Simple DataBinding to ECO Expression Handle

You can do this for all individual fields, and end up with a long detail page showing the TextBoxes one under each other. In order to get a nice layout, it's recommended to use an HTML table that you can insert using Table > Insert - Table. An HTML table of 12 rows with 2 columns is enough for the FirstName, MidInit, LastName, PhoneNo, HireDate, Job, EdLevel, Sex, Birthdate, Salary, Bonus, and Comm attributes. It helps if you give the TextBox controls sensible names like tbFirstName, tbMidInit, and so on.

After you've added the 12 ASP.NET TextBox controls in the right column of the HTML table, you can enter the labels in the left column, and a Button under the HTML table with the caption Save and Return on it, which you need to implement to save the contents of the TextBox controls back in the current Employee, and return to the original ASP.NET page (which is probably still called WebForm1.aspx).

The code for the Button Click event is as follows, note the use of the sensible names for the TextBox controls:


Listing 2. New Employee Object

procedure TWebForm2.Button1_Click(sender: System.Object; e: System.EventArgs);
var
Emp: Employee;
begin
try

Emp := (rhRoot.Element.AsObject as Employee);
Emp.FirstName := tbFirstName.Text;
Emp.MidInit := tbMidInit.Text[1];
Emp.LastName := tbLastName.Text;
Emp.PhoneNo := tbPhoneNo.Text;
Emp.HireDate := Convert.ToDateTime(tbHireDate.Text);
Emp.Job := tbJob.Text;
Emp.EdLevel := Convert.ToInt32(tbEdLevel.Text);
Emp.Sex := tbSex.Text[1];
Emp.Birthdate := Convert.ToDateTime(tbBirthdate.Text);
Emp.Salary := Convert.ToDouble(tbSalary.Text);
Emp.Bonus := Convert.ToDouble(tbBonus.Text);
Emp.Comm := Convert.ToDouble(tbComm.Text);
UpdateDatabase;
DataBind;
Response.Redirect('WebForm1.aspx');
except
on
E: Exception do
Response.Write(E.Message);
end;
end;

At design time, the ECO ASP.NET Detail Page for the Employee can be seen in Figure 8.


Figure 8. Employee Detail Page
Employee Detail Page

Save All, Compile, and Run the application. You can now add new Employee objects, and click on the Details hyperlink to go to the Employee Details page where you can edit the details, and click on Save and Return to continue. Obviously, if you don't want to save the changes, you need a button that only does the Response.Redirect in its Click event (I leave that as easy exercise for the reader).



Back to top


Adding Departments into the mix

Time to throw the Department class into the mix. On the overview page, where we have the DataGrid showing the four of the Employee fields with Buttons and Detail hyperlinks, plus an Add Button to add new Employee objects, you can do the same thing for Departments.

Place another ExpressionHandle, call it ehDepartment, point its RootHandle property to rhRoot, set the AddExternalId property to True, and as Expression property write Department.allInstances.

Place a DataGrid on the ASP.NET Web Form, connect its DataSource property to ehDepartment, set the DataKeyField to ExternalId, and make sure only the DeptName and Location properties are visible.

This time, there are only two columns, so we can Edit, Update, Cancel, and Delete from the DataGrid without the need for a detail page. But we need a special Add Button, to create new Department instances, implemented as follows:


Listing 3. New Department Object

procedure TWebForm1.Button2_Click(sender: System.Object; e: System.EventArgs);
var
NewDept: Department;
begin
NewDept := Department.Create(EcoSpace);
NewDept.DeptName := 'DeptName';
NewDept.Location := 'Location';
UpdateDatabase;
DataBind;
end;

Note that the DeptName and Location values are only default values that need to be changed using the Edit and Update buttons in the DataGrid, as you can see in Figure 9.


Figure 9. Employee and Departments at run time
Employee and Departmets at run time

Showing related departments

There's one final thing left to do: once a list of valid Departments exists, you can assign the Employee to one of the existing Departments (this takes care of the Employees association between zero or more Employees and one Department -- see Figure 3).

For this, you need to go back to the EmployeeDetails.aspx page. Below the TextBox controls for the selected Employee, you want to show a DataGrid with all available Departments, allowing you to assign the Employee to one of these Departments. First of all, show the current Department assigned to the Employee using the WorksAt association. For these, you can use a new ExpressionHandle called EmployeeDepartment, with its RootHandle pointed to ehEmployee (the ExpressionHandle) and as Expression property self.WorksAt.

This will ensure that the DeptName belonging to the Department associated via the WorksAt link can be obtained using the ehEmployeeDepartment ExpressionHandle. Note that it can have zero or one values, so you cannot use simple databinding to show the result. Instead, you can write one line of code in the Page_Load to determine the value, and then print it:


Listing 4. Select Right Employee

procedure TWebForm2.Page_Load(sender: System.Object; e: System.EventArgs);
var
Id: string;
begin
EcoSpace.Active := True;
Id := Request.Params['RootId'];
if Assigned(Id) and (Id <> '') then
rhRoot.SetElement(ObjectForId(Id));
if Assigned(ehEmployeeDepartment.Element) then
Response.Write('Works for: ' +
(ehEmployeeDepartment.Element.AsObject as Department).DeptName);
if not IsPostBack then
DataBind;
end;

Linking Employees with Departments

The last step shows the associated Department, but doesn't actually make the association. For that, we need a second ReferenceHandle, called rhDepartments. Set its EcoSpaceType property to ECO2DB2ASPEcoSpace.TECO2DB2ASPEcoSpace. Then place a third ExpressionHandle component, set rhDepartment as its root, the AddExternalId property to True, and set its Expression property to Department.allInstances in order to list all available Departments in the EcoSpace. Then, place a DataGrid to display the Department classes, set its DataSource to ehDepartments, DataKeyField to ExternalId, and use the DataGrid Property Builder to make sure only the DeptName and Location fields are shown.

Then, add a Select button with Text property set to Work Here. The implementation of the SelectedIndexChange event is as follows:


Listing 5. Link Employee to Department

procedure TWebForm2.DataGrid1_SelectedIndexChanged(sender: System.Object;
e: System.EventArgs);
var
IDep: IObject;
Dep: Department;
begin
Id := DataGrid1.DataKeys[DataGrid1.SelectedIndex].ToString;
IDep := ObjectForId(Id);
Dep := (IDep.AsObject as Department);
(rhRoot.Element.AsObject as Employee).WorksAt := Dep;
UpdateDatabase;
DataBind;
end;

For all this to work, you must ensure that the new rhDepartment root handle is assigned to the EcoSpace, which can be done in the Page_Load event again.

The final Employee Detail page can be seen in Figure 10 (where I've already assigned Robert E. Swart to work in the Training Department).


Figure 10. Employee detail and Department page
Employee detail and Department page

The other associations -- from Department to Manager and from Department to Department -- are left as exercise for the reader. With the examples shown in this article, I'm sure you can design and implement the rest of this project for yourself.



Back to top


Summary

In this article, you've used Delphi 2005 and Enterprise Core Objects II, building an ASP.NET Web application, while still using the IBM DB2 UDB database as basis for the EcoSpace, presenting the UML model of the data and generating ASP.NET Web forms. I've shown how to create class instances, how to edit, cancel, update, and delete items in the DataGrid, how to jump to detail pages (for a selected item), and even how to build association relations with ASP.NET Web pages.




Back to top


0 Comments: