Aside from Spartacus, I haven't seen those other shows. I'm not done yet with the last season, so I can't comment at this point, beyond the fact that with the death of both of his love interests seemed to indicate that he doesn't have much to live for, so his ultimate fate is hardly a surprise. Historically speaking, there's plenty of leeway to invent a twist where he slips off into the sunset, presumed dead, like Rand al'Thor or Bruce Wayne in DKR.

For the above shows, I'd go with Jin & Sayid on the submarine in the last season of Lost. I always felt they kind of got a raw deal as far as their portrayal went, Sayid being made to feel unnecessarily bad about being the killing machine (I have always had a soft spot for characters like that), and Jin being shown as a jerk just because his actions vis a vis his wife could be viewed as mean according to specific societal fads. He might have come across as this strange, incomprehensible jerk, but from his point of view, he was living alone with dozens of people just like that. And he still went around offering ignorant round-eyes free sushi, and would have laid down his life to protect the honor of a cheating unappreciative bitch who was one romantic gesture away from deserting him.

On Buffy and Angel, while I sort of agree, as with Spartacus, I feel that from a narrative standpoint, they had run out of things to do with Joyce and kind of tangled the character arc of Cordelia beyond repair, so they gave each character a dramatic death episode as a way of divesting the show of their baggage, and milking it for the emotional stuff with the hero. The death that bugged me the most on that show was Anya, whom I felt was never really appreciated by the other characters or the writing staff (except in her comic role). When you got right down to it, she was a better Watcher than Giles, and came the furthest in her development of humanity and empathy. Despite her background, she certainly never approached Willow in the moral-lapse department. On the other hand, it was not an emotional moment such as you describe for me, because I was anticipating it with regret, because Joss being Joss, he'd want cannon fodder to be killed to "raise the stakes" and she was the best candidate, because you just knew he wasn't going to lay a finger on his core foursome, or Dawn, by extension, since she was more of a love-interest/off-spring narrative adjunct to Buffy, and had to survive for Buffy to have a happy ending. From a storytelling standpoint Anya's death was shoddily and stupidly done, and her being in a position to get killed was ineptitude on the characters' parts. She might be a badass, but Buffy sucks long and hard at plans.

As for "Angel" while I regretted Cordelia's passing, it was a nice send-off for the character, who had way too much dumped on her on her Buffy days (the pre-Anya, if you will), and was a supernatural punching bag on both shows. I guess it was Fred's death that was the worst, and Wesley's that was the most emotional, for me.

For other shows with significant deaths, I'd go with the following:
MAS*H: the first colonel (Henry Blake, I believe was his name)
The Unit: Hector Williams. His funeral on the following episode was pretty gut-wrenching too.
24: I'd go with Renee Walker, by a very narrow margin on this one. She barely beats out Bill Buchanan, who, as far as I was concerned, was living on borrowed time since the end of the prior season, and at least got a heroic exit; David Palmer, since his was more of an Oh-my-gosh-I-can't-believe-it thing, with the shock value and the subsequent attacks on Tony, Michelle, Chloe & Jack keeping you too busy to feel it. Ditto with Michelle's death shortly after. As for Curtis, he undercut any emotional resonance with his idiotic action that forced Jack to kill him, and Jack's subsequent breakdown was more cumulative than a result of having to kill Curtis. As I noted at the time, he had only spent parts of three widely-separated days in the man's company anyway, and not really enough time to form a real connection. Renee Walker's death, on the other hand, came out of nowhere, she had not spent the season nagging Jack, being a hostage and getting amnesia like Terri, and it snatched a happy ending from Jack at the very last possible moment, even if we KNEW there were still a bunch more episodes to go.

Supernatural: Bobby Singer. Aside from being the most talented actor on the show by a long shot, he was a sort of anchor of plausibility, who lent the antics of the brothers a needed degree of gravitas, and gave them a paternal authority figure they each needed in their own ways. He was also as much of a moral compass as the show has. The Harvelles were a close second, and the later alternate reality where Ellen & Bobby were together added some after-the-fact salt in the wound.

Rome: Lucius Vorenus. The man could NOT catch a break, and there was just something tantalizing at how close he and Pullo came to getting away with everything. The reunion of the two, after all they had endured and their various reconciliations, and slipping into that old team-up that even Julius Ceasar walked warily around, made you think they were invincible. And then for him to go like that... And then end up on Grey's Anatomy... there is no justice in the world.

The Wire: D'Angelo Barksdale. A guy who never really had a chance, and was almost tormented by having just enough self-awareness to realize how trapped he was, and to want more, but too tied down by circumstance to escape his fate. Also, although they were not deaths, the departure of Michael and Dukie's subsequent fall to drugs was almost as bad. Even though both characters survived, when so many others hadn't, by that point in the show, the cyclical repetition of character arcs and the way they echoed the circumstances of Bubbles and Omar made you absolutely certain about the road ahead for each, and the inevitable end of that road, and how impossibly difficult getting off it would be.

Deadwood: Whitney Ellsworth. Aside from another Jim Beaver role, I felt that his character represented Deadwood more than any other. The show itself was about the development of civilization, portraying a growth of an outlaw mining camp of self-sufficient refugees or outcasts from society into a society of its own, eventually culminating in its joining the USA. Ellsworth went from delivering a profane & poetic monologue in the pilot that was a pioneer manifesto on freedom and self-reliance, to a husband and foster father and pillar of the community, due to his general decency and humanity. And his abrupt assassination, done merely to intimidate the townspeople to knuckling under to Hearst, who represented the threat of "civilized" power was so abrupt and hateful that I just spent the rest of the episode hoping someone, anyone would kill Hearst, no matter what. I was actually rooting for him to see through the ruse perpetrated by Swearengen, just to force Al to open him up with his big old knife, and damn the consequences to the camp.

Fringe: Ella. As with Spartacus, I have not finished the last season yet, but her death really cut. Not only had I liked her on short acquaintance, but the scope of her loss to the main characters was pretty rough.

The Shield: Mara & Jackson. A consequence that felt so natural and organic, but not one you anticipated. It was the perfect cap to establishing once and for all just how bad Vic was and how hard and how far he had pushed in his run.

I could go on and on about this one, but those are some that occurred to me just off hand.