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"); }