Just to add to the muddle of opinions, my (gasp) 6-yr old 2.6Ghz P4 rig with a 7800GS and 1.5GB of RAM regularly runs Left 4 Dead for my friends at about 60FPS. Most settings are turned down, but the funny part is that I can game just fine on it, even in some intense versus. TBH I really think leaving it on a CRT is a saving grace, since there's virtually no lag between the video card and screen, so the situations where frames do dip is partially counteracted by lack of monitor lag.
I will stress heavily though that maxing out your RAM if fiscally possible is a very wise move. L4D runs like crap (on the P4) with less than 1.5GB of RAM and gets noticeably better with 2+. On the rig in the sig, I average 175fps with max settings @1680x1050 and frequently see bumps into the 200fps range. The lowest I've reached since moving to 8GB during gameplay is ~120fps.
Also as expenditures allow, upgrading your GPU will extend the lifetime of your system as a gaming machine. Moving to a GTX285 has been worthwhile because of the resale value of the GTX260 at the time I sold it and the price of the GTX285, in general framerates have increased 25% in most games, and F@H does better as well. There is a point of diminishing returns with GPU upgrades, a friend of mine has hit that point from his move to SLI'd 8800GT's with his X2 4400+ being unable to really feed them. He's going to 8GB himself, and from there it seems that picking up a Phenom is next.
The upgrade cycle is mostly dependent on what you want from your computer. To really figure out how long C2Q's will be relevant, we'd need to know what level of performance you need in games. Someone I know upgrades as soon as he can't play a game at max settings with 70+fps, his brother hasn't upgraded in four something years. About two months ago his brother got a 650i, E6000 series dual core, 4GB RAM, and a 7950GT for about $165. He's been fine with reducing settings, he just needs it to somehow produce smooth framerates. Again though, the RAM became crucial in maintaining smooth gameplay. It was much smoother after moving from 1GB to 4GB in anything, on a bare XP installation.