Yes, you still will notice a big difference due to the much much faster seek times of the SSD. Just this week I installed a Crucial MX100 256GB SSD in my niece's 2008 white Macbook just like yours and there was a very noticeable difference in performance. You will be happy with the upgrade.
As far as Lion, if it has features you want, I would go ahead with it. Snow Leopard is no longer getting security updates from Apple, so there is that to consider.
Thanks, Weaselboy. That's the exact SSD that I ordered for my girlfriend's 2008 Aluminum MacBook

I got myself the 128GB version. After reading some reviews about the 128GB version, I'm glad I did not spring for the extra space (that I don't really need) because it was only after I ordered the SDDs that I realized my MacBook's controller is SATA-1, which will limit speeds to 150 MB/s. The 128GB MX100 uses different NAND that is slower than the NAND in the 256GB MX100. The difference between the two drives might be noticeable on a SATA-2 controller, but not on SATA-1, so I would have paid extra for space I probably would not use and speed I definitely could not use
What I'm wondering now is if it would be preferable to install the SSD in the optical bay. If the PATA interface is ATA/133, then the SATA-1 spot intended for the HDD would theoretically only provide 12% improved speeds (150 MB/s vs. 133 MB/s), and only for BIG reads/writes, so I might not even notice the difference. However, I cannot find detailed specs about the MacBook3,1 motherboard. Aside from that, there may be other factors that could actually slow down the PATA connection if the cable is inferior or the SATA-to-PATA optical bay adapter I ordered is not that fast.
Mostly I'm asking because I read that the Sudden Motion Sensor (SMS) to help protect the HDD is positioned where the SATA-1 spot is, so if I move the HDD to the optical bay, it won't benefit from the SMS. Also, because SSDs are supposed to run cooler than HDDs, I thought it might be better to have the warmer HDD farther away from other warm components on the motherboard.
Maybe I'm just splitting hairs, but I really don't want to test this myself by taking apart my MacBook 2-3 times. Just getting to the optical bay is not going to be very fun. I'd like to do it once and be done with it.
Any ideas about this?
----------
Some superb stories here.
I am confused how BrettApple has Yosemite working - I thought Intel 2 Core Duo models could go as far as Lion (which wasn't a good idea, really struggled, I went back down to Snow Leopard within six months).
Only today I put in a new SuperDrive as my DVD player / burner wasn't working.
Really is in amazing condition.
Only upcoming problem I can see is if iPhone 7, say, isn't supported by 10.6.8 - then not sure how to get all my music on there?
I'm pretty sure any "regular" MacBook model older than the Aluminum (like my Late 2007 White MacBook3,1) cannot go beyond Lion. I read on everymac.com that the (most significant) limiting factor is the graphics chipset. Starting with the Aluminum in 2008 (and other White MacBooks from 2008 or later) the graphics chipsets are sufficient for Mountain Lion, Mavericks, and Yosemite.
I am working on my girlfriend's Aluminum, and it has Yosemite running "okay" at the moment. I'm still waiting for an SSD to replace the HDD, and I may end up bumping her RAM to 8GB. Right now with the HDD and only 2GB RAM it's a bit slow sometimes in Yosemite because it starts paging the HDD. I'm hoping the paging isn't very noticeable with the SSD, but I'd bet bumping it up to 8GB RAM would make everything pretty smooth.