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