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 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, 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 PatientIdentification

FROM MyPatientTable

WHERE PatientIdentification='1111'

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'