public int?Delete(int id)
{
//initialize returnValue
int? returnValue = null;
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
try
{
//create connection
connection = Manager.CreateDBConnection();
//delete account
command = connection.CreateCommand();
command.CommandText = $"DELETE FROM tictactoedb2.account WHERE `accountId`={id};";
reader = command.ExecuteReader();
reader.Read();
//set returnValue to "success"
returnValue = reader.RecordsAffected;
}
catch (Exception e)
{
throw e;
}
finally
{
Manager.CleanUpDatabaseConnection(connection);
Manager.CleanUpDatabaseReader(reader);
Manager.CleanUpDatabaseCommand(command);
}
return(returnValue);
}
public int?Delete(Guid id)
{
//initialize variables
MySqlConnection connection = null;
MySqlCommand command = null;
//----initalize return (rowsAffected)
int?rowsAffected = null;
try
{
//create connnection, command
connection = Manager.CreateDBConnection();
command = connection.CreateCommand();
//delete game
command.CommandText = $"DELETE FROM tictactoedb2.game WHERE `gameId`='{id}';";
rowsAffected = command.ExecuteNonQuery();
}
catch (Exception e)
{
//if exception, throw exception
throw e;
} finally
{
//clean up
Manager.CleanUpDatabaseConnection(connection);
Manager.CleanUpDatabaseCommand(command);
}
//return rowsAffected
return(rowsAffected);
}
public Account FindByUser(string username)
{
//initialize returnValue
Account returnValue = null;
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
try
{
//create connection
connection = Manager.CreateDBConnection();
//check for existing account with username
command = connection.CreateCommand();
command.CommandText = $"SELECT * FROM tictactoedb2.account WHERE `username`='{username}';";
reader = command.ExecuteReader();
reader.Read();
if (reader.HasRows == true)
{
Account foundAccount = new Account()
{
//accountId, displayname, online, password, username, win, loss, tie
Id = Convert.ToInt32(reader["accountId"]),
Displayname = Convert.ToString(reader["displayname"]),
Online = Convert.ToBoolean(reader["online"]),
Password = Convert.ToString(reader["password"]),
Username = Convert.ToString(reader["username"]),
Win = Convert.ToInt32(reader["win"]),
Loss = Convert.ToInt32(reader["loss"]),
Tie = Convert.ToInt32(reader["tie"])
};
//set returnValue to created account
returnValue = foundAccount;
}
}
catch (Exception e)
{
throw e;
}
finally
{
Manager.CleanUpDatabaseConnection(connection);
Manager.CleanUpDatabaseReader(reader);
Manager.CleanUpDatabaseCommand(command);
}
return(returnValue);
}
public Game Update(Game model)
{
//initialize variables
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
//----initalize return (gameToReturn)
Game gameToReturn = new Game();
try
{
//create connnection, command
connection = Manager.CreateDBConnection();
command = connection.CreateCommand();
//update game's GameState using model
command.CommandText = $"UPDATE tictactoedb2.game SET `data`='{model.GameState}' WHERE `gameId`='{model.GameId}';";
command.ExecuteNonQuery();
//find game
command.CommandText = $"SELECT * FROM tictactoedb2.game WHERE `gameId`='{model.GameId}';";
reader = command.ExecuteReader();
reader.Read();
//set gameToReturn values to equal the updated game
//----change gameToReturn's GameState to match the updated game's GameState
Enum.TryParse(Convert.ToString(reader["data"]), out GameState gameState);
gameToReturn.GameState = gameState;
//----change gameToReturn's gameId to match the updated game's guid
gameToReturn.GameId = Guid.Parse(Convert.ToString(reader["gameId"]));
}
catch (Exception e)
{
//if exception, throw exception
throw e;
}
finally
{
//clean up
Manager.CleanUpDatabaseConnection(connection);
Manager.CleanUpDatabaseReader(reader);
Manager.CleanUpDatabaseCommand(command);
}
//return gameToReturn (GameState is the only property that could have updated been updated)
return(gameToReturn);
}
public Game Create(Game model)
{
//initialize variables
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
//----initalize return (gameToReturn)
Game gameToReturn = new Game();
try
{
//create connnection, command
connection = Manager.CreateDBConnection();
command = connection.CreateCommand();
//save game (GameState)
command.CommandText = $"INSERT INTO tictactoedb2.game (`gameId`, `data`) VALUES ('{model.GameId}', '{model.GameState}');";
command.ExecuteNonQuery();
//find the new saved game
command.CommandText = $"SELECT * FROM tictactoedb2.game WHERE `gameId`='{model.GameId}'";
reader = command.ExecuteReader();
reader.Read();
//change gameToReturn's GameState to match the saved game's GameState
Enum.TryParse(Convert.ToString(reader["data"]), out GameState gameState);
gameToReturn.GameState = gameState;
//change gameToReturn's gameId to match the saved game's guid
gameToReturn.GameId = Guid.Parse(Convert.ToString(reader["gameId"]));
}
catch (Exception e)
{
//if exception, throw exception
throw e;
} finally
{
//clean up
Manager.CleanUpDatabaseConnection(connection);
Manager.CleanUpDatabaseReader(reader);
Manager.CleanUpDatabaseCommand(command);
}
//return gameToReturn game (GameState is the only property that could have changed from default)
return(gameToReturn);
}
public Game Find(Guid id)
{
//initialize variables
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
//----initalize return (gameToReturn)
Game gameToReturn = new Game();
try
{
//create connnection, command
connection = Manager.CreateDBConnection();
command = connection.CreateCommand();
//find game
command.CommandText = $"SELECT * FROM tictactoedb2.game WHERE `gameId`='{id}';";
reader = command.ExecuteReader();
reader.Read();
//set gameToReturn values to equal the found game
//----change gameToReturn's GameState to match the found game's GameState
Enum.TryParse(Convert.ToString(reader["data"]), out GameState gameState);
gameToReturn.GameState = gameState;
//----change gameToReturn's gameId to match the found game's guid
gameToReturn.GameId = Guid.Parse(Convert.ToString(reader["gameId"]));
}
catch (Exception e)
{
//if exception, throw exception
throw e;
}
finally
{
//clean up
Manager.CleanUpDatabaseConnection(connection);
Manager.CleanUpDatabaseReader(reader);
Manager.CleanUpDatabaseCommand(command);
}
//return gameToReturn
return(gameToReturn);
}
public Account Update(Account model)
{
//initialize returnValue
Account returnValue = null;
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
try
{
//getting error about an open data reader
Manager.CleanUpDatabaseReader(reader);
//create connection
connection = Manager.CreateDBConnection();
//check for existing account with that id
command = connection.CreateCommand();
command.CommandText = $"SELECT * FROM tictactoedb2.account WHERE `accountId`='{model.Id}';";
reader = command.ExecuteReader();
reader.Read();
if (reader.HasRows == true)
{
//there is an account with that id
//clean up reader
Manager.CleanUpDatabaseReader(reader);
//update account
command.CommandText = $"UPDATE tictactoedb2.account SET `online`={model.Online}, `win`={model.Win}, `loss`={model.Loss}, `tie`={model.Tie} WHERE accountId={model.Id};";
command.ExecuteNonQuery();
//clean up reader
Manager.CleanUpDatabaseReader(reader);
//get updated account
command.CommandText = $"SELECT * FROM tictactoedb2.account WHERE `accountId`='{model.Id}';";
reader = command.ExecuteReader();
reader.Read();
Account updatedAccount = new Account()
{
//accountId, displayname, online, password, username, win, loss, tie
Id = Convert.ToInt32(reader["accountId"]),
Displayname = Convert.ToString(reader["displayname"]),
Online = Convert.ToBoolean(reader["online"]),
Password = Convert.ToString(reader["password"]),
Username = Convert.ToString(reader["username"]),
Win = Convert.ToInt32(reader["win"]),
Loss = Convert.ToInt32(reader["loss"]),
Tie = Convert.ToInt32(reader["tie"])
};
//set returnValue to updated account
returnValue = updatedAccount;
}
}
catch (Exception e)
{
throw e;
}
finally
{
Manager.CleanUpDatabaseConnection(connection);
Manager.CleanUpDatabaseReader(reader);
Manager.CleanUpDatabaseCommand(command);
}
return(returnValue);
}
public Account Create(Account model)
{
//initialize returnValue
Account returnValue = null;
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
try
{
//create connection
connection = Manager.CreateDBConnection();
//check for existing account with username
command = connection.CreateCommand();
command.CommandText = $"SELECT * FROM tictactoedb2.account WHERE `username`='{model.Username}';";
reader = command.ExecuteReader();
reader.Read();
if (reader.HasRows != true)
{
//account with that username does not exist
Manager.CleanUpDatabaseReader(reader);
//make sure that the displayname, password, and/or username are not the equal
if (model.Displayname == model.Password || model.Displayname == model.Username || model.Password == model.Username)
{
return(returnValue);
}
//create new account with the given params
command.CommandText = $"INSERT INTO tictactoedb2.account (`displayname`, `password`, `username`) VALUES ('{model.Displayname}', '{model.Password}', '{model.Username}');";
command.ExecuteNonQuery();
//get created account from database
command.CommandText = $"SELECT * FROM tictactoedb2.account WHERE `username`='{model.Username}';";
reader = command.ExecuteReader();
reader.Read();
Account createdAccount = new Account()
{
//accountId, displayname, online, password, username, win, loss, tie
Id = Convert.ToInt32(reader["accountId"]),
Displayname = Convert.ToString(reader["displayname"]),
Online = Convert.ToBoolean(reader["online"]),
Password = Convert.ToString(reader["password"]),
Username = Convert.ToString(reader["username"]),
Win = Convert.ToInt32(reader["win"]),
Loss = Convert.ToInt32(reader["loss"]),
Tie = Convert.ToInt32(reader["tie"])
};
//set returnValue to created account
returnValue = createdAccount;
}
}
catch (Exception e)
{
throw e;
}
finally
{
Manager.CleanUpDatabaseConnection(connection);
Manager.CleanUpDatabaseReader(reader);
Manager.CleanUpDatabaseCommand(command);
}
return(returnValue);
}