This comes down to what you define as a "good" sound. Sure the effects will work in a DI setup either before the DI input or in an effects loop but you will get the sound which is created by that particular setup. One way to think about the whole situation is The Amp Is An Effect And Adds Color To Your Sound. Plenty of guitar tracks are recorded DI Im sure and with great results at that but you will get a very "DI" sounding guitar. Amp simulators will get you part of the way there and they are getting better as time goes on but they will never completely replace putting an SM57 in front of your favorite Marshall. The main reason I would attribute this too is that when you use over drive or the like you are driving the amplifier and changing the input which in turn changes the sound the amp generates, you will also get some coloration from the amp its self, on top of that the nearly infinite amount of mic positions and angles will add even more versatility and color to a rig.
Is it worth it?
Sure if you want to use an effect that is not available in the DAW you have set up (although there is a plug in for just about everything these days). If you get a sound YOU like then thats all that matters.
overdrive/distortion pedals
I have found in my experience that going DI from OD/Dist leads to a very metallic tin like sound. Over drive in many cases just drives the signal to the point of clipping and when you go DI you can hear more of that detail than pushing it through an amplifier first (which will smooth some of that out).
This also depends what distortion sound you are going for. Its easy to get that kind of modern heavy metal (think metalica/pantera) through a DI style set up even with out an amp sim. But that older, more overdrive sound (think Zeppelin, Hendrix, even Van Halen) you will have more luck with an amp. Some of this is attributed to the fact that early distortion was simply caused by turning the amp all the way up and actually clipping the amplifier its self (this is how EVH says he got his early tone, Marshall Plexi all the way up). In this case no amount of distortion pedals will get you that warm crunch.
Pedal Suggestions
While I don't DI often, I have on occasion when recording really clean guitar tracks. I really like MXR pedals, just in general, they make a great noise gate, compressor and a few solid distortions(Distortion + and Distortion III). The Phase 90 is an all time favorite of mine but be careful when using it paired with hum buckers, the input is notoriously hot and you can get a nice little bit of harmonic distortion if driven from the right guitar.
Other than that there are lots of multi pedal simulation boards out there that I have tinkered with and generally moved away from. They are nice as they provide lots of functionality in a small package but I have always found their tone lacking and somewhat digital.
Bose makes just about every pedal under the sun and they are ok, they offer a nice range at a fair price point but some of there pedals I have found lack tone as well or are overly complex with not enough knobs. The classic boss super overdrive is a great pedal.
Electro Harmonix is a solid name as well and makes some really cool stuff. I am a fan of their big muff pi when it comes to that older fuzz sounds and the small clone is a nice chorus pedal. My only complaint from them is size, I dont know why but their pedals are HUGE. The PI is easily the size of 4 MXR pedals.