/// <summary>
/// This function handles the "Upload PGN" button.
/// Given a filename, parses the PGN file, and uploads
/// each chess game to the user's database.
/// </summary>
/// <param name="PGNfilename">The path to the PGN file</param>
private void UploadGamesToDatabase(string PGNfilename)
{
// This will build a connection string to your user's database on atr,
// assuimg you've typed a user and password in the GUI
string connection = GetConnectionString();
// TODO: Load and parse the PGN file
// We recommend creating separate libraries to represent chess data and load the file
ChessTools.PGNReader reader = new ChessTools.PGNReader(PGNfilename);
List <ChessTools.ChessGame> Games = reader.getChessGames();
// Use this to tell the GUI's progress bar how many total work steps there are
// For example, one iteration of your main upload loop could be one work step
SetNumWorkItems(Games.Count);
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
// Open a connection
conn.Open();
//Prepare necessary commands for use
MySqlCommand commandForPlayers = conn.CreateCommand();
commandForPlayers.CommandText = "INSERT INTO Players (Name, Elo) values (@var1, @var2) ON DUPLICATE KEY UPDATE Elo = IF(@var2 > Elo, @var2, Elo); " +
"SELECT pID from Players WHERE Name = @var1";
commandForPlayers.Parameters.AddWithValue("@var1", "Invalid Name");
commandForPlayers.Parameters.AddWithValue("@var2", -1);
commandForPlayers.Prepare();
MySqlCommand commandForEvents = conn.CreateCommand();
commandForEvents.CommandText = "INSERT IGNORE INTO Events (Name, Site, Date) values (@var1, @var2, @var3); " +
"SELECT eID from Events WHERE Name = @var1 && Site = @var2 && Date = @var3";
commandForEvents.Parameters.AddWithValue("@var1", "Invalid Name");
commandForEvents.Parameters.AddWithValue("@var2", "Invalid Site");
commandForEvents.Parameters.AddWithValue("@var3", "Invalid Date");
commandForEvents.Prepare();
MySqlCommand commandForGames = conn.CreateCommand();
commandForGames.CommandText = "INSERT IGNORE INTO Games (Round, Result, Moves, BlackPlayer, WhitePlayer, eID) values (@var1, @var2, @var3, @var4, @var5, @var6)";
commandForGames.Parameters.AddWithValue("@var1", "0");
commandForGames.Parameters.AddWithValue("@var2", 'E');
commandForGames.Parameters.AddWithValue("@var3", "0");
commandForGames.Parameters.AddWithValue("@var4", 0);
commandForGames.Parameters.AddWithValue("@var5", 0);
commandForGames.Parameters.AddWithValue("@var6", 0);
commandForGames.Prepare();
// iterate through data and generate appropriate insert commands
foreach (ChessTools.ChessGame game in Games)
{
int wID;
int bID;
int eID;
commandForPlayers.Parameters["@var1"].Value = game.White;
commandForPlayers.Parameters["@var2"].Value = game.WhiteElo;
wID = Convert.ToInt32(commandForPlayers.ExecuteScalar());
commandForPlayers.Parameters["@var1"].Value = game.Black;
commandForPlayers.Parameters["@var2"].Value = game.BlackElo;
bID = Convert.ToInt32(commandForPlayers.ExecuteScalar());
commandForEvents.Parameters["@var1"].Value = game.Event;
commandForEvents.Parameters["@var2"].Value = game.Site;
commandForEvents.Parameters["@var3"].Value = game.EventDate;
eID = Convert.ToInt32(commandForEvents.ExecuteScalar());
commandForGames.Parameters["@var1"].Value = game.Round;
commandForGames.Parameters["@var2"].Value = game.Result;
commandForGames.Parameters["@var3"].Value = game.Moves;
commandForGames.Parameters["@var4"].Value = bID;
commandForGames.Parameters["@var5"].Value = wID;
commandForGames.Parameters["@var6"].Value = eID;
commandForGames.ExecuteNonQuery();
// Use this to tell the GUI that one work step has completed:
WorkStepCompleted();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
/// <summary>
/// This function handles the "Upload PGN" button.
/// Given a filename, parses the PGN file, and uploads
/// each chess game to the user's database.
/// </summary>
/// <param name="PGNfilename">The path to the PGN file</param>
private void UploadGamesToDatabase(string PGNfilename)
{
// This will build a connection string to your user's database on atr,
// assuimg you've typed a user and password in the GUI
string connection = GetConnectionString();
// TODO: Load and parse the PGN file
// We recommend creating separate libraries to represent chess data and load the file
List <ChessGame> games = new ChessTools.PGNReader().ReadFromFile(PGNfilename);
// Use this to tell the GUI's progress bar how many total work steps there are
// For example, one iteration of your main upload loop could be one work step
SetNumWorkItems(games.Count);
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
// Open a connection
conn.Open();
// TODO: iterate through your data and generate appropriate insert commands
foreach (var g in games)
{
MySqlCommand command = conn.CreateCommand();
// Insert Events values
command.CommandText = "insert ignore into Events(Name, Site, Date) "
+ "values (@Name, @Site, @Date)";
command.Parameters.AddWithValue("@Name", g.Event);
command.Parameters.AddWithValue("@Site", g.Site);
command.Parameters.AddWithValue("@Date", g.EventDate);
command.ExecuteNonQuery();
// Insert White Players
command = conn.CreateCommand();
command.CommandText = "insert into Players(Name, Elo)"
+ "select * from (select @wName, @wElo) as temp where not exists (select Name from Players where Name = @wName)";
command.Parameters.AddWithValue("@wName", g.White);
command.Parameters.AddWithValue("@wElo", g.WhiteElo);
command.ExecuteNonQuery();
command = conn.CreateCommand();
command.CommandText = "update Players set Elo = @wElo "
+ "where (Name = @wName and Elo < @wElo)";
command.Parameters.AddWithValue("@wName", g.White);
command.Parameters.AddWithValue("@wElo", g.WhiteElo);
command.ExecuteNonQuery();
// Insert Black Players
command.CommandText = "insert into Players(Name, Elo)"
+ "select * from (select @bName, @bElo) as temp where not exists (select Name from Players where Name = @bName)";
command.Parameters.AddWithValue("@bName", g.Black);
command.Parameters.AddWithValue("@bElo", g.BlackElo);
command.ExecuteNonQuery();
command = conn.CreateCommand();
command.CommandText = "update Players set Elo = @bElo "
+ "where (Name = @bName and Elo < @bElo)";
command.Parameters.AddWithValue("@bName", g.Black);
command.Parameters.AddWithValue("@bElo", g.BlackElo);
command.ExecuteNonQuery();
// Insert Games values
command = conn.CreateCommand();
command.CommandText = "insert ignore into Games(Result, Moves, BlackPlayer, WhitePlayer, eID) "
+ "values (@Result, @Moves, (select pID from Players where Name = @BlackPlayer), (select pID from Players where Name = @WhitePlayer),"
+ "(select eID from Events where Name = @Name and Date = @Date))";
command.Parameters.AddWithValue("@Result", g.Result);
command.Parameters.AddWithValue("@Moves", g.Moves);
command.Parameters.AddWithValue("@BlackPlayer", g.Black);
command.Parameters.AddWithValue("@WhitePlayer", g.White);
command.Parameters.AddWithValue("@Name", g.Event);
command.Parameters.AddWithValue("@Date", g.EventDate);
command.ExecuteNonQuery();
// Use this to tell the GUI that one work step has completed:
WorkStepCompleted();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
/// <summary>
/// This function handles the "Upload PGN" button.
/// Given a filename, parses the PGN file, and uploads
/// each chess game to the user's database.
/// </summary>
/// <param name="PGNfilename">The path to the PGN file</param>
private void UploadGamesToDatabase(string PGNfilename)
{
// This will build a connection string to your user's database on atr,
// assuimg you've typed a user and password in the GUI
string connection = GetConnectionString();
// TODO: Load and parse the PGN file
// We recommend creating separate libraries to represent chess data and load the file
// Use this to tell the GUI's progress bar how many total work steps there are
// For example, one iteration of your main upload loop could be one work step
// SetNumWorkItems(...);
/*Instatiate PGN Reader and read the pgn file*/
PGNReader pgn = new ChessTools.PGNReader();
List <ChessGame> games = pgn.Reader(PGNfilename);
Dictionary <string, int> Players = pgn.createPlayers(games);
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
// Open a connection
conn.Open();
/*Add Players to database, need to add ELO in if it is higher*/
foreach (KeyValuePair <string, int> entry in Players)
{
MySqlCommand command = conn.CreateCommand();
command.CommandText = "INSERT INTO Players (Name, Elo) SELECT * FROM " +
"(SELECT @Name, @Elo) as temp WHERE NOT EXISTS (" +
"SELECT Name FROM Players WHERE Name = @Name) LIMIT 1";
command.Parameters.Add(new MySqlParameter("@Name", entry.Key));
command.Parameters.Add(new MySqlParameter("@Elo", entry.Value));
command.ExecuteNonQuery();
}
/*Add Events and Games to Database*/
foreach (ChessGame game in games)
{
WorkStepCompleted();
/*Update White Elos if we find a better elo*/
MySqlCommand command = conn.CreateCommand();
command.CommandText = "Update Players set Elo = @Elo where (Name = @Name and Elo < @Elo)";
command.Parameters.Add(new MySqlParameter("@Name", game.getWhite()));
command.Parameters.Add(new MySqlParameter("@Elo", game.getWhiteElo()));
command.ExecuteNonQuery();
/*Update Black Elos if we find a better elo*/
MySqlCommand command1 = conn.CreateCommand();
command1.CommandText = "Update Players set Elo = @Elo where (Name = @Name and Elo < @Elo)";
command1.Parameters.Add(new MySqlParameter("@Name", game.getBlack()));
command1.Parameters.Add(new MySqlParameter("@Elo", game.getBlackElo()));
command1.ExecuteNonQuery();
/*Events Query*/
MySqlCommand command2 = conn.CreateCommand();
command2.CommandText = "Insert ignore into Events(Name,Site,Date) values(@Name,@Site,@Date)";
command2.Parameters.Add(new MySqlParameter("@Name", game.getEventName()));
command2.Parameters.Add(new MySqlParameter("@Site", game.getSite()));
command2.Parameters.Add(new MySqlParameter("@Date", game.getEventDate()));
command2.ExecuteNonQuery();
/*Games Query*/
MySqlCommand command3 = conn.CreateCommand();
command3.CommandText = "Insert into Games(Result,Moves,BlackPlayer,WhitePlayer,eID) values(@Result,@Moves," +
"(select pID from Players where Name = @BlackPlayer)," + //Gets pID for BlackPlayer based on name
"(select pID from Players where Name = @WhitePlayer)," + //Gets pID for WhitePlayer based on name
"(select eID from Events where Name = @EventName and Date = @EventDate)" + //Gets eID from Events based on event name
")";
command3.Parameters.Add(new MySqlParameter("@Result", game.getResult()));
command3.Parameters.Add(new MySqlParameter("@Moves", game.getMoves()));
command3.Parameters.Add(new MySqlParameter("@BlackPlayer", game.getBlack()));
command3.Parameters.Add(new MySqlParameter("@WhitePlayer", game.getWhite()));
command3.Parameters.Add(new MySqlParameter("@EventName", game.getEventName()));
command3.Parameters.Add(new MySqlParameter("@EventDate", game.getEventDate()));
command3.ExecuteNonQuery();
}
// Use this to tell the GUI that one work step has completed:
// WorkStepCompleted();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
