The SqlTransactionRepository class can be used for additional control over the import of X12 into a SQL Server database.  In most cases you can use the OopFactory.X12.ImportX12 console app out-of-the-box and will only need to work with the SqlReadOnlyTransactionRepository class.

SqlTransactionRepository Class Diagram

These two classes are split apart because their a quite a number of constructor arguments you need when you are importing data that aren't needed when you are reading data.  If you are not using the Revision feature, all you need outside of the OopFactory.X12.ImportX12 app is to use the SqlReadOnlyTransactionRepository.

These classes have been created as generics so that you choose between using SQL's int, bigint or uniqueidentifier as your table identities, by using the class with int, long or Guid for T.

The Container table will hold the identities for interchanges, function groups, transaction sets and for loops, so it will fill up quick, but will probably still be sufficient to use Int in many cases.

SqlReadOnlyTransactionRepository Methods





GetTransactionSetSegments accepts the following arguments:

  • transactionSetID = the Id from the TransactionSet table, this can also be obtained by searching for transactions using the GetTransactionSets method and specifying filter criteria with the RepoTransactionSetSearchCriteria.
  • revisionId - the revisionId of the the transaction, use Int32.MaxValue to get the latest revision.
  • includeControlSegments - will add the ISA,GS,GE and IEA segments to the segments returned.

It will return a List<RepoSegment<T>> which is shown below:







A transaction is defined differently for each transaction set.  The SqlTransactionRepository allows you to treat any loop in your transact set as a transaction.

For example, an 837 health care claim would use SpecLoopId = "2300" to define a claim as a transaction.  When you call GetTransactionSegments, it will unbundle the segments related to that transaction (ancestor loops and their related segments, and all child segments).  This is similare to the UnbundleX12 console app.

The data structure returned is a list of RepoSegments with a Segment property.

To rebuild an unbundled X12 message, call GetTransactionSegments with includeControlSegments = true, and concatenate the Segment.SegmentString with Segment.Delimiters.SegmentTerminator for the segments returned.  This will give you a valid X12 message that you can then use existing transformations to work with. See Claim Parser for some claim transformations.

SqlTransactionRepository Methods


The Save methods accepts an Interchange object which is the output of the X12Parser's PareMultiple method.  You can send this directly to the Save method and it will return the Id value from the Interchange table.  If you intend to do any post processing, this Id can be sent to the GetTransactionSets or GetLoops method to identify the newly created elements that you may want to act upon.

Database Creation

When you call the Save method for the first time, the class will check for the expected database tables and automatically and create the necessary table, views and user-defined functions depending on your values for schema, commonSchema, and indexedSegments.  This is useful to get you started right away, but it does this check on every save.

To bypass this, you can create a derived class with no implementation for EnsureSchema.  You can use the default implementation in development to create your database objects, and then turn off EnsureSchema once you've moved to production so that it doesn't incur the extra database hit.


The SaveRevision methods accepts an IList<RepoSegment<T>> that it expects you got from the GetTransactionSetSegments or GetTransactionSegments methods.

Once you have made the updates to the segments you can send it to the SaveRevision method.  The save revision will insert new database entries for the segments that have actually changed.  Subsequent calls to GetTransactionSetSegments and GetTransactionSegments will only get the latest revision that is less than the RevisionId sent to either of those methods.

Database Concurrency

The SaveRevision method will check concurrency at the segment level.  Before saving a segment change it will make sure that the RevisionID at the time that the RepoSegment was retrieved has not changed.  If it has changed, it will rollback all changes in the revision.  This will cause the Revision table to skip some ID values.

Last edited May 5, 2013 at 1:31 PM by dstrubhar, version 15


No comments yet.