Datasets and Data Exchange

The following sections describe how to configure databases:

n        Configuring Database Connections

n        Configuring Database Tables Access

n        Configuring Database Queries

n        Configuring Files for Data Exchange

n        Dataset Runtime Objects

Configuring Database Connections

By default, Action.NET uses an embedded SQL database SQLite for the tag and alarm historians. You can configure external databases such as Microsoft SQL Server, Oracle, or other external database.If you have the expectation that these historians will need to store large amounts of data and require greater robustness and client / server characteristics, you should consider from the initial deployment the use of an external database.

When using the embedded database, the system automatically creates the database file. When using external databases, the database itself must already exist. However, Action.NET can create the tables in the database.

You can use also use external databases as a data source in your application to display such things as customer or product information or to configure systems from recipes.

You must be logged in as Administrator in Action.NET to configure the database login and password.

To configure an external database:

1.      Go to Edit > Datasets > DBs.

2.      Click Create New.

n        The Create New Database Connection window displays.

3.      Enter or select information, as needed.

4.      Click OK.

n        The database is added as a new row in the table.

5.      Enter or select information, as needed.

6.      If you are using the new external database for the alarm or tag historian, rename the existing TagHistorian or AlarmHistorian database to something else, then rename the new database configuration to TagHistorian or AlarmHistorian.

n        For the tag or alarm historian databases, the database configuration on this tab must be named TagHistorian or AlarmHistorian. You cannot have two databases with the same name.

7.      Continue adding as many database configurations as you need.

n        The databases you create are available for use on the Tags > Historian tab, in displays, and in scripts.

n        If needed, right-click a row to cut, copy, paste, or delete the row.

To Acces Manager application for Data Base:

1.      Use DBAdmin button on Datasets, DB screen.

Accessing Microsoft Excel

Connect to Excel databases using an ODBC driver, an ODBC DSN, or OleDB.

Creating the Databases ODBC

To Create an ODBC data base:

1.      Select, then name a range of rows and columns in the worksheet. This will allow the software to read the information as a table.

2.      Choose one of the following naming processes for your version of Microsoft Excel.

n        For Microsoft Office 2007:

l          Right-click the selection then choose "Name a Range".

n        For Microsoft Office 2003:

l          In Microsoft Excel go to "Insert > Name > Define".

3.      Name the selection (e.g., "itemsTable"). The Excel file is now ready for use.

Methods for communicating using ODBC

Using ODBC Microsoft Excel Driver:

1.      In the Datasets namespace, choose "DBs" tab and create a new Provider by clicking Create new.

2.      Select "Odbc Data Provider" in the "Provider" data field.

3.      "Database" field choose "Microsoft Excel Database".

4.      Click Ok.

5.      A new row is created in the data grid, click the "ConnectionString" column.

6.      In the pop-up window enter the path and the filename in the "Dbq" field.

7.      Click the "Test" button to ensure that the connection is OK.Note: "Test" is optional.

Using ODBC with a DSN:

1.      Go to your computer's "Control Panel", select "Administrative Tools".

2.      Double-click on "Data Sources (ODBC)".

3.      In the "ODBC Data Source Administrator" window click Add. You are prompted to select a driver.

4.      Select the "Microsoft Excel Driver (*.xls)".

5.      Click Select Workbook, then select the name of the Excel file created previously.

6.      Name the Data Source, (e.g., "excelDatasource").

7.      For Write access uncheck the "ReadOnly" checkbox.

8.      In the Datasets namespace, choose "DBs" tab and create a new Provider by clicking Create new.

9.      Under "Odbc Data Provider" options, choose "ODBC using DSN" then click Ok.

10.  Click the ConnectionString column of the new row, then enter the DSN in the "DSN" field.

Using OLEDB

1.      In the Datasets namespace, choose the "DBs" tab.

2.      Select the "OleDb data provider" option of the combo-box and create a new Provider by clicking Create new.

3.      Choose "Microsoft Excel Database" then click Ok.

4.      Click the ConnectionString column of the new row, then enter the path and the name of the Excel (.xls) file in the "DataSource" field.

Configuring Database Tables Access

If you are using an external database as a data source in your application, you can specify which table to use from the database.

To configure database tables:

1.      Go to Edit > Datasets > Tables.

2.      Enter or select information, as needed.

3.      Continue adding as many table configurations as you need.

Reading and writing the table contents

The runtime access to the table contents is executed automatically when the table is mapped to a DataGrid object, see Configuring a DataGrid Window.

You can also get the table contents or perform operations on the tables, using the runtime properties for the Dataset.Table object, see http://www.spinengenharia.com.br/help/an-2016/runtime/index.html.

Configuring Database Queries

You can configure queries to perform more advanced functions with SQL statements to work with data from external databases.

To configure database queries:

1.      Go to Edit > Datasets > Queries.

2.      Enter or select information, as needed.

3.      Continue adding as many queries as you need.

Getting the query contents

The runtime access to the table contents is executed automatically when the query is mapped to a DataGrid object, see Configuring a DataGrid Window.

You can also get the query contents or perform operations on the query, using the runtime properties for the Dataset.Query object, see http://www.spinengenharia.com.br/help/an-2016/runtime/index.html.

Configuring Files for Data Exchange

You can configure files to retrieve data from a network location.

To configure database files:

1.      Go to Edit > Datasets > Files.

2.      Enter or select information, as needed.

3.      Continue adding as many file configurations as you need.

Dataset Runtime Objects

The namespace Dataset is the entry point to all objects related with the Datasets module.

The Dataset.DB object lists all configured database connections and its runtime properties

The Dataset.Table object lists all configured tables and their runtime properties

The Dataset.Query object lists the defined queries and their runtime properties.

The Dataset.File object lists the defined queries and their runtime properties.

See http://www.spinengenharia.com.br/help/an-2016/runtime/index.html for the complete programming reference on runtime objects.