public List<GroupDB> SearchGroups(string search, int from, int limitPerPage) { string groupQuery = "SELECT * from 'group' where name like @search " + "order by name asc "; if (limitPerPage != DBParameter.LIMIT_PER_PAGE_NO_LIMIT) { groupQuery += "limit @limit offset @offset"; } string contactIdQuery = "SELECT * from group_contact where group_id = @groupId"; //Create a list to store the result List<GroupDB> list = new List<GroupDB>(); //Open connection if (this.OpenConnection() == true) { //Create Command SQLiteCommand cmd = new SQLiteCommand(groupQuery, connection); //Create a data reader and Execute the command cmd.Parameters.AddWithValue("@search", "%" + search + "%"); if (limitPerPage != DBParameter.LIMIT_PER_PAGE_NO_LIMIT) { cmd.Parameters.AddWithValue("@limit", limitPerPage); cmd.Parameters.AddWithValue("@offset", from); } SQLiteDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list while (dataReader.Read()) { GroupDB group = new GroupDB(); int id = Int32.Parse(dataReader["id"].ToString()); string name = dataReader["name"].ToString(); group.Id = id; group.Name = name; list.Add(group); } //close Data Reader dataReader.Close(); foreach (GroupDB group in list) { // get group contact list SQLiteCommand contactIdComm = new SQLiteCommand(contactIdQuery, connection); contactIdComm.Parameters.AddWithValue("@groupId", group.Id); SQLiteDataReader contactIdReader = contactIdComm.ExecuteReader(); List<int> contactIdList = new List<int>(); while (contactIdReader.Read()) { int contact_id = Int32.Parse(contactIdReader["contact_id"].ToString()); contactIdList.Add(contact_id); } group.ListContactId = contactIdList; contactIdReader.Close(); } //close Connection this.CloseConnection(); } return list; }
private bool IsGroupContainThisContact(GroupDB group) { if (contactId <= 0) { return false; } int count = group.ListContactId.Count(); for (int i = 0; i < count; i++) { if (contactId == group.ListContactId[i]) { return true; } } return false; }
public GroupDB GetGroupById(int id) { string query = "SELECT * from 'group' where id = @id"; string contactIdQuery = "SELECT * from group_contact where group_id = @groupId"; GroupDB group = new GroupDB(); //Open connection if (this.OpenConnection() == true) { //Create Command SQLiteCommand cmd = new SQLiteCommand(query, connection); //Create a data reader and Execute the command cmd.Parameters.AddWithValue("@id", id); SQLiteDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list while (dataReader.Read()) { string name = dataReader["name"].ToString(); group.Id = id; group.Name = name; break; } //close Data Reader dataReader.Close(); // get group contact list SQLiteCommand contactIdComm = new SQLiteCommand(contactIdQuery, connection); contactIdComm.Parameters.AddWithValue("@groupId", id); SQLiteDataReader contactIdReader = contactIdComm.ExecuteReader(); List<int> contactIdList = new List<int>(); while (contactIdReader.Read()) { int contact_id = Int32.Parse(contactIdReader["contact_id"].ToString()); contactIdList.Add(contact_id); } group.ListContactId = contactIdList; contactIdReader.Close(); //close Connection this.CloseConnection(); } return group; }