Core 2 Duo processors is dinosaur technology....... I could never imagine doing the work I do with such old tech. Editing, Photoshop, I mean anything current from the past 2+ years would rain at snail pace.
Your best bet is to sell asap and replace with a new model.
Hmm. I have 2009 15" MBP with C2D running on newest OS X (10.8.4), I have CS5 loaded onto it. It works just fine. I maxed the RAM at 8gigs which helped a lot, since then, it runs perfectly.
Of course its not super speedy, I have to wait a little longer for Apps to load etc. I do video editing and some light work in AE and its OK so I wouldnt call this "dinosaur technology" - I think PowerPC macs are becoming that.
Being on low end is sometimes hard but I always found pleasure in using my computers for bit longer - I dont think its really good if you buy new one every year unless you're a pro (in that case it makes biggest sense to sell your gear at good price and pay small difference for newest tech).
I'd say hunt down some cheap RAM sticks, stay on Snow Leopard - if you want to go Lion, I'd get SSD for fast swapping. Put your old drive in USB enclosure and use that for some data. That is the cheapest solution that will probably squeeze another 2-3 years out of that machine.
======
on the other hand if the OP feels adventurous, there is a certainly advantage in going mackintosh way. actually if i were to be satisfied with having a machine for basic use (internet, email, iPhoto, ocassional video editing, games on mid settings), i'd get i3 CPU+8G of RAM, integrated GPU on IvyBridge chipsets should be fine, pop your old harddrive in it. You could use migration assistant to transfer your operating system to new SSD drive.
I also think these old iMacs can be used as a screen so you wouldnt have to purchase additional display. there are prebuilt $200 IvyBridge i3 boxes (excluding the SSD of course) that might be good for you.