public int GetStorageSQLID(TradeHandler.TradeItem MyTradeItem) { MySqlConnection MyConnection = new MySqlConnection("Server=" + MainClass.SqlServer + ";Port=" + MainClass.SqlPort.ToString() + ";Database=" + MainClass.SqlDatabase + ";Uid=" + MainClass.SqlUsername + ";Pwd=" + MainClass.SqlPassword + ";"); try { MyConnection.Open(); } catch (Exception myException) { Console.WriteLine(myException.Message); Environment.Exit(0); } TradeHandler.TradeItem MyTempTradeItem = new TradeHandler.TradeItem(); MyTempTradeItem = MyTradeItem; // Add Item it the Global Items List and to the Inventory List // First check if the Item exists in the global Database, if not Add it. int id = -1; int count = -1; //string sql = "SELECT id FROM knownitems WHERE imageid = ?imageid and name = ?name order by imageid, name"; string sql = "SELECT id FROM knownitems WHERE lower(name) = ?name order by name"; MySqlCommand cmd = new MySqlCommand(sql, MyConnection); cmd.Parameters.AddWithValue("?imageid", MyTempTradeItem.imageid); cmd.Parameters.AddWithValue("?name", MyTempTradeItem.name.ToLower()); TheLogger.Debug(sql + "\n"); MySqlDataReader reader = cmd.ExecuteReader(); TheLogger.Debug("### cmd.ExecuteReader() in GetStorageSQLID\n"); try { while (reader.Read()) { id = reader.GetInt16(0); count++; } } catch (MySqlException oMySQLException) { myErrorHandler.errorWriter(oMySQLException); } catch (Exception oException) { myErrorHandler.errorWriter(oException); } finally { reader.Close(); TheLogger.Debug("### reader.Close() in GetStorageSQLID\n"); } if (id != -1 && count == 0) { MyConnection.Close(); return id;//we found a perfect match } count = -1; sql = "SELECT id FROM knownitems WHERE imageid = ?imageid and lower(name) = ?name order by imageid, name"; cmd = new MySqlCommand(sql, MyConnection); cmd.Parameters.AddWithValue("?imageid", -1); cmd.Parameters.AddWithValue("?name", MyTempTradeItem.name.ToLower()); TheLogger.Debug(sql + "\n"); reader = cmd.ExecuteReader(); try { while (reader.Read()) { id = reader.GetInt16(0); count++; } } catch (MySqlException oMySQLException) { myErrorHandler.errorWriter(oMySQLException); } catch (Exception oException) { myErrorHandler.errorWriter(oException); } finally { reader.Close(); } if (id != -1 && count == 0) { //update the imageid here from -1 to the right one // imageid = -1 update it Inventory.inventory_item MyInventoryItem = new Inventory.inventory_item(); MyInventoryItem.imageid = MyTradeItem.imageid; MyInventoryItem.name = MyTradeItem.name; MyInventoryItem.description = ""; MyInventoryItem.weight = MyTradeItem.weight; MyInventoryItem.is_resource = false; MyInventoryItem.is_reagent = false; MyInventoryItem.is_stackable = false; MyInventoryItem.use_with_inventory = false; updateknownitems(MyInventoryItem, id); MyConnection.Close(); return id;//we found a perfect match } if (id == -1) { if(MyTradeItem.imageid == 0) TheLogger.ErrorLog("Possibly invalid Item: " + MyTradeItem.name + " added by " + Settings.botid + " in GetStorageSQLID()"); sql = "INSERT INTO knownitems (name,description,imageid,weight,is_resource,is_reagent,is_stackable,use_with_inventory) VALUES (?name,?description,?imageid,?weight,?is_resource,?is_reagent,?is_stackable,?use_with_inventory)"; cmd.Parameters.Clear(); cmd = new MySqlCommand(sql, MyConnection); cmd.Parameters.AddWithValue("?name", MyTradeItem.name); cmd.Parameters.AddWithValue("?description", " "); cmd.Parameters.AddWithValue("?imageid", MyTradeItem.imageid); cmd.Parameters.AddWithValue("?weight", MyTradeItem.weight); cmd.Parameters.AddWithValue("?is_resource", -1); cmd.Parameters.AddWithValue("?is_reagent", -1); cmd.Parameters.AddWithValue("?is_stackable", -1); cmd.Parameters.AddWithValue("?use_with_inventory", -1); try { cmd.ExecuteNonQuery(); id = GetKnownItemsSQLID(MyTradeItem); } catch (MySqlException oMySQLException) { TheLogger.Log("ExecSql Error in " + oMySQLException.TargetSite + " due to : " + oMySQLException.Message + "\n"); TheLogger.Log("ExecSql by SQL : " + sql + "\n"); myErrorHandler.errorWriter(oMySQLException); } catch (Exception oException) { TheLogger.Log("ExecSql Error in " + oException.TargetSite + " due to : " + oException.Message + "\n"); TheLogger.Log("ExecSql by SQL : " + sql + "\n"); myErrorHandler.errorWriter(oException); } finally { //reader.Close(); } reader.Close(); MyConnection.Close(); return id; } reader.Close(); MyConnection.Close(); return id; }
public void UpdateText(string nameText) { int idx; nameText = nameText.Replace((char)10, ' '); lock (TheStorage.SyncRoot) { for (idx = 0; idx < TheStorage.Count; idx++) { StorageItem MyStorageItem = (StorageItem)TheStorage[idx]; if (MyStorageItem.name == "") { MyStorageItem.name = nameText; TradeHandler.TradeItem MyTradeItem = new TradeHandler.TradeItem(); MyTradeItem.name = MyStorageItem.name; MyTradeItem.imageid = MyStorageItem.imageid; MyTradeItem.weight = -1; TradeHandler.TradeItem MyTempTradeItem = new TradeHandler.TradeItem(); MyTempTradeItem = MyTradeItem; MyStorageItem.knownItemsID = TheMySqlManager.GetStorageSQLID(MyTempTradeItem); //get the reserved amount MyStorageItem.reservedQuantity = TheMySqlManager.ReservedAmount(MyStorageItem.knownItemsID); TheStorage[idx] = MyStorageItem; //what we wanna do here is only insert on the last one, then insert them all... if (idx == TheStorage.Count - 1) { //insert all of the rows TheMySqlManager.InsertStorageItems(TheStorage); TradeHandler.openingStorage = false; } //TheMySqlManager.InsertStorageItem(MyStorageItem); break; } } } }