Yes, that would be a problem, and beginners to object modelling (and object loading) would cause major problems with this. But begginners in anything cause problems. It's not an object vs relational/dataset issue . It's a lack of experience issue. It's also not going to extreme lengths. It may seem challenging if you haven't done it before, but it's very basic.
If you have a customer and they have a number of subscriptions and each subscription is to a service and those subscriptions need to be send out on certain days of the week (this example is from actual system).
The customer db object has a method Load that is called with the db resultset as a parameter and goes through each row and loads the data for the customer, or calls load on the Subscription object and adds that to the SubscriptionList object via the subscription db load method. It also calls the Load method of the SendDays which loads its own data. This gives you an object model instead of a relational model which is easy to manipulate and easier to imagine when thinking about the problems, as it's a logical object model instead of a relational one. And is loaded with a single database call.
I've never needed to open a second connection for a query while another query was running (unless to another database). But more generally, I think this goes back to the first issue, which really was the concept that going to a database shouldn't be done for each part of a problem, it should return all the data needed for the use case scenario in one call - in the same way that any Service Oriented Architecture should avoid chatty calls over a network.
Yeah, its there because it makes life easier. It's not logical to ignore something like that.
My main problem is that it returns relational data rather than in a logical object model. This makes you think in terms of data rather than in terms of behaviour. A system does stuff. It doesn't just hold data. And using data as you do in DataSets means it is harder to understand the conceptual entities in the system because the entities are data tables, not objects with behaviour.
I use data readers if processing large records for batch processin because the cost of creating millions of object is too much of a performance hit. You also can't use datasets for the same reason. For smaller things, I use objects for the above reasons.
I really find that datasets are only useful for very small apps where you don't need to have a conceptual model of the system becuase it's so simple, and where performance is not something you need to think about because a dataset keeps all the data in memory.
Absolutely. Datasets are ideal for simple apps that you want finish and ship super fast.
Btw, excellent point on chatty interfaces. People often talk about chatty interfaces in larger scale architectures, but miss the point that accessing anything on another machien (including the database) is exactly the same.