Using C# To Send A HL7 Message

For one of my current projects I needed to take a HL7 message and send it to our interface server.  Generally there are two ways that HL7 messages are sent, they are either dumped out as a file to a directory, or they are sent over a TCP connection.  For this project I needed to send it over a TCP connection.  Most HL7 connections that are using TCP will use a the Lower Layer Protocol (LLP).  It turns out this is pretty easy to do in C#.

First we need to understand what we need to do to send the message using LLP.  All that is required is to insert a vertical tab at the start of the message, and a field separator character and carriage return at the end of the message.  Here is the function that I created that will send a HL7 message.

Sending the message is just that same as sending anything else over a TCP/IP connection.  There are a ton of examples of how to do that, so I will not go over it.

Once you get the response back you need to check it so see if the message was processed successfully or not.  My requirements where pretty simple, so I just checked to make sure it had "MSA|AA" in the return message, signify that it was successful.

Here is the function that I created to send the HL7 message.  It has a helper function to connect the socket.

private static bool SendHL7(string server, int port, string hl7message)

{

    try

    {

   // Add the leading and trailing characters so it is LLP complaint.

        string llphl7message = Convert.ToChar(11).ToString() + hl7message + Convert.ToChar(28).ToString() + Convert.ToChar(13).ToString();

// Get the size of the message that we have to send.

        Byte[] bytesSent = Encoding.ASCII.GetBytes(llphl7message);

        Byte[] bytesReceived = new Byte[256];

        // Create a socket connection with the specified server and port.

        Socket s = ConnectSocket(server, port);

 

        // If the socket could not get a connection, then return false.

        if (s == null)

            return false;

        // Send message to the server.

        s.Send(bytesSent, bytesSent.Length, 0);

        // Receive the response back

        int bytes = 0;

        s.ReceiveTimeout = 3000;

        bytes = s.Receive(bytesReceived, bytesReceived.Length, 0);

        string page = Encoding.ASCII.GetString(bytesReceived, 0, bytes);

        s.Close();

// Check to see if it was successful

        if (page.Contains("MSA|AA"))

        {

            return true;

        }

        else

        {

            return false;

        }

    }

    catch (Exception)

    {

        return false;

    }

}

private static Socket ConnectSocket(string server, int port)

{

    Socket s = null;

    IPHostEntry hostEntry = null;

    

    // Get host related information.

    hostEntry = Dns.GetHostEntry(server);

    foreach (IPAddress address in hostEntry.AddressList)

    {

        IPEndPoint ipe = new IPEndPoint(address, port);

        Socket tempSocket =

                        new Socket(ipe.AddressFamily, SocketType.Stream, ProtocolType.Tcp);

        tempSocket.Connect(ipe);

        if (tempSocket.Connected)

        {

            s = tempSocket;

            break;

        }

        else

        {

            continue;

        }

    }

    return s;

}

Entity Framework And Encrypted Fields

So you are using SQL Server, and you have a field that needs to be encrypted with SQL Server encryption, and you are wondering how to handle this in the entity framework. Unfortunately the news is not good. If you are like me you figured you where using Microsoft's database and Microsoft's data access framework, so surely there are just a couple of properties that you set and it handles the encryption and decryption. I was wrong, and I have not found any easy answers to this issue. I will go over a couple examples of how to handle this. I will assume that to encrypt your field you used a symmetric key that is encrypted with a certificate.

Method 1 - Use A View And Stored Procedures

The first method is to use a view and stored procedures to have the entity framework access the data. The first step is to create the view that will be used to access the data, this view will need to decrypt the encrypted fields. In a view you cannot use the OPEN SYMMETRIC KEY statement. So instead you will want to use the DecryptByKeyAutoCert method to decrypt the data. Here a SQL statement that creates a view like this:

CREATE VIEW [dbo].[SampleDecryptView]

AS

SELECT Field1,

convert(varchar(250), DecryptByKeyAutoCert(CERT_ID('certificatename'), NULL, EncryptedField)) AS EncryptedField

FROM TestTable

GO

Now that you have your view created you need to add it to the Entity Framework model.  There are a number of steps that you have to take to get the view working correctly in the entity framework, especially if you want it to have associations.  You can find instructions for how to do this over at the Mehroz's Experiments blog.  However, before you decided to go this route, look at all the steps that are required, and make sure to notice one of his the final comments:

Note that anytime you update the model from database, it will reset the SSDL and we will manually need to redo all the steps (related to storage model) as described above.

That's right, every time you update the model, you have to redo a lot of these changes.  For me this was a deal killer, because the our projects are constantly growing, and we are constantly adding new tables.

Using Stored Procedures
You can map stored procedures to a entity in in the entity framework.  It allows you to specify stored procedures that will be used for update, insert, and delete operations.  So you just write the update and insert stored procedures so that they encrypt the data before saving it to the table.  Then to get the data out, you can map a stored procedure to the entity as a function.  This stored procedure can decrypt the data.

Here is a good tutorial that shows how to map the update, insert, and delete stored procedures, and the robbagby blog has a create example of how to map the select stored procedure to a function.

The issue I had with this model retrieving records.  If you use the function that you created it works great, because the stored procedure will decrypt the field.  But what if you are access the entity from another entity by a association.  At that point, it is not going to use the stored procedure and your data is not going to be decrypted.

Adding A Function

To be honest I don't see how this solution solves the problem, I will include it for completeness.  On the SafNet blog, Stephen Fuqua describes how to create a custom function in the Entity framework.  This seems to work find for inserts and updates, but I don't see how it solved the issue of retrieving the data from the database.  But I might just be missing something.

Revert Back To ADO.Net

Unfortunately this is the path that I ended up taking.  The entity framework handled this scenario to awkwardly for me, and I decided the best path was to just use plain ADO.Net for this object.  I was disappointed with this, because I was really hoping to have all data access go through the entity framework.

Summary

I have tried to do a lot of searching on this to make sure I did not miss something, but if you notice something I missed please let me know.  I am hoping the Entity Framework 4.0 will have a better solution for this, but so far I have not had a chance to test it.