Database Queries

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

• Create a temporary table of primary keys (#PrimaryKeys), one for each table
• Insert into #PrimaryKeys all keys that satisfy the SELECT statement
• Optionally include a WHERE statement
• Select all fields from MyPatientTable from #PrimaryKeys
• Select all fields from MyStudyTable from #PrimaryKeys
• Select all fields from MySeriesTable from #PrimaryKeys
• Select all fields from MyInstanceTable from #PrimaryKeys
• Drop the temporary table

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.

• protected override void PreparePatientsQueryCommand

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 

• protected override void PrepareStudiesQueryCommand

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 

• protected override void PrepareSeriesQueryCommand

  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 MySeriesTable  
     LEFT OUTER JOIN MyStudyTable      ON MyStudyTable.StudyId             = MySeriesTable.SeriesStudyId  
     LEFT OUTER JOIN MyPatientTable    ON MyPatientTable.PatientId         = MyStudyTable.StudyPatientId  
     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 )  
  SELECT * FROM MySeriesTable WHERE SeriesId IN ( SELECT SeriesId FROM #PrimaryKeys )  
   
  DROP TABLE #PrimaryKeys  
  END 

• protected override void PrepareInstanceQueryCommand

  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 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   
      
  SET NOCOUNT OFF  
   
  SELECT * FROM MyPatientTable WHERE PatientId IN ( SELECT PatientId FROM #PrimaryKeys )  
  SELECT * FROM MyStudyTable WHERE StudyId IN ( SELECT StudyId FROM #PrimaryKeys )  
  SELECT * FROM MySeriesTable WHERE SeriesId IN ( SELECT SeriesId FROM #PrimaryKeys )  
  SELECT * FROM MyInstanceTable WHERE ImageId IN ( SELECT ImageId FROM #PrimaryKeys )  
   
  DROP TABLE #PrimaryKeys  
  END 

• protected override void PrepareDeletePatientsCommand

  DELETE  
  FROM MyPatientTable  
  WHERE ( MyPatientTable.PatientId IN  
       ( SELECT MyPatientTable.PatientId  
        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   )  
        ) 

• protected override void PrepareDeleteStudiesCommand

  DELETE  
  FROM MyStudyTable  
  WHERE ( MyStudyTable.StudyId IN  
        ( SELECT MyStudyTable.StudyId  
          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   )  
        ) 

• protected override void PrepareDeleteSeriesCommand

  DELETE  
  FROM MySeriesTable  
  WHERE ( MySeriesTable.SeriesId IN  
       ( SELECT MySeriesTable.SeriesId  
        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   ) 
        ) 

• protected override void PrepareDeleteInstanceCommand

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   )  
     ) 

• protected override void PrepareDeletePatientsNoChildStudiesCommand

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

• protected override void PrepareDeleteStudiesNoChildSeriesCommand

  DELETE  
  FROM MyStudyTable  
  WHERE MyStudyTable.StudyId NOT IN ( SELECT MySeriesTable.SeriesStudyId FROM MySeriesTable ) 

• protected override void PrepareDeleteSeriesNoChildInstancesCommand

  DELETE  
  FROM MySeriesTable  
  WHERE MySeriesTable.SeriesId NOT IN ( SELECT MyInstanceTable.ImageSeriesId FROM MyInstanceTable ) 

• protected override void PrepareIsPatientExistsCommand

SELECT StudyStudyInstanceUID  
FROM MyStudyTable  
WHERE StudyStudyInstanceUID='2222' 

• protected override void PrepareIsStudyExistsCommand

SELECT StudyStudyInstanceUID  
FROM MyStudyTable  
WHERE StudyStudyInstanceUID='2222' 

• protected override void PrepareIsSeriesExistsCommand

SELECT SeriesSeriesInstanceUID  
FROM MySeriesTable  
WHERE SeriesSeriesInstanceUID='3333' 

• protected override void PrepareIsInstanceExistsCommand

SELECT SOPInstanceUID  
FROM MyInstanceTable  
WHERE SOPInstanceUID='4444'