Exemplo n.º 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();

            // 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);
                }
            }
        }
Exemplo n.º 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();

            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);
                }
            }
        }
Exemplo n.º 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();

            // 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);
                }
            }
        }
Exemplo n.º 4
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: 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);
                }
            }
        }
Exemplo n.º 9
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();

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