Scripting with Datasets
Using the scripting interface in Forms, it is possible to work with datasets directly. It is best practice to work with datasets directly rather than access the data via a DataGrid or other control showing the data. The reason for this is the DataGrids and controls showing data are know as the "Presentation Layer" - how the information is presented to the user. The data itself comes from the "Data Access Layer". If in your scripts you keep the logic in the "Presentation Layer" separate to the "Data Layer" it provides a clear distinction between "Presentation logic" and "Business logic" resulting in code or scripts that are easier to read, understand, maintain and improve.
When working in this "Data Layer" the key concepts are:
Assigning a Dataset to a variable
Datasets can be referenced directly on each use, or more simply by assigning then to a variable. For a dataset called "Project" the syntax is:
Referencing a Dataset
dataset = FormRuntime.Datasets["Project"]
This is best placed at the top of the script as a global variable.
Creating a variable for a row within a Dataset
Referencing a Row in a Dataset
datasetRow = dataset.CurrentRow
Common tasks
Specific actions on a dataset achievable through a script include:
- Finding out which row (index) is the current row in a dataset (dataset.CurrentIndex);
- Setting the current row for a dataset to enable navigation via a script (dataset.CurrentIndex = 3 - this sets to forth row as counting starts from 0);
- Adding a new row to a dataset;
- Deleting the current row from a dataset;
- Committing changes made to fields in the current row of a dataset;
- Reverting changes made to fields in the current row of a dataset;
- Changing the dataset column to which a control is bound;
- Changing the dataset to which a DataGrid is bound;
- Finding data in a dataset (returning indices based on search criteria).
Dataset properties, methods and events
You can use scripts to interact with the following Dataset properties (information), methods (actions) and events (flags/triggers indicating that something has happened):
FormRuntime
This is the 'class' in which all the datasets used in a form reside. It contains:
Datasets - the collection (list) of all the datasets available to the form at runtime.
- Individual Datasets within the Datasets collection can be referenced by name or by index (first dataset is index 0), for example FormRuntime.Datasets["Project"] or FormRuntime.Datasets[0]
- the number of Datasets available can be found using FormRuntime.Datasets.Count
- because Datasets is a collection, it can be used in a loop:
Looping through the Datasets
for d in FormRuntime.Datasets:
<add code to perform on each dataset d>
Dataset:
Once you have referenced an individual Dataset, the following information (properties) and actions (methods) are available:
| Name | Type | Description | Example |
|---|---|---|---|
| Add() | Method | Adds a new row, in memory, to the dataset. Fields in the row can then be set after which the Commit() method can be uses to save the new row to the database (Note: the database may respond with an error as a foreign key violation). the Reject method can be used to clear the new row without committing it to he database | dataset.Add() |
|
CanAdd CanCommit CanDelete CanEdit CanReject |
Property | Each of these return True or False as to whether it is permissible to Add/Commit/Delete/Edit/Reject a record in/to the dataset. Use these in if statements before an Add etc. method to avoid an error. |
if dataset.CanAdd: dataset.Add() |
|
CanMoveBack CanMoveFirst CanMoveLast CanMoveNext |
Property |
Each of these return True or False as to whether it is possible to perform the desired move, For example it is not possible to MoveBack when on the first record in a dataset Use these in if statements before a Move method to avoid a redundant action or to enable/disable buttons based on the position of the current row withing the dataset |
if dataset.CanMoveBack: dataset.MoveBack() |
| ColumnNames | Property | Returns an Array containing a list of each column name in the dataset | dataset.ColumnNames[2] #third column #or for name in dataset.ColumnNames: # add code to process for each (name) |
| Commit() | Method | Saves any changes (in memory) to the current row back to the database. Note: the database may respond with an error as a foreign key violation, in which case the row remains unchanged in memory in a uncommitted state | dataset.Commit() |
| Count | Property | Returns the number of rows (records) in the dataset. This, for example could be used in a loop to loop through all rows to perform a search. | for i in range(dataset.Count): # add code for loop here |
| CurrentIndex | Property (read/write) | The index (starting at 0 for first row) of the currently selected row in the dataset. If no row is selected (or the dataset is empty) the value for CurrentIndex is -1. a specific row can be selected by setting the Value of CurrentIndex | dataset.CurrentIndex = 3 |
| CurrentRow | Property (read/write) |
This as a reference to the data in the current row. field values can be set or read. Fields can be referenced by their 'field name' or by index Any row in the dataset can be accessed as read-only using its index (e.g dataset[1][3] is the value in row 2 column 4). Note: the Index includes the selected as well a unselected columns in the Dataset; thus named referencing is recommended. |
currentProjRow = FormRuntime.Datasets["GB_PROJECT"].CurrentRow currentProjRow['DESCRIPTION'] = 'New description' # to access a specific row (read-only) by index use: FormRuntime.Datasets["GB_PROJECT"][4] |
| DatasetName | Property | This returns the name of the dataset (see separate property, TableName, to return the name of the underlying data source) | dataset.DatasetName |
| Delete() | Method | Deletes the current row for the dataset and the underlying database. the delete is subject to any read-only settings or database validation. Note this just deletes the current row, it does not delete all rows in the dataset and does no delete the dataset itself. | dataset.Delete() |
| Id | Property | Returns the internal Micromine Geobank ID (surrogate key) of the dataset | dataset.Id |
|
MoveBack() MoveFirst() MoveLast() MoveNext() |
Method | Changes the CurrentRow based on the action specified. if the action such as MoveNext when on the last row is not possible; the current Row remains the same ( no error is returned) | dataset.MoveNext() |
| Refresh() | Refresh re-retrieves the dataset from the database and/or applies the runtime filter (if auto-refresh id off) the current row (if changed) must be committed or rejected before the dataset is refreshed | dataset.Refresh() | |
| Reject() | The Reject method can be used to clear a new row without committing it to the database or to clear uncommitted changes made to the current row | dataset.Reject() | |
| RowChanged | Event | This Event occurs then the current row in the dataset changes - (either manually or via a script). A python function can be assigned (linked) to this event so that code runs when the event occurs. the function should be defined above the assignment statement (uses '+=' ) and the function must have the standard parameters "(sender,e)" - see example | def OnDatasetRowChanged(sender, e): #add coding here dataset.RowChanged += OnDatasetRowChanged |
| RowFilter | Property (read/write) | Used to set or return the runtime filter for a dataset. Used to apply or remove a filter setting; when changed, the filter is immediately applied if the dataset's runtime "Auto-refresh" option is on | dataset.RowFilter = "[TEXT1] like 'E'+'%'" |
| TableName | Property | Returns the [schema].[object_name] or the underlying database Table/ Source | dataset.TableName |
|
DataRow: Count |
Property | The Count property of the DataRow object returns a count of the number of columns in the underlying database Table/ Source. Note the count includes the selected as well a unselected columns in the Dataset | dataset.CurrentRow.Count |
Technical Reference
The CurrentRow has a RowState property which returns the state of the current row:
| Return Value | Meaning | Description |
|---|---|---|
| 1 | Detached | The row has been created but has not been added to (or is no longer part of) a DataRowCollection. |
| 2 | Unchanged | The row has not changed since AcceptChanges() was last called. |
| 4 | Added | The row has been added to a DataRowCollection and AcceptChanges() has not been called. |
| 8 | Deleted | The row was deleted using the Delete() method of the DataRow. |
| 16 | Modified | The row has been modified and AcceptChanges() has not been called. |
For more information, refer to the: Microsoft .NET API browser: DataRowState Enum
Tip: To determine whether an edited record has been saved or not, a CanReject property of the dataset can be used. If this property returns True, changes to the dataset have not yet been saved.