C# multiple connections vs Dataset

cdatabase

I use C# and SQL Server 2005 and I need a recommendation on how to populate my objects.

I have a Customers collection containing a collection of customer objects. Each customer object contains a Orders collection, containing a collection of orders.

I use a public Fetch() method on my Customers collection to populate the customers and their orders.

You can only have one DataReader open per connection, right. So that would mean that I need one connection for the 'SELECT * Customers' reader, and while I iterate through the customers reader I would need another connection for each 'SELECT * Orders WHERE CustomerId_fk = @Id'.

My question : Would you recommend I use the above way or just plain DataSets ?

EDIT

I had 'SELECT * Customers WHERE Id = @Id' Instead of 'SELECT * Customers'.

Best Answer

Actually, your assertion ("You can only have one DataReader open per connection") is incorrect; you can enable MARS (Multiple Active Result Sets) via a tweak to the connection string, and job done; except of course that you'll still have lots of round-trips (n+1).

I also don't think that the immediate alternative is datasets. Personally, I'd use two result grids (either from a single query or 2 queries) and stitch them together back at the caller.

Alternatively, use something like LINQ-to-SQL with LoadWith<Customer>(c=>c.Orders); (DataLoadOptions). Even without LoadWith, it'll do the same n+1 behaviour automatically simply by loading the collection for each Customer (navigation properties are loaded lazily by default).

Related Topic