One of the great things about InRule is that there are numerous spots within the product where you can extend native functionalities to be able to easily support integrating with new things that we haven’t added into the core product (yet). One of those areas is around integrating with databases.
InRule has the native ability to interact with SQL databases (Azure or self-hosted), as well as frequently connecting with Oracle databases. Occasionally, we get folks interested in interacting with other types of relational databases, and today we’re going to walk through the process of retrieving the required assemblies and setting up a connection with an Azure-hosted PostgreSQL database using the ngpsql ADO.NET Data Provider (note: InRule is not affiliated with ngpsql). A very similar process could be followed to retrieve the required assemblies for any external ADO.NET data provider for a database not natively supported by InRule.
Note: Third-party assemblies are not currently supported by InRule’s SaaS offering, so this type of integration is restricted to self-hosted environments.
At a very high level, there three steps to the process:
1. Gather the assemblies required by the third-party provider.
2. Deploy those assemblies to irAuthor and your execution service and configure both as needed.
3. Configure your Rule Application Database Endpoint to use the third-party provider.
If this process seems like more than you’d like to take on, another common integration structure that we frequently see is a “helper” REST service acting as a proxy between InRule applications and an external data source. When using a custom proxy service, there is no limit to what types of backend data sources that we’re able to integrate with!
Let’s take a look at one process you can use to integrate a Rule Application with PostgreSQL using ngpsql.
Gather and deploy all required assemblies and dependencies
Because third-party libraries frequently have additional external dependencies that can be time-consuming to track down, we’re going to use a “dummy” .NET project to allow Visual Studio’s NuGet Package Manager to gather all the assemblies that ngpsql is dependent upon for us.
1. Create a temporary solution in Visual Studio and add a Project of type .NET Framework Class Library pointing to .NET 4.6.1.
a. This should match the version of irAuthor that you’re running.
2. Manage NuGet Packages in the project and add the Ngpsql package.
a. The Package Manager adds a number of dependencies to the project references as well; this is the key bit that we’re leveraging it for.
b. This will also create an app.config file; we’ll need the assembly redirects from the config file in a bit (and will make a couple small changes as well), so keep them close at hand.
3. Build the project, and then from the build directory, copy all assemblies other than the files specific to the dummy project you created. This collection is the full set of assemblies and dependencies required by the third-party data provider.
4. Paste those files into the EndPointAssemblies directory of irAuthor.
a. This directory is generally C:Program Files (x86)InRuleirAuthorEndPointAssemblies .
b. To use in irServer for REST or SOAP-based executions, you’ll also need to include these files in the EndpointAssemblies directory of irServer. For VM-based deployments, this directory is generally C:Program Files (x86)InRuleirServerRuleEngineServiceIisServicebinEndPointAssemblies . For Azure App Service-based deployments, this folder can be found within the App Service application directory; note that any assemblies in this folder and changes made to the config file will need to be re-deployed after upgrading using an ARM template.
c. If using with an irSDK-based execution architecture, the assemblies will need to be available to the executing application at runtime.
5. Update the irAuthor.exe.config file with the dependent assembly redirects found in the dummy project’s app.config (noted above), adding href file hints for ValueTuple and System.Runtime.CompilerServices.Unsafe.
a. The added file hints are a tag contained inside the dependentAssembly tag (next to the assemblyIdentity), and follow a structure like:
<codeBase version="4.0.3.0" href="FILE://C:/Program Files (x86)/InRule/irAuthor/EndPointAssemblies/System.ValueTuple.dll" />
b. Ensure that the href hint points to the correct dll for the assemblyIdentity tag.
c. As with the Endpoint Assemblies, the configuration file will also need to be updated within the app.config of irServer deployments (both VM-based and Azure App Service-based) or irSDK host applications to allow the Rule Application to execute with the referenced external assembly and dependencies.
Configure your Rule Application to use the third-party data provider
Now that we’ve set up irAuthor (and potentially irServer) with the required third-party assemblies and configuration, we need to set up the Rule Application to know what Data Provider to use when interacting with the Database Connection itself.
1. Add a Database Connection Endpoint and enter a Connection String that points to your PostgreSQL database.
2. Set the Endpoint to use a Data Provider of “Other”.
3. Configure the Database Connection Info with an Assembly Name of “Npgsql” and a Type Name of “Npgsql.NpgsqlConnection” (the Type was identified by using DotPeek <href https://www.jetbrains.com/decompiler > on the npgsql assembly to identify the class implementing the DbConnection interface). Then, validate the Connection String and click Test Connection to ensure that the deployment and configuration is set up properly.
4. From this point, you can use the Endpoint as you would any other database within InRule – set up a SQL Query, Execute the Query, and test that your data retrieval is working properly using irVerify.
This process has walked through how we can gather all required dependencies for a third-party assembly and reference them in the context of a Database Connection configuration of a Rule Application to allow in-application querying of databases without requiring any additional infrastructure components. If you find yourself needing additional assistance with a process like this, please feel free to reach out to ROAD Services, and we’d be happy to give you a hand ensuring your project’s success!
