C# SqlDatabaseClient.ExecuteQueryTable方法代码示例

        public static void ReloadRewards(SqlDatabaseClient MySqlClient)
        {
            lock (mSyncRoot)
            {
                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT * FROM recycler_rewards");

                int UniqueLevels = 0;

                foreach (DataRow Row in Table.Rows)
                {
                    int RewardLevel = (int)Row["chance_level"];

                    if (!mRewards.ContainsKey(RewardLevel))
                    {
                        mRewards.Add(RewardLevel, new List<uint>());
                        UniqueLevels++;
                    }

                    mRewards[RewardLevel].Add((uint)Row["item_id"]);
                }

                if (UniqueLevels != 5)
                {
                    Output.WriteLine("Recycler is not configured correctly and will *not* work properly. Please ensure there are 5 unique reward levels.", OutputLevel.Warning);
                    mEnabled = false;
                }
                else
                {
                    mEnabled = true;
                }
            }
        }

        public static void Initialize(SqlDatabaseClient MySqlClient)
        {
            mSets = new Dictionary<int, DrinkSet>();

            DataTable Table = MySqlClient.ExecuteQueryTable("SELECT * FROM drink_sets");

            foreach (DataRow Row in Table.Rows)
            {
                int Id = (int)Row["id"];
                string[] DrinkData = Row["drinks"].ToString().Split(',');

                List<int> Drinks = new List<int>();

                foreach (string Drink in DrinkData)
                {
                    int i = 0;
                    int.TryParse(Drink, out i);

                    if (i > 0)
                    {
                        Drinks.Add(i);
                    }
                }

                if (Drinks.Count > 0)
                {
                    mSets.Add(Id, new DrinkSet(Id, Drinks));
                }
            }
        }

        public static void Reload(SqlDatabaseClient MySqlClient)
        {
            int i = 0;

            mUserMessagePresets.Clear();
            mRoomMessagePresets.Clear();
            mUserActionPresetCategories.Clear();
            mUserActionPresetMessages.Clear();

            DataTable BasicPresetTable = MySqlClient.ExecuteQueryTable("SELECT type,message FROM moderation_presets");

            foreach (DataRow Row in BasicPresetTable.Rows)
            {
                string Message = (string)Row["message"];

                switch ((string)Row["type"])
                {
                    case "room":

                        mRoomMessagePresets.Add(Message);
                        break;

                    case "user":

                        mUserMessagePresets.Add(Message);
                        break;
                }

                i++;
            }

            DataTable UserActionCategoryTable = MySqlClient.ExecuteQueryTable("SELECT id,caption FROM moderation_preset_action_categories");

            foreach (DataRow Row in UserActionCategoryTable.Rows)
            {
                mUserActionPresetCategories.Add((uint)Row["id"], (string)Row["caption"]);
                i++;
            }

            DataTable UserActionMsgTable = MySqlClient.ExecuteQueryTable("SELECT id,parent_id,caption,message_text FROM moderation_preset_action_messages");

            foreach (DataRow Row in UserActionMsgTable.Rows)
            {
                uint ParentId = (uint)Row["parent_id"];

                if (!mUserActionPresetMessages.ContainsKey(ParentId))
                {
                    mUserActionPresetMessages.Add(ParentId, new Dictionary<string, string>());
                }

                mUserActionPresetMessages[ParentId].Add((string)Row["caption"], (string)Row["message_text"]);
                i++;
            }

            Output.WriteLine("Loaded " + i + " moderation categories and presets.", OutputLevel.DebugInformation);
        }

 public static void ReloadCache(SqlDatabaseClient MySqlClient)
 {
     mBlockedWords = new List<String>();
     DataTable words = MySqlClient.ExecuteQueryTable("SELECT word FROM wordfilter");
     foreach (DataRow Row in words.Rows)
     {
         if(!mBlockedWords.Contains((string)Row["word"])) {
             mBlockedWords.Add((string)Row["word"]);
         }
     }
 }

        public static void ReloadData(SqlDatabaseClient MySqlClient)
        {
            Dictionary<int, List<PetRaceData>> NewRaceData = new Dictionary<int, List<PetRaceData>>();
            Dictionary<int, List<string>> NewTrickData = new Dictionary<int, List<string>>();

            DataTable RaceTable = MySqlClient.ExecuteQueryTable("SELECT * FROM pet_races");

            foreach (DataRow Row in RaceTable.Rows)
            {
                int PetType = (int)Row["pet_type"];

                if (!NewRaceData.ContainsKey(PetType))
                {
                    NewRaceData.Add(PetType, new List<PetRaceData>());
                }

                NewRaceData[PetType].Add(new PetRaceData((int)Row["data1"], (int)Row["data2"], (int)Row["data3"]));
            }

            DataTable TrickTable = MySqlClient.ExecuteQueryTable("SELECT * FROM pet_tricks");

            foreach (DataRow Row in TrickTable.Rows)
            {
                int PetType = (int)Row["type"];

                if (!NewTrickData.ContainsKey(PetType))
                {
                    NewTrickData.Add(PetType, new List<string>());
                }

                NewTrickData[PetType].Add((string)Row["trick"]);
            }

            lock (mSyncRoot)
            {
                mRaces = NewRaceData;
                mTricks = NewTrickData;
            }
        }

        public static List<uint> GetFriendsForUser(SqlDatabaseClient MySqlClient, uint UserId, int Confirmed)
        {
            List<uint> Friends = new List<uint>();

            MySqlClient.SetParameter("id", UserId);
            MySqlClient.SetParameter("confirmed", Confirmed);
            DataTable Table = MySqlClient.ExecuteQueryTable("SELECT user_2_id FROM messenger_friendships WHERE user_1_id = @id AND confirmed = @confirmed");

            foreach (DataRow Row in Table.Rows)
            {
                Friends.Add((uint)Row[0]);
            }

            return Friends;
        }

        public void ReloadCache(SqlDatabaseClient MySqlClient)
        {
            lock (mSyncRoot)
            {
                mInner.Clear();

                MySqlClient.SetParameter("userid", mUserId);
                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT group_id,level,progress FROM user_achievements WHERE user_id = @userid");

                foreach (DataRow Row in Table.Rows)
                {
                    string Group = (string)Row["group_id"];

                    mInner.Add(Group, new UserAchievement(Group, (int)Row["level"], (int)Row["progress"]));
                }
            }
        }

        public void ReloadCache(SqlDatabaseClient MySqlClient)
        {
            lock (mSyncRoot)
            {
                mInner.Clear();

                MySqlClient.SetParameter("userid", mUserId);
                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT quest_id,progress,is_current FROM user_quests WHERE user_id = @userid");

                foreach (DataRow Row in Table.Rows)
                {
                    uint Id = (uint)Row["quest_id"];

                    mInner.Add(Id, (int)Row["progress"]);

                    if (Row["is_current"].ToString() == "1")
                    {
                        mCurrentQuest = Id;
                    }
                }
            }
        }

        public static void Initialize(SqlDatabaseClient MySqlClient)
        {
            mDefinitions = new Dictionary<uint, ItemDefinition>();

            int Count = 0;
            int Failed = 0;

            DataTable Table = MySqlClient.ExecuteQueryTable("SELECT * FROM item_definitions");

            foreach (DataRow Row in Table.Rows)
            {
                ItemStackingBehavior Behavior = ItemStackingBehavior.Normal;

                switch (Row["stacking_behavior"].ToString().ToLower())
                {
                    case "terminator":

                        Behavior = ItemStackingBehavior.Terminator;
                        break;

                    case "initiator":

                        Behavior = ItemStackingBehavior.Initiator;
                        break;

                    case "ignore":

                        Behavior = ItemStackingBehavior.Ignore;
                        break;

                    case "disable":

                        Behavior = ItemStackingBehavior.InitiateAndTerminate;
                        break;
                }

                ItemWalkableMode WMode = ItemWalkableMode.Never;

                switch (Row["walkable"].ToString())
                {
                    case "1":

                        WMode = ItemWalkableMode.Limited;
                        break;

                    case "2":

                        WMode = ItemWalkableMode.Always;
                        break;
                }

                mDefinitions.Add((uint)Row["id"], new ItemDefinition((uint)Row["id"], (uint)Row["sprite_id"],
                    (string)Row["name"], GetTypeFromString(Row["type"].ToString()),
                    ItemBehaviorUtil.FromString((Row["behavior"].ToString())), (int)Row["behavior_data"], Behavior,
                    WMode, (int)Row["room_limit"], (int)Row["size_x"], (int)Row["size_y"], (float)Row["height"],
                    (Row["allow_recycling"].ToString() == "1"), (Row["allow_trading"].ToString() == "1"),
                    (Row["allow_selling"].ToString() == "1"), (Row["allow_gifting"].ToString() == "1"),
                    (Row["allow_inventory_stacking"].ToString() == "1")));

                Count++;
            }

            Output.WriteLine("Loaded " + Count + " item definition(s)" + (Failed > 0 ? " (" + Failed + " skipped due to errors)" : "") + ".", OutputLevel.DebugInformation);
        }

        public static void LoadTopics(SqlDatabaseClient MySqlClient)
        {
            int i = 0;

            lock (mTopics)
            {
                mTopics.Clear();

                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT id,category,title,body,priority FROM help_topics ORDER BY id ASC");

                foreach (DataRow Row in Table.Rows)
                {
                    mTopics.Add((uint)Row["id"], new HelpTopic((uint)Row["id"], (uint)Row["category"], (string)Row["title"],
                        (string)Row["body"], (HelpTopicPriority)((int.Parse(Row["priority"].ToString())))));
                    i++;
                }
            }

            Output.WriteLine("Loaded " + i + " help topic(s).", OutputLevel.DebugInformation);
        }

        public static void LoadBotDefinitions(SqlDatabaseClient MySqlClient)
        {
            mBotDefinitions.Clear();
            mDefinedResponses.Clear();
            mDefinedSpeech.Clear();
            mPetHandlerIndex.Clear();

            DataTable ResponseTable = MySqlClient.ExecuteQueryTable("SELECT bot_id,triggers,responses,response_serve_id FROM bot_responses");

            foreach (DataRow Row in ResponseTable.Rows)
            {
                BotResponse Response = new BotResponse(Row["triggers"].ToString().Split('|').ToList(),
                    Row["responses"].ToString().Split('|').ToList(), (int)Row["response_serve_id"]);

                if (!mDefinedResponses.ContainsKey((uint)Row["bot_id"]))
                {
                    mDefinedResponses.Add((uint)Row["bot_id"], new List<BotResponse>());
                }

                mDefinedResponses[(uint)Row["bot_id"]].Add(Response);
            }

            DataTable SpeechTable = MySqlClient.ExecuteQueryTable("SELECT bot_id,message FROM bots_speech");

            foreach (DataRow Row in SpeechTable.Rows)
            {
                if (!mDefinedSpeech.ContainsKey((uint)Row["bot_id"]))
                {
                    mDefinedSpeech.Add((uint)Row["bot_id"], new List<string>());
                }

                mDefinedSpeech[(uint)Row["bot_id"]].Add((string)Row["message"]);
            }

            MySqlClient.SetParameter("enabled", "1");
            DataTable BotTable = MySqlClient.ExecuteQueryTable("SELECT * FROM bots WHERE enabled = @enabled");

            foreach (DataRow Row in BotTable.Rows)
            {
                BotWalkMode WMode = BotWalkMode.STAND;

                switch ((string)Row["walk_mode"])
                {
                    case "freeroam":

                        WMode = BotWalkMode.FREEROAM;
                        break;

                    case "defined":

                        WMode = BotWalkMode.SPECIFIED_RANGE;
                        break;
                }

                List<Vector2> DefinedPositions = new List<Vector2>();
                string[] DefPosBits = Row["pos_defined_range"].ToString().Split(';');

                foreach (string DefPosBit in DefPosBits)
                {
                    DefinedPositions.Add(Vector2.FromString(DefPosBit));
                }

                Bot Bot = new Bot((uint)Row["id"], (uint)Row["id"], (string)Row["ai_type"], (string)Row["name"],
                    (string)Row["look"], (string)Row["motto"], (uint)Row["room_id"],
                    Vector3.FromString((string)Row["pos_start"]), Vector2.FromString((string)Row["pos_serve"]),
                    DefinedPositions, WMode, (Row["kickable"].ToString() == "1"), (int)Row["rotation"],
                    (mDefinedResponses.ContainsKey((uint)Row["id"]) ? mDefinedResponses[(uint)Row["id"]] : new List<BotResponse>()),
                    (int)Row["effect"], (int)Row["response_distance"], (int)Row["health"], (string)Row["pos_now"]);

                mBotDefinitions.Add((uint)Row["id"], Bot);

                int PetHandler = (int)Row["pet_type_handler_id"];

                if (PetHandler > 0)
                {
                    mPetHandlerIndex.Add(PetHandler, Bot);
                }
            }
        }

        public static void ReloadFlatCategories(SqlDatabaseClient MySqlClient)
        {
            int NumberLoaded = 0;

            lock (mFlatCategories)
            {
                mFlatCategories.Clear();
                mEventSearchQueries.Clear();

                MySqlClient.SetParameter("enabled", "1");
                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT * FROM flat_categories WHERE enabled = @enabled ORDER BY order_num ASC");

                foreach (DataRow Row in Table.Rows)
                {
                    mFlatCategories.Add(new FlatCategory((int)Row["id"], (Row["visible"].ToString() == "1"),
                        (string)Row["title"], (Row["allow_trading"].ToString() == "1")));
                    NumberLoaded++;
                }

                DataTable EventQueries = MySqlClient.ExecuteQueryTable("SELECT * FROM navigator_event_search_categories");

                foreach (DataRow Row in EventQueries.Rows)
                {
                    mEventSearchQueries.Add(Row["query"].ToString().ToLower(), (int)Row["category_id"]);
                }
            }

            Output.WriteLine("Loaded " + NumberLoaded + " flat " + ((NumberLoaded != 1) ? "categories" : "category") + ".", OutputLevel.DebugInformation);
        }

        public void ReloadCache(SqlDatabaseClient MySqlClient)
        {
            lock (mSyncRoot)
            {
                mInner.Clear();

                MySqlClient.SetParameter("userid", mUserId);
                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT tab_id,item_id FROM new_items WHERE user_id = @userid");

                foreach (DataRow Row in Table.Rows)
                {
                    MarkNewItem(null, (int)Row["tab_id"], (uint)Row["item_id"], false);
                }
            }
        }

        public static void ReloadCache(SqlDatabaseClient MySqlClient)
        {
            lock (mSyncRoot)
            {
                mCharacterBlacklist.Clear();
                mRemoteAddressBlacklist.Clear();

                MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT * FROM bans WHERE timestamp_expire > @timestamp");

                foreach (DataRow Row in Table.Rows)
                {
                    uint UserId = (uint)Row["user_id"];
                    string RemoteAddr = (string)Row["remote_address"];

                    if (UserId > 0 && !mCharacterBlacklist.Contains(UserId))
                    {
                        mCharacterBlacklist.Add(UserId);
                    }

                    if (RemoteAddr.Length > 0 && !mRemoteAddressBlacklist.Contains(RemoteAddr))
                    {
                        mRemoteAddressBlacklist.Add(RemoteAddr);
                    }
                }
            }
        }

        public void ReloadCache(SqlDatabaseClient MySqlClient, AchievementCache UserAchievementCache)
        {
            Dictionary<int, Badge> EquippedBadges = new Dictionary<int, Badge>();
            List<Badge> StaticBadges = new List<Badge>();
            Dictionary<string, Badge> AchievementBadges = new Dictionary<string, Badge>();
            List<string> IndexCache = new List<string>();

            MySqlClient.SetParameter("userid", mUserId);
            DataTable Table = MySqlClient.ExecuteQueryTable("SELECT badge_id,slot_id,source_type,source_data FROM badges WHERE user_id = @userid");

            foreach (DataRow Row in Table.Rows)
            {
                Badge Badge = RightsManager.GetBadgeById((uint)Row["badge_id"]);

                if (Badge == null)
                {
                    continue;
                }

                string SourceType = Row["source_type"].ToString();
                string SourceData = Row["source_data"].ToString();

                Badge BadgeToEquip = null;

                if (SourceType == "static")
                {
                    BadgeToEquip = Badge;
                    StaticBadges.Add(BadgeToEquip);
                }
                else if (SourceType == "achievement")
                {
                    if (AchievementBadges.ContainsKey(SourceData))
                    {
                        continue;
                    }

                    UserAchievement UserAchievement = UserAchievementCache.GetAchievementData(SourceData);

                    if (UserAchievement == null || UserAchievement.Level < 1)
                    {
                        MySqlClient.SetParameter("userid", mUserId);
                        MySqlClient.SetParameter("badgeid", Badge.Id);
                        MySqlClient.ExecuteNonQuery("DELETE FROM badges WHERE user_id = @userid AND badge_id = @badgeid");
                        continue;
                    }

                    string Code = UserAchievement.GetBadgeCodeForLevel();

                    BadgeToEquip = (Badge.Code == Code ? Badge : RightsManager.GetBadgeByCode(Code));
                    AchievementBadges.Add(SourceData, BadgeToEquip);
                }

                if (BadgeToEquip != null)
                {
                    int SlotId = (int)Row["slot_id"];

                    if (!EquippedBadges.ContainsKey(SlotId) && SlotId >= 1 && SlotId <= 5)
                    {
                        EquippedBadges.Add(SlotId, BadgeToEquip);
                    }

                    IndexCache.Add(BadgeToEquip.Code);
                }
            }

            lock (mSyncRoot)
            {
                mEquippedBadges = EquippedBadges;
                mStaticBadges = StaticBadges;
                mAchievementBadges = AchievementBadges;
                mRightsCache = RegenerateRights();
                mIndexCache = IndexCache;
            }
        }