I have this idea for a business and data layer in .NET that would provide an object model similar to the way you program against MS Office and using DataSet/DataTables behind the scenes. I would be very interested in knowing if anyone would like to help me tackle this, or if anyone knows if it's been done before!
BACKGROUND:
Data Access Articles
It seems that every book and tutorial teaches you how to drag and drop tables onto your form and provide VERY simple CRUD operations using only your mouse. When it comes to updating rows that were pulled from the DB by join queries the articles either ignore them or simply say that you have to write your own SQL code to handle this. Is it just me or do the majority of DB interactions in a real system involve joining tables? The other problem I have with using Datatables and Datasets directly in the code to populate the view is that it really couples things together. The articles also talk about placing the data in the ASP.NET Cache, or Session, or ViewState objects, which I don't like. One ties up server memory holding seperate copies of the data for every user that requests it and the other transports all that data back and forth on each request.
Programming Office Applications
If you've ever programmed an application that interacted directly with the MS Word, Excell, or Outlook object model you know what I mean. The entire Office suite has been broken down into a heirarchy of classes. For instance, in Word the Application object has a reference to the active document which contains a collection of all the paragraphs, sentences, characters and so on.
My Last Web App
Well, it's really my first real web app. One of my goals was to cache the data pulled from the DB so that it could be used quickly by several requests. My final design has the data placed in a cache with WeakReferences, so it is up to .NET to decide when memory is running low. The model worked like this: Suppose you request an employee object, if it is not in the cache the mapper requests the data from the DB and returns the employee object. You can then access the properties on the employee object and also collections of data related to the employee. For instance a collection of projects the employee is currently working on. The list of projects is lazy loaded, in other words the data isn't pulled from the DB until you reference that employee's current projects collection. The collection is also cache in the mapper object with the reference held only through a WeakReference object.
I have been doing reading on ADO.NET and ASP.NET ever since it came out and I haven't been satisfied with the material. I have also been reading about patterns, and the Data Mapping Layer caught my eye. I realized buying one was out of the question and creating a good one myself was out of my reach. But creating a basic one that solved my caching goal while using lots of ugly repeated code was a good first step. One problem that came to my attention after building my mapping layer was that the collections didn't support sorting or filtering like the DataTable does through DataViews.
MY IDEA:
I want to create a framework in which users can use mapper classes to locate objects from the DB. The mapper object would return a business object that contained the data they requested using a DataSet/DataTable behind the scenes, but exposing the data as properties of the business object. The mapper object would also cache the data so if anyone else requested that data the same instance of the business object would be returned instead of making another round trip to the DB and allocating more memory for the returned data. If there are any collections associated with the object then those are lazy loaded when referenced in the program.
So far I have envisioned a master DataSet containing all the tables/relations/indexes that exist in the DB. Then when a record is loaded it is merged into the corresponding table in the master DataSet and a business object which wraps the record is returned. Collections could simply be wrappers of DataView objects on the various DataTables in the master DataSet. The main problem I'm running into at the moment is the data cannot be cached using WeakReferences as I had previously done. I can't figure out any quick and reasonable way to facilitate caching.
