SQL script view
In the SQL script view you enter the SQL script and define SQL parameters.
The updateable table bar
The updateable table bar has 3 elements:
1) The resultset definitions list. A resultset definition sets instructions for the C# code generator. Resultset definitions are explained in the Recordset settings view topic. The list is refreshed by clicking the [Collect] button.
2) The updateable table list. This is a list of all tables that are referenced in the selected resultset. Columns in a resultset can originate from different tables, for example when the JOIN keyword is used in a SELECT statement.
The selected table name instructs the code generator to create a recordset that can update, insert and delete rows in the selected table.
The first option in the list is "(read-only)". This will generate a read-only style recordset without Recordset.SaveChanges() method.
A table without primary keys cannot be selected as the updateable table. Updating such a table can be done by placing an UPDATE statement directly in the SQL script.
When code generation starts, the recordset is validated. The validator will make sure that all primary key columns for that table are present in the resultset. If not, then a validation error will be displayed, and the code generation process can not start. See the Generate page for more information.
3) The Collect button. Click to execute the SQL script and collect resultset information. The list of resultset definitions will be expanded as needed. If the SQL script produces fewer resultsets as there are definitions, the unused definitions will be displayed struck through. Unused resultset definitions can be removed with the [Cleanup] button in the Recordset settings view.
See Execution of SQL scripts for important information about VenturaSQL studio executing SQL scripts at design time.
SQL script editor
Here you enter the SQL script for the recordset. This is a basic text editor that color codes SQL keywords.
The most common SQL statement to use would be SELECT * FROM. You can also create complex scripts with control-of-flow statements like IF....ELSE, INSERT data directly, EXEC a stored procedure, there are no limits. It only depends on the capabilities of the selected ADO.NET Provider.
The resultsets returned by ADO.NET must be constant. There can not be any difference in the resultsets and columns produced by the SQL script at different moments.
An example of an illegal SQL script:
IF <condition> THEN
SELECT * FROM CUSTOMERS
See resultset consistency in topic Exection of SQL scripts.
The Parameter pane
In the parameter pane you define parameters for the database provider. They are declared in ADO.NET before the SQL script is executed.
New button. Add a new parameter to the list. This opens the Parameter window.
Paste button. Paste the parameter name into the SQL script editor at the current caret position.
Delete button. Remove the selected parameter from the list.
Move up and Move down buttons. Move the selected parameter up or down. The database parameters are also used as parameters for Recordset.ExecSql() method in the generated C# code. Here you change the order of the parameters.
The Design value is explained in the Parameter window.
Double-click an existing parameter to edit it in the Parameter window.