I'll attempt to explain it very briefly.
When going the "disconnected" way, you use a DataAdapter to connect to the database, retrieve the data, and store it in a Dataset. Helper methods are provided for you to filter and sort your data, and saving data back to the database is as easy as asking "please update my data, thanks" :-)
You then work with your datasets stored in the memory, that is with a disconnected version of your data.
You also can do some work very quickly if you use Visual Studio tools because it will generate strongly typed Datasets (example: you have a "Person" table, you'll obtain a "Person" class) with all relationships between tables already configured. Basically, you do very little SQL. There are other benefits such as generating XML documents, or reading data from XML documents into datasets, but I'll leave it to you to read more on that subject.
However, there are some drawbacks... I think that for professional applications it is far from being the right solution, because the generated classes are "heavy" and the memory footprint of datasets is big (very big compared to the data itself).
When going the "connected" way, you use the DataReader and RecordSets. A DataReader is the fastest tool to retrieve data from the database. As long as you are using a DataReader you are connected to the database and you may execute as many "commands" as you want. However, you're on your own as you have to write everyting yourself, that is your SQL queries, your database access layer, your Models/Domain classes, etc.
But of course, you have much more control over what you are doing. It is I guess the preferred way for professional applications... when not working with ORM APIs such as NHibernate for example.
- Advantages: Visual Studio tools (strongly typed classes --> intellisense works), writing code fast, fast refactoring (also with VS tools), nice XML support
- Drawbacks: Slower than the "connected" mode, Heavy memory footprint, "big" generated classes
- Advantages: Performance, Total Control
- Drawbacks: writing code is slower
Add-on: there is also the matter of Linq to SQL, and the Entity Framework... but I can't answer for that as I have only worked a little bit with Linq and not at all with the Entity Framework.