private async Task GetByUsers(SqlConnection connection1, SearchByIdModel model, GroupsByIdModel data)
{
SqlCommand cmd = new SqlCommand();
string cmdString = "select a.id, a.email, u.firstName + ' ' + u.LastName as name, g.ischecked from AspNetUsers a " +
"left join users u on a.id = u.id " +
"left join usersgroups g on g.idgroup = @id and g.iduser = u.id " +
"left join AspNetUserRoles ur on a.id = ur.UserId " +
"left join AspNetRoles r on ur.RoleId = r.id " +
"where a.LockoutEnabled = 0 and r.name is null " +
"order by name";
cmd.CommandText = cmdString;
cmd.Connection = connection1;
SqlParameter parameter = new SqlParameter("@id", SqlDbType.VarChar);
parameter.Value = model.id;
cmd.Parameters.Add(parameter);
SqlDataReader reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
GroupsUsersByIdGroupsDetailsModel details = new GroupsUsersByIdGroupsDetailsModel();
details.id = reader.GetString(0);
details.name = reader.GetString(1);
if (!reader.IsDBNull(2))
{
details.name = reader.GetString(2).Trim();
}
if (reader.IsDBNull(3))
{
details.ischecked = false;
details.isEdit = 0;
}
else
{
details.ischecked = reader.GetBoolean(3);
details.isEdit = 1;
}
data.users.Add(details);
}
reader.Close();
}
private async Task GetByGroups(SqlConnection connection1, SearchByIdModel model, UsersByIdModel data)
{
SqlCommand cmd = new SqlCommand();
string cmdString = "select m.id, m.name, g.ischecked from groups m " +
"left join usersgroups g on m.id = g.idgroup and g.iduser = @id " +
"left join users gr on g.iduser = gr.id and gr.id = @id " +
"where m.status = 1 " +
"order by m.name";
cmd.CommandText = cmdString;
cmd.Connection = connection1;
SqlParameter parameter = new SqlParameter("@id", SqlDbType.VarChar);
parameter.Value = model.id;
cmd.Parameters.Add(parameter);
SqlDataReader reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
GroupsUsersByIdGroupsDetailsModel details = new GroupsUsersByIdGroupsDetailsModel();
details.id = reader.GetString(0);
details.name = reader.GetString(1);
if (reader.IsDBNull(2))
{
details.ischecked = false;
details.isEdit = 0;
}
else
{
details.ischecked = reader.GetBoolean(2);
details.isEdit = 1;
}
data.groups.Add(details);
}
reader.Close();
}
