C# multiple connections vs Dataset

Question

Hi

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.

Answer 3

Damn! I'd just bite my hand and use two Connections.

Remember to close 'em and the datareader though.

I dunno why, I just can't bring myself to use DataSet anywhere...!

Answer 4

I'll just expand on Marcs answer. A DataReader can read several result sets at the same time. So you can do the following:

string sql = "SELECT * FROM Customers; SELECT * FROM Orders;";
using (SqlCommand cmd = new SqlCommand(sql, connection))
using (SqlDataReader rd = cmd.ExecuteReader())
{

  while (rd.Read())
  {
    // Read customers
  }

  if (rd.NextResult())  // Change result set to Orders
  {
    while(rd.Read())
    {
      // Read orders
    }

  }
}

You would of course only fetch the data needed, but you get the point. This allows you to get both result sets using one command.