Database Queries

Database queries for the shipping LEAD Storage Server schema and the tutorial schema both follow the same pattern:

A sample query is shown below:

Suppose you want to build a query that returns all patient names that contain "Smith", where the PatientSex is "M".  The query is identical to the sample query above, with the addition of a WHERE statement:

After defining the Storage Catalog and the CatalogEntity classes for each database table (for the tutorial these are MyPatient, MyStudy, MySeries, and MyInstance), the Leadtools.Medical.DataAccessLayer.SqlProviderUtilities.GenerateWhereStatement() method can be used to generate the WHERE statement of the query. An example showing how to this is provided later in the discussion.

The class MyStorageSqlDataAccessAgent that we create for the tutorial will override many of the StorageSqlDbDataAccessAgent methods that create SQL commands. Examples of each override that prepares an SQL query(without the SQL WHERE statement) are shown below, so that you can understand how these queries are constructed.

BEGIN  
SET NOCOUNT ON  
 
CREATE TABLE #PrimaryKeys (  PatientId [int],  StudyId [int],  SeriesId [int],  ImageId [int]  )  
INSERT INTO #PrimaryKeys   
SELECT   MyPatientTable.PatientId,  MyStudyTable.StudyId,  MySeriesTable.SeriesId,  MyInstanceTable.ImageId  
FROM MyPatientTable  
   LEFT OUTER JOIN MyStudyTable      ON MyStudyTable.StudyPatientId      = MyPatientTable.PatientId  
   LEFT OUTER JOIN MySeriesTable     ON MySeriesTable.SeriesStudyId      = MyStudyTable.StudyId  
   LEFT OUTER JOIN MyInstanceTable   ON MyInstanceTable.ImageSeriesId    = MySeriesTable.SeriesId   
    
SET NOCOUNT OFF  
 
SELECT * FROM MyPatientTable WHERE PatientId IN ( SELECT PatientId FROM #PrimaryKeys )  
 
DROP TABLE #PrimaryKeys  
END 

BEGIN  
SET NOCOUNT ON  
 
CREATE TABLE #PrimaryKeys (  PatientId [int],  StudyId [int],  SeriesId [int],  ImageId [int]  )  
INSERT INTO #PrimaryKeys   
SELECT   MyPatientTable.PatientId,  MyStudyTable.StudyId,  MySeriesTable.SeriesId,  MyInstanceTable.ImageId  
FROM MyStudyTable  
   LEFT OUTER JOIN MyPatientTable    ON MyPatientTable.PatientId         = MyStudyTable.StudyPatientId  
   LEFT OUTER JOIN MySeriesTable     ON MySeriesTable.SeriesStudyId      = MyStudyTable.StudyId  
   LEFT OUTER JOIN MyInstanceTable   ON MyInstanceTable.ImageSeriesId    = MySeriesTable.SeriesId   
 
SET NOCOUNT OFF 
  
SELECT * FROM MyPatientTable WHERE PatientId IN ( SELECT PatientId FROM #PrimaryKeys )  
SELECT * FROM MyStudyTable WHERE StudyId IN ( SELECT StudyId FROM #PrimaryKeys )  
 
DROP TABLE #PrimaryKeys  
END 

DELETE  
FROM MyInstanceTable  
WHERE ( MyInstanceTable.SOPInstanceUID  IN  
     ( SELECT MyInstanceTable.SOPInstanceUID  
      FROM MyInstanceTable  
         LEFT OUTER JOIN MySeriesTable     ON MySeriesTable.SeriesId            = MyInstanceTable.ImageSeriesId  
         LEFT OUTER JOIN MyStudyTable      ON MyStudyTable.StudyId              = MySeriesTable.SeriesStudyId  
         LEFT OUTER JOIN MyPatientTable    ON MyPatientTable.PatientId          = MyStudyTable.StudyPatientId   )  
     ) 

DELETE  
FROM MyPatientTable  
WHERE MyPatientTable.PatientId NOT IN ( SELECT MyStudyTable.StudyPatientId FROM MyStudyTable ) 

SELECT StudyStudyInstanceUID  
FROM MyStudyTable  
WHERE StudyStudyInstanceUID='2222' 

SELECT StudyStudyInstanceUID  
FROM MyStudyTable  
WHERE StudyStudyInstanceUID='2222' 

SELECT SeriesSeriesInstanceUID  
FROM MySeriesTable  
WHERE SeriesSeriesInstanceUID='3333' 

SELECT SOPInstanceUID  
FROM MyInstanceTable  
WHERE SOPInstanceUID='4444' 

Help Version 20.0.2018.1.19
Products | Support | Contact Us | Copyright Notices
© 1991-2018 LEAD Technologies, Inc. All Rights Reserved.
LEADTOOLS Imaging, Medical, and Document