essentially i'm trying to follow the 3-tier architecture used in this book but am a bit lost with the whole collections thing. i'm trying to get some data but I have a 3 table query. the query gets the fixture for the season, one getting from the game table, the full team names from the team table and venue name from venue table. I've made these details classes.

protected virtual AFLGameDetails GetAFLGameFromReader(IDataReader reader){
AFLGameDetails aflGame = new AFLGameDetails(

(int)reader[" AflGame_id"],
(DateTime)reader["Date"],

(int)reader["Round"],
reader["Home"].ToString(),

reader["Away"].ToString(),
(decimal)reader["HomeLine"],

(decimal)reader["AwayLine"],
(bool)reader["Day_Night"],

(int)reader["Crowd"],
reader["Venue_id"].ToString(),

reader["AddedBy"].ToString(), (DateTime)reader["AddedDate"]);
return aflGame;}



and



protected virtual TeamDetails GetTeamFromReader(IDataReader reader){
TeamDetails team = new TeamDetails(

reader["Team_id"].ToString(),
reader["Team"].ToString(),

reader["AddedBy"].ToString(), (DateTime)reader["AddedDate"]);
return team;}



plus one for venue but thats pretty much the same as team.

however my query is as such



SELECT AFLGame.date, AFLGame.round, home.team AS Home, away.team AS Away, Venue.venue

FROM AFLGame INNER JOIN

Venue ON AFLGame.venue_id = Venue.venue_id INNER JOIN

Team AS home ON AFLGame.home = home.team_id INNER JOIN

Team AS away ON AFLGame.away = away.team_id



i know we have to right a method like the one below but i need some confirmation as to whether the collections i've written above is meant to match my sql query or is there some other way i'm meant to be doing it. i haven't been able to do any work on it the last few days and any help would be appreciated.



protected virtual List<AFLGameDetails> GetAFLGameCollectionFromReader(IDataReader reader)
{

List<AFLGameDetails> aflGame = new List<AFLGameDetails>();while (reader.Read())
aflGame.Add(GetAFLGameFromReader(reader));

return aflGame;
}



thanks

ben

Benny,

I ran a quick site builder against a rough version of the tables that you've defined above. You can pick it up and take a look at the code which seems to work as you intend based on the initial requirement:

http://www.originaltalent.com/downloads/AFL.zip

Cheers

jimi

http://www.originaltalent.com

if i learnt linq would that make this heaps easier?

Yes and no.

Joins are still tricky using LINQ to SQL, just like they are with T-SQL. But joins are a piece of cake with LINQ To Entities, and you'll need to learn LINQ to use it.

Believe me, LINQ to Entities will be totally revolutionary. That's why learning LINQ right now is a worthwhile endeavor.