Custom Medical Storage Data Access Layer

A custom medical storage data access layer must contain the following classes:

*

·         class MyPatient : CatalogEntity

·         class MyStudy : CatalogEntity

·         class MySeries : CatalogEntity

·         class MyInstance : CatalogEntity

The medical storage data access layer used in the tutorial also includes the following helper classes, which are not strictly required but simplify implementation:

·         class MyDicomDataSetConvertor

·         class MyConstants

·         class MySqlStatments

The sample custom medical storage data access layer is called My.Medical.Storage.DataAccessLayer. It is included in the LEAD installation as a sample project.  It contains all of the classes in the two tables. 

Open the project file in Visual Studio, and you will see the files in the project:

The following table describes the contents of each file

These classes are described in detail below, and organized by namespace

namespace My.Medical.Storage.DataAccessLayer

·         class MyPatientInfo : IPatientInfo

·         class MyStudyInfo : IStudyInfo

·         class MySeriesInfo : ISeriesInfo

·         class MyInstanceInfo : IInstanceInfo

o        implements an interface to extract DICOM data from a System.Data.DataRow

·         class MyStorageDataAccessConfigurationView

o        Helper class used to create the data access agent

o        Internally stores the type that implements IStorageDataAccessAgent (MyStorageSqlDbDataAccessAgent), so it can be created dynamically

o        Associates a DataAccessLayer with a DataAccessAgent and a configuration file (GlobalPacs.config)

o        Defines the DataAccessLayer section name (used in GlobalPacs.config)

o        GlobalPacs.config in turn associates the DataAccessLayer section name with a connection string

·         class MyStorageSqlDbDataAccessAgent

o        Provides methods to create SQL queries to access (insert, update, delete) the table entries in the database

o        The methods override the default behavior of the StorageSqlDbDataAccessAgent.

o        Query Commands

§         protected override void PreparePatientsQueryCommand

§         protected override void PrepareStudiesQueryCommand

§         protected override void PrepareSeriesQueryCommand

§         protected override void PrepareInstanceQueryCommand

·         Creates a command to query the database based on a level (i.e PrepareSeriesQueryCommand creates a command for a series level database query)

§         protected override void PrepareInstancePageQueryCommand

·         Prepares an Instance level command when using pagination.  Pagination returns results in one or more pages of data, where the user specifies the page size.  The pagination feature is not implemented in this sample data access agent.

o        Count Commands

§         protected override void PreparePatientsQueryCountCommand

§         protected override void PrepareStudiesQueryCountCommand

§         protected override void PrepareSeriesQueryCountCommand

§         protected override void PrepareInstanceQueryCountCommand

·         Creates a command that returns a count of rows matching the search criteria specified in the matchingParamsCollection

o        Delete Commands

§         protected override void PrepareDeletePatientsCommand

§         protected override void PrepareDeleteStudiesCommand

§         protected override void PrepareDeleteSeriesCommand

§         protected override void PrepareDeleteInstanceCommand

·         Creates a command to delete rows of a System.Data.DataTable based on the search criteria specified in the matchingParamsCollection

§         protected override void PrepareDeletePatientsNoChildStudiesCommand

§         protected override void PrepareDeleteStudiesNoChildSeriesCommand

§         protected override void PrepareDeleteSeriesNoChildInstancesCommand

·         Creates a command to delete rows from a table that has no corresponding rows in a child table.  For example,  the PrepareDeletePatientsNoChildStudiesCommand command would be invoked if a MyPatient table entry had no corresponding entry in the MyStudy table.

§         public override int DeletePatient

§         public override int DeleteStudy

§         public override int DeleteSeries

§         public override int DeleteInstance

·         High-level commands that deletes rows from a database table based on the search criteria specified in the matchingParamsCollection

·         Uses the PrepareDeleteXxxxCommand() overrides

·         These commands also delete the corresponding rows in the tables above it in the hierarchy

·         For example, DeleteStudy will delete one or more rows from the MyStudy table. If the corresponding MyPatientTable patient rows contain no more studies, then these are also deleted.

o        Exists Commands

§         protected override void PrepareIsPatientExistsCommand

§         protected override void PrepareIsStudyExistsCommand

§         protected override void PrepareIsSeriesExistsCommand

§         protected override void PrepareIsInstanceExistsCommand

·         Creates a command to test for existence based on unique ID (PatientID, StudyInstanceUID, SeriesInstanceUID, or SOPInstanceUID)

o        Miscellaneous Commands

§         protected override string[] GetCompositeInstanceQueryDataAdapterTables

·         Returns a list containing the all tables names in your database

§         protected override DataSet CreateCompositeInstanceDataSet           

·         Creates a DataSet representing the tables of your database.  This is actually a strongly typed, but it is returned as the base class DataSet.  The default implementation returns CompositeInstanceDataSet. For the tutorial, you will return the strongly typed MyDataSet.

o        MyPatient

o        MyStudy

o        MySeries

o        MyInstance

§         public virtual void UpdateCompositeInstance

·         Updates the necessary table entries (MyPatient, MyStudy, MySeries, MyInstance) in the database, based on the input parameter myDataSet.

·         myDataSet is a DataSet that contains all the necessary additions, updates, and deletions that need to be applied

·         If there is an exception during the process, all updates are backed out.

§         public override void StoreDicom

·         Creates the necessary table entries (MyPatient, MyStudy, MySeries, MyInstance) for a Leadtools.Dicom.DicomDataSet

·         Table rows are overwritten based on the parameters

o        updateExistentPatient

o        updateExistentStudy

o        updateExistentSeries

o        updateExistentInstances

namespace My.Medical.Storage.DataAccessLayer.DataAccessLogic.BusinessEntity

·         class MyDataSet

o        A strongly typed class that represents an in-memory representation of your custom database

o        Has members that allow read/write access to database fields

o        Maintains list of foreign-key relationships between database tables

o        This class can be generated using Visual Studio.  For instructions, see Creating the Strongly Typed DataSet Class and the XML Schema File. 

namespace My.Medical.Storage.DataAccessLayer.DataAccessLogic.DataAccessAgent.Database.SqlServer

·         class MyConstants

·         class MySqlStatments

o   SQL query statements and statement fragments used by MyStorageSqlDbDataAccessAgent class to query/update/and delete items from the database

namespace My.Medical.Storage.DataAccessLayer.DataAccessLogic.DicomDataSetConvertor

·         class MyDicomDataSetConvertor

o   public void FillADONetDataSet

·         Called by MyStorageSqlDbDataAccessAgent.StoreDicom

·         Extracts the patient, study, series, and instance information from a DicomDataSet object, and add/updates the DataSet tables (patient, study, series, instance) accordingly.

·         The update parameters (updateExistentPatient, updateExistentStudy, updateExistentSeries, updateExistentInstances) affect if corresponding existing DataSet rows remain unchanged, or are overwritten. 

·         For example, if updateExistentPatient is true and there is already a row the DataSet ‘MyPatient’ table with the same Patient ID, then the patient row is overwritten with new patient data.

o   public bool AutoTruncate

·         If true, data from the DicomDataSet is truncated if necessary, so that if fits in the DataTable column maximum length.

o   private void FillPatientData

·         Adds a new row to the DataSet Patient table (MyPatientTable) if the DicomDataSet PatientID does not already exist

o   private void FillStudiesData

·         Adds a new row to the DataSet Study Table (MyStudyTable) if the DicomDataSet StudyInstanceUID does not already exist

o   private void FillSeriesData

·         Adds a new row to the DataSet Study Table (MySeriesTable) if the DicomDataSet SeriesInstanceUID does not already exist

o   private void FillInstancetData

·         Adds a new row to the DataSet Study Table (MyInstanceTable) if the DicomDataSet SOPInstanceUID does not already exist

·          

o   private void FillPatientInformation

·         If the ‘update’ parameter is true, replaces all the DataSet Patient table information with the corresponding information from the DicomDataSet.

·         This method needs to change based on your schema.  For the tutorial schema, we update all fields in the patient table

·         PatientName

·         PatientBirthday

·         PatientSex

·         PatientComments

o   private void FillStudyInformation

·         If the ‘update’ parameter is true, replaces all the DataSet Study table information with the corresponding information from the DicomDataSet.

·         This method needs to change based on your schema.  For the tutorial schema, we update all fields in the Study table

·         StudyDate

·         AccessionNumber

·         StudyDescription

·         StudyReferringPhysiciansName

o   private void FillSeriesInformation

·         If the ‘update’ parameter is true, replaces all the DataSet Series table information with the corresponding information from the DicomDataSet.

·         This method needs to change based on your schema.  For the tutorial schema, we update all fields in the Series table

·         SeriesNumber

·         SeriesDate

·         SeriesDescription

·         Modality

·         BodyPartExamined

o   private void FillInstanceInformation

·         If the ‘update’ parameter is true, replaces all the DataSet Instance table information with the corresponding information from the DicomDataSet.

·         This method needs to change based on your schema.  For the tutorial schema, we update all fields in the Series table

·         InstanceNumber

·         SOPClassUID

·         Rows

·         Columns

·         BitsAllocated

·         ImageLastStoreDate – Updated to current date.

·         ImageFilename – Full path to location where DicomDataSet is stored

namespace My.Medical.Storage.DataAccessLayer.Entities

·         public class MyPatient

·         public class MyStudy

·         public class MySeries

·         public class MyInstance

o        These classes are used with the MatchingParameterCollection to generate the WHERE statement of the database query.  

o        A MatchingParameterCollection contains one more MatchingParameterList 

o        A MatchingParameterList contains one or more ICatalogEntity

o        The MyPatient, MyStudy, MySeries, and MyInstance classes each derive from CatalogEntity (which implements the ICatalogEntity interface).

o        MyPatient contains a property for everything that can be queried in the MyPatientTable.

o        Each property is decorated with the [EntityElementAttribute] attribute.

o        For example, the MyPatientTable contains the following columns, any or all of which can be part of a query

§         PatientId

§         PatientIdentification

§         PatientName

§         PatientBirthday

§         PatientSex

§         PatientComments

      // This example builds a IDbCommand

// for querying the MyPatient table

      // The generated WHERE clause contains PatientName and PatientSex

      public void MyExample()

      {

         MatchingParameterCollection matchingParamCollection = new MatchingParameterCollection();

         MatchingParameterList matchingParamList = new MatchingParameterList();

         MyPatient myPatient = new MyPatient();

         myPatient.PatientName = "Smith";

         myPatient.PatientSex = "M";

         matchingParamList.Add(myPatient);

         matchingParamCollection.Add(matchingParamList);

         IDbCommand command = new SqlCommand();

         // This reads the storage catalog

         // Before you run this, make sure and add the following to your application configuration file

         //<configSections>

         //    <section name="xmlStorageCatalogSettings" type="Leadtools.Medical.Storage.DataAccessLayer.XmlStorageCatalogSettings, Leadtools.Medical.Storage.DataAccessLayer" />

         //</configSections>

         StorageCatalog myCatalog = new StorageCatalog();

         Collection<CatalogElement[]> catalogElements = CatalogDescriptor.GetElementsCollection(matchingParamCollection, myCatalog , true);

         PreparePatientsQueryCommand(command, catalogElements);

         // The 'WHERE' clause of command.CommandText is the following:

         //    WHERE ( (  MyPatientTable.PatientName LIKE  'Smith' ) AND (  MyPatientTable.PatientSex LIKE  'M' ) )

         Console.WriteLine(command.CommandText);

      }