In many cases, simple database tables are not enough; you need a rowset combining data from several database tables. For example, you may need CustomerID and CustomerName fields where CustomerID belongs to a database table Orders and CustomerName belongs to a database table Customers. It is customary to use SQL statements for this purpose. However, direct use of multi-table SQL statements causes many problems, the data cannot be easily updated, the resulting rowset does not preserve the structure and relations used to obtain it, and so on, not to mention inherent complexities of using SQL.

DataObjects for .NET Express automates this task, as many others. You can create a composite table, specifying database tables it consists of and joins between those tables. Fetching and updating data, DataObjects for .NET Express automatically generates necessary SQL statements, transparently to the developer. Working with data, DataObjects for .NET Express maintains the structure specified in composite table definitions. For example, changing CustomerID will automatically change the related field CustomerName, without any manual coding.

A C1ExpressTable component represents either a simple database table or a composite table. To specify a composite table, select Composite in the DbTableName property combo box or select Composite Table Editor from the context menu.

In the Composite Table Editor, add database tables constituting the composite table. Constituent database tables form a hierarchical structure (a tree). Filling a composite table with data can be represented as performing nested loops over this hierarchy. Each node, a constituent database table, is attached to its parent either by 1-M (one-to-many) or M-1 relation. A one-to many relation represents a child table with independent key, as in Customers à(1-M) Orders. A many-to-one relation represents a main table and a lookup table, as in Orders à(M-1) Customers. The difference is that in 1-M relation there are many child rows for a single parent row, whereas in an M-1 relation there only one child row for each parent row. The constituent table hierarchy satisfies a "non-branching" restriction: only one 1-M-child is allowed for each parent (M-1-relations are not restricted and can branch). Each node of the hierarchy, each constituent database table, except the first one, must be joined to its parent with one or more joins – equalities between a parent and a child field. An example of composite table structure is as follows:

Orders à (1-M) OrderDetails à (M-1) Products à (M-1) Categories

Corresponding SQL statement automatically generated by DataObjects for .NET Express (simplified):


SELECT * FROM ((Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID) INNER JOIN Products ON (OrderDetails.ProductID = Products.ProductID))
INNER JOIN Categories ON (Products.CategoryID = Categories.CategoryID)

By default, M-1 joins in a composite table define a referential integrity (foreign key) constraint. So, for example, in a composite table CustomersOrders it is not allowed to enter CustomerID that is not present in the Customers table. If you want to change this behavior, define a relation in the C1ExpressConnection component with the same joins as used in the composite table and uncheck the Enforce constraints check box. See Defining Relations for more details.