I first and foremost always play the 'campaign" of a game before I even look at the multiplayer aspect, so I know where you're coming from. Yes, in a lot of ways, the single player component is getting less and less attention in the industry. Then again, there are games like Fallout 3 that you can legitimately put nearly 100 hours into the game, and it's all 100% single player. You really just have to put some research into the games you're buying if you are primarily interested in the campaign/single player aspect these days, to make sure how long it last, what replayability there may or may not be, etc.
What difficulty did you play Halo 3 on? Normal is FAR too simple, Heroic is almost necessary if you're playing solo. It's worth trying again on Heroic if you enjoyed it, as you have more enemies, their AI is considerably better, etc.
LeMaC and I both loved Bioshock, which was entirely single player and lasted quite a while. If you enjoy "horror" type games, Dead Space was 100% single player, lasted almost 20 hours, and was easily one of my favorite games last year. There are a number of solid Role Playing type games that are entirely single player, some good racing games, The Half Life 2 "Orange Box" has a huge amount of material on it, etc.
Metacritic is a great resource for quick access to reviews and overall ratings:
Xbox 360 Game Reviews from Metacritic