With the possible exception of his incalculably influential A Treatise of Human Nature, this, I think, is Hume's finest work. The Dialogues is a paradigm of sustained philosophical argumentation on a single subject, and I can't think of a more inspiring work of philosophy. Another reason to read this book is that Hume is one of the few philosophical figures whose work is worth reading as literature. His prose is, of course, lovely and clear as can be; and the Dialogues is packed with the sort of evocative passages that readers of Hume except to find in his work. Furthermore, he's clearly mastered the dialogue format as a way of writing philosophy. He never turns his interlocutors into ciphers spouting the details of their respective positions. Each character has a forceful and distinct personality, and each of them comes to the debate with a well-defined position and adequate means of defending it. In short, I can't recommend this book highly enough.
Most of the Dialogues is devoted to discussion of a posteriori arguments for the existence of God. The main argument considered here is the classical argument from design, which Hume seems to understand as an analogical argument of the following sort: the complexity and order of the universe show that it is similar to artifacts created by human intelligences; similar causes have similar effects; therefore, the universe must have been created by a being with something like a human intelligence; therefore, the universe must have been created by God.
Hume's objections to this argument are legion, and many of the individual objections are both ingenious and forceful. He provides reasons for thinking that the universe isn't all that similar to artifacts created by human beings. He argues, for instance, that at least in some respects, the universe resembles animal or vegetable life more than it resembles artifacts created by human beings. Hume also provides for thinking that, even if we think the universe is similar to a human artifact, we ought to think the universe was created by a being quite unlike God. The relevant empirical evidence, he argues, provides us with no good reason to think that the universe wasn't created by multiple beings (large human artifacts are usually created by multiple beings), or that the being(s) who created it are still alive (human creators die), or that the being(s) who created it were infinite (it's not clear that creating the finite universe would have required infinite power), or that the being(s) who created it were morally perfect (the universe, with all its misery and despair, certainly isn't what one would expect from a perfect being). Furthermore, he proposes certain alternative naturalistic explanations of the existence and nature of the universe; and he claims that it's unclear why an appeal to divine creation is to be preferred to these speculative naturalistic stories of the universe's creation.
As I hope this all-too-brief synopsis suggests, Hume's cumulative case against the argument from design is quite impressive. It is, of course, possible to avoid some of these criticisms in various ways, and his speculative naturalistic explanations leave quite a bit to be desired. But the total case is a philosophical demolition par excellence. Indeed, I'm pretty sure that Hume has shown that the argument from design is more or less worthless as support for anything resembling traditional theism. So, if you're enamored of that argument, I suggest you pick up book and wrestle with the criticisms found here.
Now, this isn't all Hume discusses in the Dialogues. There's a section discussing a priori arguments for the existence of God; it focuses on arguments against a version of the cosmological (i.e. first cause) argument. And Hume's arguments concerning the cosmological argument also rule out any sort of ontological argument, as he claims that no sense can be made of the idea of a necessarily existing being. The book also includes a few some brief discussion of particular issues concerning religion.
Where, in the end, does Hume come down on the issue of theism? It's hard to tell, as it's not clear that any of the particular characters speaks for him. Philo, the character who often appears to be speaking for him, never denies the existence of a deity; he simply denies the ability of human reason to discover anything substantial about what such a being is like. That Hume agrees with this is, I think, the most we can glean from this text about Hume's own religious views. It seems clear that he has no sympathy for organized religion, or for any religious views that purport to describe the nature of God, His intentions, or how and why He created the universe as He did. And the only positive religious claim that is given respectful treatment here is the bare claim that we have reason to think that the cause of the universe as a whole is somewhat similar to a human intelligence.
But does acceptance of this minimal thesis amount to his being a theist? Again, it's very hard to tell. First, of course, one might wonder whether this fairly vague positive view is enough to amount to some form of theism. But let's put that issue to one side. Even if it is enough to support some form of theism, it's often difficult to tell whether Hume means to be advocating such a position here. The problem is that it often seems Hume's explicit advocation of this position amounts to little more than a description of what he thinks is an inevitable human tendency to think this way. Given how our minds actually work, he seems to think, we're bound to think something like this about the origin of the universe. Yet it's somewhat unclear that he thinks forming beliefs in this way is reliable. It may simply be that we have a brute instinct to think in a way that insures we'll see the world as resulting from some human-like intelligence, and it's at least not clear that that isn't a debunking account of the plausibility of theism. (For more support that this is a debunking explanation, see his The Natural History of Religion, where the explanations of various religious beliefs certainly seem to be one's that suggest those beliefs simply aren't plausible.)