Add HL7 Patient to Database
Return to Tutorial Directory
Video Transcript
Add HL7 Patient to Database:
In this tutorial we are going to show how to send an HL7 message to a database.
Once built, it is going to be repeatable and simple to reuse over and over again. There will be some simple .net coding, but it is all freely downloadable for your re-use and distribution as you see fit.
Here is our scenario: I would like to receive SIU^S12 HL7 messages which are new appointments, and create patients followed by appointments in our database. We’ve included a simple script that can generate this dummy database so you can easily follow along.
Let’s start by taking a look at our dummy database. It’s very simple, we just have a patients table, and an appointment table. But it illustrates a very common scenario when working in HL7.
For the sake of demonstration we are going to create two “Add patient” activities that receive and process HL7 messages differently. The first takes the whole HL7 Message as an incoming argument, it then processes the message, creates the database record, and returns the newly created Patient ID in a CSV message.
The second “Add patient” activity will specify the parameters it accepts, so the workflow only passes in what is needed. It then adds the patient, and returns the newly created Patient ID as a workflow variable.
Let’s just quickly show you our workflow in HL7 Soup. We receive an HL7 message via TCP, then add the patient to the database, followed by the appointment. These custom activities help make this a very simple workflow, so now, I’m going to clear this all out of my system and show you how to create this from scratch. Don’t forget that you can download the completed project so you can easily follow along.
Let's start by going into Visual Studio and creating a new class library project, which I’m going to call HL7SoupDatabaseActivities. Firstly I’m going to rename the default class, and I’m going to call it AddPatientByMessage. Now I’m going to add some references, so we can work with HL7 Soup. So I’m going to browse the HL7 Soup Program Files directory and add HL7Soupintegrations.dll. I’m also going to add a reference to System.Configuration, and I’m going to use the app config file to store my database connection string.
I’m now going to paste in the code for this activity so you don't need to watch me type it from scratch. If you look at the top of the screen you'll see that there's a number of using statements. We've added HL7SoupIntegrations, System.ComponentModel, and System.Data.SQLClient for the database access that we're going to be doing. Firstly for our class you'll see that there's a number of attributes at the top. The DisplayName gives our activity its official name, followed by an “in” and an “out” message. These provided the sample messages that will be used by our activity. It also defines that the in message is an HL7 message and the out message is a CSV. Following the attributes we define the class itself, and it inherits from the HL7SoupIntegrations type CustomActivity.
We then override the process method which provides us with information about the workflow instance, activity instance and parameters for this activity. Then we extract the message from the activity instance, and define it as an IHL7Message. This provides us with a number of helper features for working with HL7 messages. Next we're going to extract the values that we need to populate our database from the HL7 message that is sent in. We call HL7Message GetValueAtPath, and then pass in the HL7 path, which points to the value we require. We're going to extract out the external patient ID, the patient's first name, last name and their date of birth. With the date of birth we're also going to translate that from an HL7 date format into a .net date format using the HL7Helpers functions. Finally we're going to create a PatientID variable that will be used to hold the patient's ID that will be returned from the database when we add the patient.
Next we're going to acquire our connection string from configuration manager. We're going to create a connection string called MedicalDB. Let's add it to our project. We’ll go to settings, create the new app config, create a setting called medical DB, set it to a connection string, and then finally I’m going to paste in the connection string to my database. Now we have the code to connect to our SQL Server, it's all pretty standard. First, I create a connection, then a command, and to that command I’m going to add the text that queries the database. The query will go through and it will check if the patient already exists, and if they don't it will insert a new patient. If they do it will update the existing patient, and then either way it's going to return the patients ID, as it is stored in our local database. Then we add all of our variables as parameters into the query, then open the connection and execute it, returning the newly added patient ID. Finally we're going to populate our outbound CSV message with the values that we required from our Activity. We'll take the patient ID that was returned to us from the database and populate the other values from the inbound values. We’ll also convert their birth date back into an HL7 format using our HL7Helpers method.
Great our first activity is now complete. Let's build our project, and deploy our new activity into HL7 Soup.
We navigate to the projects directory, and go to bin/debug, and in here we can find our DLLs and config files. We'll start by taking a look at the config file, and extracting out the connection string. I'll just copy it out for now. I now navigate across the Program Files/HL7 Soup. I'll take a backup copy of the App.Config first, and now I’m just going to take the existing app config and move it into the temp directory for editing. I now load it up in notepad++, and I’m going to paste my connection string below the ConfigSections of this document. I'll save and close it and now I'll copy it back into my HL7 Soup directory.
Let's now head back to our new projects bin directory, and we're going to copy out our new dll we just created. I’m going to go back to the HL7 Soup directory, and go into its Custom Libraries folder, and paste in this new dll. Our activity is now deployed.
Now let's load up HL7 Soup and try it out.
Let's start by finding all the appointment messages. I'll select one and then add a filter on its MSH-9.2. Great, we've got two of them in our sample messages. Let's take a quick look inside the message and see where we’ll get our values. The PID-5.1 and PID-5.2 contains the patient's name, the PID-7, the date of birth, and the PID-3’s containing the patient's ID.
Let's create a new receiver and use its workflow to add our new activity. We’ll start the standard TCP receiver activity, and will call the workflow “add patient and appointment”. We’ll receive HL7 messages and we won't add the incoming message to the current list.
I’m now going to choose a custom response type so we can adjust the values we send back, and I’m going to use the current message from HL7 Soup and my default and bound message template to help with bindings. Now in our workflow, let's add a new activity and we'll choose from the drop-down list our brand new “Add Patient by Message”.
Here it is, I'll bind its message template to the inbound message that it receives from the first activity. Finally I’m going to go back to the first activity and adjust the response to return our newly created patient ID. I’m going to have to create a variable, so I'll go into the Transformers for this activity, and I'll select the source as my newly created activities outbound message. Now we simply drag the patient's ID into the transformer list, then I navigate back to the activity and insert our newly created variable into the outbound message.
Let's save this workflow and give it a try.
I start the receiver and I send through our first message. Great, we can see the activity has come back. Let's go back into our workflow designer, and we can see the incoming message, it was then received as a source for our new activity. We then send back the comma separated message in our response, and finally the response with our patients ID is sent back in the HL7 ACK message.
Now if we look in our database, we can see we've got the patient in our patients table.
Great, let's add the next activities. We’ll start by shutting down HL7 Soup and head back to Visual Studio. I’m going to paste in the new activity so you don't have to see me type them, and I'll quickly show you what they're about.
First we've got the “Add Patient by Parameter”. It's quite similar to the previous one, you'll notice that it receives parameters Inbound, and has a variable outbound. So there's no end message or out message attributes on this activity. The parameters define the incoming arguments, give them a description, and mention whether or not they’re required. Then when we set our variables, we take them directly from the parameters dictionary. Our external patient ID, first name, last name, and date of birth, are all there and available for us.
Next we've got the same SQL as with the previous activity, and finally the outbound value sets the workflow variable “patient ID” to the value that was returned from our database.
The “Add Appointment” activity is similar to the previous one. It has parameters for the inbound, we set our variables from those parameters, and it executes the SQL command again that populates the database with the new appointment.
We’ll now compile this project. Let’s redeploy the activities by copying the DLL’s back into the HL7 Soup “Custom Libraries” folder, and restart HL7 Soup.
Now we edit our existing workflow, and we'll start by deleting the activity we used before, and we'll replace it with our newly created “Add Patient by Parameters”.
You can see here, it uses parameters inbound instead of the inbound message. In order to set these values I'll have to head across to the Transformers and create some variables from our inbound message. I add the patient identifier, the patient's first name, and family name, and finally their date of birth. I head back to our new activity and I insert the values as variables, one after the other.
Now let's add our new “Add Appointments” activity. Again it uses parameters, so let's start by binding in the patient ID directly from the returned variable from our “Add Patient by Parameters” activity. Then we'll create the Transformers for all the other variables directly from the message, and add them one at a time as variables into our activity.
Now we'll go back and we'll set the response message again, as the old variable has expired. We'll insert the variable directly from our “Add Patients by Parameters” activity.
Finally I’m going to add a filter, and filter out only messages where the MSH-9.2 is equal to S12.
Let's save the workflow and try it out.
I'll start the receiver, and now I’m going to send all the messages through one after the other. Great, let's have a look at our message logs.
If we expand them, we can see we received our source message. It was passed to the “Add Patient by Parameters” activity, and it sent through its parameters appropriately. It then responded with a variable containing the Patient ID, and that was sent as the source into the parameters for the “Add Appointment”. Finally our response message includes the patient's ID in the MSA.
Let's go across to the database will refresh the patients, and we'll see now there's now two added, and the appointments have now also been added.
I also recommend you take a look at our tutorial video on “Debugging Activities” to see just how easily this can be done with visual studio.
If you'd like to see more of our videos please subscribe to our channel and you'd help us a lot if you like these videos.
Please feel free to leave any comments you'd like to make as well, thank you.