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