I rather enjoy them. Dawnguard gave us one of the most fleshed-out NPC's in the game, and a pretty sweet variant on the beast form. It also did a pretty good job of "fixing" many of the aspects of vampirism, making it more feasable and desirable.
Hearthfire doesn't bring much to the table in the way of quests, but the smaller pricetag made it worth the expendature for me. I love Winstad Manor. And I love my children. It adds a nice dimension to my character, having a family and a distinct residence.
Dragonborn has a lot of great nostalgia value. And while Miraak's weapons and mask aren't that great (well. . .I do actually like the sword. I have it smithed up to a decent level, and the tentacles make for a nice visual touch), his robes are actually pretty nice. The tentacle explosion enchantment does a decent bit of damage, and the spell/dragon breath absorption is decent. Dragonborn also adds a few really nice spells, dragon shouts, and locations (Apocrypha is GORGEOUS, and I'll fight anyone who says otherwise
).
Besides, if you want to talk about terrible DLC, look no further than Oblivion's five dollar horse armor. It was absolute crap.