Wow, tough questions, it's hard to narrow it down.
Oldschool, has to be either Baseball Stars or Tecmo Super Bowl. I don't know how many hours of my life were spent on either of those. (Really oldschool, Pepper II on Coleco)
More recent... Resident Evil: Code Veronica. I loved that game, my wife loved watching me play it. Storyline, graphics (at the time), all very immersing. Spawned my interest in the whole series.
Castlevania SoTN, incredible, although it spawned my greatest display of gamer rage. I was over 100% done with it, and planning on finishing it out. Turned it on and my POS Mad Catz Memory Card had nothing on it. It subsequently flew all the way across the house and broke against the wall. I haven't had the heart to restart over, 9 years later.
Online wise, TetriNet and Acrophobia were wonderful. Xbox Live wise, Midtown Madness 3. (As I suck tremendously at FPSers)