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