I've read the first book several times. It's one of the masterpieces of written SF, and one of about half o dozen books that are really essential. I also managed to fight my way through books 2 and 3 (which, despite Herbert's later protestations to the contrary, were never planned until the first book became hugely successful.)
The Brian Herbert/Kevin J. Anderson productions are hackwork, of about the same quality as other licensed fiction, which is to say that they're utter crap. They also undermine a good deal of the continuity that's established in Dune.
The David Lynch movie is very interesting. It has a great cast, great costumes and production design, and good special effects for the time. It adheres slavishly to the book in places, going so far as to include the character's internal dialogue. But equally important scenes in the novel are changed radically or dropped entirely, and a major incongrous plot element (the weirding modules) are added for no sensible reason. The story ends up being a complete mess, but the film is watchable beacuse of its high production quality.
The first SciFi miniseries is also interesting. The screenplay deviates from the book in any number of places, sometimes senselessly, but it's obvious that the writers understood the story much better than those of the Lynch film, and the story remains mostly intact. It's a good (but not stellar) adaptation, but, to be fair, Dune is a book that's almost uniquely difficult to put on screen, so the writers should get a lot of credit.
But the production quality was horrendous. The cast and acting (with two exceptions) are incredibly bad. If your high school drama club ever does Dune, it will be about as well-acted as this. The costumes are also laugh-out-loud funny, and the production design nonexistent. The only decent-looking sets are the palace at Arakeen (and I think that's because they used an actual palace somewhere instead of building a set.)
If you've watched the BBC's productions (say, I, Claudius,) you'll realize that elaborate sets and costumes (or special effects) aren't neccessary to a great production, if the acting and script are good enough. That's not the case with SciFi's Dune - the screenplay is good, but not good enough to compensate for the dreadful production, or good enough to keep them from being an overwhelming distraction while watching it, and the acting, except for Alec Newman and Ian McNeice, is terrible.
As you can tell, I wasn't super pumped about SciFi's follow-up miniseries, Children of Dune. The funny part is that just about everything that Frank Herbert's Dune did wrong, Children of Dune fixed. The sceenplay was tightened up, the bad actors were recast, the costumes redesigned, the sets imrpoved. Children of Dune is far better than its predecessor - the worst thing about it is the fact that the first one wasn't done this well. It's actually better than the two books it's based on.