Object/Relational Data Access Layers... 1

[steverbs]

Hi all.

A while ago, my company began implementing the Microsoft .NET framework for application development, and I have a background in Jade, which is a pretty idealistic OO development environment. Jade completely hides the DAL, meaning that all developers have to think about is whether an Object is persistent or not at instantiation time and I have been trying to carry across much of Jade theory to our .NET developments. However, while the O/R DALs I have developed work well enough, I am still concerned about their efficiency (application performance, data-access times, etc.) and am thus constantly changing their structures, which is starting to drive the rest of my development team around the bend.

My question is, is there anywhere, Internet, books or otherwise, that I can read up on Object Relational Mapping? I feel that I have come up with a satisfactory means of persisting individual Objects. My primary concern is what to do about Collection Objects to efficiently achieve one-to-many and many-to-many Object relationships.

Any help is greatly appreciated.

Regards.

Stephen.

 

[jfrost10]

If I read your question right, you may be looking for composition within your objects.

For instance, consider a Department which has a collection of employees.

In my objects, my Department object would have an EmployeeCollection object as one of its attributes, which would hold all the employees for a given department.

In this way, you keep the relationships: one department has many employees. If a department is deleted, than the department object handles dispatching the employees (which in system terms might mean setting a property of the employees to be "no dept" or something of that nature).

Anyway, I hope this was sort of the answer you were looking for. If not, let me know where I got it wrong.


D'Arcy

 

[steverbs]

Cheers for the swift reply D'Arcy. What I was trying to say is that I am already using Collections to form one-to-many, etc, relationships (in the way that you described), but the way in which I am using them concerns me in terms of their efficiency. For example, at what point should the collection be pulled from the database? At the moment, I am using a `load` method to pull the Object’s singular properties from the database whenever the Object is called, while leaving the Collection properties until they are required (these are then loaded via the Object's `get` property methods). This prevents large Collections of objects from being loaded unnecessarily, thus preventing unnecessary processing resources. I am confident in the way that singular Object properties, such as departmentName and departmentExtension from the Department Object etc, are loaded. It is the way that Collection properties, such as allEmployees, are loaded.

What I am really after is a tried and tested, efficient means for accessing Collections of Objects through an OR DAL. I’d love some material covering “OR theory” (if there is such a thing).

Regards.

Stephen.

 

[DonQuichote]

I can recommend "Patterns of Enterprise Application Architecture" by Martin fowler. (see

http://www.martinfowler.com/books.html#eaa

) This book covers a number of ways to handle persistence and also states possible problems in performance and transactions.

Best regards

 

[steverbs]

Nice one! Looks like it might just contain what I am after.

Regards.

Stephen.

 

[dragonwell]

Stephen,

Right there with ya, brother. You should check out Scott Ambler's work on the subject. He had the O/R persistence layer thing down back in the 90's and, from what I can tell, is where Folwer and others got a lot of theory.

This page has links to all his relevant papers:

http://www.ronin-intl.com/publications/index.html

In particular, you'll want to read this:

http://www.ambysoft.com/persistenceLayer.html

For some fundamental stuff, check out this:

http://chimu.com/publications/objectRelational/

AND, last but not least, there is a ton of chatter on the subject of OR/DAL over at the asp.net forums:

http://www.asp.net/Forums/ShowForum.aspx?tabindex=1&ForumID=16

...may one of the gurus there can go into detail on persistent collections in composition.

HTH
David

 

[steverbs]

Thank you David. Looks like I've got some reading to do this weekend.

Regards.

Stephen.

 

[dragonwell]

No problem, Stephen. I just happened to be gathering up this info too.

Let me know what you think of it. I'm just getting into this myself and am still trying to figure it out. Looks like we're pretty much on the same page, although I think you are little more ahead of me with the Jade background.

Speaking of efficiency - in that Ambler paper he talks about substitutions, or stand-in instances of complex objects, where you just have the basic information (like a shell without any of the complex properties or collections it is composed of) such as a key or name field. Just enough to work with in a list until you need to see the details. This addresses the efficiency question. Also, keep in mind that this was writen a few years ago and the RDBMSs of today (SQLServer 2000) are fast enough that a few extra queries to load all of the object's properties doesn't seem to hinder performance (in my experience).

BTW - have you looked at any of the commercially available O/R mappers for .net out there? The two that stand out (obvious if you follow the asp.net forums) are LLBLGen Pro (

http://www.llblgen.com)

and EntityBroker(

http://www.thona-consulting.com).

The EntityBroker seems more like the real deal but it's really a shift in thinking and I can't quite figure it out yet. LLBLGen actually "gen"-erates code based on your db schema so it's a little easier to understand at first, but maybe not as maintainable in the long run?

Regards,

David

 

[dragonwell]

me? a star? I am hardly an expert, but thanks!

 

[steverbs]

Yeah. That stuff helped alot. I now have a concrete strategy for implementing OR mapping. Thankfully, most of what I have read has mainly re-inforced much of what I was already doing, but it has definitely given me a solid direction to follow. That 'Mapping 101"

http://www.agiledata.org/essays/mappingObjects.html

was exactly what I was after.

What really helped was reading the tried and tested means of OR Mapping. The performance issues that were concerning me were covered by the lazy-loading strategy. I had heard of this before, but had no idea as to what it refered.

I have now created two Class templates (one for singular objects and one for collections) that form the basic structure of all of the classes that our team will implement. Are you using .NET your self? If so I can give you the templates if it would help. They are written in VB.NET, but, as you know, they can be directly translated into the other .NET languages.

Thanks again!

Stephen.

 

[steverbs]

Oh, and as for the commercial DALs. I have looked at several (my favorite was the Pragmatier), but I don't like the way that they take alot of the development of my hands. I fear that as I would not know exactly what a 3rd party DAL is doing, if something were to go wrong, or if I needed to add functionality etc, our developments could suffer and this could be quite expensive, not to mention damaging, for the company. For this reason, I feel that it is important to know exactly what is going on in our projects.

Regards.

Stephen.

 

[dragonwell]

Yeah, I'd like to take a look at those templates if you don't mind.

Thanks,

David

 

[steverbs]

OK. If you point your browser to

http://www.redblacksoftware.co.uk/dl/templates.zip

you will get a zip file containing the two templates.

The first template is the Class template. The idea here is that it has all of the foundation structure, and data-access code that is required to build a class. All you need to do to get started using it is to do a find/replace and replace the word 'Classname', with the name of your class (i.e. 'Employee'). Then, all that is left to do is add your desired properties and to update the methods and sql accordingly. The template contains two static (shared) methods that are used as 'Object Factories' and two public methods that are used to store or destroy the object. The class also makes use of an isPersistent flag that the objects check before persisting, so that they know whether to insert or update. The Object shaddow property is a GUID, which can also be used for the object relationships.

The ClassnameCollection is much the same. Just do a find/replace etc. This basically turns the ArrayList into a strongly typed collection of Classname objects and can be persisted to a relational table.

It should all be fairly self explanitory. Also, if you find any problems with the templates, please let me know. I only completed them over the weekend.

Regards.

Stephen.

 

[dragonwell]

Thanks, Stephen - I'll definately take a look at those. BTW - this sounds like a good candidate for the use of CodeSmith (generator based on templates)

http://www.ericjsmith.net/codesmith/

Thanks,

David

 

[steverbs]

One thing I haven't been able to find a solution for in these texts, is how to avoid problems with null-object-references when persisting an object that has associations to other objects.

For example, if I have a Person object that has the property 'myCompany', but myCompany is not set, when I try to get the ID of the non existent Company object I end up with the null object reference error (obviously). At the moment, I am testing each object association first before including it in the SQL INSERT statement, but this is very tedious and inelegant.

Anyone found a better solution?

Regards.

Stephen.

 

[DonQuichote]

That depends. Does a

Person

object always have a reference to a

Company

? If so, It should be handled in the constructor of the

Person

object. If you are creating a new (not stored yet)

Person

object, You either pass an existing

Company

object or create a new one.

If not so, it is "dangerous" to ask for properties of objects that do not necessarily exist.

In my situation, object storage IDs are usually autonumbers (database-generated sequential numbers) that can never be zero. So if I ask for the storage ID of an object and it turnes out to be zero, I know that it does not exist in the database yet. This, off course, should only be meaningful within the object persistence code itself: it means a new record should be inserted in the database instead of an existing one modified.

Best regards

 

[jfrost10]

oooo, Don brings up a good discussion point too: the classic chicken/egg, or in his example, what belongs to what: does a person have a company, or do companies have collections of people?

Thats one thing I've always struggled with: its easy when you have straight heirarchies of things, but what happens when you have a person object, and you need to see what company they work for? It would seem silly to have a company object, with a collection of people, where each had another company object within them, but I don't see how else you'd work around this. seems like alot of objects being created though.

Sorry...just speaking...er typing out loud.


D'Arcy

 

[steverbs]

I see where you are coming from Don. I think we are doing similar things there. Where you would test for a 0-value ID, I test for Null Object References.

D'Arcy, I have that exact same problem all too often. It would be so useful if there was a way to check memory for existing objects before loading them. I was looking into Identity Maps the other day and it seems that they might hold a clue to fixing this problem. Though, it was only a cursory look, so I can't be too sure. I'll look into it.

Steve.

 

[DonQuichote]

I test for the zero ID only in my database layer. I must admit that did not layer my applications too well in the past. I used to put the persistence code in the "business objects". To order my own thoughts on the subject of persistence, I wrote an article on it:

http://huizen.dds.nl/~w-p/article/wrtabrec.htm

jfrost10,
It does not really matter. My lookup code is usually in a table-wrapping collection. If

Person

and

Company

objects have a many-to many relationship, the

Persons

collection would have a

personsForCompany

method that would return the right subset, using a link table in the database behind the screens. Likewise, the

Companies

collection would have a

companiesForPerson

method. If you are using lazy collections (as mentioned in the article), you won't run into situations where Company "A" would create a Person "John" that would create a new Company "A", etc.

Best regards

 

[FransBouma]

The two that stand out (obvious if you follow the asp.net forums) are LLBLGen Pro (

http://www.llblgen.com)

and EntityBroker(

http://www.thona-consulting.com).

The EntityBroker seems more like the real deal but it's really a shift in thinking and I can't quite figure it out yet. LLBLGen actually "gen"-erates code based on your db schema so it's a little easier to understand at first, but maybe not as maintainable in the long run?
LLBLGen Pro creates a definition of entities and relations between these entities from your database schema. It also lets you create typed lists, which are views based on fields in one or more entities for fast retrieval of data.

When you are finished with these definitions, you generate code, and that is done using templates which are controlled by a configuration file. In other words: you can modify the configuration file (or create new ones) and create / modify template sets, which means how the entity definitions will look like in code is up to you. This way of working is very effective, especially when your database has over 30 or so tables, because you don't have to write anything, it is all generated for you. (the code sports a lot of functionality too, like automatic mapping of 1:1, 1:n, m:1 and m:n relations on fields in an entity, so you can traverse Order.Products (which is an m:n relation over order details) in code without having to worry about anything: llblgen pro has already figured out the m:n relationship.