Sql server join/subselect question

I have two tables in the database

Team:Id(int PK), Name(nvarchar)

and

Player:Id(int PK), Name(nvarchar), Age(int), TeamId(int FK)

I also have the following classes

class Team
{
   public int Id{get;set;}
   public string Name{get;set;}
}

and

class Player
{
   public int Id{get;set;}
   public string Name{get;set;}
   public int Age{get;set;}
   public int TeamId{get;set;}
开发者_运维知识库}

and

class TeamInfo
{
   public Team Team{get;set;}
   public List<Player> Players{get;set;}
}

I want to extract from the databse a List<TeamInfo>. I am using Ado.net, no ef or linq2sql... and I am asking what is the best way to query the db?

Right now I get all teams and for each team I query the table players with a join but for sure it's not the best way.

---

i'd create an sp in sql server with the parameters @teamid, then execute the sp for the team and get the player info

create procedure [dbo].[TeamPlayers]
as

@teamid int
begin

    set nocount on;

select
    t.ID as TeamID,
    t.name,
    p.ID as PlayerID,
    p.name,
    p.age
from dbo.Player p
    inner join Team t
        on p.TeamID=t.ID
where t.ID=@teamid


end

---

you should read all info from database with join query:

SELECT 
       Team.Id as TeamId,
       Team.Name as TeamName,
       Player.Id as PlayerId,
       Player.Name as PlayerId,
FROM Team INNER JOIN Player on Team.Id = Player.TeamId

Use this query as part of SqlCommand and get data into DataTable. Then:

var teamInfos = datatable.Rows
                    .Cast<DataRow>()
                    .GroupBy(dr => (int) dr["TeamId"])
                    .Select(t => new TeamInfo
                       {
                           Team = new Team{ Id = t.Key, 
                                            Name = (string)t.First["TeamName"] },
                           Players = t.Select(p => new PlayerInfo {
                                      Id = (int)p["PlayerId"],
                                      Name = (int)p["PlayerName"],
                                      // other player fields here
                                 })
                                 .ToList()
                       });

NOTE: Probably you need a LEFT join here instead of INNER since you won't get teams without players otherwise.