C# SqlDatabaseClient.SetParameter方法代码示例

        public static bool FriendshipExists(SqlDatabaseClient MySqlClient, uint UserId1, uint UserId2, bool ConfirmedOnly)
        {
            MySqlClient.SetParameter("user1", UserId1);
            MySqlClient.SetParameter("user2", UserId2);
            MySqlClient.SetParameter("confirmed", (ConfirmedOnly ? 0 : 2));

            return (MySqlClient.ExecuteQueryRow("SELECT null FROM messenger_friendships WHERE user_1_id = @user1 AND user_2_id = @user2 AND confirmed != @confirmed OR user_2_id = @user1 AND user_1_id = @user2 AND confirmed != @confirmed LIMIT 1") != null);
        }

 public static void CreateFriendship(SqlDatabaseClient MySqlClient, uint UserId1, uint UserId2, bool Confirmed)
 {
     for (int i = 0; i < (Confirmed ? 2 : 1); i++)
     {
         MySqlClient.SetParameter("user1", (i == 1 ? UserId1 : UserId2));
         MySqlClient.SetParameter("user2", (i == 1 ? UserId2 : UserId1));
         MySqlClient.SetParameter("accepted", (int)(Confirmed ? 1 : 0));
         MySqlClient.ExecuteNonQuery("INSERT INTO messenger_friendships (user_1_id,user_2_id,confirmed) VALUES (@user1,@user2,@accepted)");
     }
 }

        public static void DeleteRoom(SqlDatabaseClient MySqlClient, uint RoomId)
        {
            MySqlClient.SetParameter("id", RoomId);
            MySqlClient.ExecuteNonQuery("DELETE FROM rooms WHERE id = @id LIMIT 1");

            MySqlClient.SetParameter("id", RoomId);
            MySqlClient.ExecuteNonQuery("DELETE FROM navigator_frontpage WHERE room_id = @id");

            RoomInfoLoader.RemoveFromCache(RoomId);
            Navigator.ReloadOfficialItems(MySqlClient);
        }

        public static bool DestroyFriendship(SqlDatabaseClient MySqlClient, uint UserId1, uint UserId2)
        {
            int aff = 0;

            for (int i = 0; i < 2; i++)
            {
                MySqlClient.SetParameter("user1", (i == 1 ? UserId1 : UserId2));
                MySqlClient.SetParameter("user2", (i == 1 ? UserId2 : UserId1));
                aff += MySqlClient.ExecuteNonQuery("DELETE FROM messenger_friendships WHERE user_1_id = @user1 AND user_2_id = @user2 LIMIT 1");
            }

            return (aff > 0);
        }

        public static void BanUser(SqlDatabaseClient MySqlClient, uint UserId, string MessageText, uint ModeratorId, double Length)
        {
            MySqlClient.SetParameter("userid", UserId);
            MySqlClient.SetParameter("reason", MessageText);
            MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
            MySqlClient.SetParameter("timestampex", UnixTimestamp.GetCurrent() + Length);
            MySqlClient.SetParameter("moderator", ModeratorId);
            MySqlClient.ExecuteNonQuery("INSERT INTO bans (user_id,reason_text,timestamp_created,timestamp_expire,moderator_id) VALUES (@userid,@reason,@timestamp,@timestampex,@moderator)");

            lock (mSyncRoot)
            {
                mCharacterBlacklist.Add(UserId);
            }
        }

        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 static CharacterInfo GetCharacterInfo(SqlDatabaseClient MySqlClient, uint CharacterId, uint LinkedClientId, bool IgnoreCache)
        {
            if (SessionManager.ContainsCharacterId(CharacterId))
            {
                Session Session = SessionManager.GetSessionByCharacterId(CharacterId);
                return Session.CharacterInfo;
            }

            if (!IgnoreCache)
            {
                CharacterInfo CachedInfo = TryGetInfoFromCache(CharacterId);

                if (CachedInfo != null)
                {
                    return CachedInfo;
                }
            }

            MySqlClient.SetParameter("id", CharacterId);
            DataRow Row = MySqlClient.ExecuteQueryRow("SELECT * FROM characters WHERE id = @id LIMIT 1");

            if (Row != null)
            {
                return GenerateCharacterInfoFromRow(MySqlClient, LinkedClientId, Row);
            }

            return null;
        }

        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 MarkNewItem(SqlDatabaseClient MySqlClient, int TabId, uint ItemId, bool SynchronizeDatabase = true)
        {
            lock (mSyncRoot)
            {
                if (!mInner.ContainsKey(TabId))
                {
                    mInner[TabId] = new List<uint>();
                }

                mInner[TabId].Add(ItemId);

                if (SynchronizeDatabase)
                {
                    MySqlClient.SetParameter("userid", mUserId);
                    MySqlClient.SetParameter("tabid", TabId);
                    MySqlClient.SetParameter("itemid", ItemId);
                    MySqlClient.ExecuteNonQuery("INSERT INTO new_items (user_id,tab_id,item_id) VALUES (@userid,@tabid,@itemid)");
                }
            }
        }

        public static Pet CreatePet(SqlDatabaseClient MySqlClient, uint UserId, int Type, string Name, int Race)
        {
            MySqlClient.SetParameter("userid", UserId);
            MySqlClient.SetParameter("type", Type);
            MySqlClient.SetParameter("name", Name);
            MySqlClient.SetParameter("race", Race);
            MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());

            string RawId = MySqlClient.ExecuteScalar("INSERT INTO pets (user_id,type,name,race,timestamp) VALUES (@userid,@type,@name,@race,@timestamp); SELECT LAST_INSERT_ID();").ToString();

            uint Id = 0;
            uint.TryParse(RawId, out Id);

            if (Id == 0)
            {
                return null;
            }

            return new Pet(Id, Name, Type, Race, UserId, 0, new Vector3(0, 0, 0), UnixTimestamp.GetCurrent(), 0, 120, 100, 0);
        }

        public void AddOrUpdateData(SqlDatabaseClient MySqlClient, string Group, int Level, int Progress)
        {
            MySqlClient.SetParameter("userid", mUserId);
            MySqlClient.SetParameter("group", Group);
            MySqlClient.SetParameter("level", Level);
            MySqlClient.SetParameter("progress", Progress);

            lock (mSyncRoot)
            {
                if (mInner.ContainsKey(Group))
                {
                    mInner[Group].Level = Level;
                    mInner[Group].Progress = Progress;

                    MySqlClient.ExecuteNonQuery("UPDATE user_achievements SET level = @level, progress = @progress WHERE user_id = @userid AND group_id = @group LIMIT 1");
                }
                else
                {
                    mInner.Add(Group, new UserAchievement(Group, Level, Progress));

                    MySqlClient.ExecuteNonQuery("INSERT INTO user_achievements (user_id,group_id,level,progress) VALUES (@userid,@group,@level,@progress)");
                }
            }
        }

        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 FillCache(SqlDatabaseClient MySqlClient, uint LinkedItemId)
        {
            if (mCache.ContainsKey(LinkedItemId))
            {
                mCache.Remove(LinkedItemId);
            }

            uint IdValue = 0;

            MySqlClient.SetParameter("id", LinkedItemId);
            object Result = MySqlClient.ExecuteScalar("SELECT room_id FROM items WHERE id = @id LIMIT 1");

            if (Result != null)
            {
                IdValue = (uint)Result;
            }

            if (IdValue > 0)
            {
                mCache.Add(LinkedItemId, IdValue);
            }
        }

        public static Item CreateItem(SqlDatabaseClient MySqlClient, uint DefinitionId, uint UserId, string Flags, string FlagsDisplay, double ExpireTimestamp, bool Untradable = false)
        {
            MySqlClient.SetParameter("definitionid", DefinitionId);
            MySqlClient.SetParameter("userid", UserId);
            MySqlClient.SetParameter("flags", Flags);
            MySqlClient.SetParameter("flagsd", FlagsDisplay);
            MySqlClient.SetParameter("untradable", Untradable ? "1" : "0");
            MySqlClient.SetParameter("expiretimestamp", ExpireTimestamp);

            string RawId = MySqlClient.ExecuteScalar("INSERT INTO items (definition_id,user_id,flags,flags_display,untradable,expire_timestamp) VALUES (@definitionid,@userid,@flags,@flagsd,@untradable,@expiretimestamp); SELECT LAST_INSERT_ID();").ToString();

            uint Id = 0;
            uint.TryParse(RawId, out Id);

            if (Id == 0)
            {
                return null;
            }

            return new Item(Id, DefinitionId, UserId, 0, new Vector3(), string.Empty, 0, Flags, Flags, Untradable, 0, 0,
                ExpireTimestamp);
        }

        public void AddOrUpdateData(SqlDatabaseClient MySqlClient, uint QuestId, int Progress, bool ActiveQuest)
        {
            if (ActiveQuest)
            {
                if (mCurrentQuest > 0)
                {
                    MySqlClient.SetParameter("userid", mUserId);
                    MySqlClient.SetParameter("questid", mCurrentQuest);
                    MySqlClient.ExecuteNonQuery("UPDATE user_quests SET is_current = '0' WHERE user_id = @userid AND quest_id = @questid LIMIT 1");
                }

                mCurrentQuest = QuestId;
            }
            else if (mCurrentQuest == QuestId)
            {
                mCurrentQuest = 0;
            }

            MySqlClient.SetParameter("userid", mUserId);
            MySqlClient.SetParameter("questid", QuestId);
            MySqlClient.SetParameter("progress", Progress);
            MySqlClient.SetParameter("iscurrent", ActiveQuest ? "1" : "0");

            lock (mSyncRoot)
            {
                if (mInner.ContainsKey(QuestId))
                {
                    mInner[QuestId] = Progress;
                    MySqlClient.ExecuteNonQuery("UPDATE user_quests SET progress = @progress, is_current = @iscurrent WHERE user_id = @userid AND quest_id = @questid LIMIT 1");
                }
                else
                {
                    mInner.Add(QuestId, Progress);
                    MySqlClient.ExecuteNonQuery("INSERT INTO user_quests (user_id,quest_id,progress,is_current) VALUES (@userid,@questid,@progress,@iscurrent)");
                }
            }
        }