Loop over HL7 OBX fields and add the results to a database

I needed to loop over OBX segments in an HL7 message, then extract the values and write them to a database.

There are several ways of doing this, such as calling a sub-workflow, but I wanted a single-step process, so instead I just created the c# code in a Code Activity.  It's pretty straightforward.  HL7 Soup has a great API for working with HL7, and I use both Looping over segments as well as the simple to use GetValueAtPath.  API details can be found here.

Note that you can also turn this into a reusable activity following these steps.

//Bring in the assemblies that we need that are not included by default.
#r "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.7.1\System.Data.dll"

string connectionString = @"Server=xps17\sqlexpress;Database=HL7 Soup;User Id=HL7 Soup;Password=xxx;";
//Get the message template
IHL7Message message = (IHL7Message)activityInstance.Message; //Use IHL7Message for HL7 Messages and IMessage for other message types

using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
    //get other values not in the loop
    string patientName = message.GetValueAtPath("PID-5.2");
    
    connection.Open();
    
    //Get all OBX segments and loop over them
    IHL7Segments allSegments = message.GetSegments("OBX");
    foreach (IHL7Segment segment in allSegments)
    {
        //update your database
        using (System.Data.SqlClient.SqlCommand command = connection.CreateCommand())
        {
            string obxvalue2 = segment.GetField(2).Text; 
            string obxvalue5 = segment.GetField(5).GetComponent(1).Text;
            
            command.CommandText =$"Insert into MyTable (fieldname, fieldname2,fieldname3) values ('{patientName}','{obxvalue2}','{obxvalue5}')";
            command.ExecuteNonQuery();
       
        }
    }
    connection.Close();
}