Wow, you played the "gaming for over X years card" again - It doesn't mean a thing. I've been gaming for, what, 18-19 years now? Big whoop.
Command & Conquer's failure was due to EA making a massive mistake with regards to EA Westwood, one that I believe EA do regret making. That said, the spinoff independent studio that formed (Petroglyph) hasn't exactly had a world of success either. It wasn't due to the market changing, it was due to corporate screw-ups.
Fable is an exception to the rule in terms of RPG games. Lionhead are a shell of who they once were, possibly (Some might say probably) due to Molyneux being able to speak at all, thus raising expectations and disappointing the customer base. But the Fable series, even during TLC, was never supposed to be hardcore or some bastion of RPG greatness - It's an immature, light hearted fantasy romp.
The move from "hardcore" RPGs is a change in the market. Yes, there are some out there, but even the more "hardcore" series have to adapt. Development costs are getting higher, and development studios are getting bigger, so they need to take more money to make a profit on games - It's easy for many FPS/TPS games, especially from major studios, but not so much for RPG developers because it's not as popular as it was. One good thing studios have going for them is the lack of RPGs, so if they get one out there then it's possibly going to sell more than it would have done because there are so few of them.
I'm not saying whether this is a right or wrong move, but developers (And, more importantly, publishers) want to get the maximum sales out of their product. Do people want a hardcore RPG or a light RPG? If the market suggests light, that's what they do. If you look at the market, where are all the turn-based strategies? They're fading for Real Time Strategies, which have been the predominant style for a fair few years now; Possibly since the days of Age of Empires 1.
I do think there's a hardcore market still out there, and you just need to look at websites like this to see how true it is, but I don't think anyone's willing to truly capitalise on it, and we might see a shift towards Obsidian's New Vegas in that "hardcore" is a setting on RPGs for those who want it, whereas the rest of us (Who were obviously dropped on our heads as children because we can't do BG2) can have a similar experience on a different setting.
Who knows, with New Vegas and Dragon Age's popularity, we might see the market U-Turn a little and go back to slightly more "hardcore" games. Mind you, have you seen Dragon Trainwreck 2? I think that says it all.
Now, if you'll excuse me, I've got some games to play that I can only just manage with my apparent attention span issues, such as KotOR2, and I'm going to enjoy the hell out of them because they're playable and not up their own backside about being "hardcore".