/// <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); } } }