Пример #1
0
        /// <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);
                }
            }
        }
Пример #2
0
        /// <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);
                }
            }
        }
Пример #3
0
        /// <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);
                }
            }
        }