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'