Execute a database Sub Query on a Database to generate HL7 message with multiple rows of OBX's.

Are you looking to execute a database query in HL7 Soup's Integration Host and generate an HL7 message from it? Well, I have good news for you! It's super easy. But what if you needed to retrieve multiple rows of data to generate OBX results or similar (RXA, DG1 etc)? This scenario is certainly a little fiddlier as you need to execute a sub-query and loop over those results too in order to generate the segments. In addition, you don't know how many OBX fields might be needed, so you'll have to create them manually.

Thankfully, Integration Host has a very handy Add Segment Transformer, but it can only access the data in the outer query. So, it's one of those places where code is the best option.

In this post, I'll show you how to do it using a Code Transformer, but keep in mind that there are many possible ways you could also achieve this.

Let's start by executing a sub-query to retrieve the patient details. Once we have those details, we'll loop over the results and generate the OBX segments accordingly. Here's the code snippet that does just that:

#r "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.7.1\System.Data.dll"
using System.Data.SqlClient;

IHL7Message destinationMessage = (IHL7Message)activityInstance.Message; //Use IHL7Message for HL7 Messages and IMessage for other message types
IMessage sourceMessage = workflowInstance.ReceivingActivityInstance.Message;

string patientId = sourceMessage.GetValueAtPath("[0]"); //returned patient ID from my database
string connectionString = @"Server=MyServer\sqlexpress;Database=MyDB;User Id=MyUser;Password=abc123;";

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
{
    //Get the vax records 
    command.CommandText =$@"Select * from Vax Where PatientID = {patientId}";
    connection.Open();
    using(SqlDataReader reader = command.ExecuteReader())
    {
        int c = 0;
        while (reader.Read())
        {
            c++;
                
            //Get the values from the Vax table
            string administeredDate = reader.IsDBNull(0) ? null : reader.GetString(0);
            string vaccineType = reader.IsDBNull(1) ? null : reader.GetString(1);
            decimal? amount = reader.IsDBNull(2) ? null : (decimal?)reader.GetDecimal(2);
            string units = reader.IsDBNull(3) ? null : reader.GetString(3);
                
            //insert the values as a new HL7 Segment line
            destinationMessage.AddSegment($"OBX|{c}|CE|{vaccineType}||{amount}|{units}||||||F|||{administeredDate}");
        }
    }
    connection.Close();
}