Exemple #1
0
        public Session GetSessionBySessionID(string sessionid)
        {
            string sql = "select * from Session where id = @id";

            command             = DBConnection.GetDbConn().CreateCommand();
            command.CommandText = sql;
            command.Parameters.Clear();
            SqlParameter idParam = new SqlParameter("@id", SqlDbType.VarChar, 100);

            idParam.Value = sessionid;
            command.Parameters.Add(idParam);
            command.Prepare();
            IDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                string    id       = reader["id"].ToString();
                IDBPerson dbPerson = new DBPerson();
                Person    person   = dbPerson.GetPersonByID(int.Parse(reader["personid"].ToString()));
                reader.Close();
                return(ObjectBuilder(id, person));
            }
            else
            {
                reader.Close();
                throw new Exception("Empty Query(Session)");
            }
        }
        public List <PersonToHouse> GetAllHouseForPerson(int personid)
        {
            List <PersonToHouse> list = new List <PersonToHouse>();
            string sql = "select * from PersonToHouse where personid = @personid and moveoutdate is null";

            command             = DBConnection.GetDbConn().CreateCommand();
            command.CommandText = sql;
            command.Parameters.Clear();
            SqlParameter personidParam = new SqlParameter("@personid", SqlDbType.Int);

            personidParam.Value = personid;
            command.Parameters.Add(personidParam);
            IDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                IDBPerson dbPerson    = new DBPerson();
                Person    person      = dbPerson.GetPersonByID(int.Parse(reader["personid"].ToString()));
                bool      isowner     = (bool)reader["isowner"];
                DateTime  moveindate  = DateTime.Parse(reader["moveindate"].ToString());
                DateTime? moveoutdate = null;
                int       hid         = (int)reader["houseid"];
                list.Add(ObjectBuilder(person, isowner, moveindate, moveoutdate, hid));
            }
            reader.Close();
            return(list);
        }
        public PersonToHouse GetPersonToHouseByBothId(int personid, int houseid)
        {
            string sql = "select * from PersonToHouse where personid = @personid and houseid = @houseid and moveoutdate is null";

            command             = DBConnection.GetDbConn().CreateCommand();
            command.CommandText = sql;
            command.Parameters.Clear();
            SqlParameter personidParam = new SqlParameter("@personid", SqlDbType.Int);

            personidParam.Value = personid;
            SqlParameter houseidParam = new SqlParameter("@houseid", SqlDbType.Int);

            houseidParam.Value = houseid;
            command.Parameters.Add(personidParam);
            command.Parameters.Add(houseidParam);
            IDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                IDBPerson dbPerson    = new DBPerson();
                Person    person      = dbPerson.GetPersonByID(int.Parse(reader["personid"].ToString()));
                bool      isowner     = (bool)reader["isowner"];
                DateTime  moveindate  = DateTime.Parse(reader["moveindate"].ToString());
                DateTime? moveoutdate = null;
                int       hid         = (int)reader["houseid"];
                reader.Close();
                return(ObjectBuilder(person, isowner, moveindate, moveoutdate, hid));
            }
            else
            {
                reader.Close();
                return(null);
            }
        }
Exemple #4
0
        public List <ChatMessage> GetAllChatMessageInHouse(int houseid)
        {
            List <ChatMessage> list = new List <ChatMessage>();

            try
            {
                string sql = "select * from ChatMessage where houseid = @houseid";
                command             = DBConnection.GetDbConn().CreateCommand();
                command.CommandText = sql;
                command.Parameters.Clear();
                SqlParameter houseidParam = new SqlParameter("@houseid", SqlDbType.Int);
                houseidParam.Value = houseid;
                command.Parameters.Add(houseidParam);
                IDataReader reader   = command.ExecuteReader();
                IDBPerson   dBPerson = new DBPerson();

                while (reader.Read())
                {
                    int      id       = Convert.ToInt32(reader["id"]);
                    string   message  = reader["message"].ToString();
                    DateTime senddate = DateTime.Parse(reader["senddate"].ToString());
                    Person   person   = dBPerson.GetPersonByID(int.Parse(reader["senderid"].ToString()));
                    list.Add(ObjectBuilder(id, message, senddate, person));
                }
                reader.Close();
                return(list);
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
                return(null);
            }
        }
Exemple #5
0
        public MemoList GetMemoListById(int id)
        {
            string sql = "select * from List where id = @id";

            command             = DBConnection.GetDbConn().CreateCommand();
            command.CommandText = sql;
            command.Parameters.Clear();
            SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int);

            idParam.Value = id;
            command.Parameters.Add(idParam);
            IDataReader reader = command.ExecuteReader();

            if (reader.Read())
            {
                int       listid       = int.Parse(reader["id"].ToString());
                IDBPerson dbPerson     = new DBPerson();
                Person    person       = dbPerson.GetPersonByID(int.Parse(reader["personid"].ToString()));
                string    title        = reader["title"].ToString();
                string    descrtiption = reader["description"].ToString();
                reader.Close();
                return(ObjectBuilder(listid, person, title, descrtiption));
            }
            else
            {
                reader.Close();
                return(null);
            }
        }
Exemple #6
0
        public List <MemoList> GetAllListOfUser(int personid)
        {
            List <MemoList> list = new List <MemoList>();
            string          sql  = "select * from List where personid = @personid";

            command             = DBConnection.GetDbConn().CreateCommand();
            command.CommandText = sql;
            command.Parameters.Clear();
            SqlParameter personidParam = new SqlParameter("@personid", SqlDbType.Int);

            personidParam.Value = personid;
            command.Parameters.Add(personidParam);
            IDataReader reader   = command.ExecuteReader();
            IDBPerson   dbPerson = new DBPerson();

            while (reader.Read())
            {
                int    id           = int.Parse(reader["id"].ToString());
                Person person       = dbPerson.GetPersonByID(int.Parse(reader["personid"].ToString()));
                string title        = reader["title"].ToString();
                string descrtiption = reader["description"].ToString();
                list.Add(ObjectBuilder(id, person, title, descrtiption));
            }
            reader.Close();
            return(list);
        }
Exemple #7
0
        public List <Chore> GetChoresForHouse(int houseid)
        {
            List <Chore> list = new List <Chore>();
            string       sql  = "select * from Chore where houseid = @houseid";

            command             = DBConnection.GetDbConn().CreateCommand();
            command.CommandText = sql;
            command.Parameters.Clear();
            SqlParameter houseidParam = new SqlParameter("@houseid", SqlDbType.Int);

            houseidParam.Value = houseid;
            command.Parameters.Add(houseidParam);
            IDataReader reader   = command.ExecuteReader();
            IDBPerson   dBPerson = new DBPerson();

            while (reader.Read())
            {
                int      id          = int.Parse(reader["id"].ToString());
                byte     status      = Convert.ToByte(reader["status"]);
                string   description = reader["description"].ToString();
                DateTime duedate     = DateTime.Parse(reader["duedate"].ToString());
                Person   person;
                if (reader["personid"] == DBNull.Value)
                {
                    person = null;
                }
                else
                {
                    person = dBPerson.GetPersonByID(Convert.ToInt32(reader["personid"]));
                }
                list.Add(ObjectBuilder(id, status, description, duedate, person));
            }
            reader.Close();
            return(list);
        }
Exemple #8
0
        public Chore GetChoreById(int id)
        {
            string sql = "select * from Chore where id = @id";

            command             = DBConnection.GetDbConn().CreateCommand();
            command.CommandText = sql;
            command.Parameters.Clear();
            SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int);

            idParam.Value = id;
            command.Parameters.Add(idParam);
            IDataReader reader   = command.ExecuteReader();
            IDBPerson   dBPerson = new DBPerson();

            if (reader.Read())
            {
                int      choreid     = int.Parse(reader["id"].ToString());
                byte     status      = Convert.ToByte(reader["status"]);
                string   description = reader["description"].ToString();
                DateTime duedate     = DateTime.Parse(reader["duedate"].ToString());
                object   personid    = reader["personid"];
                Person   person;
                if (reader["personid"] == DBNull.Value)
                {
                    person = null;
                }
                else
                {
                    person = dBPerson.GetPersonByID(Convert.ToInt32(reader["personid"]));
                }
                reader.Close();
                return(ObjectBuilder(choreid, status, description, duedate, person));
            }
            else
            {
                reader.Close();
                return(null);
            }
        }