/// <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();
// Load and parse the PGN file
PGNReader pgn = new PGNReader(PGNfilename);
List <ChessGame> games = pgn.extractGameData();
// 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);
Console.WriteLine(games.Count + " total games...");
using (MySqlConnection conn = new MySqlConnection(connection)) {
try {
// Open a connection
conn.Open();
int i = 1;
// TODO: iterate through your data and generate appropriate insert commands
foreach (ChessGame g in games)
{
insertIntoEvents(conn, g);
insertIntoPlayers(conn, g);
insertIntoGames(conn, g);
// Use this to tell the GUI that one work step has completed:
WorkStepCompleted();
Console.WriteLine(i + " game finished");
i++;
}
}
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();
var chessGames = PGNReader.read(PGNfilename);
SetNumWorkItems(chessGames.Count);
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
// Open a connection
conn.Open();
// Iterate through games and add to DB or modify if necessary
foreach (ChessGame game in chessGames)
{
uint whitePID = QueryForPlayerAndUpdate(game.White, game.WhiteElo, conn);
uint blackPID = QueryForPlayerAndUpdate(game.Black, game.BlackElo, conn);
uint eventID = AddEvent(game.Event, game.Site, game.EventDate, conn);
AddGame(game.Round, game.Result, game.Moves, whitePID, blackPID, eventID, conn);
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();
// Load and parse the PGN file
// We recommend creating separate libraries to represent chess data and load the file
List <ChessGame> games = PGNReader.FileReader(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(...);
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
// Open a connection
conn.Open();
// iterate through your data and generate appropriate insert commands
foreach (ChessGame game in games)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
// Events
cmd.CommandText = "INSERT IGNORE INTO Events(Name,Site,Date) VALUES (@eventname, @site, @date); ";
cmd.Parameters.AddWithValue("@eventname", game.Event);
cmd.Parameters.AddWithValue("@site", game.Site);
cmd.Parameters.AddWithValue("@date", game.EventDate);
// White player - Players tbl
cmd.CommandText += "INSERT IGNORE INTO Players(Name, Elo) VALUES (@whiteName, @whiteElo) ON DUPLICATE KEY UPDATE Elo = if(values(Elo) > Elo, values(Elo), Elo); ";
cmd.Parameters.AddWithValue("@whiteName", game.WhitePlayer.Name);
cmd.Parameters.AddWithValue("@whiteElo", game.WhitePlayer.Elo);
// Black Player - Players tbl
cmd.CommandText += "INSERT IGNORE INTO Players(Name, Elo) VALUES (@blackName, @blackElo) ON DUPLICATE KEY UPDATE Elo = if(values(Elo) > Elo, values(Elo), Elo); ";
cmd.Parameters.AddWithValue("@blackName", game.BlackPlayer.Name);
cmd.Parameters.AddWithValue("@blackElo", game.BlackPlayer.Elo);
// Games
cmd.CommandText += "INSERT IGNORE INTO Games(Round, Result, Moves, BlackPlayer, WhitePlayer, eID) VALUES (@round, @result, @moves, (select pID from Players where Players.Name = @BlackName2), " +
"(select pID from Players where Players.Name = @WhiteName2), (select eID from Events where Events.Name = @eventname and Events.Site = @site and Events.Date = @date)); ";
cmd.Parameters.AddWithValue("@round", game.Round);
cmd.Parameters.AddWithValue("@result", game.Result);
cmd.Parameters.AddWithValue("@moves", game.Moves);
cmd.Parameters.AddWithValue("@blackName2", game.BlackPlayer.Name);
cmd.Parameters.AddWithValue("@whiteName2", game.WhitePlayer.Name);
// Execute
cmd.ExecuteNonQuery();
// Use this to tell the GUI that one work step has completed:
WorkStepCompleted();
}
// Close connection
conn.Close();
}
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: Use your ChessTools PGNReader to get a list of ChessGames
List <ChessGame> games = PGNReader.ReadFromFile(PGNfilename);
Console.WriteLine(games.Count);
// Use this to tell the GUI's progress bar how many total work steps there are:
// SetNumWorkItems(...);
SetNumWorkItems(games.Count);
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
// Open a connection
conn.Open();
// TODO: iterate through your list of ChessGames and upload
// each to your database.
MySqlCommand command = conn.CreateCommand();
foreach (ChessGame g in games)
{
command = conn.CreateCommand();
command.CommandText = "insert ignore into ChessEvents values(@val1, @val2, @val3)";
command.Parameters.AddWithValue("val1", g.GetEventName());
command.Parameters.AddWithValue("val2", g.GetSite());
command.Parameters.AddWithValue("val3", g.GetDate());
Console.WriteLine(command.CommandText);
command.ExecuteNonQuery();
command = conn.CreateCommand();
command.CommandText = "insert ignore into ChessPlayers values(@val)";
command.Parameters.AddWithValue("val", g.GetBlackPlayer());
Console.WriteLine(command.CommandText);
command.ExecuteNonQuery();
command.Parameters["val"].Value = g.GetWhitePlayer();
Console.WriteLine(command.CommandText);
command.ExecuteNonQuery();
command = conn.CreateCommand();
command.CommandText = "insert ignore into ChessGames(Result, Moves, " +
"EventName, WhitePlayer, BlackPlayer) values(@val1, @val2, @val3, @val4, @val5)";
command.Parameters.AddWithValue("val1", g.GetResult());
command.Parameters.AddWithValue("val2", g.GetMoves());
command.Parameters.AddWithValue("val3", g.GetEventName());
command.Parameters.AddWithValue("val4", g.GetWhitePlayer());
command.Parameters.AddWithValue("val5", g.GetBlackPlayer());
Console.WriteLine(command.CommandText);
command.ExecuteNonQuery();
}
// Use this to tell the GUI that one work step has completed:
// WorkStepCompleted();
WorkStepCompleted();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.Read();
}
}
}
/// <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();
Dictionary <string, ChessEvent> .ValueCollection events = PGNReader.ParseEventsFromPGN(PGNfilename);
SetNumWorkItems(events.Count + 2);
// Step 1: Parse (done already)
WorkStepCompleted();
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
// Open a connection
conn.Open();
// Step 2: Create procedure
var cmd = conn.CreateCommand();
cmd.CommandText = @"
DROP PROCEDURE IF EXISTS UPDATE_OR_INSERT_PLAYER;
CREATE PROCEDURE UPDATE_OR_INSERT_PLAYER(p_Name varchar(255), p_Elo int(10) unsigned)
BEGIN
IF EXISTS (SELECT * FROM Players WHERE Name=p_Name) THEN
UPDATE Players
SET Elo = (CASE WHEN Elo < p_Elo THEN p_Elo ELSE Elo END)
WHERE Name=p_Name;
ELSE
INSERT INTO Players(Name, Elo)
VALUES (p_Name, p_Elo);
END IF;
END";
cmd.ExecuteNonQuery();
WorkStepCompleted();
// Step 3..N: Insert Events.
foreach (var chessEvent in events)
{
var transaction = conn.BeginTransaction();
// Insert event.
cmd = conn.CreateCommand();
cmd.Transaction = transaction;
cmd.CommandText = "INSERT INTO Events(Name, Site, Date) VALUES (@Name, @Site, @Date)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Name", chessEvent.Name);
cmd.Parameters.AddWithValue("@Site", chessEvent.Site);
cmd.Parameters.AddWithValue("@Date", chessEvent.Date);
cmd.ExecuteNonQuery();
// Insert/Update players.
foreach (var player in chessEvent.getPlayers())
{
cmd = conn.CreateCommand();
cmd.Transaction = transaction;
cmd.CommandText = "CALL UPDATE_OR_INSERT_PLAYER(@Name, @Elo)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Name", player.Name);
cmd.Parameters.AddWithValue("@Elo", player.ELO);
cmd.ExecuteNonQuery();
}
// Insert games.
foreach (var game in chessEvent.Games)
{
cmd = conn.CreateCommand();
cmd.Transaction = transaction;
cmd.CommandText = "INSERT INTO Games(Result, Moves, BlackPlayer, WhitePlayer, eID) " +
"VALUES (" +
"@Result, " +
"@Moves, " +
"(SELECT pID FROM Players WHERE Name = @BlackName), " +
"(SELECT pID FROM Players WHERE Name = @WhiteName), " +
"(SELECT eID FROM Events WHERE Name = @EventName AND Date = @EventDate)" +
")";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Result", game.Result == ChessGame.GameResult.Draw ? "D" : (game.Result == ChessGame.GameResult.White ? "W" : "B"));
cmd.Parameters.AddWithValue("@Moves", game.Moves);
cmd.Parameters.AddWithValue("@BlackName", game.BlackPlayer.Name);
cmd.Parameters.AddWithValue("@WhiteName", game.WhitePlayer.Name);
cmd.Parameters.AddWithValue("@EventName", game.Event.Name);
cmd.Parameters.AddWithValue("@EventDate", game.Event.Date);
cmd.ExecuteNonQuery();
}
transaction.Commit();
WorkStepCompleted();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
//private PGNReader reader = new PGNReader();
/// <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
PGNReader read = new PGNReader();
string connection = GetConnectionString();
List <ChessGame> games = read.ReadPGN(PGNfilename);
// 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(games.Count);
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
conn.Open();
// Open a connection
foreach (ChessGame g in games)
{
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandText = "select 1 from Events where Name ='" + g.GetEventName() + "' and Site='" +
g.GetSite() + "';";
MySqlDataReader count = command.ExecuteReader();
if (count.HasRows == false)
{
count.Close();
command.CommandText = "insert ignore into Events(Name,Site,Date)" +
"values(@name,@site,@date) ;";
command.Parameters.AddWithValue("@name", g.GetEventName());
command.Parameters.AddWithValue("@site", g.GetSite());
command.Parameters.AddWithValue("@date", g.GetDate());
command.Prepare();
command.ExecuteNonQuery();
//eid is auto-increment
}
else
{
count.Close();
}
}
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
using (MySqlConnection conn2 = new MySqlConnection(connection))
{
try
{
// Open a connection
conn2.Open();
foreach (ChessGame g in games)
{
//conn2.Open();
MySqlCommand command2 = new MySqlCommand();
command2.Connection = conn2;
command2.CommandText = "select 1 from Players where Name ='" + g.GetWhiteName() + "'";
//command2.Parameters.AddWithValue("@Name", g.GetWhiteName());
// command2.Parameters.AddWithValue("@Elo", g.GetWhiteElo());
// command2.Prepare();
MySqlDataReader count = command2.ExecuteReader();
// MySqlDataReader count = command2.ExecuteReader();
//int count = Convert.ToInt32(command2.ExecuteScalar());
if (count.HasRows == false)
{
//conn2.Close();
count.Close();
command2.CommandText = "insert ignore into Players(Name, Elo) values(@Name1,@Elo1)";
command2.Parameters.AddWithValue("@Name1", g.GetWhiteName());
command2.Parameters.AddWithValue("@Elo1", g.GetWhiteElo());
command2.Prepare();
command2.ExecuteNonQuery();
}
else
{
command2.CommandText = "select Elo from Players where Name = '" + g.GetWhiteName() + "'; ";
MySqlDataReader dr = command2.ExecuteReader();
dr.Read();
int elo = dr.GetInt32(0);
System.Diagnostics.Debug.WriteLine("elo" + elo);
if (elo < g.GetWhiteElo())
{
command2.CommandText = "update Players set Elo= " + g.GetWhiteElo() + "where Name='" + g.GetWhiteName() + "';";
command2.ExecuteNonQuery();
}
count.Close();
}
}
conn2.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
using (MySqlConnection conn1 = new MySqlConnection(connection))
{
try
{
// Open a connection
conn1.Open();
foreach (ChessGame g in games)
{
//conn2.Open();
MySqlCommand command1 = new MySqlCommand();
command1.Connection = conn1;
// command2.CommandText = "insert into Players([Name],[Elo])" +
// "values(@Name,@Elo) where @Name not in (select Name from Players where Name =@Name);";
command1.CommandText = "select 1 from Players where Name =@Name";
command1.Parameters.AddWithValue("@Name", g.GetBlackName());
// command2.Parameters.AddWithValue("@Elo", g.GetWhiteElo());
// command2.Prepare();
MySqlDataReader count = command1.ExecuteReader();
// MySqlDataReader count = command2.ExecuteReader();
//int count = Convert.ToInt32(command2.ExecuteScalar());
if (count.HasRows == false)
{
//conn2.Close();
count.Close();
command1.CommandText = "insert ignore into Players(Name, Elo) values(@Name1,@Elo1)";
command1.Parameters.AddWithValue("@Name1", g.GetBlackName());
command1.Parameters.AddWithValue("@Elo1", g.GetBlackElo());
command1.ExecuteNonQuery();
}
else
{
command1.CommandText = "select Elo from Players where Name = '" + g.GetBlackName() + "'; ";
MySqlDataReader dr = command1.ExecuteReader();
dr.Read();
int elo = dr.GetInt32(0);
if (elo < g.GetBlackElo())
{
command1.CommandText = "update Players set Elo= " + g.GetBlackElo() + "where Name='" + g.GetBlackName() + "';";
command1.ExecuteNonQuery();
}
count.Close();
}
}
conn1.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
using (MySqlConnection conn3 = new MySqlConnection(connection))
{
try
{
// Open a connection
conn3.Open();
foreach (ChessGame g in games)
{
//conn2.Open();
MySqlCommand command3 = new MySqlCommand();
command3.Connection = conn3;
command3.CommandText = "select pID from Players where Name=@Name ";
command3.Parameters.AddWithValue("@Name", g.GetBlackName());
MySqlDataReader temp = command3.ExecuteReader();
bool hasContent = temp.Read();
String pid1 = "";
if (hasContent == true)
{
pid1 = temp.GetString(0);
}
temp.Close();
command3.CommandText = "select pID from Players where Name=@Name1";
command3.Parameters.AddWithValue("@Name1", g.GetWhiteName());
temp = command3.ExecuteReader();
hasContent = temp.Read();
String pid2 = "";
if (hasContent == true)
{
pid2 = temp.GetString(0);
}
temp.Close();
command3.CommandText = "select eId from Events where Name=@Name2 and Site=@Site";
command3.Parameters.AddWithValue("@Name2", g.GetEventName());
command3.Parameters.AddWithValue("@Site", g.GetSite());
temp = command3.ExecuteReader();
hasContent = temp.Read();
String eID = "";
if (hasContent == true)
{
eID = temp.GetString(0);
}
temp.Close();
//command2.CommandText = "select * from Games where BlackPlayer=@Black AND WhitePlayer=@White AND eID =@eID;";
//command2.Parameters.AddWithValue("@Black", pid1);
//command2.Parameters.AddWithValue("@White", pid2);
//command2.Parameters.AddWithValue("@eID", eID);
//// command2.Prepare();
//temp = command2.ExecuteReader();
//// MySqlDataReader count = command2.ExecuteReader();
////int count = Convert.ToInt32(command2.ExecuteScalar());
//if (temp.HasRows == false)
//{
//conn2.Close();
command3.CommandText = "insert ignore into Games(Result, Moves, BlackPlayer, WhitePlayer, eID) values(@Result, @Moves, @Player1, @Player2, @EEID)";
command3.Parameters.AddWithValue("@Result", g.GetResult());
command3.Parameters.AddWithValue("@Moves", g.Getmoves());
command3.Parameters.AddWithValue("@Player1", pid1);
command3.Parameters.AddWithValue("@Player2", pid2);
command3.Parameters.AddWithValue("@EEID", eID);
command3.ExecuteNonQuery();
WorkStepCompleted();
}
conn3.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
// TODO: iterate through your data and generate appropriate insert commands
// Use this to tell the GUI that one work step has completed:
}
/// <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,
// assuming you've typed a user and password in the GUI
string connection = GetConnectionString();
// Load and parse the PGN file
var reader = new PGNReader();
reader.ReadPGN(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(...);
SetNumWorkItems(reader.GetNumWorkItems());
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
// Open a connection
conn.Open();
// prepare commands to update Players table
var selectCmd = new MySqlCommand("select count(*) from Players where Name=@Name and Elo<@Elo;", conn);
selectCmd.Parameters.AddWithValue("@Name", "");
selectCmd.Parameters.AddWithValue("@Elo", 0);
var insertCmd = new MySqlCommand("insert ignore into Players(Name, Elo) values (@Name, @Elo);", conn);
insertCmd.Parameters.AddWithValue("@Name", "");
insertCmd.Parameters.AddWithValue("@Elo", 0);
var updateCmd = new MySqlCommand("update Players set Elo=@Elo where Name=@Name", conn);
updateCmd.Parameters.AddWithValue("@Name", "");
updateCmd.Parameters.AddWithValue("@Elo", 0);
// iterate over players and insert them
foreach (var player in reader.GetPlayers())
{
selectCmd.Parameters["@Name"].Value = player.Name;
selectCmd.Parameters["@Elo"].Value = player.Elo;
var o = selectCmd.ExecuteScalar();
int count = int.Parse(o.ToString());
if (count > 0)
{
updateCmd.Parameters["@Name"].Value = player.Name;
updateCmd.Parameters["@Elo"].Value = player.Elo;
updateCmd.ExecuteNonQuery();
}
else
{
insertCmd.Parameters["@Name"].Value = player.Name;
insertCmd.Parameters["@Elo"].Value = player.Elo;
insertCmd.ExecuteNonQuery();
}
WorkStepCompleted();
}
// prepare commands to update Events table
insertCmd = new MySqlCommand("insert ignore into Events(Name, Site, Date) values (@Name, @Site, @Date);", conn);
insertCmd.Parameters.AddWithValue("@Name", "");
insertCmd.Parameters.AddWithValue("@Site", "");
insertCmd.Parameters.AddWithValue("@Date", "");
// iterate over events and insert them
foreach (var e in reader.GetEvents())
{
insertCmd.Parameters["@Name"].Value = e.Name;
insertCmd.Parameters["@Site"].Value = e.Site;
insertCmd.Parameters["@Date"].Value = e.Date;
insertCmd.ExecuteNonQuery();
WorkStepCompleted();
}
// prepare commands to update Games table
insertCmd = new MySqlCommand("insert ignore into Games values (@Round, @Result, @Moves, (select pID from Players where Name=@BlackPlayer), "
+ "(select pID from Players where Name=@WhitePlayer), (select eID from Events where Name=@Name and Site=@Site and Date=@Date));", conn);
insertCmd.Parameters.AddWithValue("@Round", "");
insertCmd.Parameters.AddWithValue("@Result", "");
insertCmd.Parameters.AddWithValue("@Moves", "");
insertCmd.Parameters.AddWithValue("@BlackPlayer", "");
insertCmd.Parameters.AddWithValue("@WhitePlayer", "");
insertCmd.Parameters.AddWithValue("@Name", "");
insertCmd.Parameters.AddWithValue("@Site", "");
insertCmd.Parameters.AddWithValue("@Date", "");
// iterate over games and insert them
foreach (var game in reader.GetGames())
{
insertCmd.Parameters["@Round"].Value = game.Round;
insertCmd.Parameters["@Result"].Value = game.Result;
insertCmd.Parameters["@Moves"].Value = game.Moves;
insertCmd.Parameters["@BlackPlayer"].Value = game.BlackPlayer;
insertCmd.Parameters["@WhitePlayer"].Value = game.WhitePlayer;
insertCmd.Parameters["@Name"].Value = game.Name;
insertCmd.Parameters["@Site"].Value = game.Site;
insertCmd.Parameters["@Date"].Value = game.Date;
insertCmd.ExecuteNonQuery();
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
PGNReader pgn = new PGNReader();
List <ChessGame> games = pgn.readPGNFile(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();
using (MySqlTransaction trans = conn.BeginTransaction())
{
// TODO: iterate through your data and generate appropriate insert commands
MySqlCommand newPlayerCommand = new MySqlCommand("INSERT INTO Players(Name, Elo) " +
"VALUES(@PlayerName, @Elo)", conn);
MySqlCommand searchPlayerCommand = new MySqlCommand("select pID, Elo from Players where Name = @searchName", conn);
MySqlCommand updatePlayer = new MySqlCommand("update Players set Elo = @newElo where Name = @updateName", conn);
MySqlCommand eventCommand = new MySqlCommand("INSERT IGNORE INTO Events(Name, Site, Date)" +
"VALUES(@EventName, @Site, @EventDate)", conn);
MySqlCommand gameCommand = new MySqlCommand("INSERT IGNORE INTO Games(Result, Moves, BlackPlayer, WhitePlayer, eID) " +
"VALUES(@Result, @Moves, (select pID from Players where Name = @Black)," +
" (select pID from Players where Name = @White), (select eID from Events where Name = @gameEvent))", conn);
foreach (ChessGame g in games)
{
searchPlayerCommand.Parameters.AddWithValue("@searchName", g.White);
using (MySqlDataReader r = searchPlayerCommand.ExecuteReader())
{
UInt32 elo = 0;
while (r.Read())
{
elo = (UInt32)r["Elo"];
}
if (!r.HasRows)
{
newPlayerCommand.Parameters.AddWithValue("@PlayerName", g.White);
newPlayerCommand.Parameters.AddWithValue("@Elo", g.WhiteElo);
r.Close();
newPlayerCommand.ExecuteNonQuery();
}
else if (elo < g.WhiteElo)
{
updatePlayer.Parameters.AddWithValue("@newElo", g.WhiteElo);
updatePlayer.Parameters.AddWithValue("@updateName", g.White);
r.Close();
updatePlayer.ExecuteNonQuery();
updatePlayer.Parameters.Clear();
}
}
searchPlayerCommand.Parameters.Clear();
newPlayerCommand.Parameters.Clear();
searchPlayerCommand.Parameters.AddWithValue("@searchName", g.Black);
using (MySqlDataReader r = searchPlayerCommand.ExecuteReader())
{
UInt32 elo = 0;
while (r.Read())
{
elo = (UInt32)r["Elo"];
}
if (!r.HasRows)
{
newPlayerCommand.Parameters.AddWithValue("@PlayerName", g.Black);
newPlayerCommand.Parameters.AddWithValue("@Elo", g.BlackElo);
r.Close();
newPlayerCommand.ExecuteNonQuery();
}
else if (elo < g.BlackElo)
{
updatePlayer.Parameters.AddWithValue("@newElo", g.BlackElo);
updatePlayer.Parameters.AddWithValue("@updateName", g.Black);
r.Close();
updatePlayer.ExecuteNonQuery();
updatePlayer.Parameters.Clear();
}
}
eventCommand.Parameters.AddWithValue("@EventName", g.Event);
eventCommand.Parameters.AddWithValue("@Site", g.Site);
eventCommand.Parameters.AddWithValue("@EventDate", g.EventDate);
eventCommand.ExecuteNonQuery();
gameCommand.Parameters.AddWithValue("@Result", g.Result);
gameCommand.Parameters.AddWithValue("@Moves", g.Moves);
gameCommand.Parameters.AddWithValue("@White", g.White);
gameCommand.Parameters.AddWithValue("@Black", g.Black);
gameCommand.Parameters.AddWithValue("@gameEvent", g.Event);
gameCommand.ExecuteNonQuery();
// Use this to tell the GUI that one work step has completed:
WorkStepCompleted();
eventCommand.Parameters.Clear();
gameCommand.Parameters.Clear();
searchPlayerCommand.Parameters.Clear();
newPlayerCommand.Parameters.Clear();
}
trans.Commit();
}
}
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();
// We recommend creating separate libraries to represent chess data and load the file
PGNReader reader = new PGNReader();
List <ChessGame> games = reader.parseFile(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
int uploaded = 0;
foreach (ChessGame game in games)
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert ignore into Events(Name,Site,Date) " +
"values (@eventname, @site, @date); ";
cmd.Parameters.AddWithValue("@eventname", game.Event);
cmd.Parameters.AddWithValue("@site", game.Site);
cmd.Parameters.AddWithValue("@date", game.EventDate);
cmd.CommandText += "insert ignore into Players(Name, Elo) " +
"values (@wname, @welo) on duplicate key update Elo = if(values(Elo) > Elo, values(Elo), Elo); ";
cmd.Parameters.AddWithValue("@wname", game.White);
cmd.Parameters.AddWithValue("@welo", game.WhiteElo);
cmd.CommandText += "insert ignore into Players(Name, Elo) " +
"values (@bname, @belo) on duplicate key update Elo = if(values(Elo) > Elo, values(Elo), Elo); ";
cmd.Parameters.AddWithValue("@bname", game.Black);
cmd.Parameters.AddWithValue("@belo", game.BlackElo);
cmd.CommandText += "insert ignore into Games(Round, Result, Moves, BlackPlayer, WhitePlayer, eID) " +
"values (@round, @result, @moves, (select pID from Players where Players.Name = @blackplayer), " +
"(select pID from Players where Players.Name = @whiteplayer), (select eID from Events where Events.Name = @eventname and " +
" Events.Site = @site and Events.Date = @date)); ";
cmd.Parameters.AddWithValue("@round", game.Round);
cmd.Parameters.AddWithValue("@result", game.Result);
cmd.Parameters.AddWithValue("@moves", game.Moves);
cmd.Parameters.AddWithValue("@blackplayer", game.Black);
cmd.Parameters.AddWithValue("@whiteplayer", game.White);
cmd.ExecuteNonQuery();
uploaded++;
WorkStepCompleted();
}
// Use this to tell the GUI that one work step has completed:
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
