How to update your database from an EasyHL7 message. Using the Example MS SQL Class below here are some examples of how you might update your MS SQL Server database from an EasyHL7Message object. You will note that this sample code is VERY abbreviated and contains little error trapping to reduce the
amount of code displayed. Obviously, you would need to implement all of this code inside of a Try..Catch or with an On Error Goto Statement.
Private Sub myProcessor_AnalysisCompleted(ByVal oFileAnalyzer As EHL7_mc2005.HL7FileAnalyzer) Handles myProcessor.AnalysisCompleted
'This is your MAIN EVENT. All of your processing is done here.
Dim idx As Long 'A Loop Counter
Dim oSeg ' a message segment
Dim oMsg As EHL7_mc2005.EasyHL7Message
'Now the Database Object. Note that we're using plain ADO here (not ADO.Net)
'to help those new to .Net
Dim myDB As New ADOSqlServer 'OUR Database Class (see below)
Dim myRS As ADODB.Recordset, strSQL As String = ""
'Variables to hold some data
Dim sPatientID, sFirst, sLast,sGender
'First Connect to the Database
With MyDB
.ServerName = "SERVERNAME" ' Change to yours
.DatabaseName = "DATABASENAME" ' Change to yours
.UserID = "sa" 'Doesn't have to be sa
.Password = "saPassword" 'SQL Password
.Timeout = 60
.TrustedConnection = False
If Not .ConnectToDatabase() Then
myProcessor.StopProcessor()
Exit Sub
End If
'The database connection is now open and ready to use.
End With
'Now Get the Messages out of oFileAnalyzer
With oFileAnalyzer
For idx = 1 To .FileMessageCount
.ClearErrors()
oMsg = .GetHL7MessageObject(idx)
If .IsError Then
MsgBox("An Error Occurred Retrieving the Message.")
Exit Sub
End If
'oMSG is now an EasyHL7Message object
With oMSG
If .SegmentExists("PID")
'There is a PID segment.
oSeg = .GetSegment("PID")
sPatientID = .GetFieldValue(oSeg,2,1)
sFirst = .GetFieldValue(oSeg,5,2)
sLast = .GetFieldValue(oSeg,5,1)
sGender = .GetFieldValue(oSeg,8,1)
'And so on and so forth gathering whatever data you need
'from this segment
'Now build the SQL Statement
strSQL = "Select * From PatientData Where ExternalID = '" & sPatientID & "'"
myRS = myDB.OpenRS_ForUpdate(strSQL)
'Check for an Error
If (myRS Is Nothing) Then
MsgBox("An Error Occurred Opening the Recordset.")
Exit Sub
End If
'Does the patient exist?
If myRS.EOF Then
'They do not, this is an Insert
myRS.AddNew
myRS("ExternalID") = sPatientID
End If
'Just continue updating the fields
myRS("FirstName") = sFirst
myRS("LastName") = sLast
myRS("Gender") = sGender
'Commit the changes
myRS.UpdateBatch
'Close the recordset
myRS.Close
myRS = Nothing
End If
End With
Next
myDB = Nothing
Exit Sub
End With
End Sub
'NOTE: This example uses ADO, NOT ADO.Net 'Add a Project Reference to ADODB Public Class ADOSqlServer 'Some static ADO connection strings Private Const CONSTRING = "PROVIDER=MSDataShape;driver={SQL Server}; " & _ "server=<SERVERNAME>;UID=<USERID>;PWD=<PASSWORD>;Database=<DATABASE>;Connect Timeout=<TIMEOUT>" Private Const CON_TRUSTEDCONSTRING = "PROVIDER=MSDataShape;driver={SQL Server}; " & _ "server=<SERVERNAME>;Database=<DATABASE>;Trusted_Connection=yes;Connect Timeout=<TIMEOUT>" Private m_ServerName As String = "" Private m_DatabaseName As String = "" Private m_UserID As String = "" Private m_Password As String = "" Private m_Timeout As Long = 60 Private m_TrustedConnection As Boolean = False Private MyConnection As ADODB.Connection Private m_MyConnectionOpen As Boolean = False '------------------------------------------------ '---------Public Properties '------------------------------------------------ Public Property ServerName() As String 'SQL Server Instance name or server IP address Get ServerName = m_ServerName End Get Set(ByVal value As String) m_ServerName = value 'set the connected property to false because we 'changed a primary property m_MyConnectionOpen = False End Set End Property Public Property DatabaseName() As String Get DatabaseName = m_DatabaseName End Get Set(ByVal value As String) m_DatabaseName = value 'set the connected property to false because we 'changed a primary property m_MyConnectionOpen = False End Set End Property Public Property UserID() As String Get UserID = m_UserID End Get Set(ByVal value As String) m_UserID = value 'set the connected property to false because we 'changed a primary property m_MyConnectionOpen = False End Set End Property Public Property Password() As String Get Password = m_Password End Get Set(ByVal value As String) m_Password = value 'set the connected property to false because we 'changed a primary property m_MyConnectionOpen = False End Set End Property Public Property TimeOut() As Long Get TimeOut = m_Timeout End Get Set(ByVal value As Long) If value >= 0 And value <= 60 Then '0 = infinite in ADO m_Timeout = value 'set the connected property to false because we 'changed a primary property m_MyConnectionOpen = False End If End Set End Property Public Property TrustedConnection() As Boolean Get TrustedConnection = m_TrustedConnection End Get Set(ByVal value As Boolean) m_TrustedConnection = value 'set the connected property to false because we 'changed a primary property m_MyConnectionOpen = False End Set End Property Public ReadOnly Property Connected() As Boolean Get Connected = m_MyConnectionOpen End Get End Property Public Function ConnectToDatabase(Optional ByVal strServerName As String = "", Optional ByVal strDatabaseName As String = "", Optional ByVal strUserID As String = "", Optional ByVal strPassword As String = "", Optional ByVal nTimeOut As Long = 60) As Boolean Dim sConnectionString As String = "" Try If Connected Then CloseConnection() End If If strServerName.Length > 0 Then ServerName = strServerName If strDatabaseName.Length > 0 Then DatabaseName = strDatabaseName If strUserID.Length > 0 Then UserID = strUserID If strPassword.Length > 0 Then Password = strPassword If nTimeOut >= 0 And nTimeOut <= 60 Then TimeOut = nTimeOut End If TrustedConnection = False sConnectionString = MakeConnectionString() MyConnection = New ADODB.Connection MyConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient MyConnection.Open(sConnectionString) m_MyConnectionOpen = True ConnectToDatabase = Connected Catch ex As Exception MsgBox(ex.ToString) ConnectToDatabase = False m_MyConnectionOpen = False End Try End Function Public Function OpenRS_ForUpdate(ByVal strSQL As String) As ADODB.Recordset 'This function takes a SQL Statement and returns an updatable ADO Recordset 'OR Nothing on failure. 'Note that either the database must be connected OR the properties to connect 'must have been set OpenRS_ForUpdate = Nothing Try Dim rsReturn As ADODB.Recordset If Not Connected Then If TrustedConnection Then If Not ConnectToDatabaseTrusted() Then Exit Function End If Else If Not ConnectToDatabase() Then Exit Function End If End If End If rsReturn = New ADODB.Recordset rsReturn.CursorLocation = ADODB.CursorLocationEnum.adUseClient rsReturn.Open(strSQL, MakeConnectionString(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic) OpenRS_ForUpdate = rsReturn Exit Function Catch ex As Exception MsgBox(ex.ToString) CloseConnection()
End Try End Function Public Function ExecuteSQL(ByVal strSQL As String) As Boolean 'The function executes a SQL statement. Like an 'INSERT, DELETE, or UPDATE statement 'Note that either the database must be connected OR the properties to connect 'must have been set ExecuteSQL = False Try If Not Connected Then If TrustedConnection Then If Not ConnectToDatabaseTrusted() Then Exit Function End If Else If Not ConnectToDatabase() Then Exit Function End If End If End If MyConnection.Execute(strSQL) 'If there was a syntax error in the SQL statement the 'Catch should be invoked. ExecuteSQL = True Catch ex As Exception MsgBox(ex.ToString) CloseConnection()
End Try End Function Public Function OpenRS_ForReading(ByVal strSQL As String) As ADODB.Recordset 'This function takes a SQL Statement and returns a ReadOnly ADO Recordset 'OR Nothing on failure. 'Note that either the database must be connected OR the properties to connect 'must have been set OpenRS_ForReading = Nothing Try Dim rsReturn As ADODB.Recordset If Not Connected Then If TrustedConnection Then If Not ConnectToDatabaseTrusted() Then Exit Function End If Else If Not ConnectToDatabase() Then Exit Function End If End If End If rsReturn = New ADODB.Recordset rsReturn.CursorLocation = ADODB.CursorLocationEnum.adUseClient rsReturn.Open(strSQL, MakeConnectionString(), ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly) OpenRS_ForReading = rsReturn Exit Function Catch ex As Exception MsgBox(ex.ToString) CloseConnection() End Try End Function Public Function ConnectToDatabaseTrusted(Optional ByVal strServerName As String = "", Optional ByVal strDatabaseName As String = "", Optional ByVal nTimeOut As Long = 60) As Boolean Dim sConnectionString As String = "" Try If Connected Then CloseConnection() End If If strServerName.Length > 0 Then ServerName = strServerName If strDatabaseName.Length > 0 Then DatabaseName = strDatabaseName If nTimeOut >= 0 And nTimeOut <= 60 Then TimeOut = nTimeOut End If TrustedConnection = True sConnectionString = MakeConnectionString() MyConnection = New ADODB.Connection MyConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient MyConnection.Open(sConnectionString) m_MyConnectionOpen = True ConnectToDatabaseTrusted = Connected Catch ex As Exception MsgBox(ex.ToString) ConnectToDatabaseTrusted = False m_MyConnectionOpen = False End Try End Function
Private Function CloseConnection() As Boolean Try If Not (MyConnection Is Nothing) Then Try MyConnection.Close() Catch ex As Exception 'do nothing here End Try MyConnection = Nothing End If CloseConnection = True m_MyConnectionOpen = False Catch ex As Exception MsgBox("Error closing existing connection") m_MyConnectionOpen = False CloseConnection = False End Try End Function Private Function MakeConnectionString() As String Dim strVal As String 'Starting with the basic connection strings we'll do a series of 'Replace method calls to make the connection string MakeConnectionString = "" If TrustedConnection Then 'Since this is a trusted connection we don't need UserID 'or Password strVal = CON_TRUSTEDCONSTRING strVal = strVal.Replace("<SERVERNAME>", ServerName) strVal = strVal.Replace("<DATABASE>", DatabaseName) strVal = strVal.Replace("<TIMEOUT>", TimeOut.ToString) Else strVal = CONSTRING strVal = strVal.Replace("<SERVERNAME>", ServerName) strVal = strVal.Replace("<DATABASE>", DatabaseName) strVal = strVal.Replace("<USERID>", UserID) strVal = strVal.Replace("<PASSWORD>", Password) strVal = strVal.Replace("<TIMEOUT>", TimeOut.ToString) End If End Function
End Class
|