DataSet with multiple levels of related tables

I am trying to use DataSet and DataAdapter to "filter" and "navigate" DataRows in DataTables.

THE SITUATION: I have multiple log开发者_开发知识库ical objects, e.g. Car, Door and Hinge. I am loading a Form which will display complete Car information including each Door and their respective Hinges. In this senario, The form should display info for 1 car, 4 doors and 2 hinges for each door.

Is it possible to use a SINGLE DataSet to navigate this Data? i.e. 1 DataRow in car_table, 4 DataRow in door_table and 8 DataRow in hinge_table, and still be able to navigate correctly between the different object and their relations? AND, able to DataAdapter.Update() easily?

I have read about DataRelation but don't really understand how to use it. Not sure if it is the correct direction for my problem.

If you're looking for an answer towards using DataSets, this won't be it. Instead, I would like to offer an alternative suggestion: If possible, don't use DataSets.

(This is of course a matter of preference, but here's the reason for my suggestion: DataSets, especially when they're of the untyped sort, have the disadvantage of being very generic -- they have no inherent knowledge of what kind of objects are stored inside them. Contrast this with your "logical objects" who have a notion what they are, and what capabilities they might have. Later, when you'll need to maintain your code, such objects will be far easier to understand than an opaque DataSet! Add to this the fact that you seem to already have identified your domain objects and seem to have difficulties with the DataSet in the first place.)

Instead of using a DataSet for your scenario, I would write some data provider classes for these logical objects (Car, Door, and Hinge each get their corresponding data provider class: ICarProvider, IDoorProvider, IHingeProvider) and gather the required data from them when you load your form with data.

var carId = ...;
Car car = carProvider.GetCarById(carId);

You would then delegate loading of the door data to the Car class (since the doors "belong" to the car). Make that class have a collection property, Doors (e.g. of type ICollection<Door>). Inside your Car class, you might load door data as follows:

public ICollection<Door> Doors { get; private set; }

private readonly IDoorProvider doorProvider = ...;

...
this.Doors = doorProvider.GetDoorsOfCar(this);

Likewise, delegate loading of the hinges data to the Door class, since the hinges seem to logically "belong" to a door. The Door class would then have a Hinges property (again, e.g. of type ICollection<Hinge>). Again, your Door class might load hinge data as follows:

public ICollection<Hinge> Hinges { get; private set; }

private readonly IHingeProvider hingeProvider = ...;

...
this.Hinges = hingeProvider.GetHingesOfDoor(this);

When you then load your form, you simply set your controls' values to the corresponding properties of your car object.

P.S.: My answer makes quite a few assumptions about your object model and the relationships between your objects. Feel free to adapt my answer to the object model you've actually got.

P.P.S.: You could even go a step further. For your form, define various user controls that represent one door, or one hinge. They can get their data directly from a Door or Hinge class. Then, create a user control that can initialise itself from an ICollection<Door> (or an ICollection<Hinge>, respectively), and that creates the right number of Door/Hinge child controls inside itself. That way, you should be able to use data binding to load your form data directly from a Car object.

Reply to the first two comments by Jake:

No, I meant something different. What I said was that you can do better than using DataSets. I would personally strive for a solution that does not use DataSets at all. I had something in mind like this:

interface ICarProvider
{
    Car GetCarById(int id);
    ICollection<Car> GetAllCars();
}

public CarProvider : ICarProvider { ... }
// ^ implementation that loads Car objects, e.g. from a relational database

public class Car
{
    public int Id { get; private set; }
    public ICollection<Door> Doors { get; private set; }

    public Car(int id, IDoorProvider doorProvider)
    {
        this.Id = id;
        this.Doors = doorProvider.GetDoorsOfCar(this);
    }
    ...
}

public interface IDoorProvider
{
    ICollection<Door> GetDoorsOfCar(Car car);
    ...
}

public class DoorProvider : IDoorProvider { ... }
// ^ similar to CarProvider, but loads data for Doors instead.

public class Door
{
    public int Id { get; private set; }
    public ICollection<Hinge> Hinges { get; private set; }

    public Door(int id, IHingeProvider hingeProvider)
    {
        this.Id = id;
        this.Hinges = hingeProvider.GetHingesOfDoor(this);
    }
    ...
}

...

You would then data-bind your form controls against these objects directly (via someControlDisplayingCarData.DataSource = someCar;), not against DataSets.

Just add few properties to a Car object, so you will be able to display car information any way you prefer.

partial class Car
{
  public string Doors
  {
     get
     {
        var sb = new StringBuilder();
        foreach(var door in this.Doors)
        {
           sb.Append(door.Name);
        }
     }
     return sb.ToString();
  }

  public string Hinges
  {
     get
     {
        var sb = new StringBuilder();
        foreach(var door in this.Doors)
        {
           foreach(var hinge in door.Hinges)
           {
             sb.Append(hinge.Name);
           }
        }
        return sb.ToString();
     }
   }
}

EDIT: If you wish to be able to modify doors through Car object just add setters to these properties with decoupling logic being reverse to concatenation done in getters.

If you wish to update data to database after changing objects, there are several ways of doing it, yet the use of DataTables limits your possibilities and forces to implement code manually.

You can track changes of objects by implementing INotifyPropertyChanged interface and setting some boolean property storing information if object was modified, you can also store copy of original object and compare it's properties - all this leads to conclusion that you probably will end up implementing your own ORM.

So basically it would be better for you when presentation part requires updating the database to use existing ORM solution for read-write data, LINQ2SQL has a short learning curve.

You could do this with a single dataset, but it depends on whether you are storing complex objects/UD-types or simple related tuples in the db. Here's an example of the latter:

           Car
           id
           door1id FK references Door
           door2id ditto
           door3id ditto
           door4id ditto


           Door
           id
           position (e.g. drivers side front, driver's side rear, passenger-front, passenger-rear
           hinge1id FK references Hinges
           hinge2id ditto


           Hinges
           id
           hingetypeid FK references HingeType


           HingeType
           id
           hingetype

With ADO.NET you could create the dataset and define relations in the client-side disconnected dataset.

The alternative is to define the (nested) types and pass types to the back-end. Manufactured assemblies do go well with OODBMS.