I decided this week to scratch an itch I've had on and off for about 5 years. I was writing some data access code for a small web project I'm doing for a bank and started to get the Object/Relational mapper itch again. I have some history with the OR mapper itch. The OR mapper itch replaced the Ojbect Database itch when I realized no one was going to bite on object databases in my lifetime. The first OR tool I ever used was TOPLink, and in my opinion it remains the best by far. I've used many OR tools for many languages since then, all of them have fallen short of solving some part of my data access picture on the applications I've tried them on. To be fair, many of these were due to horrible database design.

At any rate, even in this great day and age I still finding myself writing very similar data access code over and over again for web and windows forms projects. I could, if I had no self respect, just throw SqlDataAdapters all over the place but I usually insist on writing three tier apps with custom types as the means of exchanging data between tiers and layers.

My itch, then, has been to write an OR mapper from the ground up; I want to do this primarly so I'll have one that fits my needs but also just for the experience of designing and building it. I typically work on several things at once, and have some software products of my own that are to be developed this year, so the tool should justify the time it takes to write very quickly. I have some goals for writing the tool that will hopefully make it very interesting to other people:

1. Support for stored procedures: the MSFT world is very stored proc crazy and very against dynamic SQL, required by every OR mapper I know of. Just having something that can execute a stored proc and map the results back to my application's types would be a big time saver.
2. Type-to-type mapping: As Philippe DeMilde pointed out one drawback of doing Services Oriented Architecture the right way is the fact that you will end up mapping Message types to the data structure your application uses. This is tedious and prone to human errors. Adding support in this engine to map from instances of one Type to another could be a time saver in many applications.
3. A nice user interface: This is one thing TOPLink had that I have yet to see anywhere else. The UI should make it so that you configure the tool visually and easily: Connect to a data source, load a Types assembly, highlight a Type/property/table/column/storedproc and display its mapping configuration in a PropertyGrid.
4. Support Lazy and Eager loading of related Types and Relationships: Lazy loading is really the only way to go so I may force lazy load for Collections.

As I flesh the design out, I'm sure many more things will make themselves known. After giving up on thinking of a clever name for this project I decided to just call it TRAP, meaning "Types, Relationships, and Persistence". After this ephiphany (which occurred Monday afternoon) I went wild and did a component diagram and coded a surprisingly good start to the user interface. The road to completing TRAP will probably be the subject of many of my blog posts for a while.

Behold, a component diagram

Its not much right now, but there is mucho more on the way. The responsibilities for each component should be straightforward.

Types will just represent the mapping structure and data source information and have no intelligence. The Core assembly serves as the interface between consumers of the tool and the internals of the tool. The Engine does the actual work of managing transactions and caching and such. The Provider assembly will implement a couple of default supported persistence targets, like Type-to-SQLServer, Type-to-OleDB and maybe Type-to-Access and Type-to-Type. The idea is that anything that can vary (like SQL dialect or connection string format) will be encapsulated in the provider classes. The Provider, then, is really a plug-in to the engine. I will probably make an identity map/caching mechanism part of the provider model as well.

I'm pretty eager to scratch this itch, especially since I view it as part of the bigger goal of offering 2 products for sale by the end of the year. I imagine by the end of this weekend (depends greatly on my other commitments) I will have a simple "select *" test working round trip: designing it in the UI and running the OR mapper from unit tests. That reminds me, I should add a Tests assembly out there and create a test database with all of the items I want to support. Stay tuned.