UPSInfo Class

Scenario: You have a HL7 message which has a value in Field #38, Component #11 of the "XYZ" Segment, and you need to know IF there is a column in your SQL Schema to accept this data. You could do the following:

C#

           UPSInfo myInfo = myKey.NewSQLSchemaInfo();

          // myKey is a SQLSchemaAPIKey object

          // myInfo should probably be declared

          // at the same level as your SQLSchemaAPIKey

          // First build what we KNOW the column name would be.

          string myColumnName = "XYZ_F38_C11";

          // Since the Field # is 38 I KNOW it will be in the ATable

          int i = myInfo.GetUPSHL7SegmentTables("XYZ").ATable.UPSColumns.IndexOfKey(myColumnName.ToUpper());

          // If i >= 0 then the column exists

          // This check is safe to call (no unhandled exceptions)

          // even when segment "XYZ" does not exist in your

          // schema tables.

SortedList<String, UPSTable> AllSchemaTables. Contains a UPSTable object for every SQL table detected as belonging to your SQL Schema Profile. As it is a SortedList object the Key String is the Table Name (Upper Case).

SortedList<String, UPSTable> AllSegmentTables. Contains a UPSTable object for every Segment Data table detected as belonging to your SQL Schema Profile. As it is a SortedList object the Key String is the Table Name (Upper Case).

DateTime LastUpdated. The last time the object was refreshed. While there is not an exposed Refresh() method, there might be other object which use the class which might trigger an internal Refresh() operation.

SchemaIsLoaded [Boolean] Has the UPSInfo object successfully loaded table definitions. When would this be False? If you create the object and SQL Server or your Database are offline, OR you create the object immediately after a user has removed the schema in the SQL Schema Engine.

SchemaIsValid [Boolean] Does the UPSInfo object contain a perfunctorily valid SQL Schema Engine compatible set of Schema Tables. It does this by ensuring that all necessary  SQL Schema Warehouse Tables exist and that several mandatory HL7 Segment tables exist (MSH, PID, and a few others). Since this value will be False if SchemaIsLoaded is False this is a better property to use for verifying that you can use your UPSInfo object.

Performance Metrics Properties

This class does consume database query resources whenever it is reloaded (refreshed) these properties allow you to determine the performance of this internal "refresh" operation using the LastOperationTS property (a standard .Net TimeSpan object). Evaluate (OperationEnded - OperationStarted).TotalSeconds.

DateTime LastOperationStarted - The starting time of the last "refresh" operation.

DateTime LastOperationEnded - The starting time of the last "refresh" operation.

TimeSpan LastOperationTS - A .Net TimeSpan resulting from (LastOperationEnded - LastOperationStarted)

UPSTable GetSchemaTable(String tableName).

Parameter 1: String - tableName. The name of the table in the Schema that you wish to retrieve.

Returns: A UPSTable object for the tableName passed in Parameter 1.

Note: This method is very hard to break. Barring some structural system error (SQL Connection Errors, API Key not Runnable, etc) this method will return the UPSTable instance. If the table actually does NOT exist in your schema the Exists property of that instance will be False.

UPSHL7SegmentTables GetUPSHL7SegmentTables(String segmentName).

Parameter 1: String - segmentName. The segment you wish returned ("MSH", "PID", "OBX", etc).

Returns: A new UPSHL7SegmentTables object for the HL7 segment passed in segmentName.

Note: This method is very hard to break. It will try and return an object to you whether your segmentName exists in the Schema or not.

Boolean SegmentExistsInSchema(String segmentName).

Parameter 1: String - segmentName. The segment you wish to check for ("MSH", "PID", "OBX", etc).

Returns: Boolean (Yes or No).

List <of Boolean> SegmentsExistsInSchema(List <of String> segmentsToCheck).

Parameter 1: List <of String> - segmentsToCheck. List object containing the segment names you wish to check for ("MSH", "PID", "OBX", etc).

Returns: List <of Boolean> Will return a List <of Boolean> corresponding to the List <of String> passed in Parameter 1.

Boolean Validate().

Returns: Boolean (Yes or No).

Description: This method does a cursory check of the schema to make sure that certain mandatory tables are present, this is similar to performing a "Cursory Schema Validation" in the SQL Schema Engine Profile window.

Event TransactionRunning(object sender, eventArgs e)

Parameter 1: sender will be of type HTSQLServerConnector.

Parameter 2: e will be of derived class HTExceptionEventArg.

Trigger: Fired whenever a multi-command SQL transaction is underway. The event is fired before every command is executed.

Implementation: This class uses the HTSQLServerConnector internally and passes this event through to be picked up and fired in other classes derived from the UPSInfo class. Set e.Cancel() to cancel the operation and rollback the SQL Transaction.