C# SqlDatabaseClient.ExecuteQueryTable方法代码示例

        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 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 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 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 void ReloadInterstitials(SqlDatabaseClient MySqlClient)
        {
            lock (mInterstitials)
            {
                mInterstitials.Clear();

                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT * FROM interstitials WHERE enabled = '1'");

                foreach (DataRow Row in Table.Rows)
                {
                    mInterstitials.Add((uint)Row["id"], new Interstitial((uint)Row["id"], (string)Row["url"],
                        (string)Row["image"]));
                }
            }
        }

        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 static void RebuildCache(SqlDatabaseClient MySqlClient)
        {
            lock (mRights)
            {
                mRights.Clear();
                mRightSets.Clear();
                mRightsBages.Clear();

                DataTable RankTable = MySqlClient.ExecuteQueryTable("SELECT rank_id,rights_sets,badge_code FROM ranks");
                DataTable RightsTable = MySqlClient.ExecuteQueryTable("SELECT set_id,right_id FROM rights");

                foreach (DataRow Row in RankTable.Rows)
                {
                    List<uint> Sets = new List<uint>();
                    string[] SetBits = ((string)Row["rights_sets"]).Split(',');

                    uint rank = (uint)Row["rank_id"];

                    if (!mRights.ContainsKey(rank))
                    {
                        mRights.Add(rank, new List<int>());
                    }

                    if (!mRightsBages.ContainsKey(rank))
                    {
                        mRightsBages.Add(rank, (String)Row["badge_code"]);
                    }

                    foreach (string SetBit in SetBits)
                    {
                        int Set = 0;

                        int.TryParse(SetBit, out Set);

                        if (Set > 0)
                        {
                            mRights[rank].Add(Set);
                        }
                    }
                }

                foreach (DataRow Row in RightsTable.Rows)
                {
                    uint SetId = (uint)Row["set_id"];

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

                    mRightSets[SetId].Add((string)Row["right_id"]);
                }
            }
        }

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

                MySqlClient.SetParameter("characterid", mCharacterId);
                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT room_id FROM favorites WHERE user_id = @characterid LIMIT " + Navigator.MaxFavoritesPerUser);

                foreach (DataRow Row in Table.Rows)
                {
                    mInner.Add((uint)Row["room_id"]);
                }
            }
        }

        private static List<Item> LoadPlaylist(SqlDatabaseClient MySqlClient, uint SoundManagerId)
        {
            List<Item> List = new List<Item>();

            MySqlClient.SetParameter("soundmanagerid", SoundManagerId);
            DataTable Table = MySqlClient.ExecuteQueryTable("SELECT * FROM items WHERE soundmanager_id = @soundmanagerid ORDER BY soundmanager_order ASC");

            foreach (DataRow Row in Table.Rows)
            {
                Item Item = ItemFactory.CreateFromDatabaseRow(Row);

                if (Item == null)
                {
                    continue;
                }

                List.Add(Item);
            }

            return List;
        }

        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 void ReloadCache(SqlDatabaseClient MySqlClient)
        {
            lock (mSyncRoot)
            {
                mInner.Clear();

                MySqlClient.SetParameter("user_id", mUserId);
                DataTable Table = MySqlClient.ExecuteQueryTable("SELECT ignore_id FROM ignores WHERE user_id = @user_id");

                foreach (DataRow Row in Table.Rows)
                {
                    mInner.Add((uint)Row["ignore_id"]);
                }
            }
        }

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