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();
}

How to update HL7 messages in a loop with HL7 Soup

 When working with HL7 messages, it is often necessary to modify certain segments within the message. However, when dealing with a large number of segments, it can be difficult to ensure that the message is not modified in unexpected ways during the modification process. This is where the BeginUpdate and EndUpdate methods come in handy.

Consider the following code snippet:

scss
//Get the current message we are writing as an HL7 Message 
IHL7Message destinationMessage = (IHL7Message)activityInstance.Message;

//Get all the IN1 Sgments 
var in1 = destinationMessage.GetSegments("IN1");

//Set BeginUpdate as we are updating multiple Segments and don't want our list to change untill we complete 
destinationMessage.BeginUpdate();

//Loop over all the IN1 segments 
foreach (var seg in in1) {
    //remove the individual Segment 
    destinationMessage.RemoveSegment(seg);
}

//Update the message 
destinationMessage.EndUpdate();

In this code, we retrieve the current message being processed as an HL7 message object called destinationMessage. We then retrieve all the segments in the message that are of type IN1, which is typically used to store insurance information.

Next, we call the BeginUpdate method on the message object to indicate that multiple segments will be updated, and the message should not be changed until the updates are complete. We then use a foreach loop to iterate over each IN1 segment in the message, and call the RemoveSegment method on the message object to remove each individual IN1 segment from the message.

Finally, after all the IN1 segments have been removed, we call the EndUpdate method on the message object to indicate that the updates are complete, and the message can be modified again.

Without the BeginUpdate and EndUpdate methods, it is possible that the message could be modified unexpectedly during the loop, causing issues with subsequent iterations. For example, if we did not use these methods, only the first item would be deleted as the foreach would then reference another message, and the remaining IN1 segments would not be removed.

In summary, when working with HL7 messages and modifying segments within them, it is important to use the BeginUpdate and EndUpdate methods to prevent unexpected modifications to the message. These methods ensure that the message is not updated until all modifications are complete, allowing for safe and efficient modification of large numbers of segments within the message.

Remove multiple segments from an HL7 message with HL7 Soup

 If you're working with HL7 messages, you may need to remove multiple segments from a message.

However, if you do it in a loop, you find that only the first item is removed.  This is because the delete causes the message to be recreated, and all remaining segments in the loop reference a different message instance.

Try this instead:

//Get the current message we are writing as an HL7 Message
IHL7Message destinationMessage = (IHL7Message)activityInstance.Message; 

//Get all the IN1 Sgments
var in1 = destinationMessage.GetSegments("IN1");

//Set BeginUpdate as we are updating multiple Segments and don't want our list to change until we complete
destinationMessage.BeginUpdate();

//Loop over all the IN1 segments
foreach (var seg in in1)
{
      //remove the individual Segment  
      destinationMessage.RemoveSegment(seg);
}

//Update the message
destinationMessage.EndUpdate();

The above example removes all the IN1 segments from an HL7 message. The code first gets the current message as an HL7 message and then gets all the IN1 segments. It then sets BeginUpdate as it is updating multiple segments and doesn’t want the list to change until it completes. It then loops over all the IN1 segments and removes the individual segment. Finally, it updates the message.