public static Team GetTeamPlayersInfo(int TeamId)
{
var team = new Team();
var query = @"SELECT t.TeamId, p.*
FROM [Teams] t
INNER JOIN [Players] p
ON p.PlayerId IN (t.Player1_Id, t.Player2_Id)
WHERE t.TeamId = @TeamId;";
using (var connection = new SqlConnection(_bdppmasterdb))
{
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@TeamId", TeamId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows) { return null; }
while (reader.Read()) {
team.TeamId = reader.GetInt32(reader.GetOrdinal("TeamId")); //TeamId is constant, overwrite is not a concern
var player = new Player()
{
PlayerId = reader.GetInt32(reader.GetOrdinal("PlayerId")),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
ScreenName = reader["screenName"].ToString(),
BDLoginName = reader["BDLoginName"].ToString(),
Email = reader["Email"].ToString(),
RFID = reader["RFID"].ToString(),
ImageNameWithExt = reader["ImageNameWithExt"].ToString()
};
team.Players.Add(player);
}
return team;
}
}
}
}
public static Game GetGameInfo(int GameId)
{
var game = new Game() {
Teams = new List<Team>()
};
var team = new Team();
var currentTeamId = 0;
var nextTeamId = 0;
var query = @"SELECT * FROM [Games] G
INNER JOIN [Teams] t ON t.TeamId IN (g.Team1_id, g.Team2_id)
INNER JOIN [Players] P ON p.PlayerId IN (t.Player1_id, t.Player2_id)
WHERE g.GameId = @GameId;";
using (var connection = new SqlConnection(_bdppmasterdb))
{
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@GameId", GameId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows) { return null; }
while (reader.Read())
{
//Constants, overwrite is not a concern:
game.GameId = reader.GetInt32(reader.GetOrdinal("GameId"));
game.Team1_Score = reader.GetInt32(reader.GetOrdinal("Team1_Score"));
game.Team2_Score = reader.GetInt32(reader.GetOrdinal("Team2_Score"));
nextTeamId = reader.GetInt32(reader.GetOrdinal("TeamId"));
if (currentTeamId != nextTeamId) {
if(currentTeamId > 0){ game.Teams.Add(team); }
currentTeamId = nextTeamId;
team = new Team() {
TeamId = currentTeamId,
Players = new List<Player>()
};
}
team.Players.Add(new Player()
{
PlayerId = reader.GetInt32(reader.GetOrdinal("PlayerId")),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
ScreenName = reader["screenName"].ToString(),
BDLoginName = reader["BDLoginName"].ToString(),
Email = reader["Email"].ToString(),
RFID = reader["RFID"].ToString(),
ImageNameWithExt = reader["ImageNameWithExt"].ToString()
});
}
return game;
}
}
}
}