NEWBIE: DAO pattern in ADO?

  • Thread starter Thread starter David Benoff
  • Start date Start date
D

David Benoff

Hi all,
I'm a java guy getting started with C#/.NET/ADO and working with strongly
typed datasets. I'm playing with this schema:

CATEGORIES 1-----0...* USERS 1-----0...* USERTEAM 0...*-----1 TEAMS

I created my four dataadapters, created a dataset, set up my datarelations,
make my calls to fill()....So far so good. From my user objects, I can
traverse the many-to-one relationship to get to the user's categories, and
traverse the many-to-many to get to the user's teams. But what do I do when
I only want one user, and his related objects? I don't want to call fill()
since that pulls back the complete contents of all the tables. The code
below does what I want but I'm hoping there's an easier way to do this....

String userSelect = String.Format("select users.* from users where userid =
{0}", id);

OdbcDataAdapter userDA = new OdbcDataAdapter(userSelect, odbcConnection1);

String categorySelect = String.Format("SELECT categories.* FROM categories
INNER JOIN users ON categories.categoryid = users.categoryid WHERE
users.userid = {0}", id);

OdbcDataAdapter categoryDA = new OdbcDataAdapter(categorySelect,
odbcConnection1);

String userteamSelect = String.Format("select userteam.* from userteam where
userid = {0}", id);

OdbcDataAdapter userteamDA = new OdbcDataAdapter(userteamSelect,
odbcConnection1);

String teamSelect = String.Format("SELECT teams.* FROM teams INNER JOIN
userteam ON teams.teamid = userteam.teamid WHERE userteam.userid = {0}",
id);

OdbcDataAdapter teamDA = new OdbcDataAdapter(teamSelect, odbcConnection1);

categoryDA.Fill(usersCategoriesDS1, "categories");

userDA.Fill(usersCategoriesDS1,"users");

teamDA.Fill(usersCategoriesDS1,"teams");

userteamDA.Fill(usersCategoriesDS1, "userteam");



Whew, that's a lot of work for a simple finder method! Do I really have to
write all this by hand? Is there some way to add criteria programmatically
to the default fill() method?



Any hints would be most appreciated,

David Benoff
 
Hi David,

David Benoff said:
Hi all,
I'm a java guy getting started with C#/.NET/ADO and working with strongly
typed datasets. I'm playing with this schema:

CATEGORIES 1-----0...* USERS 1-----0...* USERTEAM 0...*-----1 TEAMS

I created my four dataadapters, created a dataset, set up my datarelations,
make my calls to fill()....So far so good. From my user objects, I can
traverse the many-to-one relationship to get to the user's categories, and
traverse the many-to-many to get to the user's teams. But what do I do when
I only want one user, and his related objects? I don't want to call fill()
since that pulls back the complete contents of all the tables. The code
below does what I want but I'm hoping there's an easier way to do this....
Click to expand...

No, there isn't. If you want to retrieve selected data from database, you'll
have to adjust select stataments.
 
David Benoff said:
I'm a java guy getting started with C#/.NET/ADO and working with
strongly typed datasets. I'm playing with this schema:

CATEGORIES 1-----0...* USERS 1-----0...* USERTEAM 0...*-----1 TEAMS

I created my four dataadapters, created a dataset, set up my
datarelations, make my calls to fill()....So far so good. From my user
objects, I can traverse the many-to-one relationship to get to the
user's categories, and traverse the many-to-many to get to the user's
teams. But what do I do when I only want one user, and his related
objects? I don't want to call fill() since that pulls back the complete
contents of all the tables. The code below does what I want but I'm
hoping there's an easier way to do this....
Click to expand...

DataSets are code-less data-buckets. In other words: you can
traverse what's inside, you can't traverse data VIA the dataset that's not
inside the dataset. This means that if you don't load the data inside the
dataset, you can't traverse the relationships.
String userSelect = String.Format("select users.* from users where
userid = {0}", id);

OdbcDataAdapter userDA = new OdbcDataAdapter(userSelect,
odbcConnection1);
Click to expand...

What database are you targeting? SqlServer, Oracle?
String categorySelect = String.Format("SELECT categories.* FROM
categories INNER JOIN users ON categories.categoryid = users.categoryid
WHERE users.userid = {0}", id);

OdbcDataAdapter categoryDA = new OdbcDataAdapter(categorySelect,
odbcConnection1);

String userteamSelect = String.Format("select userteam.* from userteam
where userid = {0}", id);

OdbcDataAdapter userteamDA = new OdbcDataAdapter(userteamSelect,
odbcConnection1);

String teamSelect = String.Format("SELECT teams.* FROM teams INNER JOIN
userteam ON teams.teamid = userteam.teamid WHERE userteam.userid = {0}",
id);

OdbcDataAdapter teamDA = new OdbcDataAdapter(teamSelect,
odbcConnection1);

categoryDA.Fill(usersCategoriesDS1, "categories");
userDA.Fill(usersCategoriesDS1,"users");
teamDA.Fill(usersCategoriesDS1,"teams");
userteamDA.Fill(usersCategoriesDS1, "userteam");

Whew, that's a lot of work for a simple finder method! Do I really have
to write all this by hand? Is there some way to add criteria
programmatically to the default fill() method?
Click to expand...

You can of course write some methods which will prepare a statement
for you, and which is passed to the adapter's fill method. You're now
constructing hardcoded strings without parameters, which can be limiting.
Keep in mind that you first have to load the data to traverse it, that's
the core of the problem you're facing.

<shameless plug>
You can also (if you're targeting SqlServer or oracle) get a 3rd
party tool, for example LLBLGen Pro (see sig), which lets you do this:

UserEntity user = new UserEntity(id);
TeamCollection teams = user.Teams;

The difference is that these objects contain logic to traverse over
data that is not loaded yet. The dataset can't.
</shameless plug>

Frans
 
Back
Top