What is a Dataset?

Applies To: AutoMate 6, AutoMate 5
Published: 8/7/07

Introduction
Like variables, datasets are used to represent data in a task that may be different each time a task executes. But unlike standard variables, datasets can contain multiple rows and columns. This is useful when retrieving information that describes more than one object or represents a collection of data such as a database or spreadsheet.

Conceptually, the data is laid out like a table and is accessed in much the same way (i.e. by specifying the column and row where the data resides). Several actions in AutoMate create and populate datasets, including 'SQL Query,' 'SNMP Get,' 'POP3 Email' and specific 'Excel' actions.

Using Datasets
Every dataset created and used in AutoMate must have a unique name, much like variables. But because datasets are more like tables (as opposed to variables, which can be thought of more like containers that hold one value), they are referenced differently than other AutoMate objects. Typically, one references a value of the current row at a given column (e.g. a column named "address") using an AutoMate expression:

%MyDataset.Address%

When a dataset is created, the current row is automatically set to 1 (assuming that the dataset has any data, since it is possible for a dataset to have 0 rows, such as when a SQL Query returns no data). A dataset is of minimal use, however, unless one can access the other rows. Typically this is accomplished by using the 'Loop Dataset' action. This action takes a dataset name as a parameter and automatically increments the current row with each iteration. The loop continues until all the rows have been accessed. In this way, one could make an AutoMate task that performs operations on each row of the dataset while using the same expression.

Using a 'Loop Dataset' step is not the only way to access dataset rows, however. It is possible to directly access a particular row of a dataset by supplying the row number within the expression. For example, if the dataset contains five rows and you need to get the address of row 2, this expression would do the trick:

%MyDataset(2).Address%

In fact, AutoMate allows the previous expression (i.e. %MyDataset.Address%) by simply assuming you mean the current row if an index is not specified.

Dataset Columns
Most of the columns that are returned in a dataset are dictated by the action creating and populating the dataset. For example, when using the 'SQL Query' action, the column names are controlled by the columns returned by the query. In the 'SNMP Get' action, the column names are related to the SNMP information being returned (this is documented in the AutoMate Help File). However, all datasets have a common set of columns that can be used to work with the dataset. These columns can be accessed the same way as other columns, though some of their values are read only. Also, the values of these constant columns are the same regardless of the row being accessed.

Column Name Description
CurrentRow: The current row that will be accessed in the dataset by an expression that does not contain a specific row index
TotalRows: The total number of rows in the dataset
TotalColumns: The total number of columns (not including the static columns) in the dataset
ExecutionDate: The date and time the dataset was created and populated
RowsAffected: The number of rows affected by an update.
TotalRows: The number of rows affected by an update. (This is typically the same as RowsAffected.)
SQLQuery: The SQL Query that was used to generate this dataset (If a SQL Query was not used, this value is empty.)
Datasource: The datasource used for the SQL Query, if applicable
ColumnNames: A comma-delimited list of the column names in the dataset

Example using SQL Query
AutoMate's 'SQL Query' is an example of an action that creates and populates a dataset. The columns contained within that dataset are determined by the query that was executed. For example if the following query is executed:

SELECT firstname, lastname, company from customer where city='Los Angeles';
Then the following data set would be generated:
datasetname
|--firstname
|--lastname
|--company

Along with the "constant columns" described previously, a row is created for each record that is retrieved from the server. To access this data one would typically use the 'Loop Dataset' action to loop through the records. Inside the loop, the data can be extracted from each column (assuming the current record) by using an embedded expression like before:

%mydatasetname.firstname%
or you could combine two fields together like this:
%mydatasetname.firstname + ' ' + mydatasetname.lastname%

A task that would loop through all the returned rows and display the combined first name and last name would look like this (copy the code to the Task Builder's "Steps" window):

<AMSQLQUERY CONNECTIONSTRING="AM15H8RZy9IHYc=aME" RESULTDATASET="LosAngeles">SELECT firstname, lastname, company from customer where city='Los Angeles';</AMSQLQUERY>
<AMLOOP TYPE="DATASET" DATASET="LosAngeles">
<AMMESSAGEBOX WINDOWTITLE="Current Name">%mydatasetname.firstname + ' ' + mydatasetname.lastname%</AMMESSAGEBOX>
</AMLOOP>

Conclusion
Datasets provide an efficient and logical collection of data that can be easily accessed using AutoMate expressions. Using the Loop Dataset action makes traversing a dataset simple, while the use of expressions with specific column numbers makes accessing any row of a dataset a snap. More information on datasets, how they are used and how a particular action populates them can be found in the AutoMate Help File.