Working with Command Editor

In This Topic

This topic describes how to use command editor for setting SQL commands to the different data access components, like SQLiteDataTable, SQLiteDataAdapter, SQLiteCommand or SQLiteDataSource. To adjust such component, one generally should set up select, insert, update and delete commands, and, if applicable, assign their parameters. The command editor allows doing this in a simple and visual way.

In this topic, we work with an SQLiteDataTable object. The following steps need to be performed to set up SQLiteDataTable commands:

Create an object (e.g., add SQLiteDataTable to the form from the toolbox).
Set up a connection.

An open connection is required for some operations performed in command editor (for example, generation of update commands). If the connection is not initialized, any of such operations will prompt you setting the connection properties. For detailed information on how to establish a connection, see tutorial Logging Onto The Server.
Specify the select command.

Select type of a select command from the Type list box. The select command can be one of the following types - Text or TableDirect.
- Text option means that the specified SQL statement will be used as a command text.
- The StoredProcedure option allows you to select a stored procedure from the procedure list what are available for the current user (i.e., the user specified in the connection).
- The TableDirect option allows you to bind some table from current user's schema to your data object directly. With the All check box selected you can choose a table from all available schemas.
You can use the Preview button to see the result set that will be returned by the select command. The Preview Data window will open:

The Open and Close buttons fill and clear the result set accordingly. If you use the only source table, in the Preview Data window you can also modify data in this table and save the changes with the Update button. Note that update commands are dynamically generated based on the columns mentioned in the select query. Thus if your select command does not include table's primary key, data modification may cause data loss or corruption.
Set the select parameters.

Let the select query have some parameter(s), like the following one:
```
            SELECT * FROM dept WHERE deptno = @pDeptNo
        
```
In this case, when moving to this tab you will be prompted for generating parameters for your data object. You may also add and edit them manually.

This tab allows you to set the following parameter options:
- Common parameter properties. The data type, direction, value and size of parameter can be set in the corresponding controls.
- Parameter Value Editor is available for setting large string values of parameter or values of complex type parameter. To start the editor use the … button.
- Null Value. To set the null value of parameter, check the Null Value check box. The Is Null Mapping check box sets the SourceColumnNullMapping property of the parameter.
- Column Bindings. You may bind some column to parameter's value and use Current, Original, Proposed or Default value of this column (which may be of no use for select commands, but the same options are available for other types of commands). For this purpose, the Source Column text box and the Source Version list box should be used.
Generate commands

Also, Command Editor allows you to generate insert/update/delete commands for your data access component. You can do it on the Command Generator tab. If you work with several database tables, you should select one for which the update commands will be generated with the corresponding combo box.

Select the commands which should be generated with the corresponding check boxes. The insert and update commands can be generated in such way that the assigned values will be returned as output parameters. To enable this mode, select the With refresh SQL check boxes. The Quote names check box specifies whether database objects' names will be quoted.

Click the Get Table Fields button to see the grid with fields for the selected table. In this grid you may check whether particular fields will be Key, Updating and Refreshing.
- For update and delete statement parameter specifying use the Key fields.
- For specifying values that can be set by the generated insert and update commands use the Update checkboxes.
- To select fields that which values will be returned, if the With refresh SQL option is enabled, use Refreshing fields.
Edit update commands

If you want to edit update commands go to the Update Commands tab.

You can specify the SQL statement for each command, delete a redundant command using the Remove button, and tune a command with the Edit button. It opens SQLiteCommandEditor dialog with two pages - CommandText and Parameters.

See Also