My boss wants me to write a home grown DAL. He is not interested in OR Mappers or purchasing a product that will create a DAL based on our database schema. Since he has closed the debate on that issue I would appreciate it if responses dealt strictly with
my questions instead of focusing why an OR Mapper or purchasing a DAL Generator is the way to go.
My DAL Design
For each table in my database I create two classes. A Row class that has public properties that represent each of the fields in a database table and a RowManager class which handles all of the CRUD operations for a database table.
For instance, I have a table called Addresses. Therefore I have an AddressesRow class and an AddressesRowManager class. My questions involve the design of the AddressesRow and AddressesRowManager classes.
My Address Table has the following fields SendTo, AddressLine1, AddressLine2, City, StateCode, Zip and CountryCode. Both StateCode and CountryCode are foreign keys into the State Table and the CountryCode table.
Questions
1. Should my AddressesRow represent the StateCode and Country Code as Strings or as StateCodeRow and CountryCodeRow objects?
2. If I represent the StateCode and CountryCode database fields as StateCodeRow and CountryCodeRow objects should my AddressesRowManager class populate these fields as:
Option #1
row.StateCodesRow = new StateCodesRow(dr.GetInt32(stateCodeField));
row.CountryCodesRow = new CountryCodesRow(dr.GetInt32(countryCodeField));
Option #1 will construct a StateCodesRow and CountryCodesRow that only has values for the State and CountryCode fields (the rest of the fields in these objects will be blank or null).
Option #2 completely loads the StateCodeRow and CountryCodeRow objects by reading the data from the corresponding database tables.
Can you tell me which of these two solutions is a better option and why (or if you have a better solution please let me know what that is)?
I'd choose the first option applying a pattern called "Lazy Load", using this pattern you just have to keep the value (primary key) received from the class constructor. In this case you don't need to consume another field neither StateCodeRow
class and CountryCodeRow class.
In summary, your AddressRow class knows just the StateCode, so you can get an instance of StateCodeRow passing this value and the StateCodeRow class will not get all the values until it's necessary. The same sample applies to CountryCodeRow
class.
I normally provide three properties: One with a string value (State), one with an integer value (StateID), and another with an object (AddressState). I use the first two because in many cases, all I care about is the foreign key ID and its real name. I would
construct my AddressesRow class from a view that joined all relevant tables so that I could get these two values.
If it turned out that I actually needed to use the StateCodeRow object, I would lazy load it into the AddressState property.
Question 2:
I would simple make a property in the AddressesRow class called AddressState of the StateCodeRow type and only load it when accessed (lazy load). It would look something like:
StateCodesRow AddressState {
return new StateCodesRow(this._StateID);
}
This way you have a string value for your view pages, an integer value to select the proper value in your edit pages, and you have a handle on an object in cases where you may need total access to the StateCodesRow type but you don't incur the overhead
of instantiating the StateCodesRow class when the object is constructed.
Can you tell me which of these two solutions is a better option and why (or if you have a better solution please let me know what that is)?
I suggest an alternative approach (though similar to the ones you mentioned). You have xxxRow classes which contain public properties and behvae like DTOs. So create a DTO named AddressRow which will have StateRow and CountryRow DTO's as its public members.
Now create a DAL class AddressDAL. This class will have methods like Load(), GetAddress() etc.
For Load(AddressRow) method, pass the address DTO with only the primary key and simply fill all the fields of this DTO. This DTO will have have State as well as country DTOs also loaded. Regarding how many fields to load, I recommend using Lazy Loading
approach as suggested in one of the posts above. Keep DAL really simple and "dumb" , as its basic job should be just to fetch DATA from the DB and nothing else. The decision to load everything or only part parameters should be left to BL, and you need to study
Lazy Loading approach on how to handle this.
In Pattens of Enterprise Application Architecture, Martin Fowler et all describes the core parts of what you need in a "DAL", specifically if you want to use a domain model style of development. If you want the domain model style (i.e. a Customer object coresponding
to each row in a customer table, etc), which is the preferred style, you will need to do SOME kind of O/R mapping, even if you've written it yourself and not used an exisitng framework. Given the restriction of not being able to use an existing ormapper or
code generator, you might not want to use a domain model and just go for DataSets.
Thanks. I am going to find out more about Lazy Loading. In the meantime I was wondering how I would implement Lazy Loading. Instead of Loading the State Code information in my AddressesRowManager class should Load it in the following manner in my StateCodeRow
class?
public string Description
{
get
{
if ((this.description == null) && (this.StateCode != null))
{
this = StatesRowManager.GetByPrimaryKey(this.stateCode);
return this.description;
}
}
set { description = value; }
}
Thanks. Instead of changing the StateCodesRow class in the manner of my previous post, I was wondering if I would be better off changing the AddressesRow class in the following manner:
From:
public StateCodesRow StateCodesRow
{ get { return this.stateCodesRow; } set { this.stateCodesRow =
value; }
}
To:
public StateCodesRow StateCodeRow
{ get
{ if (this.stateCodesRow ==
null)
{ this.stateCodesRow = StateCodesRowManager.GetByPrimaryKey(this.stateCode);
}
return this.stateCodesRow;
}
set { this.stateCodesRow =
value; }
}
I came up with this solution because the prior solution of changing the StateCodesRow class forced me to check every property of the StateCodesRow class to
a) check to see if the property was null and
b) to call StateCodesRowManager.GetByPrimaryKey(this.stateCode).
This way I the changes are isolated in the AddressesRow class.
Is this a better solution?
Thanks
Ps. putting the return this.description within the if block was a foolish typo on my part.
from what I've read so far, it seems that you could solve many of your problems with Typed DataSets representing your DTO's (
you actually get a class for each table, and a class for the rows of each table .. if that sounds familiar ) . Then, focus on a little DAL that loads / updates those DataSets in a generic way to your DB, and there you go. You have an absolutely generic
DAL implementation, and you also get all the benefits & functionality of Typed DataSets ( things like Compute, Search, Sort, Views and countless others ).
We've been using this approach for 2 years now, and never went back to the DAL code for a single change for all that time ( dunno if that's good or bad though :D ). It all works by generating Stored Procedures underneath to load, store save & update tables,
based on a simple naming convention e.g. User_SelectByKey_sp or User_SelectBy<Foreign key name>_sp etc.
It'd seem to me that you could benefit from that approach, rather than focus on a Domain Model implemented completely as C# classes with too much hardcoding in it. If you're bound on using those classes, you could just implement a simple mapping mechanism
from rows & fields of the dataset onto instances & properties of your classes - but that's just overkill i think and it's use is greatly dependent on how psycho your boss is.
mikener
Member
534 Points
238 Posts
Question About DAL's
Mar 08, 2007 02:43 PM|LINK
My DAL Design
For each table in my database I create two classes. A Row class that has public properties that represent each of the fields in a database table and a RowManager class which handles all of the CRUD operations for a database table.
For instance, I have a table called Addresses. Therefore I have an AddressesRow class and an AddressesRowManager class. My questions involve the design of the AddressesRow and AddressesRowManager classes.
My Address Table has the following fields SendTo, AddressLine1, AddressLine2, City, StateCode, Zip and CountryCode. Both StateCode and CountryCode are foreign keys into the State Table and the CountryCode table.
Questions
1. Should my AddressesRow represent the StateCode and Country Code as Strings or as StateCodeRow and CountryCodeRow objects?
2. If I represent the StateCode and CountryCode database fields as StateCodeRow and CountryCodeRow objects should my AddressesRowManager class populate these fields as:
Option #1 will construct a StateCodesRow and CountryCodesRow that only has values for the State and CountryCode fields (the rest of the fields in these objects will be blank or null).
Option #2 completely loads the StateCodeRow and CountryCodeRow objects by reading the data from the corresponding database tables.
Can you tell me which of these two solutions is a better option and why (or if you have a better solution please let me know what that is)?
Thanks
FerVitale
Participant
1114 Points
185 Posts
Re: Question About DAL's
Mar 08, 2007 04:06 PM|LINK
Hi there,
I'd choose the first option applying a pattern called "Lazy Load", using this pattern you just have to keep the value (primary key) received from the class constructor. In this case you don't need to consume another field neither StateCodeRow class and CountryCodeRow class.
In summary, your AddressRow class knows just the StateCode, so you can get an instance of StateCodeRow passing this value and the StateCodeRow class will not get all the values until it's necessary. The same sample applies to CountryCodeRow class.
Please check this link http://www.martinfowler.com/eaaCatalog/lazyLoad.html
If you need some code where it's applying this pattern just let me know.
Regards,
Fernando
RZaakir
Member
130 Points
40 Posts
Re: Question About DAL's
Mar 08, 2007 06:19 PM|LINK
mike,
Question 1:
I normally provide three properties: One with a string value (State), one with an integer value (StateID), and another with an object (AddressState). I use the first two because in many cases, all I care about is the foreign key ID and its real name. I would construct my AddressesRow class from a view that joined all relevant tables so that I could get these two values.
If it turned out that I actually needed to use the StateCodeRow object, I would lazy load it into the AddressState property.
Question 2:
I would simple make a property in the AddressesRow class called AddressState of the StateCodeRow type and only load it when accessed (lazy load). It would look something like:
StateCodesRow AddressState { return new StateCodesRow(this._StateID); }This way you have a string value for your view pages, an integer value to select the proper value in your edit pages, and you have a handle on an object in cases where you may need total access to the StateCodesRow type but you don't incur the overhead of instantiating the StateCodesRow class when the object is constructed.vivek_iit
All-Star
17778 Points
3189 Posts
MVP
Re: Question About DAL's
Mar 08, 2007 07:01 PM|LINK
I suggest an alternative approach (though similar to the ones you mentioned). You have xxxRow classes which contain public properties and behvae like DTOs. So create a DTO named AddressRow which will have StateRow and CountryRow DTO's as its public members.
Now create a DAL class AddressDAL. This class will have methods like Load(), GetAddress() etc.
For Load(AddressRow) method, pass the address DTO with only the primary key and simply fill all the fields of this DTO. This DTO will have have State as well as country DTOs also loaded. Regarding how many fields to load, I recommend using Lazy Loading approach as suggested in one of the posts above. Keep DAL really simple and "dumb" , as its basic job should be just to fetch DATA from the DB and nothing else. The decision to load everything or only part parameters should be left to BL, and you need to study Lazy Loading approach on how to handle this.
Hope this helps,
Vivek
Communifire: Social Networking and Business Collaboration Platform
ALFKI
Member
448 Points
98 Posts
Re: Question About DAL's
Mar 08, 2007 08:15 PM|LINK
mikener
Member
534 Points
238 Posts
Re: Question About DAL's
Mar 09, 2007 12:57 PM|LINK
Thanks. I am going to find out more about Lazy Loading. In the meantime I was wondering how I would implement Lazy Loading. Instead of Loading the State Code information in my AddressesRowManager class should Load it in the following manner in my StateCodeRow class?
public string Description { get { if ((this.description == null) && (this.StateCode != null)) { this = StatesRowManager.GetByPrimaryKey(this.stateCode); return this.description; } } set { description = value; } }Thanks again, I appreciate the advice.
ALFKI
Member
448 Points
98 Posts
Re: Question About DAL's
Mar 09, 2007 01:27 PM|LINK
mikener
Member
534 Points
238 Posts
Re: Question About DAL's
Mar 09, 2007 02:09 PM|LINK
Thanks. Instead of changing the StateCodesRow class in the manner of my previous post, I was wondering if I would be better off changing the AddressesRow class in the following manner:
From:
public StateCodesRow StateCodesRow
{
get { return this.stateCodesRow; }
set { this.stateCodesRow = value; }
}
To:
public StateCodesRow StateCodeRow
{
get
{
if (this.stateCodesRow == null)
{
this.stateCodesRow = StateCodesRowManager.GetByPrimaryKey(this.stateCode);
}
return this.stateCodesRow;
}
set { this.stateCodesRow = value; }
}
I came up with this solution because the prior solution of changing the StateCodesRow class forced me to check every property of the StateCodesRow class to
a) check to see if the property was null and
b) to call StateCodesRowManager.GetByPrimaryKey(this.stateCode).
This way I the changes are isolated in the AddressesRow class.
Is this a better solution?
Thanks
Ps. putting the return this.description within the if block was a foolish typo on my part.
anjelinio
Member
116 Points
34 Posts
Re: Question About DAL's
Mar 11, 2007 09:25 AM|LINK
Hey mikener,
from what I've read so far, it seems that you could solve many of your problems with Typed DataSets representing your DTO's ( you actually get a class for each table, and a class for the rows of each table .. if that sounds familiar ) . Then, focus on a little DAL that loads / updates those DataSets in a generic way to your DB, and there you go. You have an absolutely generic DAL implementation, and you also get all the benefits & functionality of Typed DataSets ( things like Compute, Search, Sort, Views and countless others ).
We've been using this approach for 2 years now, and never went back to the DAL code for a single change for all that time ( dunno if that's good or bad though :D ). It all works by generating Stored Procedures underneath to load, store save & update tables, based on a simple naming convention e.g. User_SelectByKey_sp or User_SelectBy<Foreign key name>_sp etc.
It'd seem to me that you could benefit from that approach, rather than focus on a Domain Model implemented completely as C# classes with too much hardcoding in it. If you're bound on using those classes, you could just implement a simple mapping mechanism from rows & fields of the dataset onto instances & properties of your classes - but that's just overkill i think and it's use is greatly dependent on how psycho your boss is.
Hope this was helpful man,
O:]
xrock
Member
10 Points
5 Posts
Re: Question About DAL's
Mar 11, 2007 11:22 AM|LINK