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