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