//-----------------------------------------------------------------------------------//
/// <summary>
/// Insert scoreboard data
/// </summary>
public bool InsertScoreboard(ScoreboardMODEL model, out string exceptionMessage)
{
bool hasExecuted = false;
MySqlConnection connection = databaseConnection.GetConnection();
try
{
connection.Open();
// Command
StringBuilder sql = new StringBuilder();
sql.Append(" INSERT INTO scoreboard (USERNAME, SCORE, SCORE_DATE) ");
sql.Append(" VALUES (@USERNAME, @SCORE, @SCORE_DATE) ");
// Parameters
MySqlCommand command = new MySqlCommand(sql.ToString(), connection);
command.Parameters.AddWithValue("@USERNAME", model.Username);
command.Parameters.AddWithValue("@SCORE", model.Score);
command.Parameters.AddWithValue("@SCORE_DATE", model.ScoreDate);
hasExecuted = (command.ExecuteNonQuery() == 1);
exceptionMessage = "";
}
catch (Exception ex)
{
exceptionMessage = ex.Message;
}
finally
{
connection.Close();
}
return(hasExecuted);
}
private void RegisterScore()
{
// Checks username
if (string.IsNullOrEmpty(usernameText.text) || string.IsNullOrWhiteSpace(usernameText.text))
{
messageText.text = "Please, inform the Username!";
return;
}
// Checks score
if (string.IsNullOrEmpty(scoreText.text) || string.IsNullOrWhiteSpace(scoreText.text))
{
messageText.text = "Please, inform the Score!";
return;
}
// Fills model
ScoreboardMODEL model = new ScoreboardMODEL();
model.Username = usernameText.text;
model.Score = decimal.Parse(scoreText.text);
model.ScoreDate = DateTime.Now;
ScoreboardDAO scoreboardDAO = new ScoreboardDAO();
string exceptionMessage;
bool hasInserted = scoreboardDAO.InsertScoreboard(model, out exceptionMessage);
messageText.text = (hasInserted ? "Score registered sucessfully!" : string.Concat("Error on register the Score! ", exceptionMessage));
}
private void DrawTable()
{
CleanTable();
// Search
ScoreboardDAO scoreboardDAO = new ScoreboardDAO();
List <ScoreboardMODEL> listModels = scoreboardDAO.ListScoreboard();
// Params
int rankingIndex = 1;
float currentPosY = -160;
for (int index = 0; index < listModels.Count; index++)
{
ScoreboardMODEL model = listModels[index];
for (int j = 0; j < 1; j++)
{
CreateTextElement("Cell", rankingIndex.ToString(), defaultPosition);
CreateTextElement("Cell", model.ScoreID.ToString(), defaultPosition);
CreateTextElement("Cell", model.Username, defaultPosition);
CreateTextElement("Cell", model.Score.ToString(), defaultPosition);
CreateTextElement("Cell", model.ScoreDate.ToString(), defaultPosition);
CreateButtonElement("Cell", model.ScoreID.ToString(), "Update", defaultPosition);
CreateButtonElement("Cell", model.ScoreID.ToString(), "Delete", defaultPosition);
currentPosY -= 100f;
defaultPosition = new Vector3(-1100, currentPosY, 0);
}
rankingIndex++;
}
}
private void LoadScoreData(int scoreID)
{
if (scoreID <= 0)
{
return;
}
// Loads data
ScoreboardDAO scoreboardDAO = new ScoreboardDAO();
ScoreboardMODEL model = scoreboardDAO.ListScoreboardByID(scoreID);
usernameText.text = model.Username;
scoreText.text = model.Score.ToString();
}
/// <summary>
/// Recover scoreboard data by ID
/// </summary>
public ScoreboardMODEL ListScoreboardByID(int scoreID)
{
ScoreboardMODEL model = new ScoreboardMODEL();
MySqlConnection connection = databaseConnection.GetConnection();
try
{
connection.Open();
// Command
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT SCORE_ID, USERNAME, SCORE, SCORE_DATE ");
sql.Append(" FROM scoreboard ");
sql.Append(" WHERE SCORE_ID = @SCORE_ID ");
// Params
MySqlCommand command = new MySqlCommand(sql.ToString(), connection);
command.Parameters.AddWithValue("@SCORE_ID", scoreID);
// Read data
MySqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
model.ScoreID = reader.GetInt16("SCORE_ID");
model.Username = reader.GetString("USERNAME");
model.Score = reader.GetDecimal("SCORE");
model.ScoreDate = reader.GetDateTime("SCORE_DATE");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return(model);
}
/// <summary>
/// List all scoreboard data
/// </summary>
public List <ScoreboardMODEL> ListScoreboard()
{
List <ScoreboardMODEL> models = new List <ScoreboardMODEL> ();
MySqlConnection connection = databaseConnection.GetConnection();
try
{
connection.Open();
// Command
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT SCORE_ID, USERNAME, SCORE, SCORE_DATE ");
sql.Append(" FROM scoreboard ");
sql.Append(" ORDER BY SCORE DESC ");
// Read data
MySqlCommand command = new MySqlCommand(sql.ToString(), connection);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ScoreboardMODEL model = new ScoreboardMODEL();
model.ScoreID = reader.GetInt16("SCORE_ID");
model.Username = reader.GetString("USERNAME");
model.Score = reader.GetDecimal("SCORE");
model.ScoreDate = reader.GetDateTime("SCORE_DATE");
models.Add(model);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return(models);
}
/// <summary>
/// Update scoreboard data
/// </summary>
public bool UpdateScoreboard(ScoreboardMODEL model, out string exceptionMessage)
{
exceptionMessage = "";
bool hasExecuted = false;
MySqlConnection connection = databaseConnection.GetConnection();
try
{
connection.Open();
// Command
StringBuilder sql = new StringBuilder();
sql.Append(" UPDATE scoreboard ");
sql.Append(" SET USERNAME = @USERNAME, ");
sql.Append(" SCORE = @SCORE, ");
sql.Append(" SCORE_DATE = @SCORE_DATE ");
sql.Append(" WHERE SCORE_ID = @SCORE_ID ");
// Parameters
MySqlCommand command = new MySqlCommand(sql.ToString(), connection);
command.Parameters.AddWithValue("@USERNAME", model.Username);
command.Parameters.AddWithValue("@SCORE", model.Score);
command.Parameters.AddWithValue("@SCORE_DATE", model.ScoreDate);
command.Parameters.AddWithValue("@SCORE_ID", model.ScoreID);
hasExecuted = (command.ExecuteNonQuery() == 1);
}
catch (Exception ex)
{
exceptionMessage = ex.Message;
}
finally
{
connection.Close();
}
return(hasExecuted);
}
