SQLBasedTablesEasy
-------------------------
This sample demonstrates how to create a table based on a SQL SELECT statement or a stored procedure using ADO.NET DataAdapter.

This makes working with SQL-based tables in C1DataExpress almost as
easy as working with bound tables (when DbTableName set to a database
table name).  However, bound tables should be always preferable when
you have a choice. Bound tables are more intimately related to
database tables, so C1DataExpress can support features that are
impossible for SQL-based tables, namely (a) bound tables can be
combined to form a composite table, SQl-based tables cannot be used in
a composite table; (b) bound tables can be used in virtual mode (with
large data sets), SQL-based tables cannot.

Set C1ExpressTable.SelectCommandText = select * from employees

Note that when you specify SelectCommandText, table Fields are
automatically retrieved (there is also a Retrieve Fields button in the Fields editor)
But, unlike with bound tables (where DbTableName property is used),
some property settings in table fields retrieved from the database must be adjusted,
because the database does not return them as needed for a SQL statement (as opposed
to DbTableName), notably, the primary key (without a primary key, your table will have
to be read-only). So, set PrimaryKey=True and AutoIncrement=ClientAndServer for the
EmployeeID field.

Select "Create DataAdapter" from the C1ExpressTable context menu. It will create a data
adapter and a connection component (necessary for the data adapter, the connection component
is used only at design time, your C1ExpressTable or C1ExpressConnection ConnectionString
property will be used at run time instead of the connection component) and start the standard
Microsoft DataAdapter Configuration Wizard (available only for OleDb and SqlServer connections).
Answer the questions in the wizard dialog. If you choose Use SQL Statements,
the SELECT statement you specified in the schema will appear by default.
When the wizard is finished, you have a data adapter component attached to the
C1ExpressTable.DataAdapter property. Alternatively, you can create a data adapter
component manually and assign it to the C1ExpressTable.DataAdapter property.

C1ExpressTable.DataAdapter is used to fill and update the table.
Filling the table, only its SelectCommand property is used, not the
whole data adapter component. The SelectCommand is executed to fetch the
data on filling the data set. Updating modified rows to the
database ("Commit changes" button in the sample), C1DataExpress calls
data adapter's method DbDataAdapter.Update(DataRow[]) for each
modified row (each call uses a single element in the DataRow[] array).

You are not required to set up the adapter's TableMappings property.
It is not used at run time. C1DataExpress always uses the mapping
between your CqExpressTable fields and the database fields that you already
have in your fields' DbFieldName property. In fact, the adapter's TableMappings
are automatically updated for you at design time every time you modify fields
in the Fields Editor.


Using stored procedures
-----------------------

To fill a SQL-based table with data returned by a stored procedure,
set SelectCommandType=StoredProcedure and type the procedure name in the
SelectCommandText property or select it from the dropdown list of stored
procedures in SelectCommandText. When you set SelectCommandText to a procedure
name, the fields returned by the procedure are automatically
retrieved (if the procedure returns a recordset; only procedures returning
a recordset as return value or one of out parameters can be used in
C1DataObjects as well as in ADO.NET).

The rest is the same for stored procedures as for SQL SELECT statements.
When you use a stored procedure as your SelectCommandText
(SelectCommandType=StoredProcedure; in this case your adapter's
SelectCommand will also be the same stored procedure), you can
specify parameter values in the data adapter's SelectCommand property.
Even if you don't use data adapter for update (in which case you can
leave its UpdateCommand/InsertCommand/DeleteCommand properties empty),
you can use it this way to specify parameter values for the stored
procedure you use to fill the table.

Regardless of whether you use a stored procedure or a SQL statement
in SelectCommandText, your data adapter commands for updating
modified rows (data adapter properties UpdateCommand, InsertCommand,
DeleteCommand) can be either SQL statements or stored procedures, as
supported by the data adapter (so you can select Create New Stored
Procedures or Use Existing Stored Procedures in the wizard).


