business object design help...

[Graham Pengelly]

Hi

I'll try to spell out my problem as succinctly as possible...

My database has a User table, an Organisation table, a Department table
and a JobType table (amongst others)

The Organisation table has a one to many relationship with the other
three. The User table contains foreign keys to the Department and
JobType tables (and the Organisation table).

I want to build User, Organisation, Department and JobType objects
reflecting the data that I can fill from a SqlDataReader. How should I
best include the relationships in the objects? If I add a collection of
Department and JobType objects as properties of the Organisation object
for example, things slow right down when I try to fill a big collection
of Organisations. Similarly, I would ideally like the User object to
have Organisation, Department and JobType properties but filling a
collection of users again becomes very laborious.

I have come up with a few solutions which I would be happy to discuss in
future posts but none seem very elegant or efficient. Does anyone else
have any thoughts?

Thanks

Graham

[James Divine]

RE: business object design help...


I faced a similar problem when my data objects began to grow out of control.
For my Customer object, rather than having a fully populated 'Employee'
object in a 'SalesProfessio nal' property of the Customer, I swiched to just
holding the EmployeeID (the primary key in my db) and then fetching the
entire Employee object only when it was needed.

Doing this greatly improved the manageabilty and speed of my systems.

Let me know if you need more explaination, it's early here and I'm still on
my first cup of coffee, so it may not make the most sense :)





"Graham Pengelly" wrote:
[color=blue]
> Hi
>
> I'll try to spell out my problem as succinctly as possible...
>
> My database has a User table, an Organisation table, a Department table
> and a JobType table (amongst others)
>
> The Organisation table has a one to many relationship with the other
> three. The User table contains foreign keys to the Department and
> JobType tables (and the Organisation table).
>
> I want to build User, Organisation, Department and JobType objects
> reflecting the data that I can fill from a SqlDataReader. How should I
> best include the relationships in the objects? If I add a collection of
> Department and JobType objects as properties of the Organisation object
> for example, things slow right down when I try to fill a big collection
> of Organisations. Similarly, I would ideally like the User object to
> have Organisation, Department and JobType properties but filling a
> collection of users again becomes very laborious.
>
> I have come up with a few solutions which I would be happy to discuss in
> future posts but none seem very elegant or efficient. Does anyone else
> have any thoughts?
>
> Thanks
>
> Graham
>[/color]

[Richard Blewett [DevelopMentor]]

Re: business object design help...

Is it slow because the each of the objects goes to the database to gets it's own data or because you have an absolutely vast organisation? Creating objects, in-of-itself, in .NET is very very fast.

Regards

Richard Blewett - DevelopMentor

http://staff.develop.com/richardb/weblog

nntp://news.microsoft. com/microsoft.publi c.dotnet.langua ges.csharp/<OUxEsuSrEHA.25 80@TK2MSFTNGP15 .phx.gbl>

Hi

I'll try to spell out my problem as succinctly as possible...

My database has a User table, an Organisation table, a Department table
and a JobType table (amongst others)

The Organisation table has a one to many relationship with the other
three. The User table contains foreign keys to the Department and
JobType tables (and the Organisation table).

I want to build User, Organisation, Department and JobType objects
reflecting the data that I can fill from a SqlDataReader. How should I
best include the relationships in the objects? If I add a collection of
Department and JobType objects as properties of the Organisation object
for example, things slow right down when I try to fill a big collection
of Organisations. Similarly, I would ideally like the User object to
have Organisation, Department and JobType properties but filling a
collection of users again becomes very laborious.

I have come up with a few solutions which I would be happy to discuss in
future posts but none seem very elegant or efficient. Does anyone else
have any thoughts?

Thanks

Graham

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.771 / Virus Database: 518 - Release Date: 28/09/2004



[microsoft.publi c.dotnet.langua ges.csharp]

[Chris Ballard]

RE: business object design help...

Graham,

This really depends upon the intended useage of your classes. If they are to
be used for ad-hoc access in a situation where only a small proportion of all
the available data is likely to be accessed at any one time, then there is no
real need to load in everything at once. In this instance you could:

1. Only initially load the Organisation table into a collections of
Organisation objects.
2. Add methods such as GetUsers() to the Organisation object, which read in
just those users associated with that Organisation object and returns them in
another collection. For greater efficiency this could be done via a stored
procedure which takes the organisation PK as a parameter.

If you do need everything in memory, then you might consider populating a
DataSet (using a SqlDataAdapter rather than the SqlDataReader) with the
relationships added as DataRelation objects. Using a DataSet also gives you
the advantage of being able to make updates to the data without needing a
permanent connection to the database.

You may also wish to create a strongly typed DataSet to represent your
tables as specific classes. In this way you can access the data fields as
properties of these classes, rather than using generic indexers. The easiest
way to do this is by dragging and dropping from Server Explorer in VS.NET and
using the "Generate Dataset" command from the properties window.

I hope this is the sort of info you were looking for, and that I haven't
confused matters even more for you. There are always a lot of different ways
of doing things using ADO.NET.

Cheers,
Chris.

"Graham Pengelly" wrote:
[color=blue]
> Hi
>
> I'll try to spell out my problem as succinctly as possible...
>
> My database has a User table, an Organisation table, a Department table
> and a JobType table (amongst others)
>
> The Organisation table has a one to many relationship with the other
> three. The User table contains foreign keys to the Department and
> JobType tables (and the Organisation table).
>
> I want to build User, Organisation, Department and JobType objects
> reflecting the data that I can fill from a SqlDataReader. How should I
> best include the relationships in the objects? If I add a collection of
> Department and JobType objects as properties of the Organisation object
> for example, things slow right down when I try to fill a big collection
> of Organisations. Similarly, I would ideally like the User object to
> have Organisation, Department and JobType properties but filling a
> collection of users again becomes very laborious.
>
> I have come up with a few solutions which I would be happy to discuss in
> future posts but none seem very elegant or efficient. Does anyone else
> have any thoughts?
>
> Thanks
>
> Graham
>[/color]

[Graham Pengelly]

Re: business object design help...

Hi... Thanks to all of you for your advice

James:
Your advice regarding only including the primary keys in the object
properties is the solution I have settled on so far. This seems like the
easiest solution and is working fine.

Richard:
As you say, my problem is not creating the objects but rather the number
of db calls. If I create a user collection in my ideal scenario of
having Organiastion , Department and JobType object properties I have to
make a seperate call with each step of the SqlDataReader to create the
Organisation object. This object has to make a couple of calls to get
its Department and JobType collections etc... This seemed like a lot of
seperate db calls. I said it slows down but it isn't that slow doing it
that way it just seems really inefficient to make all of those seperate
calls rather than one query.

Chris
On your points 1 and 2, that is a great idea. I hadn't thought of
creating the Organisations first and then getting the users for each
Organisation. I was thinking - Get all the users and then get an
organisation for each... Doh... That will simplify things a lot. As
regards the DataSet approach, typed or otherwise, I have used DataSets a
lot in the past and am having a go at the custom business object
approach for this job as I am beginning to see it as a more efficient
and lean way to do things, in the right situation obviously, I don't
want to start a big DataSet vs Custom Business Object war on the group!
I initially started getting a DataSet and then traversing it with an
XPathNavigator and filling my objects that way but I am having a go with
the SqlDataReader now as I think I was getting a bit carried away before .

Thankyou all for your input, that was the quickest and most
comprehensive reply to a post I have ever had. Thanks again...

Graham


Chris Ballard wrote:
[color=blue]
> Graham,
>
> This really depends upon the intended useage of your classes. If they are to
> be used for ad-hoc access in a situation where only a small proportion of all
> the available data is likely to be accessed at any one time, then there is no
> real need to load in everything at once. In this instance you could:
>
> 1. Only initially load the Organisation table into a collections of
> Organisation objects.
> 2. Add methods such as GetUsers() to the Organisation object, which read in
> just those users associated with that Organisation object and returns them in
> another collection. For greater efficiency this could be done via a stored
> procedure which takes the organisation PK as a parameter.
>
> If you do need everything in memory, then you might consider populating a
> DataSet (using a SqlDataAdapter rather than the SqlDataReader) with the
> relationships added as DataRelation objects. Using a DataSet also gives you
> the advantage of being able to make updates to the data without needing a
> permanent connection to the database.
>
> You may also wish to create a strongly typed DataSet to represent your
> tables as specific classes. In this way you can access the data fields as
> properties of these classes, rather than using generic indexers. The easiest
> way to do this is by dragging and dropping from Server Explorer in VS.NET and
> using the "Generate Dataset" command from the properties window.
>
> I hope this is the sort of info you were looking for, and that I haven't
> confused matters even more for you. There are always a lot of different ways
> of doing things using ADO.NET.
>
> Cheers,
> Chris.
>
> "Graham Pengelly" wrote:
>
>[color=green]
>>Hi
>>
>>I'll try to spell out my problem as succinctly as possible...
>>
>>My database has a User table, an Organisation table, a Department table
>>and a JobType table (amongst others)
>>
>>The Organisation table has a one to many relationship with the other
>>three. The User table contains foreign keys to the Department and
>>JobType tables (and the Organisation table).
>>
>>I want to build User, Organisation, Department and JobType objects
>>reflecting the data that I can fill from a SqlDataReader. How should I
>>best include the relationships in the objects? If I add a collection of
>>Department and JobType objects as properties of the Organisation object
>>for example, things slow right down when I try to fill a big collection
>>of Organisations. Similarly, I would ideally like the User object to
>>have Organisation, Department and JobType properties but filling a
>>collection of users again becomes very laborious.
>>
>>I have come up with a few solutions which I would be happy to discuss in
>>future posts but none seem very elegant or efficient. Does anyone else
>>have any thoughts?
>>
>>Thanks
>>
>>Graham
>>[/color][/color]

[Nick Malik]

Re: business object design help...

James' advice is quite sound, Graham. Only load the data you need.

However, how do you keep from loading the data that you already have?
For example, let's say you have opened organization AUS and focussed on
employee "Howard" in dept "Parliament " (sorry... couldn't help it)
Now, you switch to employee "Latham" also in dept "Parliament "
How do you keep from loading the "Parliament " department again?

First off, create an object that is responsible for managing the list of
"organizati on" objects that are in memory. Use a singleton. You ask this
object for the organization by id. It checks the list. If it has the
organization, it returns it. Otherwise, fetch from the database. For
organization, you may also have a "GetAll" method that returns the list
itself.

For employee, you create a similar object. It too will return an employee
by id or by "GetAll" except that the GetAll for employee requires an
organization id, and/or organization + department. Overloading comes to
mind.

Same thing goes for Department and JobType.

Don't look up data until it is needed, but once you look it up, keep it
around, especially if it doesn't change very much in the db. (How often
does the name of a department change, anyway). One bit of logic: if the
Department object "believes" that it's list is complete, but a department id
comes in that isn't in the list, do a complete refresh from the db... a new
record has been added, and you don't know what other changes may be in
there. You could also go by dates: if the date of last refresh is more than
25 minutes ago, go get it again.

My personal opinion is that your objects would maintain either a collection
of data row objects or a simple dataset object, and you just return a
DataRow when the U/I layer asks for data. It's so much easier to manage
than copying data into a "child" object that you manage.

That's my two bits.

--- Nick

"Graham Pengelly" <graham@REMOVEM Emainlineconsul tants.com> wrote in message
news:OUxEsuSrEH A.2580@TK2MSFTN GP15.phx.gbl...[color=blue]
> Hi
>
> I'll try to spell out my problem as succinctly as possible...
>
> My database has a User table, an Organisation table, a Department table
> and a JobType table (amongst others)
>
> The Organisation table has a one to many relationship with the other
> three. The User table contains foreign keys to the Department and
> JobType tables (and the Organisation table).
>
> I want to build User, Organisation, Department and JobType objects
> reflecting the data that I can fill from a SqlDataReader. How should I
> best include the relationships in the objects? If I add a collection of
> Department and JobType objects as properties of the Organisation object
> for example, things slow right down when I try to fill a big collection
> of Organisations. Similarly, I would ideally like the User object to
> have Organisation, Department and JobType properties but filling a
> collection of users again becomes very laborious.
>
> I have come up with a few solutions which I would be happy to discuss in
> future posts but none seem very elegant or efficient. Does anyone else
> have any thoughts?
>
> Thanks
>
> Graham[/color]

[Graham Pengelly]

Re: business object design help...

Hi Nick

Thanks for your thoughts. I have begun to implement OrganisationMan ager
and UserManager classes that expose similar methods to those you
describe and it definitely sounds the way forward. My User object only
has the Id's of its Organisation, Dept etc. I am also thinking of
something like a UserEmploymentI nfo class that I can load with just the
bare details of the user and their Organisation and Dept names as this
is something that will often need to be listed in my app.

Thanks again for you advice.

Graham

Nick Malik wrote:[color=blue]
> James' advice is quite sound, Graham. Only load the data you need.
>
> However, how do you keep from loading the data that you already have?
> For example, let's say you have opened organization AUS and focussed on
> employee "Howard" in dept "Parliament " (sorry... couldn't help it)
> Now, you switch to employee "Latham" also in dept "Parliament "
> How do you keep from loading the "Parliament " department again?
>
> First off, create an object that is responsible for managing the list of
> "organizati on" objects that are in memory. Use a singleton. You ask this
> object for the organization by id. It checks the list. If it has the
> organization, it returns it. Otherwise, fetch from the database. For
> organization, you may also have a "GetAll" method that returns the list
> itself.
>
> For employee, you create a similar object. It too will return an employee
> by id or by "GetAll" except that the GetAll for employee requires an
> organization id, and/or organization + department. Overloading comes to
> mind.
>
> Same thing goes for Department and JobType.
>
> Don't look up data until it is needed, but once you look it up, keep it
> around, especially if it doesn't change very much in the db. (How often
> does the name of a department change, anyway). One bit of logic: if the
> Department object "believes" that it's list is complete, but a department id
> comes in that isn't in the list, do a complete refresh from the db... a new
> record has been added, and you don't know what other changes may be in
> there. You could also go by dates: if the date of last refresh is more than
> 25 minutes ago, go get it again.
>
> My personal opinion is that your objects would maintain either a collection
> of data row objects or a simple dataset object, and you just return a
> DataRow when the U/I layer asks for data. It's so much easier to manage
> than copying data into a "child" object that you manage.
>
> That's my two bits.
>
> --- Nick
>
> "Graham Pengelly" <graham@REMOVEM Emainlineconsul tants.com> wrote in message
> news:OUxEsuSrEH A.2580@TK2MSFTN GP15.phx.gbl...
>[color=green]
>>Hi
>>
>>I'll try to spell out my problem as succinctly as possible...
>>
>>My database has a User table, an Organisation table, a Department table
>>and a JobType table (amongst others)
>>
>>The Organisation table has a one to many relationship with the other
>>three. The User table contains foreign keys to the Department and
>>JobType tables (and the Organisation table).
>>
>>I want to build User, Organisation, Department and JobType objects
>>reflecting the data that I can fill from a SqlDataReader. How should I
>>best include the relationships in the objects? If I add a collection of
>>Department and JobType objects as properties of the Organisation object
>>for example, things slow right down when I try to fill a big collection
>>of Organisations. Similarly, I would ideally like the User object to
>>have Organisation, Department and JobType properties but filling a
>>collection of users again becomes very laborious.
>>
>>I have come up with a few solutions which I would be happy to discuss in
>>future posts but none seem very elegant or efficient. Does anyone else
>>have any thoughts?
>>
>>Thanks
>>
>>Graham[/color]
>
>
>[/color]