Customizing a LEADTOOLS MWL Database

In this tutorial you will customize a database by adding two new columns to tables in an existing database. Then create an Leadtools.Medical.Worklist.DataAccessLayer.IWorklistDataAccessAgent object to query and update the new columns. Finally you will map the new columns to the appropriate DICOM elements for the client query responses.

For a complete working demo program and source code that incorporates the following steps refer to the CustomizingWorklistDAL demo in <InstallationDirectory>\Bin\Dotnet\Win32 and the source code in <InstallationDirectory>\Examples\Dotnet\.

Note: These tasks and code snippets assume that you have created and configured the base Modality Worklist database and a valid connection exists. LEADTOOLS provides Microsoft SQL Server Compact Edition database files for you to use if necessary. The files are named LeadDicomWorklist32.sdf and LeadDicomWorklist64.sdf. They are a 32-bit and 64-bit respectively and are located in C:\Users\Public\Documents. Choose the database that is appropriate for your application and environment

The two new columns to create are a "SmokingStatus" column in the "Patient" table and a "CommentsOnTheScheduledProcedureStep" in the "ScheduledProcedureStep" table. The two new columns will be mapped to the following DICOM elements:

Smoking Status(0010,21A0)
Procedure Step Comments on the Scheduled(0040,0400)

You can use a visual tool like SQL Enterprise Manager to add the new columns to the appropriate tables in the database. Alternatively, you can use the following SQL commands:


The two new columns to create are a "SmokingStatus" column in the "Patient" table and a "CommentsOnTheScheduledProcedureStep" in the "ScheduledProcedureStep" table. The two new columns will be mapped to the following DICOM elements:

Smoking Status(0010,21A0)
Procedure Step Comments on the Scheduled(0040,0400)



            
                 You can use a visual tool like SQL Enterprise Manager to add the new columns to the 
                 appropriate tables in the database. Alternatively, you can use the following SQL commands:
             
             alter table [Patient] add [SmokingStatus]nvarchar(10)
             alter table [ScheduledProcedureStep] add [CommentOnTheSchedulerProcedureStep] nvarchar(400)

Select a valid data type for the database columns that represents the actual DICOM VR of the elements you are mapping them to.

Use the Leadtools.Medical.Worklist.DataAccessLayer.IWorklistDataAccessAgent object to to query and insert a new value into the new fields in the "SmokingStatus" and "CommentsOnTheScheduleProcedureStep" colums. The following code accomplished these tasks. This code assumes that you have successfully created and configured the base Modality Worklist database and that there is a valid database connection.

[C#]

                 Use the Leadtools.Medical.Worklist.DataAccessLayer.IWorklistDataAccessAgent object to to query and insert a new value into the new fields in the "SmokingStatus" and "CommentsOnTheScheduleProcedureStep" colums.
                 The following code accomplished these tasks. This code assumes that you have successfully created and configured the base Modality Worklist database and that there is a valid database connection.

[C#]
              private void QueryWorklistItems ()
              {
                 IWorklistDataAccessAgent agent = GetDataAccessAgent() ;
                 
                 MWLDataset queryResults = agent.QueryModalityWorklists(new MatchingParameterCollection(), new StringCollection());
                 
                 if (queryResults.Patient.Rows.Count > 0)
                 {
                    queryResults.Patient[0] ["SmokingStatus"] = "UNKNOWN";
                 }
                 
                 if (queryResults.ScheduledProcedureStep.Rows.Count > 0)
                 {
                    queryResults.ScheduledProcedureStep[0] ["CommentsOnTheScheduledProcedureStep"] = "Some comments on the procedure
                 }
                 
                 agent.UpdateMWL(queryResults);
              }
            
              private IWorklistDataAccessAgent GetDataAccessAgent()
              {
                    WorklistDataAccessConfigurationView view = new WorklistDataAccessConfigurationView();
                    IWorklistDataAccessAgent agent;
                    
                    string connectionString = view.GetConnectionStringSettings().ConnectionString;
                    string provider         = view.GetConnectionStringSettings().ProviderName;
                    
                    if (provider == DataAccessMapping.DefaultSqlProviderName)
                    {
                       agent    = new WorklistSqlDbDataAccessAgent (connectionString);
                    }
                    else if (provider == DataAccessMapping.DefaultSqlCe3_5ProviderName)
                    {
                       agent    = new WorklistSqlCeDataAccessAgent(connectionString);
                    }
                    else
                    {
                       throw new NotImplementedException();
                    }
                    
                    return agent;
              }

Now that the Leadtools.Medical.Worklist.DataAccessLayer.IWorklistDataAccessAgent object is able to query and update the new columns you created, you need to support these columns in the DICOM C-Find responses. The ModalityWorklistAddIn uses the Leadtools.Dicom.Scp.Command.MWLCFindCommand to map these two columns to the correct DICOM element in the response to a client query.

The Leadtools.Dicom.Scp.Command.MWLCFindCommand has a static property, Leadtools.Dicom.Scp.Command.MWLCFindCommand.DefualtMWLIOD, which returns a stream. The stream is an XML file containing the DICOM Modality Worklist IOD elements and tells the Leadtools.Dicom.Scp.Command.MWLCFindCommand how each element is mapped in the returned QueryModalityWorklists.

Write the MWL IOD stream to the disk using the following code:

[C#]

                 Write the MWL IOD stream to the disk using the following code:

[C#]
              using (Stream iodStream = MWLCFindCommand.DefualtMWLIOD)
              {
                  using (FileStream customIODStream = new FileStream(_iodPath, FileMode.Create))
                  {
                     int readCount;
                     var buffer = new byte[8192];
                     while((readCount = iodStream.Read(buffer, 0, buffer.Length)) != 0)
                     {
                     customIODStream.Write(buffer, 0, readCount);
                     }
                  }
              }

Open the new xml file in any text editor and find the SmokingStatus and the ScheduledProcedureStep elements. Update the tableName and columnsName attributes with the new values:
- <element tag="(0010,21a0)" tagName="SmokingStatus" vr="CS" minVM="1" maxVM="1" vmDivider="1" returnType="Type3" matchingType="NotApplicable" returning="true" tableName="Patient" matchingEntity="" columnsName="SmokingStatus" />
- <element tag="(0040,0400)" tagName="CommentsOnTheScheduledProcedureStep" vr="LT" minVM="1" maxVM="1" vmDivider="1" returnType="Type3" matchingType="NotApplicable" returning="true" tableName="ScheduledProcedureStep" matchingEntity="" columnsName="CommentsOnTheScheduledProcedureStep" />

You should validate your new XML file according to the schema. Use the following code to validate the new XML file:

[C#]

                 You should validate your new XML file according to the schema. Use the following code to validate the new XML file:

[C#]
              private static void ValidateDocument(string iodPath)
              {
                 XmlSchema         mwlSchema;
                 XmlReaderSettings settings;
                 
                 mwlSchema = MWLCFindCommand.IODSchema;
                 
                 settings = new XmlReaderSettings();
                 
                 settings.Schemas.Add(mwlSchema);
                 settings.ValidationType = ValidationType.Schema;
                 
                 settings.ValidationEventHandler += new ValidationEventHandler(schemaValidationHandler);
                 
                 using (XmlReader reader = XmlTextReader.Create(iodPath, settings))
                 {
                    while (reader.Read());
                 }
              }
              private static void schemaValidationHandler(object sender, ValidationEventArgs args) 
              {
                 throw args.Exception;
              }

Use this new file for the Leadtools.Dicom.Scp.Command.MWLCFindCommand. Use the following code in the ModalityWorklistAddIn add-in:

[C#]

                 Use this new file for the Leadtools.Dicom.Scp.Command.MWLCFindCommand. Use the following code in the ModalityWorklistAddIn add-in:

[C#]
              MWLCFindCommand command = new MWLCFindCommand(clientSession, requestDS, GetDataAccessAgent());
              AutoResetEvent resetEvent = new AutoResetEvent(false);
              command.MWLConfiguration.ModalityWorklistIODPath = _iodPath;
                    
              command.Execute(resetEvent);

The following are notes and descriptions of the attributes in the MWL IOD file:

All elements of the Modality Work-list IOD are listed in the document.
Elements should be sorted according to the DICOM specs based on the Tag Number.
The MWLCFindCommand command will validate the dataset request against the IOD, so if sorting is not right a validation error will occur at runtime.
Extra DICOM element or Private elements might be added, but the new items should be placed in the right sorting order.
Simple DICOM Elements are added to the document with the XML tag <element>
Sequence DICOM Elements are added to the document with the XML tag <sequence>
Sequence Item DICOM Element are added to the document with the XML tag <item> and they must appear only after a <sequence> tag.
The IOD is defined by a schema, when updating an IOD you should make sure that it is valid according to the defined schema.
The following is an explanation of each xml tag <element> and <sequence>

The following is an explanation of the <element> and <sequence> XML tags:


In this tutorial you will customize a database by adding two new columns to tables in an 
             existing database. Then create an Leadtools.Medical.Worklist.DataAccessLayer.IWorklistDataAccessAgent object to query and update the new columns. 
             Finally you will map the new columns to the appropriate DICOM elements  for the client query responses. 
             
For a complete working demo program and source code that incorporates the following steps refer to the 
             CustomizingWorklistDAL demo in <InstallationDirectory>\Bin\Dotnet\Win32 and the source code in 
             <InstallationDirectory>\Examples\Dotnet\.
             
Note: These tasks and code snippets assume that you have created and configured the base 
             Modality Worklist database and a valid connection exists. 
             LEADTOOLS provides Microsoft SQL Server Compact Edition database files for you to use if necessary. 
             The files are named LeadDicomWorklist32.sdf and LeadDicomWorklist64.sdf. 
             They are a 32-bit and 64-bit respectively and are located in C:\Users\Public\Documents. 
             Choose the database that is appropriate for your application and environment

Smoking Status(0010,21A0)
Procedure Step Comments on the Scheduled(0040,0400)

You can use a visual tool like SQL Enterprise Manager to add the new columns to the appropriate tables in the database. Alternatively, you can use the following SQL commands:


The two new columns to create are a "SmokingStatus" column in the "Patient" table and a "CommentsOnTheScheduledProcedureStep" in the "ScheduledProcedureStep" table. The two new columns will be mapped to the following DICOM elements:

Smoking Status(0010,21A0)
Procedure Step Comments on the Scheduled(0040,0400)



            
                 You can use a visual tool like SQL Enterprise Manager to add the new columns to the 
                 appropriate tables in the database. Alternatively, you can use the following SQL commands:
             
             alter table [Patient] add [SmokingStatus]nvarchar(10)
             alter table [ScheduledProcedureStep] add [CommentOnTheSchedulerProcedureStep] nvarchar(400)

Select a valid data type for the database columns that represents the actual DICOM VR of the elements you are mapping them to.

                 Use the Leadtools.Medical.Worklist.DataAccessLayer.IWorklistDataAccessAgent object to to query and insert a new value into the new fields in the "SmokingStatus" and "CommentsOnTheScheduleProcedureStep" colums.
                 The following code accomplished these tasks. This code assumes that you have successfully created and configured the base Modality Worklist database and that there is a valid database connection.

[C#]
              private void QueryWorklistItems ()
              {
                 IWorklistDataAccessAgent agent = GetDataAccessAgent() ;
                 
                 MWLDataset queryResults = agent.QueryModalityWorklists(new MatchingParameterCollection(), new StringCollection());
                 
                 if (queryResults.Patient.Rows.Count > 0)
                 {
                    queryResults.Patient[0] ["SmokingStatus"] = "UNKNOWN";
                 }
                 
                 if (queryResults.ScheduledProcedureStep.Rows.Count > 0)
                 {
                    queryResults.ScheduledProcedureStep[0] ["CommentsOnTheScheduledProcedureStep"] = "Some comments on the procedure
                 }
                 
                 agent.UpdateMWL(queryResults);
              }
            
              private IWorklistDataAccessAgent GetDataAccessAgent()
              {
                    WorklistDataAccessConfigurationView view = new WorklistDataAccessConfigurationView();
                    IWorklistDataAccessAgent agent;
                    
                    string connectionString = view.GetConnectionStringSettings().ConnectionString;
                    string provider         = view.GetConnectionStringSettings().ProviderName;
                    
                    if (provider == DataAccessMapping.DefaultSqlProviderName)
                    {
                       agent    = new WorklistSqlDbDataAccessAgent (connectionString);
                    }
                    else if (provider == DataAccessMapping.DefaultSqlCe3_5ProviderName)
                    {
                       agent    = new WorklistSqlCeDataAccessAgent(connectionString);
                    }
                    else
                    {
                       throw new NotImplementedException();
                    }
                    
                    return agent;
              }

Now that the Leadtools.Medical.Worklist.DataAccessLayer.IWorklistDataAccessAgent object is able to query and update the new columns you created, you need to support these columns in the DICOM C-Find responses. The ModalityWorklistAddIn uses the Leadtools.Dicom.Scp.Command.MWLCFindCommand to map these two columns to the correct DICOM element in the response to a client query.

The Leadtools.Dicom.Scp.Command.MWLCFindCommand has a static property, Leadtools.Dicom.Scp.Command.MWLCFindCommand.DefualtMWLIOD, which returns a stream. The stream is an XML file containing the DICOM Modality Worklist IOD elements and tells the Leadtools.Dicom.Scp.Command.MWLCFindCommand how each element is mapped in the returned QueryModalityWorklists.

Write the MWL IOD stream to the disk using the following code:

[C#]

                 Write the MWL IOD stream to the disk using the following code:

[C#]
              using (Stream iodStream = MWLCFindCommand.DefualtMWLIOD)
              {
                  using (FileStream customIODStream = new FileStream(_iodPath, FileMode.Create))
                  {
                     int readCount;
                     var buffer = new byte[8192];
                     while((readCount = iodStream.Read(buffer, 0, buffer.Length)) != 0)
                     {
                     customIODStream.Write(buffer, 0, readCount);
                     }
                  }
              }

Open the new xml file in any text editor and find the SmokingStatus and the ScheduledProcedureStep elements. Update the tableName and columnsName attributes with the new values:
- <element tag="(0010,21a0)" tagName="SmokingStatus" vr="CS" minVM="1" maxVM="1" vmDivider="1" returnType="Type3" matchingType="NotApplicable" returning="true" tableName="Patient" matchingEntity="" columnsName="SmokingStatus" />
- <element tag="(0040,0400)" tagName="CommentsOnTheScheduledProcedureStep" vr="LT" minVM="1" maxVM="1" vmDivider="1" returnType="Type3" matchingType="NotApplicable" returning="true" tableName="ScheduledProcedureStep" matchingEntity="" columnsName="CommentsOnTheScheduledProcedureStep" />

You should validate your new XML file according to the schema. Use the following code to validate the new XML file:

[C#]

                 You should validate your new XML file according to the schema. Use the following code to validate the new XML file:

[C#]
              private static void ValidateDocument(string iodPath)
              {
                 XmlSchema         mwlSchema;
                 XmlReaderSettings settings;
                 
                 mwlSchema = MWLCFindCommand.IODSchema;
                 
                 settings = new XmlReaderSettings();
                 
                 settings.Schemas.Add(mwlSchema);
                 settings.ValidationType = ValidationType.Schema;
                 
                 settings.ValidationEventHandler += new ValidationEventHandler(schemaValidationHandler);
                 
                 using (XmlReader reader = XmlTextReader.Create(iodPath, settings))
                 {
                    while (reader.Read());
                 }
              }
              private static void schemaValidationHandler(object sender, ValidationEventArgs args) 
              {
                 throw args.Exception;
              }

Use this new file for the Leadtools.Dicom.Scp.Command.MWLCFindCommand. Use the following code in the ModalityWorklistAddIn add-in:

[C#]

                 Use this new file for the Leadtools.Dicom.Scp.Command.MWLCFindCommand. Use the following code in the ModalityWorklistAddIn add-in:

[C#]
              MWLCFindCommand command = new MWLCFindCommand(clientSession, requestDS, GetDataAccessAgent());
              AutoResetEvent resetEvent = new AutoResetEvent(false);
              command.MWLConfiguration.ModalityWorklistIODPath = _iodPath;
                    
              command.Execute(resetEvent);


The following are notes and descriptions of the attributes in the MWL IOD file:

All elements of the Modality Work-list IOD are listed in the document.
Elements should be sorted according to the DICOM specs based on the Tag Number.
The MWLCFindCommand command will validate the dataset request against the IOD, so if sorting is not right a validation error will occur at runtime.
Extra DICOM element or Private elements might be added, but the new items should be placed in the right sorting order.
Simple DICOM Elements are added to the document with the XML tag <element>
Sequence DICOM Elements are added to the document with the XML tag <sequence>
Sequence Item DICOM Element are added to the document with the XML tag <item> and they must appear only after a <sequence> tag.
The IOD is defined by a schema, when updating an IOD you should make sure that it is valid according to the defined schema.
The following is an explanation of each xml tag <element> and <sequence>


The following is an explanation of the <element> and <sequence> XML tags:
             <element  tag="" tagName="" vr="" minVM="" maxVM="" vmDivider="" returnType="" matchingType="" returning="" tableName="" matchingEntity="" columnsName="" />
             <sequence tag="" tagName="" vr="SQ" minVM="" maxVM="" vmDivider="" returnType="" returning="" tableName="">

tag: The DICOM tag for the element: This is the most important tag which define which DICOM element this tag corresponds to.
tagName: This can be ANY human readable name.
vr: The DICOM Value Representation which will be used to validate the request value.
MinVM: The minimum value multiplicity which is used for validation.
MaxVM: The maximum value multiplicity which is used for validation.
vmDivider: The step size of the value multiplicity.
returnType: The DICOM return type (check the schema for allowed values), used to validate that elements of type1 has value on returning and remove type3 elements with no value from response.
matchingType: The DICOM matching type (check the schema for allowed values), used to tell the MWL Command how to perform matching on each element.
returning: Tells the MWL Command whether the element should be returned in the response.
tableName:Tells the MWL Command the ADO.NET table where this element exists.
ColumnsName:Tells the MWL Command the ADO.NET DataColumn(s) where it should get the value for this DICOM element.
matchingEntity:If the value of matchingType is anything other than "NotApplicable", this value should be filled with the object used as matching parameters when communication with the MWL Data Access Layer.

tag: The DICOM tag for the element: This is the most important tag which define which DICOM element this tag corresponds to.
tagName: This can be ANY human readable name.
vr: The DICOM Value Representation which will be used to validate the request value.
MinVM: The minimum value multiplicity which is used for validation.
MaxVM: The maximum value multiplicity which is used for validation.
vmDivider: The step size of the value multiplicity.
returnType: The DICOM return type (check the schema for allowed values), used to validate that elements of type1 has value on returning and remove type3 elements with no value from response.
matchingType: The DICOM matching type (check the schema for allowed values), used to tell the MWL Command how to perform matching on each element.
returning: Tells the MWL Command whether the element should be returned in the response.
tableName:Tells the MWL Command the ADO.NET table where this element exists.
ColumnsName:Tells the MWL Command the ADO.NET DataColumn(s) where it should get the value for this DICOM element.
matchingEntity:If the value of matchingType is anything other than "NotApplicable", this value should be filled with the object used as matching parameters when communication with the MWL Data Access Layer.