using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
namespace db1
{
class DBAccess
{
static String connectString =
@"DRIVER={MySQL ODBC 3.51 Driver};
SERVER=localhost;
DATABASE=football;
UID=root;
PASSWORD=oliver;
OPTION=3";
static public String getGames(String country, String level, String year)
{
String retString = "<html><body>";
String myQuery =String.Format(
@"SELECT hteam.name,ateam.name,h_goals,a_goals FROM fmatch,team hteam, team ateam
WHERE fmatch.h_team_id = hteam.team_id AND
fmatch.a_team_id = ateam.team_id AND liga_id in
(SELECT liga_id FROM liga WHERE
season_id='{0}'AND country_id='{1}' AND level='{2}'
)
;", year,country,level);
OdbcConnection con=null;
OdbcDataReader myReader=null;
try
{
con = new OdbcConnection(connectString);
con.Open();
OdbcCommand myCommand = new OdbcCommand(myQuery, con);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
retString += "<div>"+
myReader.GetString(0) + " - " +
myReader.GetString(1) + " : " +
Convert.ToString(myReader.GetInt32(2)) + " - " +
Convert.ToString(myReader.GetInt32(3)) +
"</div>\r\n";
}
catch (Exception ex)
{
retString += "<p>"+ex.Message+"</p>";
}
if (con!=null)
con.Close();
if (myReader!=null)
myReader.Close();
return retString + "</body></html>";
}
static public String getTeams(String country, String level, String year)
{
String retString = "<html><body>";
String myQuery = String.Format(
@"SELECT name from team WHERE team_id IN
(SELECT team_id FROM member WHERE liga_id IN
(SELECT liga_id FROM liga WHERE
season_id='{0}'AND country_id='{1}' AND level='{2}'
)
);", year, country, level);
OdbcConnection con = null;
OdbcDataReader myReader = null;
try
{
con = new OdbcConnection(connectString);
con.Open();
OdbcCommand myCommand = new OdbcCommand(myQuery, con);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
retString += "<div>" + myReader.GetString(0) + "</div>\r\n";
}
catch (Exception ex)
{
retString += "<p>" + ex.Message + "</p>";
}
if (con != null)
con.Close();
if (myReader != null)
myReader.Close();
return retString + "</body></html>";
}
}
}