object/relational mapping...

[XyBoy]

Hi,

 

I'm new to .NET and am trying to take advantage of the object structure while accessing relational databases.

 

I started a small project for managing "projects".

 

==== Data Model ====

 

Here is a description of my data structure :

 

A project would be made of tasks, and each task can contain sub-tasks, so I would have something like

- Project1

- task1

- subtask A

- subtask B

- task 2

- Project2

...

(each line being a task).

 

Then, each task can "depend" on others : it could only start when all the tasks it "depends on" are performed. For instance : Project1/task2 "depends on" Project1/task1/subtaskA.

So I end up with the following datamodel :

 

[Table "TASKS"]

int id_task [key] : unique identifier of the task

string name, description : ...

int id_parent : identifier of the parent task

 

[Table "TASK_RELATIONS"]

int id_task_1 [key] : identifier of the "preceding" task

int id_task_2 [key] : identifier of the "following" task (the one that can only be started once id_task_1 is done).

 

==== Object Model ====

 

And (here comes the problem), I would like to have an object structure close to the following (I describe each type ; "+" indicates a public property or method of the described type) :

 

public static "MyApplication" : "global" container object.

+ TaskCollection "Projects" {get;} : collection of all the top-level tasks (id_parent=0).

+ TaskCollection "AllTasks" {get;} : collection of all the tasks.

 

public TaskCollection : a collection of "Task"s.

+ Task this[int index] {}

 

public Task : a task

+ int ID (this one might not even be useful as a public prop)

+ string Name, Description : ...

+ string FullName {get;} // returns the concatenation of this task and of all its ascendants : "Project1/task1/subtaskA" for instance.

+ Task Parent {get;} // returns the task's parent task.

+ TaskCollection Children {get;} // returns a collection with all the children tasks

+ TaskCollection PrecedingTaks // returns the tasks that must be completed before this task can begin

+ TaskCollection FollowingTask // returns the task that can begin once this one is done.

 

MyApplication contains also a "Data" property that is a an object holding my 2 DataTables (MyApplication.Data.Tasks and MyApplication.Data.Task_relations).

 

==== Mapping the 2 models ?? ====

 

My BIG question is : how do I operate the mapping between the two (the DB data model and the Object structure) ?

 

To have you better understand the requirements of my model, here is a description of a "Projects" Form I already started to write to allow the user to edit the projects :

It contains a tree on the left (that uses MyApplication.Projects to display all the tasks), and a "detail" pane on the right. The detail pane is bound to MyApplication.AllTasks, and the position of the BindingManager is set by the Tree when the user clicks a task.

The pane on the right contains standard controls (TextBox "txt_Name", with property Text bound to "Name") and some more complex controls (ListBox "lst_preceding_tasks" with property DataSource bound to "PrecedingTask").

 

Here are the first conclusions I could draw from reading the documentation :

 

- for my "Tasks" to be bindable and editable in Windows Forms, TaskCollection should implement IBindingList, and Task should implement IEditableObject.

 

- I wanted to extend the DataView and DataRowView classes as follow :

Task : DataRowView {}

TaskCollection : DataView {}

and make them "point" to the MyApplication.Data.Tasks DataTable.

I had to give up as I saw that DataRowView cannot be inherited (problem with the constructor, which is internal, or something like that... [could you inherit it ?]).

 

- Finally I tried to write to new classes "containing" the DataRowView and DataView classes :

 

public class Task : IEditabledObject {

private DataRowView row;

public Task (DataRowView aRow) {

row = aRow;

}

public string FullName {...}

...etc.

}

 

public class TaskCollectoin : IBindingList {

private DataView view;

public TaskCollection (string filter) {

view = new DataView (MyApplication.Data.Tasks, filter, "", DataViewRowState.CurrentRows);

}

... (implementation of some of the IBindingList interface methods and properties) :

object AddNew() {

return new Task(view.AddNew());

}

object this[int index] {

get {

return new Task (view[int]); // what do you think of this ?

}

}

... (mapping of ALL other methods and properties on the view object) :

bool IBindingList.AnyProperty { get{return view.AnyProperty;}}

...

}

 

 

What do you think of this solution ? Is it the right way to do it ?

 

Here is a first problem, to begin with :

"Linking" a TaskCollection to a DataView of MyApplication.Data.Tasks is OK as long as I want to use it to edit the tasks. So for MyApplication.Projects it is fine. I can Add Tasks to the Collection, it will insert a new row in my "Tasks" table.

But for the "Children" or "PrecedingTasks" properties of the Task object, the collection should rather be linked to the MyApplication.Data.Task_relations table. Adding a Task to the collection should insert a row in the "Task_relations" table !

Should I have two different objects ?

For instance a TaskCollectionBase object, and one extension of this class per type of collection I need ? (PrecedingTaskCollection and FollowingTaskCollection would point to the same table, but adding a Task to them would insert a row with id_task_1=this.row[id_task] for the one and with id_task_2=this.row[id_task] for the other).

 

Well, thank you very much for any hint, piece of advice, or comment.

Please tell me if you know of any documentation on that topic, or forums more specifically dealing with that kind of problems...

 

Thanks again !

Olivier.

[jssk]

Sorry that I do not answer your questions, but why don't you try an easier way when there is one:

 

http://www.15seconds.com/issue/031013.htm

[XyBoy]

OODBMS, yes but........

 

Well, thanks for this link !

 

Object Databases are definitely an option that looks good to me.

 

The problems with it are :

 

1. I don't know of a free OODBMS that is as good as Access or MS SQL Server (in terms of performance + windows compatibility (drivers) + .NET integration). Matisse seems to have all of this, but I don't know the prices.

 

2. We as developers aren't always able to make technology choices. Employers/Customers often want to have a .NET project running on the same DB as other, older applications. Having to switch to a new DBMS might dissuade most of them to switch to .NET....

 

So I would really like to discuss the solutions that we can set up for mapping a .NET object model to a relational DB.

 

 

Let me first look at the so-called mapping "solutions" described in the 15seconds article :

 

1. Loading the objects with DataReaders

 

As explained in the article, this is a good solution for the data to be stored only once in memory. Each object retrieves its data in the constructor (or just after, or, even better, when the corresponding properties are called (just-in-time retrieval)).

Drawbacks :

- the object has to manage its own data. It must in particular know what data has been modified or deleted, in order to issue the corresponding Update and Delete statements when saving (in short : each object must have a transaction management policy, or at least some transaction supporting methods (if the transaction is managed on a application or per-window scale).

- loading of data must be smartly done. The same data should not be loaded twice. There must hence be some kind of collection or factory objects that manage the loading of other objects. If two methods want to access the same object, the object should be instantiated only once (we could also think about cloning the existing object, but we have to think about synchronization policy between the two instance of the same object). To achieve this, the factory object should maintain the list of currently loaded objects (a list linking each instance pointer to the object id should be ok for this). If the factory object is also a collection (exposing a list of all the objects of a given type that exist in the DB), it contains data that is also part of the instances. So we have to find a way to keep it synchronized with the data of the loaded objects (when updating the DB, each objects has to update the collection's list if necessary).

 

2. Loading the data through a DataSet

As also said in the article, this is a good solution to handle the relations between tables : you define once the dataset, with its datatables and relations, and then it's easy to navigate between parent/child records. BUT :

- I think the way the objects' data management described by this article is dumm : the data is loaded from the DataBase into DataTables, and then loaded from the DataTables into the objects (upon instantiation or, again, when needed). So the data is stored twice in memory. The article's author points this problem, but he obviously did not think (or want to tell us) of a better solution... [we'll discuss better solutions later]

- besides storing data twice, this technique implies that the objects must have a transaction management policy, just as in the DataReader's case. This transaction management can be done in two ways : either the modified data is stored back in the DataTable and then the DataTable updates the DB table (all this when saving the object), or each property of the object can update the DataTable in the "set" accessor (so the change is immediate) and the DataTable updates the DB table when saving.

- as in the 1st case, the synchronization of the data between two instances of the same object must be thought about...

 

 

 

====== ANOTHER SOLUTION ?? ======

 

I don't have a solution that is really good yet. I am just thinking about it and trying some techniques before I really choose one for my future development. I would be very grateful to anybody who would take part in this discussion to list the different available techniques or bring any comment/idea about this object/relation mapping problem.

 

The aim is to find a main structure for the "business logic" layer of a typical .NET application.

 

I) Requirements

Here are some of the requirements I think we need.

 

1. Object-Tables correspondence

I agree with the analysis of the 15seconds article (http://www.15seconds.com/issue/031013.htm)

A table <-> a class

A row <-> an object (intance of a class)

A column <-> an object's property

 

(I am not sure about inheritance, I believe there are several possibilities.)

 

Note : we should have some kind of collection objects, for some of the tables. It could also be used to navigate relations between objects (see below 'properties'). A collection would contain objects of a given type. Its behavior when adding/deleting objects could vary.

 

2. DataBinding (entity- & collection-objects)

We must be able to bind objects to forms and controls. DataBinding is a great functionnality of Windows Forms, and since it is primarily designed to display DataTables or DataViews, one is always tempted to write an application where the user interface directly access the Data Access Layer (the dataset). This is bad 'cause the business logic of the application is then written in the UI layer (baaaad !). To solve this problem, the objects should be bindable to forms (-> should implement IEditableObject) and the object collections should implement IBindingList. So we can bind to objects of our Business Logic layer, instead of binding to the DataTables/DataViews.

 

3. Object transaction (entity-objects)

The objects should have some kind of transaction management. For now, I see the lifecycle of an object like this:

- instanciation : initialization of its core properties, like the object ID.

- loading of the data : the data should be loaded when needed, for instance in the Get accessors of the properties.

- saving the data : the object should expose a save method that would push the modified data back to the DB.

The management of the transaction itself could probably done at an upper level, with several objects relating to the same transaction. For instance, a window would instanciate several objects when loading, then work on them, and finally save them when closing.

 

4. Object properties

An object should expose the following properties of the following types :

- persistent properties : properties that correspond to a DB column. Can be read and written.

- collection properties : a property that returns a collection of related objects. For instance, in the example given in the now famous article, a Task would have a "Predecessors" and a "Sucessors" properties that would return a collection of tasks. Although both are collections of tasks, their behavior would not be the same (think of an Add(aTask) method : both method will use it to add a row in the "PredecessorsSuccessors" table, but the tasks ID's would not be stored the same way!).

- other properties : any custom property that we need for our business logic. This include non-persistent properties (the one that are useful only during one instanciation of the object), computed properties (concatenation of other properties, ...) etc.

 

5. Synchronization between objects

Depending on the application, some objects should be only instantiable once (we would then use a factory), or several times. In the latter case, the data between two instances of the same object should be synchronized. When modifying an instance, the other instance would ideally receive an event.

 

... maybe others ?

 

 

[ well, see you all soon, I will describe tomorrow the (parts of a) solution I am working on to match (some of) these requirements ;-)

Until then, any comment, idea would be much appreciated !! ]

 

Olivier.

[XyBoy]

Well, first, here is what I wanted to do. I might drop the idea as I have found a library that might do the mapping for me (see below)....

 

The idea was to hold all the data in a dataset. In the examples given in the 15seconds article (and in the usual object architecture), each object contains its own data.

If we store the data in a dataset, the objects would only contain methods, and work on a DataRow. Each class would map to a DataTable, and each instance would be attached to a DataRow of its class's DataTable.

I've started to implement this using DataView and DataRowViews, but I'm having to many problems with handling DataRowViews (among other problems, they cannot be inherited).

 

So the idea would be to have :

- collection objects : map to a DataTable ; can be browsed (indexer return an object of the given type) ; implements IBindingList.

- business objects : map to a DataRow of a given Table ; would contain a property per DataTable's column, plus some custom properties ; optionally implements IEditableObject (or we can add a layer of objects inheriting from the business objects and implementing IEditableObject, a little bit like the DataRow/DataRowView pattern).

 

- the transaction is handled at a dataset level : since each object is manipulating the data of the datatables, saving (update()) a DataTable would save all the objects of a given type. The typical scheme would be to load a dataset when opening a window, create/use objects and save them (update the dataset) when closing the window.

- the data could be loaded when needed. A DataTable can be loaded in several times (if the Adapter Fills the DataTable using the Primary Keys).

 

Well, that's it for now. I may try to implement this seriously at a later time. Right now I'm getting interested in what Christian Liensberger has developed : the OPF.Net library (Object Persistent Framework for .NET).

It's free and I've just tried it yesterday, it seems to work pretty well. Check this out : http://www.littleguru.net/ and http://www.codeproject.com/csharp/opfnet.asp for an introduction.