Configuring Database Connections

By default, Action.NET uses a built-in SQLite SQL database manager for Tag and Alarm Historians. You can configure other external databases, such as Microsoft SQL Server, Oracle, or others. 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 since initial deployment the use of an external database.

When using the built-in database, Action.NET automatically creates the database file. When using external databases, the database file must be created by the utilities by using the chosen manager. However, the Action.NET can create the tables in the database, made available.
You can also use external databases as a data source in your application to display data as customer or product information, or to configure revenue systems.

The button SQL Query Builder enables the call to an application that is a tool for building SQL queries. See in this documentation the section Visual SQL Query Builder with details about the use of this application.

You must be logged in as an administrator Action.NET the database to set up the database login and password.

To set up an external database:

1. Go to Edit> Datasets > Dbs.

2. Click Create New York, U..

   • The Create New Database Connection window is shown.

3. Enter or select the information as needed, a new row in the table will be inserted.

• Click OK.

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

• Enter or select the information in the new row of the table as needed to complete the configuration of the database

• If you are using a new external database for the tag or alarm historian, change the name of the existing TagHistorian or AlarmHistorian databases to others, and then change the name of the new database configuration to TagHistorian or AlarmHistorian.

• For tag historian or alarm databases, the configuration of the database names in this tab must be TagHistorian or AlarmHistorian. You cannot have two databases with the same name.

• Keep adding as many databases as you need.

  • The databases you create will be available for use in the Tags> Historian, in the making of screens and for the scripts.

  • If necessary, right-click a line to cut, copy, paste, or delete the line.

To access the Database Server Manager

The button DBAdmin is used to activate the database server manager application used for the external database. 

Historian and Alarms Connection String 

The connection string for the TagHistorian and AlarmHistorian has parameters that can be configured to avoid data loss. They are:

• Data Source: The server path and instance that will have the databases.

• Initial Catalog: The name of the database that will be used.

• Store and Forward: Enabling this option will cause the system to store the data locally if communication with the database is lost, and forwards the data to synchronize once the connection is back again.

• Keep the local copy: Enabling this option will make the system store a local database with the same data that has been stored in the main DB. The system will use the internal database to store it, and the file can be located on the same folder that the project is in. 

Accessing Microsoft Excel

To connect to Excel databases you can use an ODBC driver, an ODBC DSN, or OleDB. The following sections detail the steps to configure each of these connection methods.

Creating ODBC databases

1. Select, and assign a Name to 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.

   • For Microsoft Office 2007:

     • Right-click on the selected area and choose "Name of a Range".

• For Microsoft Office 2003:

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

• 

• Name the selection (for example, "Table items"). The Excel file is now ready for use.

Methods for communicating using ODBC

Using ODBC Microsoft Excel Driver:

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

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

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

4. Click Ok.

1. A new row will be created in the table, click the "ConnectionString" column.

2. A window appears: It enter the path and file name in the Extended Properties field.

3. Optionally click the "Test" button to make sure the connection is OK.

Using ODBC with a DSN:

1. In MS-Windos go to "Control Panel" and select "Administrative Tools".

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

In the "ODBC Data Source Administrator" window:

1. Click Add. Another window will appear asking you to select a "driver",

2. Select the "Microsoft Excel Driver (*.xls)". Click Finish.

3. Click Select Work Folder and select the name of the previously created Excel file.

4. Give the data source a name. example:. "excelDatasource". If you want to make write accesses, uncheck the "ReadOnly" option

1. In the workspace Datasets choose the DBs tab and create a new Provider by clicking the Create New.

2. In the list of options "Odbc Data Provider" choose "ODBC using DSN" and click Ok.

3. Click the ConnectionString column of the new row created in the connection table and enter with DSN in the DSN field.

Using OLEDB

1. In the workspace Datasets choose the "DBs" tab.

2. Select "OleDb data provider" and create a new Connection by clicking Create New

3. Select "Microsoft Excel Database" and then click Ok.

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

Configuring data base Tables

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

To configure database tables:

1. Go to Edit> Datasets >Tables.

2. Enter or select the information as needed.

• Keep adding as many table settings as you need.

Read and write table contents

Runtime access to table content switches automatically when the table is mapped to a DataGrid Object, placed on a screen, or Report. See "Setting up a DataGrid Window" .
You can also get the contents of the table or perform operations on tables, using the runtime properties of 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 set up database queries:

1. Go to Edit > Datasets > Queries.

2. Enter or select the information as needed.

• Keep adding as many queries as you need.

Getting the contents of the query

Runtime access to table content runs automatically when the query is mapped to a DataGrid Object, placed on a screen, or report. See "Setting up a DataGrid Window".
You can also get the contents of the query or perform query operations on tables, using the runtime properties of the Dataset.Table object.

Configuring Files for Data Exchange

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

To set up database files:

1. Go to Edit> Datasets > Files, files.

2. Enter or select the information as needed.

• Keep adding as many file settings as you need.

The Datasets Namespace

The namespace Dataset is the entry point for all objects related to the Datasets module.
The object Dataset.DB. contains the list of all databases and configured connections and their properties at run time.
The object Dataset.Table lists all configured tables and their properties at run time.
The object Dataset.Query lists all defined queries and their properties at run time.
The object Dataset.File lists the data exchange files defined and their properties at run time.
See in {+}http://www.spinengenharia.com.br/help/an-2016/runtime/index.html+ .for the full programming reference on runtime objects.