SQLBasedTablesEasy
------------------
Demonstrates how to create a table based on a SQL SELECT statement or a stored procedure.

This sample demonstrates how to create a table based on a SQL SELECT statement
or a stored procedure (about stored procedures, see below), without manual code,
using ADO.NET DataAdapter. This makes working with SQL-based tables in C1DataObjects
almost as easy as working with bound tables (when DataMode=Bound, DbTableName set to
the 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 C1DataObjects 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.

In the Schema Designer, connect to the database (using Schema|Connect to database
menu item) and add a new simple table setting its name to "Employees".
For the Employees table, set properties:

Connection = Connection
DataMode = SqlBased
SelectCommandText = select * from employees

Note that when you specify SelectCommandText, table Fields are
automatically retrieved (there is also a Retrieve Fields button on the Fields
toolbar). But, unlike with DataMode=Bound (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.

Create a new data set with single Employees table view. Close the Schema Designer.
Attach C1DataSet component to c1SchemaDef.

Attach a C1TableLogic component to table Employees. Select "Create DataAdapter" from
the C1TableLogic's 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 schema connection 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. When asked to specify data connection, if the correct connection string does not
appear in the combo box by default (it only appears there if connection string in your
schema is one of the connection strings existing in Server Explorer), specify a connection
string corresponding to your schema connection (this is needed for the connection
component that is used only at design time, your schema connection will be used
at run time regardless of what you specify here). 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
C1TableLogic.DataAdapter property. Alternatively, you can create a data adapter
component manually and assign it to the C1TableLogic.DataAdapter property.

C1TableLogic.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), C1DataObjects 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. C1DataObjects always uses the mapping
between your table fields and the database fields that you already
have in the schema in the field's DbFieldName property. In fact,
the adapter's TableMappings are automatically updated for you
at design time every time you save the schema with updated field mappings.


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).


