/// <summary>
/// updates the game table of the database
/// </summary>
/// <param name="game"></param>
private DateTime addGame(BoggleGame game)
{
int p1ID;
int p2ID;
String board = game.Board();
int timeLimit = gameLength;
int p1Score = game.Player1Score;
int p2Score = game.Player2Score;
DateTime time = DateTime.Now;
// Create connection object
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
// Open a connection
conn.Open();
// Create a command
MySqlCommand command = conn.CreateCommand();
command.CommandText =
@"SELECT idPlayers FROM matelau.Players where Players.Name = @PlayerName or Players.Name = @PlayerName2";
// Prepare the command
command.Prepare();
command.Parameters.AddWithValue("@PlayerName", game.Player1Name());
command.Parameters.AddWithValue("@PlayerName2", game.Player2Name());
// Execute the command
command.ExecuteNonQuery();
// Execute the command and cycle through the DataReader object
using (MySqlDataReader reader = command.ExecuteReader())
{
reader.Read();
p1ID = (int) reader["idPlayers"];
reader.Read();
p2ID = (int)reader["idPlayers"];
}
// Create a command
command = conn.CreateCommand();
command.CommandText =
@"insert into Games (Player1ID, Player2Id, Time, Board, TimeLimit, Player1Score, Player2Score) values (@Player1ID, @Player2Id, @Time, @Board, @TimeLimit, @Player1Score, @Player2Score)";
// Prepare the command
command.Prepare();
command.Parameters.AddWithValue("@Player1ID", p1ID);
command.Parameters.AddWithValue("@Player2ID", p2ID);
command.Parameters.AddWithValue("@Time", time);
command.Parameters.AddWithValue("@Board", board);
command.Parameters.AddWithValue("@TimeLimit", timeLimit);
command.Parameters.AddWithValue("@Player1Score", p1Score);
command.Parameters.AddWithValue("@Player2Score", p2Score);
// Execute the command
command.ExecuteNonQuery();
conn.Close();
}
return time;
}
/// <summary>
/// Adds this games words to the database
/// </summary>
/// <param name="game"></param>
private void addWords(BoggleGame game, DateTime time)
{
int p1ID;
int p2ID;
// Create connection object
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
// Open a connection
conn.Open();
//get the gameId
int gameId;
// Create a command
MySqlCommand command = conn.CreateCommand();
command.CommandText =
@"SELECT idGames FROM matelau.Games where Games.Time = @time and Games.Board = @Board";
// Prepare the command
command.Prepare();
command.Parameters.AddWithValue("@time", time);
command.Parameters.AddWithValue("@Board", game.Board());
// Execute the command
command.ExecuteNonQuery();
// Execute the command and cycle through the DataReader object
using (MySqlDataReader reader = command.ExecuteReader())
{
reader.Read();
gameId = (int)reader["idGames"];
}
// get the players Ids
// Create a command
command = conn.CreateCommand();
command.CommandText =
@"SELECT idPlayers FROM matelau.Players where Players.Name = @PlayerName or Players.Name = @PlayerName2";
// Prepare the command
command.Prepare();
command.Parameters.AddWithValue("@PlayerName", game.Player1Name());
command.Parameters.AddWithValue("@PlayerName2", game.Player2Name());
// Execute the command
command.ExecuteNonQuery();
// Execute the command and cycle through the DataReader object
using (MySqlDataReader reader = command.ExecuteReader())
{
reader.Read();
p1ID = (int)reader["idPlayers"];
reader.Read();
p2ID = (int)reader["idPlayers"];
}
//add words p1
HashSet<String> p1Legal = game.P1Legal;
foreach (String word in p1Legal)
{
// Create a command
command = conn.CreateCommand();
command.CommandText =
@"insert into WordsPlayed (GameId, Word, Legal,PlayerID)
values(@GameId, @Word, @Legal, @PlayerID)";
// Prepare the command
command.Prepare();
command.Parameters.AddWithValue("@GameId", gameId);
command.Parameters.AddWithValue("@Word", word);
command.Parameters.AddWithValue("@Legal", 1);
command.Parameters.AddWithValue("@PlayerID", p1ID );
// Execute the command
command.ExecuteNonQuery();
}
HashSet<String> p1Illegal = game.P1Illegal;
foreach (String word in p1Illegal)
{
// Create a command
command = conn.CreateCommand();
command.CommandText =
@"insert into WordsPlayed (GameId, Word, Legal,PlayerID)
values(@GameId, @Word, @Legal, @PlayerID)";
// Prepare the command
command.Prepare();
command.Parameters.AddWithValue("@GameId", gameId);
command.Parameters.AddWithValue("@Word", word);
//illegal
command.Parameters.AddWithValue("@Legal", 0);
command.Parameters.AddWithValue("@PlayerID", p1ID);
// Execute the command
command.ExecuteNonQuery();
}
HashSet<String> p2Legal = game.P2Legal;
foreach (String word in p2Legal)
{
// Create a command
command = conn.CreateCommand();
command.CommandText =
@"insert into WordsPlayed (GameId, Word, Legal,PlayerID)
values(@GameId, @Word, @Legal, @PlayerID)";
// Prepare the command
command.Prepare();
command.Parameters.AddWithValue("@GameId", gameId);
command.Parameters.AddWithValue("@Word", word);
//illegal
command.Parameters.AddWithValue("@Legal", 1);
command.Parameters.AddWithValue("@PlayerID", p2ID);
// Execute the command
command.ExecuteNonQuery();
}
HashSet<String> p2Illegal = game.P2Illegal;
foreach (String word in p2Illegal)
{
// Create a command
command = conn.CreateCommand();
command.CommandText =
@"insert into WordsPlayed (GameId, Word, Legal,PlayerID)
values(@GameId, @Word, @Legal, @PlayerID)";
// Prepare the command
command.Prepare();
command.Parameters.AddWithValue("@GameId", gameId);
command.Parameters.AddWithValue("@Word", word);
//illegal
command.Parameters.AddWithValue("@Legal", 0);
command.Parameters.AddWithValue("@PlayerID", p2ID);
// Execute the command
command.ExecuteNonQuery();
}
// add common words
HashSet<String> common = game.Common;
foreach (String word in common)
{
// Create a command
command = conn.CreateCommand();
command.CommandText =
@"insert into WordsPlayed (GameId, Word, Legal,PlayerID, Common)
values(@GameId, @Word, @Legal, @PlayerID, @Common)";
// Prepare the command
command.Prepare();
command.Parameters.AddWithValue("@GameId", gameId);
command.Parameters.AddWithValue("@Word", word);
//legal and common
command.Parameters.AddWithValue("@Legal", 1);
command.Parameters.AddWithValue("@Common", 1);
command.Parameters.AddWithValue("@PlayerID", p1ID);
// Execute the command
command.ExecuteNonQuery();
}
conn.Close();
}
}
/// <summary>
/// Updates the database at the conclusion of a game
/// </summary>
/// <param name="game"></param>
private void updateDatabase(BoggleGame game)
{
//add players
addPlayer(game.Player2Name());
addPlayer(game.Player1Name());
//add game
DateTime time = addGame(game);
//add words played
addWords(game, time);
}