Dawnguard is the only DLC I deleted because I hate the vampire attacks and the main quest is tedious as sh!t and I never intend to go through all that bollocks with the vampire princess again. Dawnguard does offer alternative storylines depending on whether you choose to side with the vampires or vampire hunters, but you still have to go through the same major quest, and it's all totally unrelated to everything else that's going on in Skyrim (you know, like civil wars and dragons) and does nothing for my role-play style except ruin it by trying to force me into pursuing an irrelevant vampire quest. Yes, there are some fun features if you're a casual gamer, including crossbows and different types of explosive bolts and dogs and trolls that can be used as followers and spells and items that work well against undead enemies, but ultimately I'm glad to be rid of it. Try it out as a Halloween decoration and see if you like it.
Dragonborn is much better because it actually goes into the history of the Dragonborn and ancient Nord culture and lets you explore some Dunmer culture and there are some great new NPCs to meet. and of course, lots of new shouts and powers to gain. Only problem is there's not much reason to head back there if you start another character, there's not many things you can play out differently, no new factions to join or alternative storyline events. But again, good for casual gaming, and Dragonborn doesn't force itself on you as badly as Dawnguard does.
Hearthfire is my fave DLC, it doesn't offer much to PC gamers with their fancy mods, but us lowly console gamers get to enjoy having new customizable houses that can enhance our role-playing and some new housecarls (I do love my followers) and the option to recruit stewards and grow your own ingredients - a must for alchemy fans, or if you fancy taking a break from adventuring to do some farming and cooking and raising a family. Ultimately Hearthfire is what I want in a DLC - added features, not whole new quests because Skyrim already has enough quests and dungeons and factions as it is.