public bool updateUserInfo(UserInfo user) { bool bRet = false; string sql = string.Empty; sql += "UPDATE " + Table_of_UserInfo + " SET simip=?simip,avatar=?avatar,pass=?pass WHERE user=?user;"; MySqlCommand cmd = new MySqlCommand(sql, dbcon); cmd.Parameters.AddWithValue("?simip", user.SimIP); cmd.Parameters.AddWithValue("?avatar", user.Avatar); cmd.Parameters.AddWithValue("?pass", user.PswHash); cmd.Parameters.AddWithValue("?user", user.UserID); if (cmd.ExecuteNonQuery() > 0) bRet = true; else bRet = false; cmd.Dispose(); return bRet; }
public UserInfo fetchUserInfo(string userID) { UserInfo userInfo = new UserInfo(); userInfo.UserID = userID; string sql = string.Empty; sql += "SELECT * from " + Table_of_UserInfo + " WHERE user = ?userID;"; MySqlCommand cmd = new MySqlCommand(sql, dbcon); cmd.Parameters.AddWithValue("?userID", userID); using (MySqlDataReader r = cmd.ExecuteReader()) { if (r.Read()) { try { userInfo.SimIP = (string)r["simip"]; userInfo.Avatar = (string)r["avatar"]; userInfo.PswHash = (string)r["pass"]; } catch (Exception e) { m_log.Error("[MYSQL] Fetching UserInfo failed: " + e.ToString()); return null; } } else { return null; } r.Close(); } return userInfo; }
public bool addUserInfo(UserInfo userInfo) { bool bRet = false; string sql = string.Empty; if (userInfo.Avatar==null) return false; sql += "INSERT INTO " + Table_of_UserInfo +"(`user`,`simip`,`avatar`,`pass`) VALUES"; sql += "(?user,?simip,?avatar,?password);"; MySqlCommand cmd = new MySqlCommand(sql, dbcon); cmd.Parameters.AddWithValue("?user", userInfo.UserID); cmd.Parameters.AddWithValue("?simip", userInfo.SimIP); cmd.Parameters.AddWithValue("?avatar", userInfo.Avatar); cmd.Parameters.AddWithValue("?password", userInfo.PswHash); try { if (cmd.ExecuteNonQuery() > 0) bRet = true; cmd.Dispose(); } catch (Exception e) { m_log.Error("Unable to add user information to database: " + e.ToString()); bRet = false; } return bRet; }
/// <summary> /// Get the user balance when user entering a parcel. /// </summary> /// <param name="request"></param> /// <returns></returns> public XmlRpcResponse handleClientLogin(XmlRpcRequest request, IPEndPoint remoteClient) { //m_log.InfoFormat("[MONEY RPC] handleClientLogin:"******"clientUUID")) clientUUID = (string)requestData["clientUUID"]; if (requestData.ContainsKey("clientSessionID")) sessionID = (string)requestData["clientSessionID"]; if (requestData.ContainsKey("clientSecureSessionID")) secureID = (string)requestData["clientSecureSessionID"]; if (requestData.ContainsKey("userServIP")) userServerIP = (string)requestData["userServIP"]; if (requestData.ContainsKey("openSimServIP")) simIP = (string)requestData["openSimServIP"]; if (requestData.ContainsKey("userName")) avatarName = (string)requestData["userName"]; if (avatarName=="") { responseData["success"] = false; //responseData["description"] = "Avatar Name is empty"; responseData["clientBalance"] = 0; return response; } userID = clientUUID + "@" + userServerIP; //Update the session and secure session dictionary lock (m_sessionDic) { if (!m_sessionDic.ContainsKey(userID)) { m_sessionDic.Add(userID, sessionID); } else m_sessionDic[userID] = sessionID; } lock (m_secureSessionDic) { if (!m_secureSessionDic.ContainsKey(userID)) { m_secureSessionDic.Add(userID, secureID); } else m_secureSessionDic[userID] = secureID; } try { //m_log.InfoFormat("[MONEY RPC] handleClientLogin: User {0} has logged in, getting balance...", userID); balance = m_moneyDBService.getBalance(userID); //add user if not exist if (balance==-1) { if (m_moneyDBService.addUser(userID, m_defaultBalance, 0)) { responseData["success"] = true; responseData["description"] = "add user successfully"; responseData["clientBalance"] = m_defaultBalance; } else { responseData["success"] = false; responseData["description"] = "add user failed"; responseData["clientBalance"] = 0; } } else if (balance >= 0) //Success { responseData["success"] = true; responseData["description"] = "get user balance successfully"; responseData["clientBalance"] = balance; } UserInfo user = new UserInfo(); user.UserID = userID; user.SimIP = simIP; user.Avatar = avatarName; //TODO: Add password protection here user.PswHash = UUID.Zero.ToString(); if (!m_moneyDBService.TryAddUserInfo(user)) { m_log.ErrorFormat("[MONEY RPC] handleClientLogin: Unable to refresh information for user \"{0}\" in DB", avatarName); responseData["success"] = false; responseData["description"] = "Update or add user information to db failed"; responseData["clientBalance"] = balance; } return response; } catch (Exception e) { m_log.ErrorFormat("[MONEY RPC] handleClientLogin: Can't get balance of user {0}", clientUUID); responseData["success"] = false; responseData["description"] = "Exception occured" + e.ToString(); responseData["clientBalance"] = 0; } return response; }
public bool TryAddUserInfo(UserInfo user) { MySQLSuperManager dbm = GetLockedConnection(); try { if (dbm.Manager.fetchUserInfo(user.UserID) != null) { m_log.InfoFormat("[Money DB] Found user \"{0}\",now update information", user.Avatar); if (m_moneyManager.updateUserInfo(user)) return true; } else if (dbm.Manager.addUserInfo(user)) { m_log.InfoFormat("[Money DB] Unable to find user \"{0}\", add it to DB successfully", user.Avatar); return true; } return false; } catch (Exception e) { dbm.Manager.Reconnect(); // Fumi.Iseki m_moneyManager.Reconnect(); m_log.Error(e.ToString()); return false; } finally { dbm.Release(); } }
private void UpdateUserInfoTable1() { m_log.Info("[MONEY DB]: Converting UserInfo Table..."); string sql = string.Empty; sql = "SELECT COUNT(*) FROM " + Table_of_UserInfo; MySqlCommand cmd = new MySqlCommand(sql, dbcon); int resultCount = int.Parse(cmd.ExecuteScalar().ToString()); cmd.Dispose(); sql = "SELECT * FROM " + Table_of_UserInfo; cmd = new MySqlCommand(sql, dbcon); MySqlDataReader dbReader = cmd.ExecuteReader(); int l = 0; string[,] row = new string[resultCount, dbReader.FieldCount]; while (dbReader.Read()) { for (int i=0; i<dbReader.FieldCount; i++) { row[l, i] = dbReader.GetString(i); } l++; } dbReader.Close(); cmd.Dispose(); bool updatedb = true; for (int i=0; i<resultCount; i++) { string uuid = Regex.Replace(row[i, 0], @"@.+$", ""); if (uuid!=row[i, 0]) { UserInfo userInfo = fetchUserInfo(uuid); if (userInfo==null) { userInfo = new UserInfo(); userInfo.UserID = uuid; userInfo.SimIP = row[i,1]; userInfo.Avatar = row[i,2]; userInfo.PswHash = row[i,3]; updatedb = addUserInfo(userInfo); } } } // Delete if (updatedb) { for (int i=0; i<resultCount; i++) { string uuid = Regex.Replace(row[i, 0], @"@.+$", ""); if (uuid!=row[i, 0]) { sql = "DELETE FROM " + Table_of_UserInfo + " WHERE user = ?uuid"; cmd = new MySqlCommand(sql, dbcon); cmd.Parameters.AddWithValue("?uuid", row[i,0]); cmd.ExecuteNonQuery(); cmd.Dispose(); } } // sql = "BEGIN;"; sql += "ALTER TABLE `" + Table_of_UserInfo + "`"; sql += "COMMENT = 'Rev.2';"; sql += "COMMIT;"; cmd = new MySqlCommand(sql, dbcon); cmd.ExecuteNonQuery(); } }