public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames) { SecUtility.CheckArrayParameter(ref roleNames, true, true, true, 255, "roleNames"); SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames"); #region - T-SQL - StringBuilder strSQL = new StringBuilder(BuildTable(usernames, roleNames)); strSQL.Append(" DELETE dbo.Tech2Type"); strSQL.Append(" FROM @t AS t"); strSQL.Append(" INNER JOIN dbo.SiteRole AS r ON t.rolename = r.RoleName"); strSQL.Append(" INNER JOIN dbo.v_User2ID AS u ON u.Username= t.username"); strSQL.Append(" INNER JOIN dbo.Tech2Type AS t2 ON r.id = t2.TechId AND u.id = t2.TypeId"); strSQL.Append(" WHERE t2.[Type] = 'UsersInRoles'"); #endregion SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL.ToString()); }
public override void AddUsersToRoles(string[] usernames, string[] roleNames) { SecUtility.CheckArrayParameter(ref roleNames, true, true, true, 255, "roleNames"); SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames"); #region - T-SQL - StringBuilder strSQL = new StringBuilder(BuildTable(usernames, roleNames)); strSQL.Append(" INSERT INTO MembersInRoles(RoleId,UserId)"); strSQL.Append(" SELECT r.id,u.UserId FROM @t AS t"); strSQL.Append(" INNER JOIN dbo.SiteRoles AS r ON t.rolename = r.RoleName"); strSQL.Append(" INNER JOIN dbo.Members AS u ON u.Username= t.username"); strSQL.Append(" WHERE NOT EXISTS"); strSQL.Append(" ("); strSQL.Append(" SELECT * FROM dbo.MembersInRoles AS t2"); strSQL.Append(" INNER JOIN dbo.SiteRoles AS r1 ON r1.id = t2.RoleId"); strSQL.Append(" WHERE u.UserId = t2.UserId"); strSQL.Append(" AND r1.GroupName = r.GroupName"); strSQL.Append(" )"); #endregion SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL.ToString()); }