static void Main()
{
//Problem 2. Export the Leagues and Teams as JSON
//Write a C# application based on your EF data model for exporting all leagues along with their teams.
//Write the output in a JSON file named leagues-and-teams.json. Include in the output the leagues with no teams (if any).
//Order the leagues and the teams in each league alphabetically.
//For better performance, ensure your program executes a single DB query and retrieves from the database only the required data
//(without unneeded rows and columns).
var context = new FootballEntities();
var leagsWithTeams = context.Leagues
.OrderBy(l => l.LeagueName)
.Select(l => new
{
l.LeagueName,
Teams = l.Teams
.OrderBy(t => t.TeamName)
.Select(t => t.TeamName)
}).ToList();
//foreach (var league in leagsWithTeams)
//{
// Console.WriteLine("--" + league.LeagueName);
// foreach (var team in league.Teams)
// {
// Console.WriteLine(team);
// }
//}
//export data to JSON using build-in JSON serializer (have to add reference to System.Web.Extensions):
//var serializer = new JavaScriptSerializer();
//var json = serializer.Serialize(leagsWithTeams);
//export data to JSON using JSON.NET
//in Nuget Package Manager Console type: Install-Package Newtonsoft.Json
var json = JsonConvert.SerializeObject(leagsWithTeams, Formatting.Indented);
//write the output in a JSON file named leagues-and-teams.json in the directory of the project
File.WriteAllText("../../leagues-and-teams.json", json);
Console.WriteLine(json);
}
static void Main()
{
//Problem 1. Entity Framework Mappings (Database First)
//Create an Entity Framework (EF) data model of the existing database (map the database tables to C# classes).
//Use the "database first" model in EF. To test your EF data model, list all team names.
//Make sure all navigation properties have good (self-describing) names.
var context = new FootballEntities();
//list all team names
var teamsNames = context.Teams.Select(t => t.TeamName);
foreach (var team in teamsNames)
{
Console.WriteLine(team);
}
//като се правят задаяите в отделни проекти: всеки проект трябва да има референция към този, където е връзката с базата;
//на всеки проект да се сложи Entity Framework от package manager-a;
//и да се копира connection string от App.config на този проект и да се сложи в App.config на всички други проекти
}
static void Main()
{
//Problem 4. Import Leagues and Teams from XML
//Write a C# application based on your EF data model for importing leagues and teams.
//The application should process multiple requests and write logs for each operation at the console.
//The input comes from an XML file leagues-and-teams.xml
//The input XML holds a sequence of requests given in the <league>…</league> elements.
//The element "league-name" is optional. The specified league should be created in the database, if it does not exist.
//The elements "teams" and "team" are optional. The specified teams should be created in the database, if they do not exist.
//Note that team "name" is mandatory, but team "country" is optional.
//A team is considered existing in the database when it is matched by both name and country.
//If both elements "league-name" and "teams" exist in a certain query, all teams, not in the given league, should be added to it.
//The output should be printed on the console.
//Your program should correctly parse the input XML.
//Your program should correctly import leagues (new and existing).
//Your program should correctly import teams (new and existing).
//Your program should correctly add teams to leagues (when a team does not already belong to a league).
var context = new FootballEntities();
//parsing XML document
XmlDocument doc = new XmlDocument();
doc.Load("../../leagues-and-teams.xml");
var root = doc.DocumentElement; //it is <leagues-and-teams>
int id = 1;
foreach (XmlNode xmlLeague in root.ChildNodes) //root.ChildNodes are <league>
{
Console.WriteLine("Processing league #{0} ...", id++);
XmlNode leagueNameNode = xmlLeague.SelectSingleNode("league-name");
League league = null;
if (leagueNameNode != null)
{
string leagueName = leagueNameNode.InnerText; //get league name
league = context.Leagues.FirstOrDefault(l => l.LeagueName == leagueName);
if (league != null) //check if database has already this league
{
Console.WriteLine("Existing league: {0}", leagueName); //if league exists in database; it is same as: context.Leagues.Any(l => l.LeagueName == leagueName)
}
else
{
league = new League()
{
LeagueName = leagueName
};
context.Leagues.Add(league); //if league doesn't exist add it to database
Console.WriteLine("Created league: {0}", leagueName);
}
}
XmlNode teamsNode = xmlLeague.SelectSingleNode("teams"); //get element <teams>
if (teamsNode != null) //check if tere is element <teams>
{
foreach (XmlNode xmlTeam in teamsNode.ChildNodes) //tese are elements <team>
{
Team team = null;
string teamName = xmlTeam.Attributes["name"].Value; //get team name from attribute "name" (it is mandatory)
string countryName = null; //get country name from attribute "country" (it is optional)
if (xmlTeam.Attributes["country"] != null)
{
countryName = xmlTeam.Attributes["country"].Value;
}
team =
context.Teams.FirstOrDefault(
t => t.TeamName == teamName && t.Country.CountryName == countryName); //it will return Team or null
if (team != null) //check if database has already this team
{
Console.WriteLine("Existing team: {0} ({1})", teamName, countryName ?? "no country"); //if team exists in database
}
else
{
Country country = context.Countries.FirstOrDefault(c => c.CountryName == countryName); //it will returns null or Country
team = new Team()
{
TeamName = teamName,
Country = country
};
context.Teams.Add(team); //if league doesn't exist add it to database
Console.WriteLine("Created team: {0} ({1})", teamName, countryName ?? "no country");
}
if (league != null)
{
if (league.Teams.Contains(team))
{
Console.WriteLine("Existing team in league: {0} belongs to {1}", team.TeamName, league.LeagueName);
}
else
{
league.Teams.Add(team);
Console.WriteLine("Added team to league: {0} to league {1}", team.TeamName, league.LeagueName);
}
}
}
}
}
context.SaveChanges();
}
static void Main()
{
//Problem 3. Export International Matches as XML
//Write a C# application based on your EF data model for exporting all international matches and their score
//in a XML file named international-matches.xml.
//Each match should have home country and away country. Each country should have country code (as attribute).
//Use an XML parser by choice.
//Attach attribute "date-time" when the match has date and time.
//Attach attribute "date" when the match has date only (without time). Do not attach any attributes when the match has no date.
//List the score if the match has score (home goals and away goals). List the league name if the match has a league.
//Order the matches by date (from the earliest) and by home country and away country alphabetically as second and third criteria.
//For better performance, ensure your program executes a single DB query and retrieves from the database only the required data
//(without unneeded rows and columns).
var context = new FootballEntities();
var internationlMatches = context.InternationalMatches
.OrderBy(m => m.MatchDate)
.ThenBy(m => m.CountryHome.CountryName)
.ThenBy(m => m.CountryAway.CountryName)
.Select(m => new
{
date = m.MatchDate,
homeCountryName = m.CountryHome.CountryName,
awayCountryName = m.CountryAway.CountryName,
homeCountryCode = m.HomeCountryCode,
awayCountryCode = m.AwayCountryCode,
homeGoals = m.HomeGoals,
awayGoals = m.AwayGoals,
leagueName = m.League.LeagueName
}).ToList();
//foreach (var match in internationlMatches)
//{
// Console.WriteLine(match);
//}
XElement matches = new XElement("matches");
foreach (var match in internationlMatches)
{
XElement xmlMatch =
new XElement("match",
new XElement("home-country",
new XAttribute("code", match.homeCountryCode),
match.homeCountryName),
new XElement("away-country",
new XAttribute("code", match.awayCountryCode),
match.awayCountryName)
);
//check if match has league and if has add element
if (match.leagueName != null)
{
xmlMatch.Add(new XElement("league", match.leagueName));
}
//check if match has score and if has add element
if (match.homeGoals != null && match.awayGoals != null)
{
xmlMatch.Add(new XElement("score", match.homeGoals + "-" + match.awayGoals));
}
//check if match has only date or date-time and add corresponding attribute
if (match.date != null)
{
DateTime dateTime;
DateTime.TryParse(match.date.ToString(), out dateTime);
if (dateTime.TimeOfDay.TotalSeconds == 0)
{
xmlMatch.Add(new XAttribute("date", dateTime.ToString("dd-MMM-yyyy")));
}
else
{
xmlMatch.Add(new XAttribute("date-time", dateTime.ToString("dd-MMM-yyyy hh:mm")));
}
}
matches.Add(xmlMatch);
}
Console.WriteLine(matches);
//export data to .xml file
matches.Save("../../international-matches.xml");
}
