Numerous C # vs Dataset connections

Question

I am using 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 client objects. Each customer object contains a collection of orders containing a collection of orders.

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

You can have only one DataReader for each connection, correctly. Thus, this would mean that I need one connection for the "SELECT * Customers" reader, and while I repeat through the customer reader, I will need another connection for each "SELECT * Orders WHERE CustomerId_fk = @Id".

My question is: Would you recommend me to use the above method or just a DataSet?

EDIT

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

Answer 1

Actually, your statement ("You can have only one DataReader for each connection") is incorrect; you can enable MARS (several active result sets) by setting up the connection string and the work done; except that you will still have many round trips (n + 1).

I also don't think datasets are an immediate alternative. Personally, I would use two result grids (either from one query or from two queries) and sew them together with the caller.

- LINQ-to-SQL LoadWith<Customer>(c=>c.Orders); (DataLoadOptions). LoadWith n + 1, Customer ( ).

Answer 2

, , SqlDataAdapter DataSet. - :

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

, - :

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

, , , .

Answer 3

. DataReader . , :

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
    }

  }
}

, , . , .

Answer 4

! .

datareader, .

I don’t know why, I just can’t force myself to use the DataSet anywhere ...!