Data Access Object Patterns

[mjweyland]

I am posting this here because this is where I felt I would find the brilliant minds who are familiar with OO theory rather than at the MSAccess Forums. So bear with me.

Background:
I am trying to write a VBA app using OO methodology. This allows for the DB adn the Code to live independently.

More Background:
I have an object A. For every A there may be any number of Object Bs. For every object B there may be any number of Object Cs.

Dilemma:
In some of my research I came across the idea that an object is an object itself. It needn't know from whence it came or how to update itself in the DB. Thus a second layer is develped Data Access Object. It acts as a conduit between said object (A, B, C) and the DB (Oracle, Access, SQL2000). Should I create an ADO (man I hate to use that acronym) for each Object or should I create some great class that can receive any number of objects, hit the DB and get information from a Global Data Dictionary (GDD) so the class now knows based on the object type it has received what information to go into what tables. The dynamically create a SQL statement based on what the object is trying to do.

Is this making any sense?

MW

 

[Rob Mills]

I'm a little confused by what you're trying to accomplish. I guess you're trying to store multiple objects in another object, yes? If so, in the parent class you can declare a collection variable. Then you can add the other objects to that collection. Hope that helped.

 

[mjweyland]

Data Access Object

I apologize if I wasn't clear. I do currently store collections of object within an object. The problem is that I want to keep the DB connectivity in a separate class from the Object itself. From a development perspective this makes cleaner code and will be easier to manage etc. I hope this makes more sense. If you have any other questions or ideas pls email me.

mweyland at mnqio dot sdps dot org

Thanks.

MW

 

[Rob Mills]

Would a separate module work? You are building this in access is that right? What's your overall objective? To store the connections somewhere like pooling?

 

[mjweyland]

I am developing this in Access, but want to make sure that if I do migrate this over to .NET it is a smooth transition. I want to follow good OO implementation and methodology. I want to ensure that this is scalable and any addition of other modules is a smooth easy transition. I want to make sure that regardless of the DB I can make this work (i.e. MSSQL, Oracle, MySQL).

I do anticipate making this a seperate module, but more specifically a class and am thinking that the the specific object classes for the connection will extend some type of parent class. If I need to connect to different DBs I would more than likely just store that information (connection strings) in a GDD Database and dynmically connect to the respective DB.

Here is an email transmission from a friend who is a Java developer and rather gifted code writer, but doesnt know anything about .NET so I am trying to take what he is saying regarding Java and translate that to VBA but ensure that the rewriting of the code is eliminated or more specifically eliminated with the migration to .NET from VBA.
You are treating B1 and B1Sql as different objects, but they are the same object. All the data fields the object knows about are stored in B1, where B1SQL extends B1 and has just the sql methods to manipulate the parents data.

Lets change the example to Account and AccountSql instead of B1 and B1Sql

Account has the following fields:

account number
customer name
account balance
AccountSql implements the MattSqlInterface, which has methods such as:

load(MattSqlInterface)

save(MattSqlInterface)

update(MattSqlInterface)

find(MattSqlInterface)

In our example, if AccountSql is passed to load, then load function performs an sql statement like

'select * from account where id = MattSqlInterface.getId()'

and then sets the account number, name, and balance of its parent(Account) with the results

This way classes can just pass around a normal Account object that doesn't have sql statements in it. When the account needs to be saved, you pass it to a class that knows about your database system and mappings such as MattDataStore -

MattDataStore.save(account);

* NOTE that you don't pass around keys here, you pass in the object *

MattDataStore can use reflection to determine the full name of the class, such as:

com.matt.Account

then it looks in your mappings for com.matt.Account and finds it, along with:

sql table name

variable mappings

variable mappings would be something like:

java variable sql column
accountno accountno
name name
balance balance

then MattDataStore uses reflection to call the appropriate getters or setters, for example the java variable balance has a method getBalance and setBalance
Any code that has sql statements in it is dependent on the DB. Any object that has data and sql statements both is not encapsulated, it is not abstract, and it is not reusable. It is harder to maintain, harder to bug, and harder to understand when you are looking at the code. It becomes increasingly difficult when other people have to read this code or make changes. Coupling the sql and the object is a procedural way of programming, not an object oriented way of programming.

**** end of email transmission ****

I have some of this stuff written in a dummy db with some objects and can dynamically create the required SQL to select, insert, update or delete (right now it is just select). This is for demonstrative purposes so imagine that GDD an GDD1 are in a specific DB called GDD and have more applicable names.

I have attached an example of what I am working on. I am just trying to get my head around the extension aspect or what I can do to try and create something similar in VBA or .NET

I hope this helps clarify my question.

**Edit...
Forgot to add the attachment

MW