public static RowList <T> Get <T>(RowList <T> rowList = null, string database = null) where T : IDataModel, new() { // TODO: Add new config option "Verify data against DB" if (!SQLConnector.Enabled) { return(null); } var result = new RowList <T>(); using (var command = SQLConnector.CreateCommand(new SQLSelect <T>(rowList, database).Build())) { if (command == null) { return(null); } var fields = SQLUtil.GetFields <T>(); var fieldsCount = fields.Select(f => f.Item3.First().Count).Sum(); using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var instance = (T)Activator.CreateInstance(typeof(T)); var values = GetValues(reader, fieldsCount); var i = 0; foreach (var field in fields) { if (values[i] is DBNull) { if (field.Item2.FieldType == typeof(string)) { field.Item2.SetValue(instance, string.Empty); } else if (field.Item3.Any(a => a.Nullable)) { field.Item2.SetValue(instance, null); } } else if (field.Item2.FieldType.BaseType == typeof(Enum)) { field.Item2.SetValue(instance, Enum.Parse(field.Item2.FieldType, values[i].ToString())); } else if (field.Item2.FieldType.BaseType == typeof(Array)) { var arr = Array.CreateInstance(field.Item2.FieldType.GetElementType(), field.Item3.First().Count); for (var j = 0; j < arr.Length; j++) { var elemType = arr.GetType().GetElementType(); if (elemType.IsEnum) { arr.SetValue(Enum.Parse(elemType, values[i + j].ToString()), j); } else if (Nullable.GetUnderlyingType(elemType) != null) //is nullable { arr.SetValue(Convert.ChangeType(values[i + j], Nullable.GetUnderlyingType(elemType)), j); } else { arr.SetValue(Convert.ChangeType(values[i + j], elemType), j); } } field.Item2.SetValue(instance, arr); } else if (field.Item2.FieldType == typeof(bool)) { field.Item2.SetValue(instance, Convert.ToBoolean(values[i])); } else if (Nullable.GetUnderlyingType(field.Item2.FieldType) != null) // is nullable { var uType = Nullable.GetUnderlyingType(field.Item2.FieldType); field.Item2.SetValue(instance, uType.IsEnum ? Enum.Parse(uType, values[i].ToString()) : Convert.ChangeType(values[i], uType)); } else { field.Item2.SetValue(instance, values[i]); } i += field.Item3.First().Count; } result.Add(instance); } } } return(result); }
public static StoreMulti <Tuple <T, TG>, TK> GetDictMulti <T, TG, TK>(List <Tuple <T, TG> > entries, string primaryKeyName1, string primaryKeyName2) { if (entries.Count == 0) { return(null); } // TODO: Add new config option "Verify data against DB" if (!SQLConnector.Enabled) { return(null); } var tableAttrs = (DBTableNameAttribute[])typeof(TK).GetCustomAttributes(typeof(DBTableNameAttribute), false); if (tableAttrs.Length <= 0) { return(null); } var tableName = tableAttrs[0].Name; var fields = Utilities.GetFieldsAndAttribute <TK, DBFieldNameAttribute>(); fields.RemoveAll(field => field.Item2.Name == null); var fieldCount = 2; var fieldNames = new StringBuilder(); fieldNames.Append(primaryKeyName1 + ","); fieldNames.Append(primaryKeyName2 + ","); foreach (var field in fields) { fieldNames.Append(field.Item2); fieldNames.Append(","); fieldCount += field.Item2.Count; } // WHERE (a = x1 AND b = y1) OR (a = x2 AND b = y2) OR ... var whereClause = new StringBuilder(); var ji = 0; foreach (var tuple in entries) { ji += 1; whereClause.Append("(") .Append(primaryKeyName1) .Append(" = ") .Append(tuple.Item1) .Append(" AND ") .Append(primaryKeyName2) .Append(" = ") .Append(tuple.Item2) .Append(")"); if (ji != entries.Count) { whereClause.Append(" OR "); } } var query = string.Format("SELECT {0} FROM {1}.{2} WHERE {3}", fieldNames.ToString().TrimEnd(','), Settings.TDBDatabase, tableName, whereClause); var dict = new MultiDictionary <Tuple <T, TG>, TK>(true); using (var reader = SQLConnector.ExecuteQuery(query)) { if (reader == null) { return(null); } while (reader.Read()) { var instance = (TK)Activator.CreateInstance(typeof(TK)); var values = new object[fieldCount]; var count = reader.GetValues(values); if (count != fieldCount) { throw new InvalidConstraintException( "Number of fields from DB is different of the number of fields with DBFieldName attribute"); } var i = 2; foreach (var field in fields) { #if __MonoCS__ // Mono does not support __makeref (only added in the upcoming 2.12 version) if (values[i] is DBNull && field.Item1.FieldType == typeof(string)) { field.Item1.SetValue(instance, string.Empty); } else if (field.Item1.FieldType.BaseType == typeof(Enum)) { field.Item1.SetValue(instance, Enum.Parse(field.Item1.FieldType, values[i].ToString())); } else if (field.Item1.FieldType.BaseType == typeof(Array)) { var arr = Array.CreateInstance(field.Item1.FieldType.GetElementType(), field.Item2.Count); for (var j = 0; j < arr.Length; j++) { var elemType = arr.GetType().GetElementType(); var val = Convert.ChangeType(values[i + j], elemType); arr.SetValue(val, j); } field.Item1.SetValue(instance, arr); } else if (field.Item1.FieldType == typeof(bool)) { field.Item1.SetValue(instance, Convert.ToBoolean(values[i])); } else { field.Item1.SetValue(instance, values[i]); } #else if (values[i] is DBNull && field.Item1.FieldType == typeof(string)) { field.Item1.SetValueDirect(__makeref(instance), string.Empty); } else if (field.Item1.FieldType.BaseType == typeof(Enum)) { field.Item1.SetValueDirect(__makeref(instance), Enum.Parse(field.Item1.FieldType, values[i].ToString())); } else if (field.Item1.FieldType.BaseType == typeof(Array)) { var arr = Array.CreateInstance(field.Item1.FieldType.GetElementType(), field.Item2.Count); for (var j = 0; j < arr.Length; j++) { var elemType = arr.GetType().GetElementType(); var val = Convert.ChangeType(values[i + j], elemType); arr.SetValue(val, j); } field.Item1.SetValueDirect(__makeref(instance), arr); } else if (field.Item1.FieldType == typeof(bool)) { field.Item1.SetValueDirect(__makeref(instance), Convert.ToBoolean(values[i])); } else { field.Item1.SetValueDirect(__makeref(instance), values[i]); } #endif i += field.Item2.Count; } var key = Tuple.Create((T)values[0], (TG)values[1]); if (!dict.ContainsKey(key)) { dict.Add(key, instance); } } } return(new StoreMulti <Tuple <T, TG>, TK>(dict)); }
/// <summary> /// Loads the broadcast texts form the database. /// </summary> private static void LoadBroadcastText() { string query = "SELECT ID, Text, Text1, EmoteID1, EmoteID2, EmoteID3, EmoteDelay1, EmoteDelay2, EmoteDelay3, EmotesID, LanguageID, Flags, ConditionID, SoundEntriesID1, SoundEntriesID2 " + $"FROM {Settings.HotfixesDatabase}.broadcast_text;"; if (Settings.TargetedDatabase == TargetedDatabase.WrathOfTheLichKing || Settings.TargetedDatabase == TargetedDatabase.Cataclysm) { query = "SELECT ID, LanguageID, Text, Text1, EmoteID1, EmoteID2, EmoteID3, EmoteDelay1, EmoteDelay2, EmoteDelay3, SoundEntriesID, EmotesID, Flags " + $"FROM {Settings.TDBDatabase}.broadcast_text;"; } using (var command = SQLConnector.CreateCommand(query)) { if (command == null) { return; } using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var id = Convert.ToInt32(reader["Id"]); var text = Convert.ToString(reader["Text"]); var text1 = Convert.ToString(reader["Text1"]); if (!BroadcastTexts.ContainsKey(text)) { BroadcastTexts[text] = new List <int>(); } BroadcastTexts[text].Add(id); if (!BroadcastText1s.ContainsKey(text1)) { BroadcastText1s[text1] = new List <int>(); } BroadcastText1s[text1].Add(id); if (!Settings.UseDBC) { continue; } var broadcastText = new BroadcastTextEntry() { Text = text, Text1 = text1, }; broadcastText.EmoteID = new ushort[3]; broadcastText.EmoteID[0] = Convert.ToUInt16(reader["EmoteID1"]); broadcastText.EmoteID[1] = Convert.ToUInt16(reader["EmoteID2"]); broadcastText.EmoteID[2] = Convert.ToUInt16(reader["EmoteID3"]); broadcastText.EmoteDelay = new ushort[3]; broadcastText.EmoteDelay[0] = Convert.ToUInt16(reader["EmoteDelay1"]); broadcastText.EmoteDelay[1] = Convert.ToUInt16(reader["EmoteDelay2"]); broadcastText.EmoteDelay[2] = Convert.ToUInt16(reader["EmoteDelay3"]); broadcastText.EmotesID = Convert.ToUInt16(reader["EmotesID"]); broadcastText.LanguageID = Convert.ToByte(reader["LanguageID"]); broadcastText.Flags = Convert.ToByte(reader["Flags"]); if (Settings.TargetedDatabase == TargetedDatabase.WrathOfTheLichKing || Settings.TargetedDatabase == TargetedDatabase.Cataclysm) { broadcastText.ConditionID = 0; broadcastText.SoundEntriesID = new uint[2]; broadcastText.SoundEntriesID[0] = Convert.ToUInt32(reader["SoundEntriesID"]); broadcastText.SoundEntriesID[1] = 0; } else { broadcastText.ConditionID = Convert.ToUInt32(reader["ConditionID"]); broadcastText.SoundEntriesID = new uint[2]; broadcastText.SoundEntriesID[0] = Convert.ToUInt32(reader["SoundEntriesID1"]); broadcastText.SoundEntriesID[1] = Convert.ToUInt32(reader["SoundEntriesID2"]); } if (!DBC.DBC.BroadcastText.ContainsKey(id)) { DBC.DBC.BroadcastText.Add(id, broadcastText); } else { DBC.DBC.BroadcastText[id] = broadcastText; } } } } }
/// <summary> /// Loads the broadcast texts form the database. /// </summary> private static void LoadBroadcastText() { string query = "SELECT ID, Text, Text1, EmoteID1, EmoteID2, EmoteID3, EmoteDelay1, EmoteDelay2, EmoteDelay3, EmotesID, LanguageID, Flags, ConditionID, SoundEntriesID1, SoundEntriesID2 " + $"FROM {Settings.HotfixesDatabase}.broadcast_text;"; using (var reader = SQLConnector.ExecuteQuery(query)) { if (reader == null) { return; } while (reader.Read()) { var id = Convert.ToInt32(reader["Id"]); var text = Convert.ToString(reader["Text"]); var text1 = Convert.ToString(reader["Text1"]); if (!BroadcastTexts.ContainsKey(text)) { BroadcastTexts[text] = new List <int>(); } BroadcastTexts[text].Add(id); if (!BroadcastText1s.ContainsKey(text1)) { BroadcastText1s[text1] = new List <int>(); } BroadcastText1s[text1].Add(id); if (!Settings.UseDBC) { continue; } var broadcastText = new BroadcastTextEntry() { Text = text, Text1 = text1, }; broadcastText.EmoteID = new ushort[3]; broadcastText.EmoteID[0] = Convert.ToUInt16(reader["EmoteID1"]); broadcastText.EmoteID[1] = Convert.ToUInt16(reader["EmoteID2"]); broadcastText.EmoteID[2] = Convert.ToUInt16(reader["EmoteID3"]); broadcastText.EmoteDelay = new ushort[3]; broadcastText.EmoteDelay[0] = Convert.ToUInt16(reader["EmoteDelay1"]); broadcastText.EmoteDelay[1] = Convert.ToUInt16(reader["EmoteDelay2"]); broadcastText.EmoteDelay[2] = Convert.ToUInt16(reader["EmoteDelay3"]); broadcastText.EmotesID = Convert.ToUInt16(reader["EmotesID"]); broadcastText.LanguageID = Convert.ToByte(reader["LanguageID"]); broadcastText.Flags = Convert.ToByte(reader["Flags"]); broadcastText.ConditionID = Convert.ToUInt32(reader["ConditionID"]); broadcastText.SoundEntriesID = new uint[2]; broadcastText.SoundEntriesID[0] = Convert.ToUInt32(reader["SoundEntriesID1"]); broadcastText.SoundEntriesID[1] = Convert.ToUInt32(reader["SoundEntriesID2"]); if (!DBC.DBC.BroadcastText.ContainsKey(id)) { DBC.DBC.BroadcastText.Add(id, broadcastText); } else { DBC.DBC.BroadcastText[id] = broadcastText; } } } }
/// <summary> /// Gets from `world` database a dictionary of the given struct. /// Structs fields name and type must match the name and type of the DB columns /// </summary> /// <typeparam name="T">Type of the elements of the list of entries (usually uint)</typeparam> /// <typeparam name="TK">Type of the struct</typeparam> /// <param name="entries">List of entries to select from DB</param> /// <param name="primaryKeyName"> </param> /// <returns>Dictionary of structs of type TK</returns> public static Dictionary <T, TK> GetDict <T, TK>(List <T> entries, string primaryKeyName = "entry") { if (entries.Count == 0) { return(null); } // TODO: Add new config option "Verify data against DB" if (!SQLConnector.Enabled) { return(null); } var tableAttrs = (DBTableNameAttribute[])typeof(TK).GetCustomAttributes(typeof(DBTableNameAttribute), false); if (tableAttrs.Length <= 0) { return(null); } var tableName = tableAttrs[0].Name; var fields = Utilities.GetFieldsAndAttribute <TK, DBFieldNameAttribute>(); var fieldCount = 1; var fieldNames = new StringBuilder(); fieldNames.Append(primaryKeyName + ","); foreach (var field in fields) { fieldNames.Append(field.Item2.ToString()); fieldNames.Append(","); fieldCount += field.Item2.Count; } var query = string.Format("SELECT {0} FROM {1}.{2} WHERE {3} IN ({4})", fieldNames.ToString().TrimEnd(','), Settings.TDBDatabase, tableName, primaryKeyName, String.Join(",", entries)); var dict = new Dictionary <T, TK>(entries.Count); using (var reader = SQLConnector.ExecuteQuery(query)) { if (reader == null) { return(null); } while (reader.Read()) { var instance = (TK)Activator.CreateInstance(typeof(TK)); var values = new object[fieldCount]; var count = reader.GetValues(values); if (count != fieldCount) { throw new InvalidConstraintException( "Number of fields from DB is different of the number of fields with DBFieldName attribute"); } var i = 1; foreach (var field in fields) { if (values[i] is DBNull && field.Item1.FieldType == typeof(string)) { field.Item1.SetValueDirect(__makeref(instance), string.Empty); } else if (field.Item1.FieldType.BaseType == typeof(Enum)) { field.Item1.SetValueDirect(__makeref(instance), Enum.Parse(field.Item1.FieldType, values[i].ToString())); } else if (field.Item1.FieldType.BaseType == typeof(Array)) { var arr = Array.CreateInstance(field.Item1.FieldType.GetElementType(), field.Item2.Count); for (var j = 0; j < arr.Length; j++) { var elemType = arr.GetType().GetElementType(); var val = Convert.ChangeType(values[i + j], elemType); arr.SetValue(val, j); } field.Item1.SetValueDirect(__makeref(instance), arr); } else if (field.Item1.FieldType == typeof(bool)) { field.Item1.SetValueDirect(__makeref(instance), Convert.ToBoolean(values[i])); } else { field.Item1.SetValueDirect(__makeref(instance), values[i]); } i += field.Item2.Count; } dict.Add((T)values[0], instance); } } return(dict); }
/// <summary> /// Loads the broadcast texts form the database. /// </summary> private static void LoadBroadcastText() { string query = "SELECT ID, MaleText, FemaleText, EmoteID1, EmoteID2, EmoteID3, EmoteDelay1, EmoteDelay2, EmoteDelay3, UnkEmoteID, Language, Type, SoundID1, SoundID2, PlayerConditionID " + $"FROM {Settings.HotfixesDatabase}.broadcast_text;"; using (var reader = SQLConnector.ExecuteQuery(query)) { if (reader == null) { return; } while (reader.Read()) { var id = Convert.ToInt32(reader["Id"]); var maleText = Convert.ToString(reader["MaleText"]); var femaleText = Convert.ToString(reader["FemaleText"]); if (!BroadcastMaleTexts.ContainsKey(maleText)) { BroadcastMaleTexts[maleText] = new List <int>(); } BroadcastMaleTexts[maleText].Add(id); if (!BroadcastFemaleTexts.ContainsKey(femaleText)) { BroadcastFemaleTexts[femaleText] = new List <int>(); } BroadcastFemaleTexts[femaleText].Add(id); if (!Settings.UseDBC) { continue; } var broadcastText = new BroadcastTextEntry() { MaleText = maleText, FemaleText = femaleText, }; broadcastText.EmoteID = new ushort[3]; broadcastText.EmoteID[0] = Convert.ToUInt16(reader["EmoteID1"]); broadcastText.EmoteID[1] = Convert.ToUInt16(reader["EmoteID2"]); broadcastText.EmoteID[2] = Convert.ToUInt16(reader["EmoteID3"]); broadcastText.EmoteDelay = new ushort[3]; broadcastText.EmoteDelay[0] = Convert.ToUInt16(reader["EmoteDelay1"]); broadcastText.EmoteDelay[1] = Convert.ToUInt16(reader["EmoteDelay2"]); broadcastText.EmoteDelay[2] = Convert.ToUInt16(reader["EmoteDelay3"]); broadcastText.UnkEmoteID = Convert.ToUInt16(reader["UnkEmoteID"]); broadcastText.Language = Convert.ToByte(reader["Language"]); broadcastText.Type = Convert.ToByte(reader["Type"]); broadcastText.SoundID = new uint[2]; broadcastText.SoundID[0] = Convert.ToUInt32(reader["SoundID1"]); broadcastText.SoundID[1] = Convert.ToUInt32(reader["SoundID2"]); broadcastText.PlayerConditionID = Convert.ToUInt32(reader["PlayerConditionID"]); if (!DBC.DBC.BroadcastText.ContainsKey(id)) { DBC.DBC.BroadcastText.Add(id, broadcastText); } else { DBC.DBC.BroadcastText[id] = broadcastText; } } } }
/// <summary> /// Gets from `world` database a dictionary of the given struct/class. /// Structs fields type must match the type of the DB columns. /// DB columns names are set by using DBFieldNameAttribute. /// </summary> /// <typeparam name="T">Type of the first element of the list of entries (usually uint)</typeparam> /// <typeparam name="TG">Type of the second element of the list of entries (usually uint)</typeparam> /// <typeparam name="TK">Type of the struct</typeparam> /// <typeparam name="TH"></typeparam> /// <param name="entries">List of entries to select from DB</param> /// <param name="primaryKeyName1">Name of the first primary key</param> /// <param name="primaryKeyName2">Name of the second primary key</param> /// <returns>Dictionary of structs of type TK</returns> public static StoreDictionary <Tuple <T, TG, TH>, TK> GetDict <T, TG, TH, TK>(List <Tuple <T, TG, TH> > entries, string primaryKeyName1, string primaryKeyName2, string primaryKeyName3, string database = null) where T : struct where TG : struct where TH : struct { if (entries.Count == 0) { return(null); } // TODO: Add new config option "Verify data against DB" if (!SQLConnector.Enabled) { return(null); } var tableAttrs = (DBTableNameAttribute[])typeof(TK).GetCustomAttributes(typeof(DBTableNameAttribute), false); if (tableAttrs.Length <= 0) { return(null); } var tableName = tableAttrs[0].Name; var fields = Utilities.GetFieldsAndAttribute <TK, DBFieldNameAttribute>(); fields.RemoveAll(field => field.Item2.Name == null); var fieldCount = 3; var fieldNames = new StringBuilder(); fieldNames.Append(primaryKeyName1 + ","); fieldNames.Append(primaryKeyName2 + ","); fieldNames.Append(primaryKeyName3 + ","); foreach (var field in fields) { fieldNames.Append(field.Item2); fieldNames.Append(","); fieldCount += field.Item2.Count; } // WHERE (a = x1 AND b = y1) OR (a = x2 AND b = y2) OR ... var whereClause = new StringBuilder(); var ji = 0; foreach (var tuple in entries) { ji += 1; whereClause.Append("(") .Append(primaryKeyName1) .Append(" = ") .Append(Convert.ToInt64(tuple.Item1)) .Append(" AND ") .Append(primaryKeyName2) .Append(" = ") .Append(Convert.ToInt64(tuple.Item2)) .Append(" AND ") .Append(primaryKeyName3) .Append(" = ") .Append(Convert.ToInt64(tuple.Item3)) .Append(")"); if (ji != entries.Count) { whereClause.Append(" OR "); } } var query = string.Format("SELECT {0} FROM {1}.{2} WHERE {3}", fieldNames.ToString().TrimEnd(','), database ?? Settings.TDBDatabase, tableName, whereClause); Trace.WriteLine(query); var dict = new Dictionary <Tuple <T, TG, TH>, TK>(entries.Count); using (var reader = SQLConnector.ExecuteQuery(query)) { if (reader == null) { return(null); } while (reader.Read()) { var instance = (TK)Activator.CreateInstance(typeof(TK)); var values = new object[fieldCount]; var count = reader.GetValues(values); if (count != fieldCount) { throw new InvalidConstraintException( "Number of fields from DB is different of the number of fields with DBFieldName attribute"); } var i = 3; foreach (var field in fields) { if (values[i] is DBNull && field.Item1.FieldType == typeof(string)) { field.Item1.SetValueDirect(__makeref(instance), string.Empty); } else if (field.Item1.FieldType.BaseType == typeof(Enum)) { field.Item1.SetValueDirect(__makeref(instance), Enum.Parse(field.Item1.FieldType, values[i].ToString())); } else if (field.Item1.FieldType.BaseType == typeof(Array)) { var arr = Array.CreateInstance(field.Item1.FieldType.GetElementType(), field.Item2.Count); for (var j = 0; j < arr.Length; j++) { var elemType = arr.GetType().GetElementType(); var val = elemType.IsEnum ? Enum.Parse(elemType, values[i + j].ToString()) : Convert.ChangeType(values[i + j], elemType); arr.SetValue(val, j); } field.Item1.SetValueDirect(__makeref(instance), arr); } else if (field.Item1.FieldType == typeof(bool)) { field.Item1.SetValueDirect(__makeref(instance), Convert.ToBoolean(values[i])); } else { field.Item1.SetValueDirect(__makeref(instance), values[i]); } i += field.Item2.Count; } T key1; TG key2; TH key3; if (typeof(T).IsEnum) { key1 = (T)Enum.ToObject(typeof(T), values[0]); } else { key1 = (T)values[0]; } if (typeof(TG).IsEnum) { key2 = (TG)Enum.ToObject(typeof(TG), values[1]); } else { key2 = (TG)values[1]; } if (typeof(TH).IsEnum) { key3 = (TH)Enum.ToObject(typeof(TH), values[2]); } else { key3 = (TH)values[2]; } var key = Tuple.Create(key1, key2, key3); if (!dict.ContainsKey(key)) { dict.Add(key, instance); } } } return(new StoreDictionary <Tuple <T, TG, TH>, TK>(dict)); }