PowerServer 2022 R2 Help
Managing database connections using APIs

To store the database connection (including the cache, application settings, and transaction-to-cache mappings) in the database,

Step 1: Build and deploy the PowerServer project.

The database connection will be stored to the PowerServer C# solution > UserExtensions project > AppConfig > Application.json or Applications.[DBConnectionProfile].json file by default.

Step 2: Open the PowerServer C# solution in SnapDevelop.

Step 3: Expand the solution > UserExtensions project > AppConfig, open the AppConfig.json file, and modify the "ConnectionStrings" section to point to the database that will store the database connections. The database can be an existing database that has or has no data.

Below is an example of connecting with an Oracle database:

  "ConnectionStrings": {
    "AppConfig": "POOLING=True;ENLIST=true;USER ID=snaptest;DATA SOURCE=\"(DESCRIPTION=(ADDRESS=(HOST=192.15.6.211)(PORT=1521)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=pdborcl)))\";CONNECTION TIMEOUT=15;STATEMENT CACHE SIZE=0;PROMOTABLE TRANSACTION=promotable;CONNECTION LIFETIME=0;METADATA POOLING=False;PERSIST SECURITY INFO=False;LOAD BALANCING=False;STATEMENT CACHE PURGE=False;PASSWORD=AppTest;VALIDATE CONNECTION=False;MIN POOL SIZE=1;MAX POOL SIZE=100;INCR POOL SIZE=5;HA EVENTS=False"
  }

Step 4: Open the AppConfigExtensions.cs file (which is in the same location as AppConfig.json), comment the following script so that it will no longer read the database connection from the file.

            ////Reads the static application configuration and db configuration
            //services.AddAppConfigFromFileSystem(context =>
            //{
            //    // ASP.NET Core configuration, to get relevant configuration data (environment variables, commandline argument from different sources
            //    // see https://docs.microsoft.com/en-us/aspnet/core/fundamentals/configuration/
            //    context.Configuration = configuration;

            //    // The folder of the configuration file. It shall be the path relative to the project folder, and can also be the absolute path or shared path
            //    context.AppConfigDirectory = "AppConfig";

            //    // ASP.NET Core hosting environment, for reading the root directory of the application
            //    // see https://docs.microsoft.com/en-us/aspnet/core/fundamentals/host/generic-host#ihostenvironment
            //    context.HostingEnvironment = hostingEnvironment;

            //    //Uncomment the script below if you want to read configuration from self-defined program/file
            //    //context.ProviderFactory = (applicationsFilePath, argumentConfigurationProvider) =>
            //    //{
            //    //    // Self define the program/file that provides the configuration. It may be useful for some scenarios, such as, you need to refresh the db connection password periodically
            //    //    // It is recommended to inherit FileSystemConfigurationProvider and only customize some necessary logics
            //    //    return new YourCustomConfigurationProvider(applicationsFilePath, argumentConfigurationProvider);
            //    //};
            //});

Step 5: In the same file, go down and uncomment the script according to the type of the database so that it can successfully read the database connection from the database.

If you specify an Oracle database in step 3, then you will need to uncomment the script for Oracle here (must be the same database type).

            // Uncomment the script below if you want to read configuration from Oracle
            services.AddAppConfigFromOracle(context =>
            {
                // ASP.NET Core configuration, to get relevant configuration data (environment variables, commandline argument from different sources
                // see https://docs.microsoft.com/en-us/aspnet/core/fundamentals/configuration/
                context.Configuration = configuration;

                // The folder of the configuration file. It shall be the path relative to the project folder, and can also be the absolute path or shared path
                // This setting will send the configuration data in the configuration file to the database for initialization at the first startup of PowerServer
                context.AppConfigDirectory = "AppConfig";

                // ASP.NET Core hosting environment, for reading the root directory of the application
                // see https://docs.microsoft.com/en-us/aspnet/core/fundamentals/host/generic-host#ihostenvironment
                context.HostingEnvironment = hostingEnvironment;

                // The connection string to the db server that stores the configuration. Configure it in AppConfig/AppConfig.json
                context.ConnectionString = configuration.GetConnectionString("AppConfig");
            });

Step 6: Double check Step 3, 4 & 5, and then click the Run button in SnapDevelop IDE.

When Web APIs runs, it creates three tables in the database: powerserver_applicationconfig, powerserver_connectionconfig, and powerserver_transactionconfig; and then synchronizes the database caches, application settings, and transaction-to-cache mappings from the file to these tables.

Note that this information will be synchronized for only one time after these tables are created. If you want to synchronize the information to the tables again, you will need to manually delete these three tables in the database and then run the Web APIs (Step 6) again.