En enkel databaseløsning
Form
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
namespace db1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
// initiate combo box for countries
ArrayList countries = DBAccess.getAllCountries();
if (countries==null)
{
webBrowser1.DocumentText = "sorry";
return;
}
for (int ix = 0; ix < countries.Count; ix++)
comboLand.Items.Add(countries[ix]);
// combos for years and level are filled in design
// set initial selection
comboLand.SelectedItem=comboLand.Items[0];
comboLevel.SelectedItem = comboLevel.Items[0];
comboAr.SelectedItem = comboAr.Items[0];
}
private void buttonVis_Click(object sender, EventArgs e)
{
// Control selection, collect data from form,
// access database and show result
String country = comboLand.SelectedItem.ToString();
String level = comboLevel.SelectedItem.ToString();
String year = comboAr.SelectedItem.ToString();
// fix parameters to match database
year = year.Substring(0, 4);
country = DBAccess.GetCountryID(country);
if (country == null)
{
webBrowser1.DocumentText = "sorry";
return;
}
// do it
if (sender.Equals(VisLag))
webBrowser1.DocumentText =
DBAccess.getTeamList(country, level, year);
else if(sender.Equals(VisTabell))
webBrowser1.DocumentText =
DBAccess.getTable(country, level, year);
else // vis kamper
webBrowser1.DocumentText =
DBAccess.getMatcList(country, level, year);
}
}
}DBAccess
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Collections;
namespace db1
{
class DBAccess
{
// using mysql:
// http://dev.mysql.com/tech-resources/articles/dotnet/
// make this accessible to student:
// grant select on fotball.* to student identified by 'student';
// localhost,root,oliver
static String connectString =
@"DRIVER={MySQL ODBC 3.51 Driver};
SERVER=frigg.hiof.no;
DATABASE=fotball;
UID=student;
PASSWORD=student;
OPTION=3";
static Liga theLiga = null;
static String theCountry = "";
static String theLevel = "";
static String theYear = "";
static String ErrorMessage = "";
static String Page = @"
<html>
<head>
</head>
<body>
{0}
</body>
</html>";
static public ArrayList getAllCountries()
{
// set up a query based on the parameters
String myQuery = String.Format(
@"SELECT navn From land;");
OdbcConnection con = null;
OdbcDataReader myReader = null;
try
{
con = new OdbcConnection(connectString);
con.Open();
// execute the query
OdbcCommand myCommand = new OdbcCommand(myQuery, con);
myReader = myCommand.ExecuteReader();
// and process the results
ArrayList list=new ArrayList(100);
while(myReader.Read()){
list.Add(myReader.GetString(0));
};
return list;
}
catch (Exception ex)
{
return null;
}
finally
{
if (con != null)
con.Close();
if (myReader != null)
myReader.Close();
}
}
static public String GetCountryID(String countryName)
{
// set up a query based on the parameters
String myQuery = String.Format(
@"SELECT land_id From land where navn='{0}';",countryName);
OdbcConnection con = null;
OdbcDataReader myReader = null;
try
{
con = new OdbcConnection(connectString);
con.Open();
// execute a query
OdbcCommand myCommand = new OdbcCommand(myQuery, con);
myReader = myCommand.ExecuteReader();
// and process the results
myReader.Read();
return myReader.GetString(0);
}
catch (Exception ex)
{
return "en";
}
finally
{
if (con != null)
con.Close();
if (myReader != null)
myReader.Close();
}
}
static public bool EstablishLiga(String country,
String level,
String year)
{
// no need to access it again if it is already there
if ((theLiga != null) &&
(theCountry.CompareTo(country) == 0) &&
(theLevel.CompareTo(level) == 0) &&
(theYear.CompareTo(year) == 0))
return true;
// set up a query based on the parameters
String myQuery = String.Format(
@"SELECT hlag.navn,blag.navn,h_maal,b_maal,kamp_dato
FROM kamp,lag hlag, lag blag
WHERE kamp.h_lag_id = hlag.lag_id AND
kamp.b_lag_id = blag.lag_id AND
liga_id in
(SELECT liga_id FROM liga
WHERE sesong_id='{0}'AND
land_id='{1}' AND
nivaa='{2}'
)
;", year, country, level);
OdbcConnection con = null;
OdbcDataReader myReader = null;
theLiga = new Liga();
try
{
con = new OdbcConnection(connectString);
con.Open();
// execute a query
OdbcCommand myCommand = new OdbcCommand(myQuery, con);
myReader = myCommand.ExecuteReader();
// and process the results
int count = 0;
while (myReader.Read())
{
theLiga.AddMatch(
myReader.GetString(0),
myReader.GetString(1),
myReader.GetInt32(2),
myReader.GetInt32(3),
myReader.GetString(4)
);
count++;
}
if (count < 2)
{
ErrorMessage =
"<p>Ligaen eksisterer ikke i databasen</p>";
return false;
}
return true;
}
catch (Exception ex)
{
ErrorMessage =
"<p>Fikk ikke kontakt med databasen:" +
ex.Message +
"</p>";
theLiga = null;
return false;
}
finally
{
if (con != null)
con.Close();
if (myReader != null)
myReader.Close();
}
}
static public String getTable(String country,
String level,
String year)
{
if (!EstablishLiga(country, level, year))
return String.Format(Page, ErrorMessage);
return String.Format(Page, theLiga.GetNormalTable());
}
static public String getTeamList(String country,
String level,
String year)
{
if (!EstablishLiga(country, level, year))
return String.Format(Page, ErrorMessage);
return String.Format(Page, theLiga.GetSimpleTeamList());
}
static public String getMatcList(String country,
String level,
String year)
{
if (!EstablishLiga(country, level, year))
return String.Format(Page, ErrorMessage);
return String.Format(Page, theLiga.GetMatchList());
}
}
}
Liga
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
namespace db1
{
#region sorthelpers
// ICompare class for sorting teams on name
class TeamNameCompare : IComparer
{
public int Compare(Object a, Object b)
{
return String.Compare(((Team)a).TeamName,
((Team)b).TeamName);
}
}
// ICompare class for sorting teams on results
class TeamCompare : IComparer
{
public int Compare(Object a, Object b)
{
if (((Team)a).IsBetter((Team)b))
return -1;
if (((Team)b).IsBetter((Team)a))
return 1;
return 0;
}
}
// ICompare class for sorting matches on date
class MatchCompare : IComparer
{
public int Compare(Object a, Object b)
{
if (((Match)a).IsAfter((Match)b))
return -1;
if (((Match)b).IsAfter((Match)a))
return 1;
return 0;
}
}
#endregion sorthelpers
#region liga
class Liga
{
ArrayList Teams;
ArrayList Matches;
public Liga()
{
Teams = new ArrayList(25);
Matches = new ArrayList(400);
}
public void AddMatch(String h_team,
String a_team,
int h_gls,
int a_gls,
String date)
{
Matches.Add(new Match(h_team,a_team,h_gls,a_gls,date));
Team home = TeamWithName(h_team);
if (home == null)
{
home = new Team(h_team);
Teams.Add(home);
}
home.AddHomeResult(h_gls, a_gls);
Team away = TeamWithName(a_team);
if (away == null)
{
away = new Team(a_team);
Teams.Add(away);
}
away.AddAwayResult(a_gls, h_gls);
}
private Team TeamWithName(String name)
{
for (int ix = 0; ix < Teams.Count; ix++)
if (((Team)Teams[ix]).Name.CompareTo(name) == 0)
return (Team)Teams[ix];
return null;
}
public String GetNormalTable()
{
// sort Teams
Teams.Sort(new TeamCompare());
StringBuilder tbody = new StringBuilder(2000);
for (int tix = 0; tix < Teams.Count; tix++)
{
tbody.Append(((Team)Teams[tix]).PrepareNormalRow());
}
String Table =
String.Format(@"<table>{0}</table>", tbody.ToString());
return Table;
}
public String GetSimpleTeamList()
{
// sort Teams on name
Teams.Sort(new TeamNameCompare());
StringBuilder tlist = new StringBuilder(2000);
for (int tix = 0; tix < Teams.Count; tix++)
{
tlist.Append("<li>" + ((Team)Teams[tix]).Name + "</li>");
}
String Table =
String.Format(@"<ul>{0}</ul>", tlist.ToString());
return Table;
}
public String GetMatchList()
{
// sort matches on date
Matches.Sort(new MatchCompare());
StringBuilder tlist = new StringBuilder(4000);
for (int tix = 0; tix < Matches.Count; tix++)
{
tlist.Append(((Match)Matches[tix]).PrepareRow());
}
String Table =
String.Format(@"<table>{0}</table>", tlist.ToString());
return Table;
}
}
#endregion liga
}
Match
using System;
using System.Collections.Generic;
using System.Text;
namespace db1
{
class Match
{
String homeTeam;
String awayTeam;
int homeGoals;
int awayGoals;
String matchDate;
public Match(String hT, String aT, int hG, int aG,String date)
{
homeTeam=hT;
awayTeam=aT;
homeGoals=hG;
awayGoals=aG;
matchDate = date;
}
public String MatchDate{
get { return matchDate; }
}
public String PrepareRow()
{
return String.Format(
@"<tr {6}><td {6}>{0}</td>
<td>{1}</td><td>{2}</td>
<td {5}>{3}-{4}</td>
</tr>",
matchDate,
homeTeam,
awayTeam,
Convert.ToString(homeGoals),
Convert.ToString(awayGoals),
"style=\"color:red\"",
"style=\"font-size:11px\"");
}
public bool IsAfter(Match M)
{
return MatchDate.CompareTo(M.MatchDate) > 0;
}
}
}
Team
using System;
using System.Collections.Generic;
using System.Text;
namespace db1
{
class Team
{
int h_win, h_draw, h_loose,
a_win, a_draw, a_loose,
h_pnts, a_pnts,
g_h_pluss, g_h_minus, g_a_pluss, g_a_minus;
public String Name;
public Team(String name)
{
h_win = h_draw = h_loose =
a_win = a_draw = a_loose =
h_pnts = a_pnts =
g_h_pluss = g_h_minus = g_a_pluss = g_a_minus = 0;
Name = name;
}
public void AddHomeResult(int ownGoals, int oppGoals)
{
g_h_pluss += ownGoals;
g_h_minus += oppGoals;
if (ownGoals > oppGoals)
{
h_win += 1;
h_pnts += 3;
}
else if (ownGoals < oppGoals)
h_loose += 1;
else
{
h_draw += 1;
h_pnts += 1;
}
}
public void AddAwayResult(int ownGoals, int oppGoals)
{
g_a_pluss += ownGoals;
g_a_minus += oppGoals;
if (ownGoals > oppGoals)
{
a_win += 1;
a_pnts += 3;
}
else if (ownGoals < oppGoals)
a_loose += 1;
else
{
a_draw += 1;
a_pnts += 1;
}
}
public String PrepareNormalRow()
{
// name,matchcount,wins,draws,lost,owngoals-lostgoals,pnts
String S=String.Format(@"<tr>
<td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td>
<td>{4}</td><td>{5}</td><td>{6}</td>
</tr>
",
Name,
Convert.ToString(h_win +h_draw +h_loose +a_win +a_draw +a_loose),
Convert.ToString(h_win+a_win),
Convert.ToString(h_draw+a_draw),
Convert.ToString(h_loose+a_loose),
Convert.ToString(g_h_pluss + g_a_pluss) +
"-" + Convert.ToString(g_h_minus + g_a_minus),
Convert.ToString(h_win*3 +h_draw +a_win*3 +a_draw )
);
return S;
}
public bool IsBetter(Team T)
{
if(TotalPoints>T.TotalPoints)
return true;
if (TotalPoints == T.TotalPoints)
return GoalDiff > T.GoalDiff;
return false;
}
public String TeamName
{
get { return Name; }
}
public int TotalPoints
{
get { return h_win * 3 + h_draw + a_win * 3 + a_draw; }
}
public int GoalDiff
{
get{ return g_h_pluss+g_a_pluss-g_h_minus-g_a_minus;}
}
}
}
Databasen er bygget opp slik:
|
create database fotball; use fotball; create table sesong( sesong_id YEAR PRIMARY KEY NOT NULL, beskrivelse TEXT ); create table land( land_id CHAR(2) PRIMARY KEY, navn VARCHAR(30), beskrivelse TEXT ); create table lag( lag_id VARCHAR(35) PRIMARY KEY, navn VARCHAR(30), land_id CHAR(2), beskrivelse VARCHAR(100) ); create table kamp( kamp_id INT AUTO_INCREMENT, liga_id VARCHAR(35), h_lag_id VARCHAR(35), b_lag_id VARCHAR(35), h_maal INT, b_maal INT, kamp_dato DATE, PRIMARY KEY(kamp_id), UNIQUE(kamp_dato,h_lag_id,b_lag_id) ); create table liga( liga_id VARCHAR(35) PRIMARY KEY, sesong_id YEAR, land_id CHAR(2), nivaa INT, antall_aar INT, tittel VARCHAR(40), beskrivelse TEXT); create table lag_i_liga( lag_i_liga_id INT PRIMARY KEY AUTO_INCREMENT, liga_id VARCHAR(35), lag_id VARCHAR(35), UNIQUE(liga_id,lag_id) ); |
