/// <summary>
/// Register a new player
/// Returns null if it fails
/// </summary>
/// <param name="username">Username of the player</param>
/// <param name="password">Password of the player</param>
/// <returns>The player or null depending on how it's result</returns>
public static KeyValuePair <DBProfile_Login, string> RegisterPlayer(string username, string password)
{
string hash = DBPasswordHash.GetHashString(password);
if (!PlayerExist(username))
{
string sql = String.Format("INSERT INTO [dbo].[{0}] (Username,Password) VALUES('{1}', '{2}'); "
, tableName
, username
, hash);
var sqlResult = DBEndPoint.ExecuteSQL(sql);
if (sqlResult.Key)
{
var player = GetPlayer(username, hash);
RegisterPlayerInOtherTables(player.id);
return(new KeyValuePair <DBProfile_Login, string>(player, "Succesfully generated"));
}
else
{
return(new KeyValuePair <DBProfile_Login, string>(null, "Internal issue"));
}
}
else
{
return(new KeyValuePair <DBProfile_Login, string>(null, "A player with that name already exist"));
}
}
/// <summary>
/// Gets all columns from a single row
/// </summary>
/// <param name="sql">Sql to execute</param>
/// <param name="listToPopulate">Column name and value found</param>
/// <returns>If a row was found</returns>
public static bool GetSingleRowSqlSearch(string sql, Dictionary <string, object> listToPopulate)
{
SqlConnection connection = DBEndPoint.GetConnection();
SqlCommand command;
SqlDataReader dataReader;
bool succes = false;
try
{
connection.Open();
command = new SqlCommand(sql, connection);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
for (int i = 0; i < dataReader.VisibleFieldCount; i++)
{
listToPopulate.Add(dataReader.GetName(i).Trim(), dataReader.GetValue(i));
}
succes = true;
}
dataReader.Close();
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
string msg = "Exception caught in SQL " + ex.ToString();
Console.WriteLine(msg);
}
return(succes);
}
/// <summary>
/// Generates and executes the sql to increment a certain row and updating kills and deaths
/// </summary>
/// <param name="id">ID of the player, gotten from the LoginTable</param>
/// <param name="rowNameToIncrement"></param>
/// <param name="kills"></param>
/// <param name="deaths"></param>
static void Increment(int id, string rowNameToIncrement, int kills, int deaths)
{
string sql = String.Format("UPDATE {0} SET Losses = Losses + 1, Kills = Kills + {1}, Deaths = Deaths + {2} WHERE Id = {3}",
tableName,
kills,
deaths,
id);
DBEndPoint.ExecuteSQL(sql);
}
/// <summary>
/// Search for a player
/// Returns if a player is found
/// </summary>
/// <param name="username">Username of the player</param>
/// <returns>Returns if a player is found with the usernames</returns>
static bool PlayerExist(string username)
{
string sql = String.Format("SELECT * FROM [dbo].[{0}] WHERE Username = '******'"
, tableName
, username);
Dictionary <string, object> results = new Dictionary <string, object>();
if (DBEndPoint.GetSingleRowSqlSearch(sql, results))
{
return(true);
}
return(false);
}
/// <summary>
/// Creates an row for a new player on the PlayerAchievements table, with the id matching the id of LoginTable
/// </summary>
/// <param name="id">Id of the player on LoginTable</param>
public static void RegisterNewPlayer(int id)
{
string sql = String.Format("INSERT INTO [dbo].[{0}] (Id,Wins,Losses,Kills,Deaths) VALUES({1},0,0,0,0); "
, tableName
, id);
var sqlResult = DBEndPoint.ExecuteSQL(sql);
if (!sqlResult.Key)
{
throw new Exception("Register player failed in DBPlayerAchievements with msg " + sqlResult.Value);
}
}
/// <summary>
/// Search for a player
/// Returns null if no player is found
/// </summary>
/// <param name="username">Username of the player</param>
/// <param name="hashedPassword">Hashed password of the player </param>
/// <returns>The player profile, null if none were found</returns>
public static DBProfile_Login GetPlayer(string username, string hashedPassword)
{
string sql = String.Format("SELECT * FROM [dbo].[{0}] WHERE Username = '******' and Password = '******'"
, tableName
, username
, hashedPassword);
Dictionary <string, object> results = new Dictionary <string, object>();
if (DBEndPoint.GetSingleRowSqlSearch(sql, results))
{
DBProfile_Login profile;
profile = new DBProfile_Login((int)results["Id"], (string)results["Username"]);
return(profile);
}
return(null);
}
/// <summary>
/// Executes sql represented as strings
/// </summary>
/// <param name="sql"></param>
/// <returns>Returns whether or not it succeded, and possible error messages</returns>
public static KeyValuePair <bool, string> ExecuteSQL(string sql)
{
SqlConnection connection = DBEndPoint.GetConnection();
SqlCommand command;
try
{
connection.Open();
command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
return(new KeyValuePair <bool, string>(true, "Command succesful"));
}
catch (Exception ex)
{
return(new KeyValuePair <bool, string>(false, ex.ToString()));
}
}
/// <summary>
/// Returns the profile of a player
/// </summary>
/// <param name="username"></param>
/// <param name="hashedPassword"></param>
/// <returns>Returns the profile of a player, returns null if none were found</returns>
public static DBProfile_Stats GetPlayer(string username, string hashedPassword)
{
var player = DBLogin.GetPlayer(username, hashedPassword);
if (player == null)
{
return(null);
}
string sql = String.Format("SELECT * FROM [dbo].[{0}] WHERE Id = {1}"
, tableName
, player.id);
Dictionary <string, object> results = new Dictionary <string, object>();
if (DBEndPoint.GetSingleRowSqlSearch(sql, results))
{
DBProfile_Stats profile;
profile = new DBProfile_Stats((int)results["Wins"], (int)results["Losses"], (int)results["Kills"], (int)results["Deaths"]);
return(profile);
}
return(null);
}
