C# multiple connections vs Dataset

Question

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'.

Answer 1

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).

Answer 2

I would probably use a SqlDataAdapter to query both in one pass into a DataSet. Something like this:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM CUSTOMERS WHERE CustomerId = @id; SELECT * FROM ORDERS WHERE CustomerId = @id",connection);
adapter.Fill(dataSet);

Then, I'd set up the relation using something like this:

dataSet.Relations.Add(new DataRelation("relationName", dataSet.Tables[0].Columns["CustomerId"], dataSet.Tables[1].Columns["CustomerId"]);

This way, you only open one connection, you query all the data, and then set up the relationship in memory.