A few of my "most frightening gaming moments":
1. Silent Hill 2 - The shining moment of glory for Silent Hill was when the writers tossed all of that cruft about cults and demons and brought the conflict down to a deeply personal level. There is nothing less frightening than some squeaky-voiced idiot in a robe, ranting about demons and the end of the world.
2. Amnesia: The Dark Descent - I might write an article on this later, but I thought this game was amazing.
3. Thief III - The game as a whole wasn't really intended to be a horror game, but Thief III makes it onto this list for that one level. And if you played the game, you know what level I'm talking about.
4. Silent Hill 4: The Room - Most people did not like this game, but parts of it really worked for me. I've always thought that first-person lent itself more readily to fear than third-person, and so I found the apartment sections of the game to be pretty tense. The second half of the game was a chore and not worth sitting though, but those first couple of hours were really satisfying.
5. Half-Life 2 - It's hard for me to believe it now, but the Ravenholm section of the game was genuinely scary the first time through. Now the game feels more like Zombieland, a funhouse ride of zobmie-pulverizing antics. But back when I hadn't mastered the gravity gun, I didn't know what was going to happen next, fast zombies were still new, and I hadn't yet figured out that most of the spooky sounds were just sounds. That was really something.