Looking back through my games, the only two parts that I can think that have made me cry, was one, when 343 Guilty Spark killed Master Sergeant Avery J. Johnson near the end of Halo 3, it was just a mix of rage and sorrow at the passing of the most badass (and unkillable) Halo character that ever lived. The second is the end of Gears of War 3's Act 3. Dom's sacrifice, after having read the books and played the games, and see him develop as a sympathetic and devoted character, it was just honestly saddening, added with Mad World piano playing at the same time, it was just a great tear-jerker.
And Zeckt, I have to agree, that was just an amazing plot twist.