public Character GetCharacterByCharacterId(int characterId)
        {
            DbParameter characterIdParameter = _db.CreateParameter(DbNames.GETCHARACTERBYCHARACTERID_CHARACTERID_PARAMETER, characterId);
            characterIdParameter.DbType = DbType.Int32;

            _db.Open();

            DbDataReader reader = _db.ExcecuteReader(DbNames.GETCHARACTERBYCHARACTERID_STOREDPROC, CommandType.StoredProcedure, characterIdParameter);

            int ordinalCHARACTER_ID = reader.GetOrdinal(DbNames.CHARACTER_ID);
            int ordinalCHARACTER_ACCOUNTID = reader.GetOrdinal(DbNames.CHARACTER_ACCOUNTID);
            int ordinalCHARACTER_NAME = reader.GetOrdinal(DbNames.CHARACTER_NAME);
            int ordinalCHARACTER_CLASS = reader.GetOrdinal(DbNames.CHARACTER_CLASS);
            int ordinalCHARACTER_LEVEL = reader.GetOrdinal(DbNames.CHARACTER_LEVEL);
            int ordinalCHARACTER_MAPID = reader.GetOrdinal(DbNames.CHARACTER_MAPID);
            int ordinalCHARACTER_CURRENTHP = reader.GetOrdinal(DbNames.CHARACTER_CURRENTHP);
            int ordinalCHARACTER_CURRENTMANA = reader.GetOrdinal(DbNames.CHARACTER_CURRENTMANA);
            int ordinalCHARACTER_STRENGTH = reader.GetOrdinal(DbNames.CHARACTER_STRENGTH);
            int ordinalCHARACTER_STAMINA = reader.GetOrdinal(DbNames.CHARACTER_STAMINA);
            int ordinalCHARACTER_DEXTERITY = reader.GetOrdinal(DbNames.CHARACTER_DEXTERITY);
            int ordinalCHARACTER_ENERGY = reader.GetOrdinal(DbNames.CHARACTER_ENERGY);
            int ordinalCHARACTER_X = reader.GetOrdinal(DbNames.CHARACTER_X);
            int ordinalCHARACTER_Y = reader.GetOrdinal(DbNames.CHARACTER_Y);
            int ordinalCHARACTER_FAME = reader.GetOrdinal(DbNames.CHARACTER_FAME);
            int ordinalCHARACTER_MONEY = reader.GetOrdinal(DbNames.CHARACTER_MONEY);
            int ordinalCHARACTER_STATPOINT = reader.GetOrdinal(DbNames.CHARACTER_STATPOINT);
            int ordinalCHARACTER_TRAININGPOINT = reader.GetOrdinal(DbNames.CHARACTER_TRAININGPOINT);
            int ordinalCHARACTER_GAINEDTRAININGS = reader.GetOrdinal(DbNames.CHARACTER_GAINEDTRAININGS);
            int ordinalCHARACTER_TPLEVEL = reader.GetOrdinal(DbNames.CHARACTER_TPLEVEL);
            int ordinalCHARACTER_FIVEELEMENTPOINT = reader.GetOrdinal(DbNames.CHARACTER_FIVEELEMENTPOINT);
            int ordinalCHARACTER_REPULATIONPOINT = reader.GetOrdinal(DbNames.CHARACTER_REPULATIONPOINT);
            int ordinalCHARACTER_CURRENTEXP = reader.GetOrdinal(DbNames.CHARACTER_CURRENTEXP);
            int ordinalCHARACTER_CURRENTFEEXP = reader.GetOrdinal(DbNames.CHARACTER_CURRENTFEEXP);
            int ordinalCHARACTER_REBIRTH = reader.GetOrdinal(DbNames.CHARACTER_REBIRTH);

            Character c = null;

            while (reader.Read())
            {
                byte ClassType = reader.GetByte(ordinalCHARACTER_CLASS);

                switch (ClassType)
                {
                    case 1:
                        c = new Swordman();
                        break;
                    case 2:
                        c = new Mage();
                        break;
                    case 3:
                        c = new Warrior();
                        break;
                    case 4:
                        c = new GhostFighter();
                        break;
                    default:
                        break;
                }

                c.CharacterId = reader.GetInt32(ordinalCHARACTER_ID);
                c.AccountId = reader.GetInt32(ordinalCHARACTER_ACCOUNTID);
                c.Name = reader.GetString(ordinalCHARACTER_NAME);
                c.Class = ClassType; //ofc nn for that
                c.Level = reader.GetInt16(ordinalCHARACTER_LEVEL);
                c.MapId = reader.GetInt32(ordinalCHARACTER_MAPID);
                c.CurrentHp = reader.GetInt32(ordinalCHARACTER_CURRENTHP);
                c.CurrentMana = reader.GetInt32(ordinalCHARACTER_CURRENTMANA);
                c.Strength = reader.GetInt16(ordinalCHARACTER_STRENGTH);
                c.Stamina = reader.GetInt16(ordinalCHARACTER_STAMINA);
                c.Dexterity = reader.GetInt16(ordinalCHARACTER_DEXTERITY);
                c.Energy = reader.GetInt16(ordinalCHARACTER_ENERGY);
                c.Position = new Position();
                c.Position.X = reader.GetInt16(ordinalCHARACTER_X);
                c.Position.Y = reader.GetInt16(ordinalCHARACTER_Y);
                c.Fame = reader.GetInt32(ordinalCHARACTER_FAME);
                c.Money = reader.GetInt32(ordinalCHARACTER_MONEY);
                c.StatPoint = reader.GetInt16(ordinalCHARACTER_STATPOINT);
                c.TrainingPoint = reader.GetInt16(ordinalCHARACTER_TRAININGPOINT);
                c.GainedTrainings = reader.GetInt16(ordinalCHARACTER_GAINEDTRAININGS);
                c.TpLevel = reader.GetInt16(ordinalCHARACTER_TPLEVEL);
                c.FiveElementPoint = reader.GetInt16(ordinalCHARACTER_FIVEELEMENTPOINT);
                c.RepulationPoint = reader.GetInt32(ordinalCHARACTER_REPULATIONPOINT);
                c.CurrentExp = reader.GetInt64(ordinalCHARACTER_CURRENTEXP);
                c.CurrentFEExp = reader.GetInt32(ordinalCHARACTER_CURRENTFEEXP);
                c.Rebirth = reader.GetByte(ordinalCHARACTER_REBIRTH);

            }

            reader.Close();
            _db.Close();

            int ShopID = MakeShop(c.CharacterId); // should always return an id as it creates new shop if theres none existing..
            c.Shop = GetShopByID(ShopID);
            c.Map = mapManager.GetMapById(c.MapId);
            c.ExpToLevel = GetExperienceToLevelByLevel(c.Level);
            c.NegativeExp = GetNegativeExpByLevel(c.Level);
            c.ExpToTraining = GetExperienceToTpByTpLevel(c.TpLevel);
            c.QuickSlots = GetQuickSlotByCharacterId(c.CharacterId);
            c.SkillList = GetAllSkillsByCharacterID(c.CharacterId);
            c.FriendList = GetFriendsByCharacterId(c.CharacterId);

            c.Online = true;
            c.Alive = true;

            var equips = itemDataManager.GetAllItemsInBag(0, c.CharacterId).Cast<Equipment>().ToList();

            //c.Equipments = GetAllItemsInBag(0, c.CharacterId).Cast<Equipment>().ToList();

            var neck = equips.Where(x => x is Necklace);
            var ring = equips.Where(x => x is Ring);
            var head = equips.Where(x => x is Head);
            var body = equips.Where(x => x is Body);
            var feet = equips.Where(x => x is Feet);
            var hand = equips.Where(x => x is Hand);
            var cape = equips.Where(x => x is Cape);
            var charm = equips.Where(x => x.Slot == (byte)Slot.CrystalMirror);
            var mirror = equips.Where(x => x.Slot == (byte)Slot.Mirror);

            c.Necklace = neck.Count() == 0 ? null : neck.First() as Necklace;
            c.Ring = ring.Count() == 0 ? null : ring.First() as Ring;
            c.Head = head.Count() == 0 ? null : head.First() as Head;
            c.Body = body.Count() == 0 ? null : body.First() as Body;
            c.Feet = feet.Count() == 0 ? null : feet.First() as Feet;
            c.Hand = hand.Count() == 0 ? null : hand.First() as Hand;
            c.Cape = cape.Count() == 0 ? null : cape.First() as Cape;
            c.Charm = charm.Count() == 0 ? null : charm.First() as Equipment;
            c.Mirror = mirror.Count() == 0 ? null : mirror.First() as Mirror;

            c.CalculateTotalStats();

            c.Pet = petManager.GetAlivePetByCharacterID(c.CharacterId);
            if (c.Pet != null && !c.Pet.IsSealed)
            {
                c.Pet.Position = new Position();
                c.Pet.Position.X = c.Position.X;
                c.Pet.Position.Y = c.Position.Y;
            }

            c.Guild = guildManager.GetGuildByCharacterID(c.CharacterId);

            return c;
        }
        public List<GuildMember> GetGuildMembersByGuildID(int guildID)
        {
            DbParameter guildIdParameter = _db.CreateParameter(DbNames.GETGUILDMEMBERSBYID_GUILDID, guildID);
            guildIdParameter.DbType = DbType.Int32;

            List<GuildMember> members = new List<GuildMember>();

            _db.Open();

            DbDataReader reader = _db.ExcecuteReader(DbNames.GETGUILDMEMBERSBYID, CommandType.StoredProcedure, guildIdParameter);

            int ordinalGUILDMEMBER_TITLE = reader.GetOrdinal(DbNames.GUILDMEMBER_TITLE);
            int ordinalGUILDMEMBER_RANK = reader.GetOrdinal(DbNames.GUILDMEMBER_RANK);
            int ordinalCHARACTER_ID = reader.GetOrdinal(DbNames.CHARACTER_ID);
            int ordinalCHARACTER_ACCOUNTID = reader.GetOrdinal(DbNames.CHARACTER_ACCOUNTID);
            int ordinalCHARACTER_NAME = reader.GetOrdinal(DbNames.CHARACTER_NAME);
            int ordinalCHARACTER_CLASS = reader.GetOrdinal(DbNames.CHARACTER_CLASS);
            int ordinalCHARACTER_LEVEL = reader.GetOrdinal(DbNames.CHARACTER_LEVEL);
            int ordinalCHARACTER_MAPID = reader.GetOrdinal(DbNames.CHARACTER_MAPID);
            int ordinalCHARACTER_CURRENTHP = reader.GetOrdinal(DbNames.CHARACTER_CURRENTHP);
            int ordinalCHARACTER_CURRENTMANA = reader.GetOrdinal(DbNames.CHARACTER_CURRENTMANA);
            int ordinalCHARACTER_STRENGTH = reader.GetOrdinal(DbNames.CHARACTER_STRENGTH);
            int ordinalCHARACTER_STAMINA = reader.GetOrdinal(DbNames.CHARACTER_STAMINA);
            int ordinalCHARACTER_DEXTERITY = reader.GetOrdinal(DbNames.CHARACTER_DEXTERITY);
            int ordinalCHARACTER_ENERGY = reader.GetOrdinal(DbNames.CHARACTER_ENERGY);
            int ordinalCHARACTER_X = reader.GetOrdinal(DbNames.CHARACTER_X);
            int ordinalCHARACTER_Y = reader.GetOrdinal(DbNames.CHARACTER_Y);
            int ordinalCHARACTER_FAME = reader.GetOrdinal(DbNames.CHARACTER_FAME);
            int ordinalCHARACTER_MONEY = reader.GetOrdinal(DbNames.CHARACTER_MONEY);
            int ordinalCHARACTER_STATPOINT = reader.GetOrdinal(DbNames.CHARACTER_STATPOINT);
            int ordinalCHARACTER_TRAININGPOINT = reader.GetOrdinal(DbNames.CHARACTER_TRAININGPOINT);
            int ordinalCHARACTER_GAINEDTRAININGS = reader.GetOrdinal(DbNames.CHARACTER_GAINEDTRAININGS);
            int ordinalCHARACTER_TPLEVEL = reader.GetOrdinal(DbNames.CHARACTER_TPLEVEL);
            int ordinalCHARACTER_FIVEELEMENTPOINT = reader.GetOrdinal(DbNames.CHARACTER_FIVEELEMENTPOINT);
            int ordinalCHARACTER_REPULATIONPOINT = reader.GetOrdinal(DbNames.CHARACTER_REPULATIONPOINT);
            int ordinalCHARACTER_CURRENTEXP = reader.GetOrdinal(DbNames.CHARACTER_CURRENTEXP);
            int ordinalCHARACTER_CURRENTFEEXP = reader.GetOrdinal(DbNames.CHARACTER_CURRENTFEEXP);
            int ordinalCHARACTER_REBIRTH = reader.GetOrdinal(DbNames.CHARACTER_REBIRTH);

            while (reader.Read())
            {
                Character c = new Character();

                byte ClassType = reader.GetByte(ordinalCHARACTER_CLASS);

                switch (ClassType)
                {
                    case 1:
                        c = new Swordman();
                        break;
                    case 2:
                        c = new Mage();
                        break;
                    case 3:
                        c = new Warrior();
                        break;
                    case 4:
                        c = new GhostFighter();
                        break;
                    default:
                        break;
                }

                c.CharacterId = reader.GetInt32(ordinalCHARACTER_ID);
                c.AccountId = reader.GetInt32(ordinalCHARACTER_ACCOUNTID);
                c.Name = reader.GetString(ordinalCHARACTER_NAME);
                c.Class = ClassType; //ofc nn for that
                c.Level = reader.GetInt16(ordinalCHARACTER_LEVEL);
                c.MapId = reader.GetInt32(ordinalCHARACTER_MAPID);
                c.CurrentHp = reader.GetInt32(ordinalCHARACTER_CURRENTHP);
                c.CurrentMana = reader.GetInt32(ordinalCHARACTER_CURRENTMANA);
                c.Strength = reader.GetInt16(ordinalCHARACTER_STRENGTH);
                c.Stamina = reader.GetInt16(ordinalCHARACTER_STAMINA);
                c.Dexterity = reader.GetInt16(ordinalCHARACTER_DEXTERITY);
                c.Energy = reader.GetInt16(ordinalCHARACTER_ENERGY);
                c.Position = new Position();
                c.Position.X = reader.GetInt16(ordinalCHARACTER_X);
                c.Position.Y = reader.GetInt16(ordinalCHARACTER_Y);
                c.Fame = reader.GetInt32(ordinalCHARACTER_FAME);
                c.Money = reader.GetInt32(ordinalCHARACTER_MONEY);
                c.StatPoint = reader.GetInt16(ordinalCHARACTER_STATPOINT);
                c.TrainingPoint = reader.GetInt16(ordinalCHARACTER_TRAININGPOINT);
                c.GainedTrainings = reader.GetInt16(ordinalCHARACTER_GAINEDTRAININGS);
                c.TpLevel = reader.GetInt16(ordinalCHARACTER_TPLEVEL);
                c.FiveElementPoint = reader.GetInt16(ordinalCHARACTER_FIVEELEMENTPOINT);
                c.RepulationPoint = reader.GetInt32(ordinalCHARACTER_REPULATIONPOINT);
                c.CurrentExp = reader.GetInt64(ordinalCHARACTER_CURRENTEXP);
                c.CurrentFEExp = reader.GetInt32(ordinalCHARACTER_CURRENTFEEXP);
                c.Rebirth = reader.GetByte(ordinalCHARACTER_REBIRTH);

                string title = reader.GetString(ordinalGUILDMEMBER_TITLE);
                GuildOrder rank = (GuildOrder)reader.GetInt32(ordinalGUILDMEMBER_RANK);

                members.Add(new GuildMember(title, rank, c));
            }

            reader.Close();
            _db.Close();

            return members;
        }
        public Character GetCharacterByCharacterId(int characterId)
        {
            DbParameter characterIdParameter = _db.CreateParameter(DbNames.GETCHARACTERBYCHARACTERID_CHARACTERID_PARAMETER, characterId);
            characterIdParameter.DbType = DbType.Int32;

            _db.Open();

            DbDataReader reader = _db.ExcecuteReader(DbNames.GETCHARACTERBYCHARACTERID_STOREDPROC, CommandType.StoredProcedure, characterIdParameter);

            int ordinalCHARACTER_ID = reader.GetOrdinal(DbNames.CHARACTER_ID);
            int ordinalCHARACTER_ACCOUNTID = reader.GetOrdinal(DbNames.CHARACTER_ACCOUNTID);
            int ordinalCHARACTER_NAME = reader.GetOrdinal(DbNames.CHARACTER_NAME);
            int ordinalCHARACTER_CLASS = reader.GetOrdinal(DbNames.CHARACTER_CLASS);
            int ordinalCHARACTER_LEVEL = reader.GetOrdinal(DbNames.CHARACTER_LEVEL);
            int ordinalCHARACTER_MAPID = reader.GetOrdinal(DbNames.CHARACTER_MAPID);
            int ordinalCHARACTER_CURRENTHP = reader.GetOrdinal(DbNames.CHARACTER_CURRENTHP);
            int ordinalCHARACTER_CURRENTMANA = reader.GetOrdinal(DbNames.CHARACTER_CURRENTMANA);
            int ordinalCHARACTER_STRENGTH = reader.GetOrdinal(DbNames.CHARACTER_STRENGTH);
            int ordinalCHARACTER_STAMINA = reader.GetOrdinal(DbNames.CHARACTER_STAMINA);
            int ordinalCHARACTER_DEXTERITY = reader.GetOrdinal(DbNames.CHARACTER_DEXTERITY);
            int ordinalCHARACTER_ENERGY = reader.GetOrdinal(DbNames.CHARACTER_ENERGY);
            int ordinalCHARACTER_X = reader.GetOrdinal(DbNames.CHARACTER_X);
            int ordinalCHARACTER_Y = reader.GetOrdinal(DbNames.CHARACTER_Y);
            int ordinalCHARACTER_FAME = reader.GetOrdinal(DbNames.CHARACTER_FAME);
            int ordinalCHARACTER_MONEY = reader.GetOrdinal(DbNames.CHARACTER_MONEY);
            int ordinalCHARACTER_STATPOINT = reader.GetOrdinal(DbNames.CHARACTER_STATPOINT);
            int ordinalCHARACTER_TRAININGPOINT = reader.GetOrdinal(DbNames.CHARACTER_TRAININGPOINT);
            int ordinalCHARACTER_FIVEELEMENTPOINT = reader.GetOrdinal(DbNames.CHARACTER_FIVEELEMENTPOINT);
            int ordinalCHARACTER_REPULATIONPOINT = reader.GetOrdinal(DbNames.CHARACTER_REPULATIONPOINT);
            int ordinalCHARACTER_CURRENTEXP = reader.GetOrdinal(DbNames.CHARACTER_CURRENTEXP);
            int ordinalCHARACTER_CURRENTFEEXP = reader.GetOrdinal(DbNames.CHARACTER_CURRENTFEEXP);
            int ordinalCHARACTER_REBIRTH = reader.GetOrdinal(DbNames.CHARACTER_REBIRTH);

            Character c = null;

            while (reader.Read())
            {
                byte ClassType = reader.GetByte(ordinalCHARACTER_CLASS);

                switch (ClassType)
                {
                    case 1:
                        c = new Swordman();
                        break;
                    case 2:
                        c = new Mage();
                        break;
                    case 3:
                        c = new Warrior();
                        break;
                    case 4:
                        c = new GhostFighter();
                        break;
                    default:
                        break;
                }

                c.CharacterId = reader.GetInt32(ordinalCHARACTER_ID);
                c.AccountId = reader.GetInt32(ordinalCHARACTER_ACCOUNTID);
                c.Name = reader.GetString(ordinalCHARACTER_NAME);
                c.Class = ClassType; //ofc nn for that
                c.Level = reader.GetInt16(ordinalCHARACTER_LEVEL);
                c.MapId = reader.GetInt32(ordinalCHARACTER_MAPID);
                c.CurrentHp = reader.GetInt32(ordinalCHARACTER_CURRENTHP);
                c.CurrentMana = reader.GetInt32(ordinalCHARACTER_CURRENTMANA);
                c.Strength = reader.GetInt16(ordinalCHARACTER_STRENGTH);
                c.Stamina = reader.GetInt16(ordinalCHARACTER_STAMINA);
                c.Dexterity = reader.GetInt16(ordinalCHARACTER_DEXTERITY);
                c.Energy = reader.GetInt16(ordinalCHARACTER_ENERGY);
                c.X = reader.GetInt16(ordinalCHARACTER_X);
                c.Y = reader.GetInt16(ordinalCHARACTER_Y);
                c.Fame = reader.GetInt32(ordinalCHARACTER_FAME);
                c.Money = reader.GetInt32(ordinalCHARACTER_MONEY);
                c.StatPoint = reader.GetInt16(ordinalCHARACTER_STATPOINT);
                c.TrainingPoint = reader.GetInt16(ordinalCHARACTER_TRAININGPOINT);
                c.FiveElementPoint = reader.GetInt16(ordinalCHARACTER_FIVEELEMENTPOINT);
                c.RepulationPoint = reader.GetInt32(ordinalCHARACTER_REPULATIONPOINT);
                c.CurrentExp = reader.GetInt64(ordinalCHARACTER_CURRENTEXP);
                c.CurrentFEExp = reader.GetInt32(ordinalCHARACTER_CURRENTFEEXP);
                c.Rebirth = reader.GetByte(ordinalCHARACTER_REBIRTH);

            }

            reader.Close();
            _db.Close();

            var equips = itemDataManager.GetAllItemsInBag(0, c.CharacterId).Cast<Equipment>().ToList();

            //c.Equipments = GetAllItemsInBag(0, c.CharacterId).Cast<Equipment>().ToList();

            var neck = equips.Where(x => x is Necklace);
            var ring = equips.Where(x => x is Ring);
            var head = equips.Where(x => x is Head);
            var body = equips.Where(x => x is Body);
            var feet = equips.Where(x => x is Feet);
            var hand = equips.Where(x => x is Hand);
            var cape = equips.Where(x => x is Cape);
            var charm = equips.Where(x => x.Slot == (byte)Slot.CrystalMirror);
            var mirror = equips.Where(x => x.Slot == (byte)Slot.Mirror);

            c.Necklace = neck.Count() == 0 ? null : neck.First() as Necklace;
            c.Ring = ring.Count() == 0 ? null : ring.First() as Ring;
            c.Head = head.Count() == 0 ? null : head.First() as Head;
            c.Body = body.Count() == 0 ? null : body.First() as Body;
            c.Feet = feet.Count() == 0 ? null : feet.First() as Feet;
            c.Hand = hand.Count() == 0 ? null : hand.First() as Hand;
            c.Cape = cape.Count() == 0 ? null : cape.First() as Cape;
            c.Charm = charm.Count() == 0 ? null : charm.First() as Equipment;
            c.Mirror = mirror.Count() == 0 ? null : mirror.First() as Equipment;

            c.CalculateTotalStats();

            return c;
        }