TecnoTRENDS has been using PowerBuilder as its main development platform for years. The ultimate reason for choosing PowerBuilder was the versatility of the Sybase DataWindow control available under this platform. In short, the DataWindow control provides a significant productivity boost in the development cycle by making both the manipulation and visualization of relational data a simple task. As Sybase made the DataWindow control for .NET generally available, we at TecnoTRENDS were given an opportunity to migrate our code base into the .NET platform and, at the same time, improve its development using the DataWindow control by introducing a more object-oriented development approach.

Unfortunately, object-oriented development faces a critical problem that remains largely unsolved, namely, the integration of relational data into the object-oriented development model. Such integration is usually accomplished by using object/relational mapping (ORM) tools, such as Hibernate, a popular Java tool with a .NET incarnation called NHibernate. Standardizing on an ORM tool, however, introduces a major problem for DataWindow developers: how to combine the best practices of both development models. Ideally, developers should be able to use an object-oriented approach and at the same time explore the DataWindow's simplicity in dealing with relational data (see Figure 1).

NHibernate
Before delving into the details of the mapping service, we will review how the NHibernate framework works. In a nutshell, NHibernate populates objects with data from a database, based on an XML file that maps objects and corresponding attributes to tables and corresponding fields, respectively. NHibernate also provides several ways to query the underlying database, just one of which is SQL.

For instance, assume that the PERSON table is mapped to the Person object. We can add a new record to the table using the following code:

ISession session = NHibernateHelper.GetCurrentSession();
Person person = new Person();
Person.Id = "1";
person.FirstName = "John";
person.LastName = "Smith";
person.DateOfBirth = new DateTime(1970, 1, 1);
session.Save(person);
NHibernateHelper.CloseSession();

It is also fairly easy to retrieve a person with ID=1 using SQL:

ISession session = NHibernateHelper.GetCurrentSession();
IQuery query = session.CreateSQLQuery("SELECT * FROM PERSON WHERE ID = ‘1'")
.AddEntity(typeof(Person));
Person person = query.List<Person>()[0];|
NHibernateHelper.CloseSession();

Of course there is more to it than just what was described. For further and more detailed information on NHibernate, visit http://www.hibernate.org.

The Mapping Service
Our answer to the issue of how to combine a DataWindow with NHibernate was to implement a custom ORM service for the DataWindow control. This service let us continue using most of the flexibility of the DataWindow control at design time and visualization at runtime, while letting the programmer manipulate data in an object-oriented way by relying on the persistence services provided by NHibernate.

The implementation of an ORM tool is a difficult task and extrapolates the scope of the activities performed at TecnoTRENDS; therefore, the mapping service is quite simple: it converts data from a DataWindow into objects and vice versa. To accomplish this, the service needs to know only the associations among objects and database entities.

Instead of using the complex NHibernate XML-based mapping, we used a more direct approach based on attributes. At present, the ORM service recognizes:

• TableAttribute: maps tables
• ColumnAttribute: maps columns of a particular table
• ForeignKeyAttribute: maps one-to-one and many-to-one relationships

The first step is to build the object model and then annotate each class with the corresponding attributes described above. The model classes can be generated by PowerDesigner, which can also generate all NHibernate-supporting classes in a generic way via the Data Access Layer (DAL). PowerDesigner can also be customized to create automatically annotated classes.

The service itself does not retrieve or update the data; its only responsibility is to "translate" between the relational and object representations of the data (see Figure 2). The retrieve and update operations are delegated to some other object identified by a UseCase attribute. The mapping service recognizes the UseCase object associated with its DataWindow by name and uses a dispatcher to locate and execute the appropriate method of the UseCase object.

The implementation of the service requires that the Retrieve and UpdateData methods of the DataWindowControl be overridden to call the respective methods of the service. To do this, it is necessary to create a new control inheriting from the DataWindowControl class.

Let's illustrate the use of the service with a concrete example.

Using PowerDesigner 12, we can generate assemblies containing the classes that describe our database model. Say the model contains a PERSON table. Then the Sybase tool creates the following classes:

• Person - class representing the PERSON table
• PersonDAL - concrete implementation of several CRUD methods based on the NHibernate Framework
• IPersonDAL - interface for PersonDAL

PowerDesigner generates a class factory named ConcreteDALFactory to instantiate DAL objects. It defines a static method CreatePersonDAL that returns an IPersonDAL interface. Hence, we can effectively work with the returned DAL interface independently of Nhibernate.

Listing 1 shows the Person class annotated with the mapping service attributes. We assume the names of the table and columns are the same as the corresponding class and attributes. We note, however, that it is possible to pass schema names (table and columns) as parameters to each attribute, as shown in Figure 3, in the code generated by PowerDesigner.

With the Person class properly annotated, it is necessary to inform the mapping service how to find the Person class by calling its static method AddAssembly in the program initialization. Analogously, the UseCase dispatcher must be informed how to find the UseCase implementation (explained later).

static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
MappingService.AddAssembly("Example, Version=1.0.0.0, Culture=neutral");
UseCaseDispatcher.AddAssembly("ExampleCases, Version=1.0.0.0, Culture=neutral ")
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new MainForm());
}
}

 The AddAssembly method iterates through the assembly looking for all types annotated with the TableAttribute in the case of MappingService, and all types annotated with the UseCaseAttribute in the case of UseCaseDispatcher, storing all relevant type information in memory.

The use of the service is straightforward:

 public partial class PersonForm : Form
{
private CustomDataWindow personDataWindow;

public PersonForm()
{
InitializeComponent();
...
personDataWindow.Services.UseMapping = true;
personDataWindow.Services.Mapping.RetrieveStyle = RetrieveStyle.SQL;
personDataWindow.Services.Mapping.RetrieveTypes = new Type[] { typeof(Person) };
personDataWindow.Services.Mapping.UseCaseName = "Person UseCase";
...
}
}

CustomDataWindow is a class derived from DataWindowControl, responsible for calling the Retrieve and UpdateData methods of the mapping service. It enables the service to perform these operations instead of the default implementation. The "Services" property is a repository of services available to the programmer, one of which is the MappingService.

In the first line, we set the RetrieveStyle property to SQL mode. It forces the service to use the query defined in the DataWindow control when retrieving data. To do that, it is also necessary to inform the type of object or objects returned by the query; hence, the need to define the RetrieveTypes property. The other existing mode, RetrieveStyle.DAL, performs the retrieve using a generic method of the DAL object that returns all records from the database.

The retrieve is performed according to the following steps:

1. Information for the retrieve operation is gathered and stored in a structure to be passed to the UseCase object; 
2. Execution of the retrieve command of the UseCase object is requested by the service to  UseCaseDispatcher;
3. The UseCase object returns an ObjectGrid object that stores all the objects returned in a grid-like manner: multiple rows, each with an unique id, that can store several objects as columns;
4. The list of objects returned by the UseCase object is stored and processed by the service; using the mapping attributes and the meta information of its associated DataWindow control, the service reads all the property values from each object and stores them in the respective column of the DataWindow.

For optimization, the service keeps all the objects in memory and associates each row of the DataWindow with its respective row of the ObjectGrid object, using the unique id as a key. To accomplish this, the service has to dynamically modify the DataWindow definition to include a new column to hold the row id.

Usually after a retrieve, we expect one or more updates of the DataWindow. These updates encompass insert, update, and delete operations and are performed according to the following steps:

1. The service processes data from the DataWindow, storing any modifications in the same objects returned by the retrieve operation; it also saves the operation associated with the row (insert, update, or delete) in each row of the ObjectGrid
2. The generated objects are passed to the respective UseCase
3. The service updates the DataWindow to reflect the changes made by the UseCase processing

Now let's explore the implementation of the UseCase object responsible for the retrieve and update operations. Thanks to inheritance, this task is straightforward:

[UseCase("Person UseCase")]
class PersonUseCase : AbstractUseCase
{
   public PersonUseCase() : base(ConcreteDALFactory.CreatePersonDAL())
   {
   }
}

AbstractUseCase only needs a DAL object to function properly; using reflection, it calls the necessary methods for retrieve or update. The standard retrieve behavior, for instance, can use the DataWindow query and execute it using NHibernate. As a generic class, though, it has its shortcomings. On the other hand, it offers several methods that can be overridden, such as Retrieve, Update, Insert, Delete, and Validate, allowing the programmer to customize the behavior of the base class and implement whatever business logic is necessary for the manipulation of the database entity. For instance, it can change the default retrieve operation, returning a list of objects based in the retrieve arguments of the DataWindow:

protected override System.Collections.ICollection Retrieve(object[] args)
{
  if( args == null )
    return base.Retrieve(args);
  return this.Wrap(((PersonDAL)this.DAL).Load(args[0].ToString()));
}

It can also validate all or part of the data before an update or insert operation:

protected override void Validate(object obj)
{
  Person person = (Person)obj;
  if( person.FirstName == "" )
    throw new Exception("Invalid first name");
  ...
}

The default update operation does the following:

1. Begins a transaction
2. Iterates through each row of the ObjectGrid and verifies its pending operation (insert, update, or delete)
3. Tries to execute the operation just for the object represented by the DAL (if the application has to update more than one object, it has to override the default implementation)
4. Commits the transaction and, in case of error, rolls it back

The default implementation of AbstractUseCase is very flexible and is sufficient for most data manipulation cases.

The UseCase objects can be used independently of the mapping service for the implementation of application business rules. The service provides two useful methods:

• GetAllObjects - returns an ObjectGrid containing all DataWindow objects;
• GetObjectsForUpdate - returns an ObjectGrid containing only the objects modified by the user.

Conclusion
The solution we described here is still a work in progress but has the potential to solve the most relevant aspects of the ORM problem for the DataWindow developer, allowing the use of an object-oriented development model while maintaining the use of the Sybase DataWindow control. However, the service still has shortcomings we should point out:

• It adds yet another layer in the application, on top of NHibernate, thereby burdening the overall performance of the application
• In the current implementation, it somewhat duplicates the existing mapping for the ORM layer
• The DataWindow still has to access the database; it needs a Transaction object and has to retrieve ChildDataWindow objects
• The purely relational solution is still the best answer to deal with large amounts of data, like reports

Although the service doesn't completely replace relational data access, which in many cases might be impossible or undesirable, it allows the use of an object-oriented approach for most of the business logic of an application. This justifies the decoupling of the MappingService and UseCaseDispatcher: the latter can be used to retrieve or update the DataWindow as well as execute business rule code independently of the view layer, while the former functions as a bridge between the view and use cases.

References

• Hibernate
• DataWindow.NET
• PowerDesigner
• TecnoTRENDS (in Portuguese)