//添加一系列赛事球员信息到表中
public static bool addMatchPlayerInfoRecord(int matchID, List<FootballPlayer> list)
{
DBUtility dbutility = new DBUtility();
string sql = "";
try
{
dbutility.openConnection();
for (int i = 0; i < list.Count; i++)
{
sql = "INSERT INTO matchplayerinfo(matchID,playerID,teamID) values(" + matchID + "," + list[i].getID() + ",";
sql = sql + "(SELECT ID FROM team WHERE teamName ='"+list[i].getBelongTeam()+"'))";
dbutility.ExecuteUpdate(sql);
}
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
//往数据库中添加赛事，并且返回新被添加的赛事
public static SeasonMatch addNewMatch(string name,string description,int switchNum,int serialNum)
{
SeasonMatch match = new SeasonMatch();
DBUtility dbutility = new DBUtility();
int matchID;
string SQL = "insert into matchinfo(seasonName,description,switchNum,serialNum) values('";
SQL=SQL+name + "','" + description + "'," + switchNum + "," + serialNum + ")";
try
{
dbutility.openConnection();
matchID = dbutility.ExecuteQuery_Last_Insert_ID(SQL);
// dbutility.ExecuteUpdate(SQL);
match.setID(matchID);
match.setName(name);
match.setDescription(description);
match.setSwitchNum(switchNum);
match.setSerialNum(serialNum);
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return match;
}
//检查某一个赛事中，比赛数量是否满足条件
public static bool checkGameNumLegal(int matchID,int gameNum)
{
bool flag = false;
//执行查询数据库操作
DBUtility dbutility = new DBUtility();
string SQL = "select serialNum from matchinfo where ID=" + matchID + "";
int _gameNum = -1;
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(SQL);
while (rd.Read())
{
_gameNum = Convert.ToInt32(rd[0]);
}
//判断某一赛事中比赛的总量是否是大于新添加的比赛的比赛序号
if (gameNum <= _gameNum)
{
flag = true;
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return flag;
}
//检查某一个比赛的名称是否存在,某一赛事中比赛的名称必须是唯一的
public static bool checkGameNameExist(int matchID,string gameName)
{
bool flag = false;
DBUtility dbutility = new DBUtility();
string sql = "select uniqueID,matchID,matchGameNum,gameName,homeTeam,guestTeam,gameWinner,";
sql = sql + "gameResult,gameAddress,gameDate from gameInfo where gameName='" + gameName + "'";
sql = sql + " and matchID=" + matchID;
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(sql);
while (rd.Read())
{
flag = true;
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return flag;
}
public static void getDefaultSet()
{
DBUtility dbutility = new DBUtility();
string sql = "select * from defaultset;";
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(sql);
while (rd.Read())
{
DefaultSet.setDefaultSeason(Convert.ToString(rd[0]));
DefaultSet.setDedaultMatch(Convert.ToString(rd[1]));
DefaultSet.setDefaultTurn(Convert.ToInt32(rd[2]));
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
// MessageBox.Show(ex.ToString());
}
finally
{
dbutility.Close();
}
}
////////获取当前赛事的球队信息
/*
* 查询数据库中的球队的基本信息，并且返回到一个线性表中
*/
public static List<Schedule> getScheduleInfo(SeasonOfMatch season)
{
//声明实例
List<Schedule> list = new List<Schedule>();
//执行查询数据库操作
DBUtility dbutility = new DBUtility();
string SQL = "select seasonId,matchId,turn,gameDate,homeTeamName,guestTeamName from schedule where seasonId= " + season.getNumOfSeason();
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(SQL);
while (rd.Read())
{
Schedule s = new Schedule();
s.setSeasonId(Convert.ToInt32(rd[0]));
s.setMatchId(Convert.ToInt32(rd[1]));
s.setTurn(Convert.ToInt32(rd[2]));
s.setGameDate(Convert.ToString(rd[3]));
s.setHomeTeam(Convert.ToString(rd[4]));
s.setGuestTeam(Convert.ToString(rd[5]));
list.Add(s);
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return list;
}
///
///<Summary> 用来处理球员停赛信息的数据库操作</Summary>
///
//检查某一场比赛中，某一个球员是否已经有停赛记录
public static bool checkPlayerSuspended(int playerID,int matchID,int gameNum)
{
bool flag = false;
DBUtility dbutility = new DBUtility();
string sql = "select * from suspendedrecord where matchID=" + matchID + " and gameNum=" + gameNum;
sql = sql + " and playerID=" + playerID;
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(sql);
while (rd.Read())
{
flag = true;
}
}
catch (MySqlException ex)
{
Console.Write(ex.ToString());
}
finally
{
dbutility.Close();
}
return flag;
}
//往数据库中添加球队列表，返回是否添加成功的信息
public static bool addNewTeamList(List<Team> teamList)
{
DBUtility dbutility = new DBUtility();
try
{
dbutility.openConnection();
for (int i = 0; i < teamList.Count;i++ )
{
string SQL = "insert into team(teamName,teamLeader,teamManager,teamCoach,teamFullName,birthDate,matchName,location,introduction) values('";
SQL = SQL + teamList[i].getName() + "','" + teamList[i].getLeader() + "','" + teamList[i].getManager() + "','" + teamList[i].getCoach() + "','" + teamList[i].getTeamFullName() +
"','" + teamList[i].getBirthDate() + "','" + teamList[i].getMatchName() + "','" + teamList[i].getLocation() + "','" + teamList[i].getIntroduction() + "')";
dbutility.ExecuteUpdate(SQL);
}
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
public static List<SeasonOfMatch> AllSeasons()
{
List<SeasonOfMatch> list = new List<SeasonOfMatch>();
//执行查询数据库操作
DBUtility dbutility = new DBUtility();
string SQL = "select id,matchname,seasondescription,numofseason,numOfTurn from gameseason order by id";
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(SQL);
while (rd.Read())
{
list.Add(new SeasonOfMatch(Convert.ToInt32(rd[0]), Convert.ToString(rd[1]), Convert.ToString(rd[2]), Convert.ToInt32(rd[3]), Convert.ToInt32(rd[4])));
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return list;
}
public static void setDefaultSet()
{
DBUtility dbutility = new DBUtility();
string sql="update defaultset set defaultSeason='"+DefaultSet.getDefaultSeason()+"',defaultMatch='"+DefaultSet.getDefaultMatch()+"',defaultTurn='"+DefaultSet.getDefaultTurn()+"';";
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
// MessageBox.Show(ex.ToString());
}
finally
{
dbutility.Close();
}
}
//往某个赛事中添加球队信息
public static bool addMatchTeamInfo(int matchID,int teamID)
{
DBUtility dbutility = new DBUtility();
string sql = "INSERT INTO matchteaminfo(matchID,teamID) values(" + matchID + "," + teamID + ")";
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
//添加停赛记录信息
public static void addNewSuspendedRecord(int matchID,int gameNum,int playerID,DateTime date)
{
DBUtility dbutility = new DBUtility();
//查询某个球员所有停赛记录，同一场球赛不能被停赛两次或两次以上
string sql = "INSERT INTO suspendedrecord(matchID,playerID,gameNum,Date) VALUES(" + matchID + ",";
sql = sql + playerID + "," + gameNum + ",'" + date + "')";
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
}
catch (MySqlException ex)
{
Console.Write(ex.ToString());
}
finally
{
dbutility.Close();
}
}
/****
*** 新增某一个赛季的信息
****/
public static void addNewSeasonInfo(SeasonOfMatch season)
{
DBUtility dbutility = new DBUtility();
string sql = "insert into gameseason(id,matchname,numofseason,seasondescription,isCurrentSeason,numOfTurn) values('" + season.getId()
+"','" + season.getmatchName() + "','" + season.getNumOfSeason() + "','" + season.getSeasonDescription() + "',"+1+","+season.getNumOfTurn()+")";
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
}
//查询某一赛事的所有罚牌记录信息
public static List<PlayerCardRecord> getMatchCardRecord(int matchID)
{
List<PlayerCardRecord> list = new List<PlayerCardRecord>();
DBUtility dbutility = new DBUtility();
string sql = "select playergetcardrecord.uniqueID,playergetcardrecord.matchID,matchinfo.seasonName,";
sql = sql + "playergetcardrecord.playerID,player.playerName,playergetcardrecord.yellowCardNum,";
sql = sql + "playergetcardrecord.redCardNum,playergetcardrecord.serialNum,playergetcardrecord.getDate,gameinfo.gameName from ";
sql = sql + "playergetcardrecord,player,matchinfo,gameinfo where playergetcardrecord.matchID=matchinfo.ID and ";
sql = sql + "gameinfo.matchID=playergetcardrecord.matchID and playergetcardrecord.serialNum=gameinfo.matchGameNum and ";
sql = sql + "playergetcardrecord.playerID=player.ID and playergetcardrecord.matchID=" + matchID;
sql = sql + " order by playergetcardrecord.serialNum";
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(sql);
while (rd.Read())
{
PlayerCardRecord playerCardRecord = new PlayerCardRecord();
playerCardRecord.setUniqueID(Convert.ToInt32(rd[0]));
playerCardRecord.setMatchID(Convert.ToInt32(rd[1]));
playerCardRecord.setMatchName(Convert.ToString(rd[2]));
playerCardRecord.setPlayerID(Convert.ToInt32(rd[3]));
playerCardRecord.setPlayerName(Convert.ToString(rd[4]));
playerCardRecord.setYellowCardNum(Convert.ToInt32(rd[5]));
playerCardRecord.setRedCardNum(Convert.ToInt32(rd[6]));
playerCardRecord.setSerialNum(Convert.ToInt32(rd[7]));
playerCardRecord.setDateTime(Convert.ToDateTime(rd[8]));
playerCardRecord.setGameName(Convert.ToString(rd[9]));
list.Add(playerCardRecord);
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return list;
}
//往数据库中添加球员，返回是否添加成功的信息
public static bool addNewPlayer(FootballPlayer player)
{
DBUtility dbutility = new DBUtility();
string SQL = "insert into player(playerName,postion,teamName,number,playerIDnum) values('";
SQL = SQL + player.getName() + "','" + player.getPostion() + "','" + player.getBelongTeam() + "'," + player.getNumber() + ",'" +player.getIDnum() + "')";
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(SQL);
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
//添加球员罚牌记录信息
public static void addNewPlayerCardNum(PlayerCardRecord _playerCardNum)
{
DBUtility dbutility = new DBUtility();
string SQL = "insert into playerGetCardRecord(matchID,playerID,serialNum,yellowCardNum,redCardNum,";
SQL = SQL + "getDate) values(" + _playerCardNum.getMatchID() + "," + _playerCardNum.getPlayerID() + ",";
SQL = SQL + _playerCardNum.getSerialNum() + "," + _playerCardNum.getYellowCardNum() + ",";
SQL = SQL + _playerCardNum.getRedCardNum() + ",'" + _playerCardNum.getDateTime() + "')";
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(SQL);
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
}
//插入赛程记录
public static void addSchedule(List<Schedule> list)
{
DBUtility dbutility = new DBUtility();
try
{
dbutility.openConnection();
for (int i = 0; i < list.Count;i++ ) {
string sql = "insert into schedule(seasonId,matchId,turn,gameDate,homeTeamName,guestTeamName) values("+list[i].getSeasonId()+","+list[i].getMatchId()
+ ","+list[i].getTurn()+",'"+list[i].getGameDate()+"','"+list[i].getHomeTeam()+"','"+list[i].getGuestTeam()+"')";
dbutility.ExecuteUpdate(sql);
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally { dbutility.Close(); }
}
//添加比赛信息
public static void addNewGameInfo(Game game)
{
DBUtility dbutility = new DBUtility();
string sql = "insert into gameInfo(matchID,matchGameNum,gameName,homeTeam,guestTeam,gameWinner,";
sql = sql + "gameResult,gameAddress,gameDate) values(" + game.getMatchID() + "," + game.getMatchGameNum() + ",'";
sql=sql+game.getGameName() + "','"+game.getHomeTeam()+"','"+game.getGuestTeam()+"','"+game.getGameWinner()+"','";
sql = sql + game.getGameResult() + "','" + game.getGameAddress() + "','" + game.getGameDate() + "')";
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
// MessageBox.Show(ex.ToString());
}
finally
{
dbutility.Close();
}
}
//添加赛事球员信息到表中
public static bool addMatchPlayerInfoRecord(int matchID, int playerID)
{
DBUtility dbutility = new DBUtility();
string sql = "INSERT INTO matchplayerinfo(matchID,playerID,teamID) values(" + matchID + "," + playerID + ",";
sql=sql+"(SELECT team.ID FROM team,player WHERE team.teamName = player.teamName ";
sql=sql+" AND player.ID = "+playerID+"))";
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
//往数据库中添加球员列表，返回是否添加成功的信息
public static void addPlayerList(List<FootballPlayer> playerlList)
{
DBUtility dbutility = new DBUtility();
try
{
dbutility.openConnection();
foreach (FootballPlayer player in playerlList)
{
string SQL = "insert into player(playerName,postion,teamName,number,playerIDnum) values('";
SQL = SQL + player.getName() + "','" + player.getPostion() + "','" + player.getBelongTeam() + "'," + player.getNumber() + ",'" + player.getIDnum() + "')";
dbutility.ExecuteUpdate(SQL);
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
}
//往某个赛事中添加球队信息，添加一系列球队信息到赛事球队表中
public static bool addMatchTeamInfo(int matchID, List<Team> list)
{
DBUtility dbutility = new DBUtility();
string sql = "";
try
{
dbutility.openConnection();
for (int i = 0; i < list.Count; i++)
{
sql = "INSERT INTO matchteaminfo(matchID,teamID) values(" + matchID + "," + list[i].getID() + ")";
dbutility.ExecuteUpdate(sql);
}
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
//自定义下拉列表的列表项
public void InitHomeTeamColumnsSet(string team)
{
DataTable teamTable = new DataTable();
DBUtility dbutility = new DBUtility();
string SQL = "select teamName from team where matchName='"+SystemParam.getMatch().getName()+"' order by ID";
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(SQL);
teamTable.Load(rd);
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
((DataGridViewComboBoxColumn)this.dataGridView1.Columns[team]).DataSource =teamTable;
((DataGridViewComboBoxColumn)this.dataGridView1.Columns[team]).DataPropertyName = "teamName";
((DataGridViewComboBoxColumn)this.dataGridView1.Columns[team]).DisplayMember = "teamName";
}
//从某个赛事中移除球队信息
public static bool deleteMatchTeamInfo(int matchID, int teamID)
{
DBUtility dbutility = new DBUtility();
string sql = "DELETE FROM matchteaminfo WHERE matchID=" + matchID + " AND teamID=" + teamID;
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
//移除属于某个赛事的某个球队的球员信息
if (MatchPlayerInfoDAO.deleteMatchTeamPlayerInfo(matchID, teamID))
{
return true;
}
else //当没有全部移除时
{
return false;
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
//取出不属于某一个赛事的所有球队信息，并且返回到一个线性表中
public static List<Team> getNotAssignedMatchTeamInfo(int matchID)
{
DBUtility dbutility = new DBUtility();
List<Team> _teamList = new List<Team>();
string sql = "SELECT ID,teamName,teamLeader,teamManager,teamCoach FROM team WHERE ID ";
sql = sql + "NOT IN (SELECT teamID FROM matchteaminfo WHERE matchID = " + matchID + ")";
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(sql);
while (rd.Read())
{
_teamList.Add(new Team(Convert.ToInt32(rd[0]), Convert.ToString(rd[1]), Convert.ToString(rd[2]), Convert.ToString(rd[3]), Convert.ToString(rd[4])));
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return _teamList;
}
//查询某一场比赛的信息
public static Game getGameInfo(int matchID,string gameName)
{
DBUtility dbutility = new DBUtility();
Game game = new Game();
string sql = "select uniqueID,matchID,matchGameNum,gameName,homeTeam,guestTeam,gameWinner,";
sql = sql + "gameResult,gameAddress,gameDate from gameInfo where gameName='" + gameName+"'";
sql = sql + " and matchID=" + matchID;
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(sql);
while (rd.Read())
{
game = new Game(Convert.ToInt32(rd[0]), Convert.ToInt32(rd[1]), Convert.ToInt32(rd[2]),
Convert.ToString(rd[3]), Convert.ToString(rd[4]), Convert.ToString(rd[5]), Convert.ToString(rd[6]),
Convert.ToString(rd[7]), Convert.ToString(rd[8]), Convert.ToDateTime(rd[9]));
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return game;
}
//检查赛事名称是否存在
public static bool checkMatchNameExist(string name)
{
bool flag = false;
//执行查询数据库操作
DBUtility dbutility = new DBUtility();
string SQL = "select ID,seasonName,description from matchinfo where seasonName='" + name + "'";
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(SQL);
while (rd.Read())
{
flag = true;
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return flag;
}
//获取某一赛事中其最大的比赛序号的记录
public static int getMatchMaxGameNum(int matchID)
{
int _maxGameNum = 0; //记录最大的比赛序号
DBUtility dbutility = new DBUtility();
string sql = "select max(matchGameNum) from gameInfo where matchID=" + matchID;
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(sql);
while (rd.Read())
{
if (Convert.ToString(rd[0]) != "")
{
_maxGameNum = Convert.ToInt32(rd[0]);
}
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return _maxGameNum;
}
//删除赛事信息
public static void deleteMatchInfo(int matchID)
{
DBUtility dbutility = new DBUtility();
string sql = "delete from matchinfo where ID=" + matchID;
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
}
//取出所有比赛信息
public static List<Game> getAllGameInfo()
{
DBUtility dbutility = new DBUtility();
List<Game> list = new List<Game>();
string sql = "select uniqueID,matchID,matchGameNum,gameName,homeTeam,guestTeam,gameWinner,";
sql = sql + "gameResult,gameAddress,gameDate from gameInfo ORDER BY uniqueID";
try
{
dbutility.openConnection();
MySqlDataReader rd = dbutility.ExecuteQuery(sql);
while (rd.Read())
{
list.Add(new Game(Convert.ToInt32(rd[0]), Convert.ToInt32(rd[1]), Convert.ToInt32(rd[2]),
Convert.ToString(rd[3]), Convert.ToString(rd[4]), Convert.ToString(rd[5]), Convert.ToString(rd[6]),
Convert.ToString(rd[7]), Convert.ToString(rd[8]), Convert.ToDateTime(rd[9])));
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
return list;
}
//从某个赛事中移除球队信息
public static bool deleteMatchTeamInfo(int matchID, List<int> list)
{
DBUtility dbutility = new DBUtility();
string sql = "";
try
{
dbutility.openConnection();
for (int i = 0; i < list.Count; i++)
{
sql = "DELETE FROM matchteaminfo WHERE matchID=" + matchID + " AND teamID=" + list[i];
dbutility.ExecuteUpdate(sql);
//移除属于某个赛事的某个球队的球员信息
MatchPlayerInfoDAO.deleteMatchTeamPlayerInfo(matchID, list[i]);
}
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}