Question regarding OOP and database access

  • Thread starter Thread starter leodippolito
  • Start date Start date
L

leodippolito

Hello sirs!


Suppose I have these objects (and their attributes):


Car
- string carName
- Wheel aWheel


Truck
- string truckName
- Wheel aWheel


Wheel
- string wheelColor
- bool hasAHonk


(as I lack imagination for a better example, let's assume that the car
and the truck use the same kind of wheel).


In my relational database I have tables to persist these objects:


Car
- Car_ID int (pk)
- carName varchar
- Wheel_ID int (fk)


Truck
- Truck_ID int (pk)
- truckName varchar
- Wheel_ID int (fk)


Wheel
- Wheel_ID int (pk)
- wheelColor varchar
- hasAHonk int


And then I have this code:


// CAR BUSINESS LAYER


public Car getCarById(int p_id)
{
// just get the car from the database
CarDataLayer carDB = new CarDataLayer();
return carDB.getCarById(p_id);



}


// CAR DATA LAYER (doing a 'full load' of the associated object)

public Car getCarById(int p_id)
{
SQLCommand sql = new SQLCommand();


sql.Text =
'SELECT A.Car_ID, A.carName, B.Wheel_ID, B.wheelColor, B.hasAHonk



' +
'FROM Car A, Wheel B ' +
'WHERE (A.Wheel_ID = B.Wheel_ID) AND (A.Car_ID = ' + p_id + ')';


SqlReader sqlReader = sql.ExecuteReader();


// create the car object
Car objCar = new Car();
objCar.carName = sqlReader.getValue('carName');


// create the wheel object
Wheel objWheel = new Wheel();
objWheel.wheelColor = sqlReader.getValue('wheelColor­­');
objWheel.hasAHonk = sqlReader.getValue('hasAHonk')­­;


// associate the wheel with the car
objCar.aWheel = objWheel;


return objCar;



}


Now, let's suppose that besides using the wheel object in Car and Truck


I have several other classes that also use it.


My problem: if the Wheel table changes in the database, I have
wheel-related SQL code spread all over different DB layers (those DB
layers that 'join' with the wheel table). For example, the Truck DB
layer. If this happens I would have to track where I have these joins
and modify the sql code.


I could solve this by making the DB layers touch only data related to
the object (Car DB layer access only Car table and Wheel DB layer
access only Wheel table), and making different trips in the business
layer to build the object:


// CAR BUSINESS LAYER


public Car getCarById(int p_id)
{
CarDataLayer carDB = new CarDataLayer();
Car objCar = carDB.getCarById(p_id);


WheelDataLayer wheelDB = new WheelDataLayer();
Wheel objWheel = wheelDB.getWheelById(objCar.aW­­heel.ID);


objCar.aWheel = objWheel;


return objCar;



}


This way, if the Wheel table changes I am ok with the sql code. I don't


need to track anything in DB layers. If I ask for a car or truck
object, it will return a car or truck with the new-version wheel.


The cost of this is the n-round-trips that I have to make to the DB
layer in the business layer. I lose the power of SQL join.


Another possibility I can see would be to use an O/R mapping library.
The cost would be runtime processing.


So... I am trying to decide / visualize which kind of architecture will



be less expensive to me, since the entities in the business of my
application will likely change a lot.


I'm not very experienced in systems architecture so I'd appreciate any
advice on this issue.


Thanks.


Leonardo
 
Leo,

You are already in trouble when you would have a Formula 1 car with in front
and rear different wheels, why not use datatables.

For the Cars and for the wheels. A greath inbuild class in which you can
hold almost everything. Of course is every kind of wheel a row in your
datatable wheels, which is nothing more than a collection of wheels. In the
same way as in your DB.

If you want, you can build your own strongly typed datatable (class) around
it by just inheriting the datatable Class.

Just my thought,

Cor
 
I addressed this with my code generator for SQL Server and one
for Microsoft Access. It not only writes all the data classes
and .net code to call the procedures. It creates a middle OOP
layer that you can manipulate. To handle queries that span
across multiple tables, you can quickly modify the pre-written
Output class to inherit from the primary table and either
add on your own properties or inherit interfaces from the
other classes.

The article and sample source code will better explain this.
I've found it dramatically reduces the code I have to write
while still maintaining an OOP layer to my database.

http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp



Hello sirs!


Suppose I have these objects (and their attributes):


Car
- string carName
- Wheel aWheel


Truck
- string truckName
- Wheel aWheel


Wheel
- string wheelColor
- bool hasAHonk


(as I lack imagination for a better example, let's assume that the car
and the truck use the same kind of wheel).


In my relational database I have tables to persist these objects:


Car
- Car_ID int (pk)
- carName varchar
- Wheel_ID int (fk)


Truck
- Truck_ID int (pk)
- truckName varchar
- Wheel_ID int (fk)


Wheel
- Wheel_ID int (pk)
- wheelColor varchar
- hasAHonk int


And then I have this code:


// CAR BUSINESS LAYER


public Car getCarById(int p_id)
{
// just get the car from the database
CarDataLayer carDB = new CarDataLayer();
return carDB.getCarById(p_id);



}


// CAR DATA LAYER (doing a 'full load' of the associated object)

public Car getCarById(int p_id)
{
SQLCommand sql = new SQLCommand();


sql.Text =
'SELECT A.Car_ID, A.carName, B.Wheel_ID, B.wheelColor, B.hasAHonk



' +
'FROM Car A, Wheel B ' +
'WHERE (A.Wheel_ID = B.Wheel_ID) AND (A.Car_ID = ' + p_id + ')';


SqlReader sqlReader = sql.ExecuteReader();


// create the car object
Car objCar = new Car();
objCar.carName = sqlReader.getValue('carName');


// create the wheel object
Wheel objWheel = new Wheel();
objWheel.wheelColor = sqlReader.getValue('wheelColor­­');
objWheel.hasAHonk = sqlReader.getValue('hasAHonk')­­;


// associate the wheel with the car
objCar.aWheel = objWheel;


return objCar;



}


Now, let's suppose that besides using the wheel object in Car and Truck


I have several other classes that also use it.


My problem: if the Wheel table changes in the database, I have
wheel-related SQL code spread all over different DB layers (those DB
layers that 'join' with the wheel table). For example, the Truck DB
layer. If this happens I would have to track where I have these joins
and modify the sql code.


I could solve this by making the DB layers touch only data related to
the object (Car DB layer access only Car table and Wheel DB layer
access only Wheel table), and making different trips in the business
layer to build the object:


// CAR BUSINESS LAYER


public Car getCarById(int p_id)
{
CarDataLayer carDB = new CarDataLayer();
Car objCar = carDB.getCarById(p_id);


WheelDataLayer wheelDB = new WheelDataLayer();
Wheel objWheel = wheelDB.getWheelById(objCar.aW­­heel.ID);


objCar.aWheel = objWheel;


return objCar;



}


This way, if the Wheel table changes I am ok with the sql code. I don't


need to track anything in DB layers. If I ask for a car or truck
object, it will return a car or truck with the new-version wheel.


The cost of this is the n-round-trips that I have to make to the DB
layer in the business layer. I lose the power of SQL join.


Another possibility I can see would be to use an O/R mapping library.
The cost would be runtime processing.


So... I am trying to decide / visualize which kind of architecture will



be less expensive to me, since the entities in the business of my
application will likely change a lot.


I'm not very experienced in systems architecture so I'd appreciate any
advice on this issue.


Thanks.


Leonardo
 
Back
Top