//添加一系列赛事球员信息到表中
public static bool addMatchPlayerInfoRecord(int matchID, List<int> 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] + ",";
sql = sql + "(SELECT team.ID FROM team,player WHERE team.teamName = player.teamName ";
sql = sql + " AND player.ID = " + list[i] + "))";
dbutility.ExecuteUpdate(sql);
}
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
//往数据库中添加球队列表，返回是否添加成功的信息
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 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 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 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 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 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 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 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 static void updateMatchInfo(SeasonMatch match)
{
DBUtility dbutility = new DBUtility();
string sql = "update matchinfo set seasonName='"+match.getName()+"' ,description='"+match.getDescription();
sql = sql + "' ,switchNum=" + match.getSwitchNum() + " ,serialNum=" + match.getSerialNum();
sql = sql + " where ID=" + match.getID();
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
}
//联动删除某一个赛事中属于某一球队的球员信息
public static bool deleteMatchTeamPlayerInfo(int matchID, int teamID)
{
DBUtility dbutility = new DBUtility();
string sql = "DELETE FROM matchplayerinfo WHERE matchID=" + matchID + " AND teamID=" + teamID;
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
/*
* 更新某一个球队的信息
*/
public static bool updateTeamInfo(Team team)
{
DBUtility dbutility = new DBUtility();
string sql = "update team set teamName='" + team.getName() + "' ,teamLeader='" + team.getLeader();
sql = sql + "' ,teamManager='" + team.getManager() + "' ,teamCoach='" + team.getCoach();
sql = sql + "' where ID=" + team.getID();
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
//从某个赛事中移除球队信息
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 void deletePlayerInfo(List<FootballPlayer> list)
{
DBUtility dbutility = new DBUtility();
string sql = "";
try
{
dbutility.openConnection();
for (int i = 0; i < list.Count; i++)
{
sql = "delete from player where ID=" + list[i].getID();
dbutility.ExecuteUpdate(sql);
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
dbutility.Close();
}
}
//从某个赛事中移除球队信息
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();
}
}
//删除赛事信息
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 bool updatePlayerInfo(FootballPlayer player)
{
DBUtility dbutility = new DBUtility();
string sql = "update player set playerName='" + player.getName() + "',postion='" + player.getPostion()+ "' ,number=" + player.getNumber() +" ,teamName='" + player.getBelongTeam()+"' where ID =" + player.getID();
try
{
dbutility.openConnection();
dbutility.ExecuteUpdate(sql);
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
//删除一系列的赛事球员信息表中的球员信息
public static bool deletMatchPlayerInfoRecord(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 matchplayerinfo WHERE matchID=" + matchID + " AND playerID=" + list[i];
dbutility.ExecuteUpdate(sql);
}
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
return false;
}
finally
{
dbutility.Close();
}
}
