public List<GroupDB> SearchGroups(string search, int from, int limitPerPage)
{
string groupQuery = "SELECT * from 'group' where REPLACE(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;
}
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;
}
public GroupDB GetGroupById(int id)
{
string query = "SELECT id as Id, name as Name from 'group' where id = ?";
GroupDB group = connection.Query<GroupDB>(query, id).First();
if (group != null)
{
group.ListContactId = GetGroupContactIdList(id);
}
else
{
group = new GroupDB();
}
return group;
}
public bool IsGroupNameValid(string name, int groupId)
{
string query = "SELECT id as Id, name as Name from 'group' where name = ? and id != ?";
bool res = true;
GroupDB group = new GroupDB();
List<GroupDB> list = new List<GroupDB>();
list = connection.Query<GroupDB>(query, name, groupId);
if (list != null && list.Count() > 0)
{
res = false;
}
return res;
}
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;
}