Fotballresultater
Vevsiden har to funksjoner. Vi kan hente alle lag i en bestemt divisjon for et bestemt år, og vi kan inspisere alle kampene et lag har spilt.
Koden for å hente lagliste
protected void VisLag_Click(object sender, EventArgs e) { // dropdowns are set up with AutoPostBack, we get all changes here TextTest1.Visible = false; ListBox1.Visible = false; ListBox2.Visible = false; // get country_id from countryname(shortcut) String countryId; if (country.CompareTo("England") == 0) countryId = "en"; else if (country.CompareTo("Italia") == 0) countryId = "it"; else if (country.CompareTo("Spania") == 0) countryId = "es"; else countryId = "de"; // Set up query string sql = String.Format( @"SELECT name FROM db_owner.team WHERE (team_id IN (SELECT team_id FROM db_owner.member WHERE (liga_id IN (SELECT liga_id FROM db_owner.liga WHERE (season_id = '{0}') AND (country_id = '{1}') AND (level = '{2}')))));", year, countryId, level); MakeTeamList(sql); } public void MakeTeamList(string sql) { // connect to database String connectString = @"SERVER=donau.hiof.no; DATABASE=bs; UID=student; PASSWORD=student; PROVIDER=SQLOLEDB"; //use OLE OleDbConnection con = null; OleDbDataReader myReader = null; ListBox1.Items.Clear(); try { con = new OleDbConnection(connectString); con.Open(); // execute the query OleDbCommand myCommand = new OleDbCommand(sql, con); myReader = myCommand.ExecuteReader(); while (myReader.Read()) { String name = myReader.GetString(0); ListBox1.Items.Add(name); } ListBox1.Visible = true; return; } catch (Exception ex) { // error message in textfield TextTest1.Visible = true; TextTest1.Text = ex.Message; return ; } finally { if (con != null) con.Close(); if (myReader != null) myReader.Close(); } }
Koden for å hente kampliste
protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e) { // ListBox1 is set up with AutoPostBack = True // so we get this as a direct consequence of selection change // get country_id from countryname(shortcut) String countryId; if(country.CompareTo("England")==0) countryId="en"; else countryId = "de"; String team = ListBox1.SelectedValue; // set up query String sql=String.Format (@"SELECT hteam.name, ateam.name AS Expr1, db_owner.fmatch.h_goals, db_owner.fmatch.a_goals FROM db_owner.fmatch INNER JOIN db_owner.team AS hteam ON db_owner.fmatch.h_team_id = hteam.team_id INNER JOIN db_owner.team AS ateam ON db_owner.fmatch.a_team_id = ateam.team_id WHERE (hteam.name = '{0}') AND (db_owner.fmatch.liga_id IN (SELECT liga_id FROM db_owner.liga WHERE (season_id = '{1}') AND (country_id = '{2}') AND (level = '{3}'))) OR (db_owner.fmatch.liga_id IN (SELECT liga_id FROM db_owner.liga AS liga_1 WHERE (season_id = '{1}') AND (country_id = '{2}') AND (level = '{3}'))) AND (ateam.name = '{0}');", team, year, countryId, level); MakeMatchList(sql); } public void MakeMatchList(string sql) { String connectString = @"SERVER=donau.hiof.no; DATABASE=bs; UID=student; PASSWORD=student; PROVIDER=SQLOLEDB"; OleDbConnection con = null; OleDbDataReader myReader = null; ListBox2.Items.Clear(); try { con = new OleDbConnection(connectString); con.Open(); // execute a query OleDbCommand myCommand = new OleDbCommand(sql, con); myReader = myCommand.ExecuteReader(); while (myReader.Read()) { String name = myReader.GetString(0) + " - " + myReader.GetString(1) + " -- " + myReader.GetInt32(2).ToString() + ":" + myReader.GetInt32(2).ToString(); ListBox2.Items.Add(name); } ListBox2.Visible = true; return; } catch (Exception ex) { // error message TextTest1.Visible = true; TextTest1.Text = ex.Message; return; } finally { if (con != null) con.Close(); if (myReader != null) myReader.Close(); } }
Her er koden for databaseoppslag skrevet eksplisitt for hver funksjon. Koden kunne vært rasjonalisert betraktelig og vi burde i et et mer omfattende system pakke all databaseaksess inn i en egen klasse.