A composite view represents data from multiple joined tables as a single rowset, similar to a view in SQL server. Each view row, or C1ViewRow object, doesn't actually store column values; it only references DataRow objects from underlying base DataTable objects. These view rows are accessible through the C1ViewRow.BaseRows collection. The base DataRow can be retrieved from this collection by an alias of a corresponding DataTable. Note that in the case of an OUTER join it's possible that there is no base DataRow for a certain table; instead, a null value is stored in the C1ViewRow.BaseRows collection for this table alias.
A join between two DataTable objects can have slightly different semantics and is classified in ADO.NET DataExtender as the following:
Relation between the left and right table of a join is one-to-many.
Relation between the left and right table of a join is many-to-one.
We'll call the right table of the Main join the Main table and the right table of the Lookup join the Lookup table. The first table of a view definition statement (which has no left joined table) is always treated as Main table.
The left table cannot have more that one right table with relations to the Main join. If this requirement is violated then the view definition statement is considered incorrectly defined. On the contrary, the left table can have any number of "Lookup" joins with the right tables.
For example, consider a join between the Customers and Orders tables. Note that the relation between Customers and Orders is one-to-many. If you set the view definition as follows:
SELECT * FROM Customers JOIN Orders
then you have the Main join between Customers and Orders. However, if you define it as:
SELECT * Orders FROM JOIN Customers
then you get the Lookup join.
Main/ Lookup table semantics affect the row deletion behavior of C1DataView. When you perform a C1ViewRow deletion, only DataRow objects that correspond to Main tables can be deleted (DataRow objects of Lookup tables are always kept untouched). In general, the right most Main DataRow is always deleted. Suppose that this row is in a DataTable named T. Then a Main DataRow which is left to the already deleted row is investigated if this DataRow has no other related DataRow objects from DataTable T, then this row is deleted as well and this process is repeated for the next left row. Otherwise, if this row has related rows from T, it leaves the row untouched.
When you add a new C1ViewRow, ADO.NET DataExtender can create corresponding base DataRow objects and/or reference the existing ones. By default, new DataRow objects are created. The special case is when you enter a value of a column that represents a foreign key column of base DataTable and a parent DataTable that is referenced by this foreign key is in the set of base DataTables joining by C1DataView. For example, consider the following definition for the C1ViewColumn representing the Orders.CustomerID DataColumn in the view:
SELECT * FROM Customers JOIN Orders
When a value of such a foreign key column is being set, C1DataView first looks whether DataRow with the corresponding primary key value exists in the parent DataTable. If so, the modified C1ViewRow starts to reference those parent DataRow. If not, a new parent DataRow is created and its primary key value is set to equal the entered foreign key value.
If a view definition has at least one INNER join[INNER join means that only matching records present in both views (tables) will appear in the result] , new base DataRow objects will be created for each base DataTable by default. In the case when all joins in the view definition are OUTER[OUTER join means that records of the first view (table) will appear in the result even if there are no matching records in the second view (table)] , a new base DataRow will be created only if you enter a value for a view column representing that base DataRow column. In this case, base rows will be created also for each of the Main DataTable objects which are at the left side for this DataTable.
When you edit an existing C1ViewRow object, the same rules as for row addition apply.