Each C1DataView of a C1DataViewSet can represent data from a single DataTable of an underlying ADO.NET dataset (simple view), or a row set constructed as a join of multiple DataTables (composite view). The latter case is similar to SQL Views, except that the join is constructed against client data.
Once a data source has been specified for C1DataViewSet, you can define C1DataView by assigning a special SQL SELECT-like statement to the Definition property. The formal syntax of this statement is as follows:
SELECT <column list>FROM <table1> [AS <table1_alias>] [ [INNER|OUTER] JOIN <table2> [AS <table2_alias>] [ON <relation_condition>] ]*
WHERE <condition>
To define a view representing data from a single DataTable named Orders, for example, you would use the following statement:
SELECT * FROM Orders
To define a composite view joining data from the Orders and Order Details DataTables, you would use the following statement:
SELECT * FROM Orders JOIN [Order Details]
This is a simplified version of the statement. The full form of this statement can be written as:
SELECT * FROM [Orders] OUTER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
The ON Orders.OrderID = [Order Details].OrderID construction explicitly defines DataRelation connecting the specified DataTables that will be used in a join. Note that expression in the ON clause cannot be an arbitrary one, but it must explicitly map on a join condition of one of the DataRelations connecting the joining DataTables. If this clause is omitted, then an appropriate relation is determined automatically. Note that if there is no relation connecting the tables, then the statement is treated as incorrectly defined.
JOIN, as in a SQL SELECT statement, can be OUTER or INNER, with the same semantics as used in SQL. If this keyword is omitted, JOIN is treated as OUTER. In contrast to SQL, the outer joins in a C1DataView definition are always LEFT, and there is no way to define RIGHT or FULL joins.
You have the option of entering these definition statements manually at design time or you can use the C1DataViewSet's Definition Statement Builder. For more information on using the Definition Statement Builder, see Working with the C1DataView Definition Statement Builder.