Database Connections for Server Workflows

VertiGIS Studio Workflow has activities that enable you to access databases from server workflows, for example, the Run SQL Query and Run SQL Non-Query activities. To use these activities in server workflows, you must first configure access to your database:

Check the list in Supported Data Providers to make sure that Workflow Server supports your type and version of data provider. If necessary, install the DLL file for the data provider.
Configure the database's connection string.

Supported Data Providers

The table below lists the data providers supported by VertiGIS Studio Workflow Server. If Workflow Server supports the type and version of data provider that you use to access a database, then Workflow Server can use that data provider to access the database in server activities.

Workflow Server can access any database version that the supported data providers support. If you need to know which database versions your data provider supports, find out from the data provider.

Data Providers Supported by VertiGIS Studio Workflow Server

Database	Supported Data Provider	Install Provider DLL?
SQL Server	System.Data.SqlClient 4.5.1	No
MySQL	MySql.Data 8.0.12 and newer	Yes
ODBC	System.Data.Odbc 4.5.0	No
Oracle	Oracle.ManagedDataAccess.Core 2.18.3 and newer	Yes
SQLite	System.Data.SQLite 1.0.109	No

Install a Data Provider's DLL File

To access a MySQL or Oracle database from server workflows, you must first install a DLL file for the data provider in your on-premises installation of VertiGIS Studio Workflow.

To install a data provider's DLL file:

Download the data provider's NuGet package to the VertiGIS Studio Workflow server:
- MySQL: Download the MySql.Data package.
- Oracle: Download the Oracle.ManagedDataAccess.Core (ODP.NET) package.
Extract the contents of the package using a tool like 7-Zip.
In the extracted contents, copy the DLL file for the data provider to the clipboard:
- MySQL: Copy lib/net6.0/MySql.Data.dll.
- Oracle: Copy lib/netstandard2.1/Oracle.ManagedDataAccess.dll.
Navigate to the Deployment\service folder in your VertiGIS Studio Workflow installation.

The default installation location is C:\Program Files\VertiGIS\VertiGIS Studio Workflow.
Paste the DLL file into Deployment\service.
In IIS, recycle the VertiGISStudioWorkflow application pool.

Configure Database Connection Strings

Connection strings provide VertiGIS Studio Workflow with the information it needs to access the databases from server workflows. There are two ways to specify a connection string:

Activities that access a database, like Run SQL Query and Run SQL Non-Query, have a Connection String input where you can configure the connection string. This method requires you to configure the connection string every time you use one of these activities.
VertiGIS Studio Workflow has a database settings file where you can configure the connection strings for all the databases that you want to access from server workflows. This method allows you to configure the connection strings once. You can then use them in any server workflow that you author. See the procedure below for instructions.

You must know in advance what information is required to connect to a particular database. If you aren't sure, ask the database administrator or refer to a resource like The Connection Strings Reference.

To configure connection strings in the database settings file:

This procedure is optional. If you prefer, you can specify the connection string each time you use an activity that accesses the database.

Navigate to the data directory in your VertiGIS Studio Workflow installation.

The default location is C:\ProgramData\Geocortex\Workflow.

To find out the location of the data directory, open the Post Installer and look on the Data panel.
Edit the databasesettings.json file as an administrator.
Set the value of ConnectionStrings to the connection strings for the databases that you want to access in server workflows.

Each connection string is specified as a name/value pair ("name": "value"), where value is the connection string. If you specify multiple connections, separate them with commas. You can use any valid JSON name that you want, provided it is unique across the connection strings. You will use the name to specify the database when you configure a server activity that accesses the database.

For example:
```
{
    "DatabaseSettings": {
        "ConnectionStrings": {
            "MySQLDatabase": "Server=host.domain.com; User Id=sa; Password=myPass;",
            "OracleDatabase": "User Id=system; Password=myPass; Data Source=host.domain.com:1521;"
        }
    }
}
```
Save and close the file.
In IIS, recycle the VertiGISStudioWorkflow application pool.