Writing to a database

Writing to a database is a common task in workflows, but Connexion doesn’t have a dedicated device for this. Instead, we’ve built database support into the Custom Code Control. A dedicated database writer device would either need to have an extremely complex UI, or, would only work for the simplest of cases.

By utilizing the Custom Code device, you can perform simple database ingestion easily, while also having the scope to perform far more complex operations via C# code (and pre-built libraries).

We recommend using a fully compiled device for writing to a database. This lets you properly expose the connection settings to the end user, as well as perform proper validation.

The following examples are to help you get an understanding of the underlying logic.

Code Only

The first example is going to only use code to write some raw HL7 into a Postgres database. This database has a test table with one field rawhl7.

This example references the Npgsql and NetStandard 2.0 libraries.

The class:

using System;
using System.Threading;
using Connexion.Core;
using System.Threading.Tasks;
using Npgsql;
using System.Data;

namespace Connexion.Device
{
  [ClientRunnable(false)]
  public partial class CustomDevice : BaseCustomDevice
  {
    private NpgsqlConnection _DatabaseConnection;
    
    public override void Start()
    {
      var builder = new NpgsqlConnectionStringBuilder
      {
        Host = "localhost",
        Database = "test",
        Username = "postgres",
        Password = "*******",
      };
      
      _DatabaseConnection = new NpgsqlConnection(builder.ConnectionString);
    }

    public override void Stop()
    {
      // clean up the connection
      _DatabaseConnection?.Dispose();
    }
      
    public override async Task ProcessMessageAsync(IMessageContext context, CancellationToken token)
    {
      if(_DatabaseConnection.State != ConnectionState.Open)
        await _DatabaseConnection.OpenAsync();

      using var cmd = new NpgsqlCommand("INSERT INTO hl7 (rawhl7) VALUES (@rawhl7);", _DatabaseConnection);
      cmd.Parameters.AddWithValue("@rawhl7", context.GetAsHL7Message().RawMessage);
      await cmd.ExecuteNonQueryAsync();
    } 
  
    public override void OnError(IMessageContext context, Connexion.Core.ErrorEventArgs args)
    {
      if(args.Exception is NpgsqlException sqlEx)
      {
        _DatabaseConnection?.SafeDispose();
        
        if(sqlEx.IsTransient)
        {
          args.ShouldRetry = true;
          
          if (args.TotalRetries > 0 && args.TotalRetries % 2 == 0)
            Logger.Write(EventSeverity.Error, "Unable to write message (retry {0}): {1}", args.TotalRetries, args.Exception.Message);
    
          // wait before trying to process again
          args.SleepTime = TimeSpan.FromSeconds(args.TotalRetries > 2 ? 30 : 5);
        }
      }

      args.ShouldRetry = false;
    }    
  }
}

This class uses an NpgsqlCommand and a simple SQL INSERT statement to write the data. If an exception occurs, we’ll retry if the error is transient.

Entity Framework / Wizard

Data Connections are Entity Framework based models of databases. Entity Framework is an ORM which hides much of the complexity of database interaction, and includes support for many different types of database (Sql Server, Postgres, MySql, Oracle, etc.).

Version 17 has some incompatibilities with certain Entity Framework provides. If possible, use regular ADO .NET or another ORM. We expect these incompatibilities to be solved in version 18.

After clicking the OK button, a tree of database objects will be displayed. Select the items you wish to be available to your code.

Standard Entity Framework Database Access