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
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 )
)
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'
SELECT StudyStudyInstanceUID
FROM MyStudyTable
WHERE StudyStudyInstanceUID='2222'
SELECT SeriesSeriesInstanceUID
FROM MySeriesTable
WHERE SeriesSeriesInstanceUID='3333'
SELECT SOPInstanceUID
FROM MyInstanceTable
WHERE SOPInstanceUID='4444'
Raster .NET | C API | C++ Class Library | JavaScript HTML5
Document .NET | C API | C++ Class Library | JavaScript HTML5
Medical .NET | C API | C++ Class Library | JavaScript HTML5
Medical Web Viewer .NET