Connection Strings

Connection Strings

The connections strings found in your configuration file, holds the information on how the API will connect to your database. These settings are DBMS dependant, meaning that there are different details for Microsoft and Oracle.

See the chapter “Database connection” on how to configure your first database connection.

Default, connection string.

In your “Connections Strings” configuration there need to be at least one database connection. This is typically given the JSON property name, or key “Default”. The API will use this connection unless instructed to do otherwise.

Multiple database connection strings.

The API support multiple database connection string. Where each is identified by its own JSON property name or key. Each database connection could connect to the same or different server, database and using different database user to connect.

The calling application needs to identify the database connection to use during the endpoint call. If no such instruction is given, the API will use the “Default, connection string.”

The application calling the API can identify the connection string to use by adding to the URL this parameter:

• ConnectionStringKey=default
  • Here the application is instructing the API to use the “default” connection string.
• ConnectionStringKey=default_ora
  • Here the application is instructing the API to use the “default_ora” connection string.

Example:

appsettings.json file content:

"ConnectionStrings": {
  "default": "Data Source=127.0.0.1:1521/SafranDatabase;User Id=SAFRANSA;Password=safran;Max Pool Size=200;",
SafranDatabase
  "default_mss": "Server=127.0.0.1;Database=SafranDatabase;User Id=SAFRANSA;Password=safran;",
  "default_ora": "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SafranDatabase)));User Id=SAFRANSA;Password=safran;"
  },

Calling the Safran API endpoint with specified database connection string key:

https://mySafranAPI/v1/codesets?id=9&ConnectionStringKey=default_mss&meta_field_select=r1

Additional connection string parameters

In addition to what is shown in the chapter “Database connection” other parameters can be provided, as per what your DBMS support.

• Oracle has one called “Max Pool Size”:
  • "default": "Data Source=MyServer:1521/TestBase1;User Id=SAFRANSA;Password=safran;Max Pool Size=200;"
• Sample of additional Oracle parameters
  • "default": "Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;"

Oracle TNS-less connection string

Typically, your Oracle connection string will use what is called TNS names (Transparent Network Substrate), which means you will have to configure your Oracle client installation. TNS refers to the network layer that facilitates communication between clients (such as applications or tools) and Oracle database servers. It handles tasks like resolving service names to connection details, managing connections, and routing requests. Essentially, TNS enables seamless communication over the network.
This can be avoided by using TNS-less connection string. By using such connection string, there is no need to add configuration in to your “tnsnames.ora” file.
Also see the Glossary.

"ConnectionStrings": {
  "default": "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;"
},

Oracle connection string, content
The Oracle connection string typically involves the following items:

1. Data Source: Specifies the Oracle database location. It can be an Oracle service name, an Oracle SID (System Identifier), or a TNS (Transparent Network Substrate) alias.
2. User Id (or User Name): The username used to authenticate with the Oracle database.
3. Password: The corresponding password for the specified user.
4. Provider: The data provider used to connect to Oracle. For example:
   1. ODP.NET (Oracle Data Provider for .NET)
   2. OLE DB Provider
   3. ODBC (Oracle Database Connectivity)
5. Connection Pooling Parameters (optional):
   1. Min Pool Size: Minimum number of connections in the pool.
   2. Max Pool Size: Maximum number of connections in the pool.
   3. Connection Lifetime: Maximum time a connection can remain in the pool.
   4. Connection Timeout: Maximum time to wait for a connection from the pool.
   5. Incr Pool Size: Number of connections added when needed.
   6. Decr Pool Size: Number of connections removed when not needed.
6. Additional Parameters (optional):
   1. DBA Privilege: For privileged connections (e.g., SYSDBA, SYSOPER).
   2. Integrated Security: For Windows authentication.
   3. Pooling: Enable or disable connection pooling.
   4. Enlist: Enable or disable automatic enlistment in distributed transactions.