I don't know much about Stamford, specifically, but I love the state. I still have family in both Hartford and New Haven, which are beautiful towns, are you set on Stamford?
I too am from Ct. and am familiar with Stamford but live about 35 minutes away, its a busy city but nice. Not familiar with areas as to what neighborhoods are good or bad though.
Stamford's nice. Easy to get to the train and jump to the City, but head a little bit north and you get a little vibe of back country. I enjoy it, but some people don't.
Stamford is nice, downtown is nothing to cheer about and you certainly will pay a huge premium for real estate there. I would suggest that you look in the towns surrounding Stamford for better values. Fairfield is nice 35 min east, even parts of Norwalk and Darien are nice
The Springdale/Glenbrook area is nice and not as overpriced as the back country areas. It's very close to downtown, and yet still doesn't make you feel like you're living in the middle of a busy city, it is very much more town like than the downtown area.
My brother in law lives in Glenbrook. Nice neighborhood and you can walk to the glenbrook train stop which is a BIG plus. We park at his house and take the train to Grand Central all the time. Pricey down there though.