Scripting with Data

Using the code panel in Flow, it is possible to work with data directly rather than access it via the Winform controls that show the data.

Assigning Data to a Variable

Data can be referenced directly on each use, or more simply by assigning them to a variable. Scripts can be entered for the Flow using the Code panel in the designer.

In the example displayed, our data is titled testItems- as shown in lines 5 and 10.

Copy 
namespace Flow
{
    public partial class MainForm : Form
    {
        private DataTable testItems;
        
        public MainForm()
        {
            InitializeComponent();
            testItems = test_Table.DataSet.Tables[0];

  • Line 5 creates a property (variable) which can be used in your code to access the data.

  • Line 10 sets this property to the data from a _table or _query object.

For your own designs, you would add the references to your objects in place of testItems.

Common tasks

Specific actions on a dataset achievable through a script include:

  • Finding out which row (index) is the current row in a dataset;
  • Setting the current row for a dataset to enable navigation via a script;
  • 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 DataGridView 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:

Dataset:

Once you have referenced an individual Dataset, a number of properties, methods and events are available for the following objects:

  • _Dataset - The referenced dataset for the design.

  • _Table / _Query - A table is a database object containing data organised in rows and columns. Queries are objects which request information from the tables in a database.

  • _BindingSource - The data source for the Flow from the specified dataset, associating data with controls on a form.

  • DataTable - the variable to which the data has been assigned.

The following table lists the properties, methods and events that can be used with the associated objects, as well as a simple example for each.

Object Name Type Description Example
DataTable.Rows Add() Method Adds a new row, in memory, to the dataset. Fields in the row can then be set after which the Save() method can be used to save the new row to the database. The Reject method can be used to clear the new row without committing it to the database.

Note: the database may respond with an error as a foreign key violation. 		testItems.Rows.Add(newRow);
_BindingSource IsReadOnly Property The IsReadOnly property can be True or False to determine whether it is permissible to Add/Commit/Delete/Edit/Reject a record in/to the dataset.

Use the property in an if statements before an Add etc. method to avoid an error.		 if (!test_BindingSource.IsReadOnly) { ...}
DataTable Columns Property Returns an Array containing a list of each column name in the dataset.  testItems.Columns[0]
_Table Save() 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.		 test_Table.Save()
_BindingSource 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. testBindingSource.Count
_BindingSource Position 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. testBindingSource.Position
DataTable Rows[CurrentRowIndex] Property (read/write) This is 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.

int current_row = testBindingSource.Position

...

testItems.Rows[current_row]
_Dataset DatasetName Property This returns the name of the dataset (see separate property, TableName, to return the name of the underlying data source). test_DataSet.DatasetName
DataTable.Rows 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 not delete the dataset itself.		 testItems.Rows[2].Delete()
_BindingSource MovePrevious()
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). test_BindingSource.MoveNext()
_Table Refresh()

Active

Method

Property

 Refresh re-retrieves the dataset from the database and/or applies the runtime filter (if auto-refresh is off). The current row (if changed) must be committed or rejected before the dataset is refreshed. By setting Active to False and then back to True, data will be re-retrieved from the database to the table.

test_Table.Refresh()
_DataSet RejectChanges() Method The RejectChanges 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. The corresponding AcceptChanges method saves any changes to memory but does not save them to the database (see Save()). test_DataSet.RejectChanges()
_BindingSource PositionChanged Event This Event occurs, then the current row in the dataset changes - (either manually or via a script). A VB or C# 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. test_BindingSource.PositionChanged += new EventHandler(save_testItems_changes);
_DataSet HasChanges Property This property when True indicates that data in the dataset has changed and can be used to determine whether or not a Save() method should be applied, for example. if (test_DataSet.HasChanges())
{
test_Table.Save();
}
_BindingSource Filter 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.

 test_BindingSource.Filter
DataTable TableName Property Returns the [schema].[object_name] or the underlying database Table/ Source. test_DataSet.TableName
DataTable.Rows

_BindingSource		 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 as unselected columns in the Dataset. testItems.Rows.Count

Tip: To determine whether an edited record has been saved or not, a HasChanges property of the dataset can be used. If this property returns True, changes to the dataset have not yet been saved.

Scripting with Controls