概述
本文将主要介绍LEAD存储服务器数据库的数据查询方式，LEAD存储服务器数据库和教程数据库的数据库查询都遵循相同的模式：
- 为每个表创建一个临时表的主键（#PrimaryKeys）
- 插入#PrimaryKeys所有满足SELECT语句的键
- 可以包含一个WHERE语句
- 从#PrimaryKeys中选择MyPatientTable中的所有字段
- 从#PrimaryKeys中选择来自MyStudyTable的所有字段
- 从#PrimaryKeys中选择来自MySeriesTable的所有字段
- 从#PrimaryKeys中选择来自MyInstanceTable的所有字段
- 删除临时表
示例查询如下所示：
假设您想构建一个查询，该查询返回包含“Smith”的所有患者姓名，其中PatientSex为“M”。 查询与上面的示例查询完全相同，但增加了WHERE语句：
在为每个数据库表定义存储目录和CatalogEntity类（对于本教程为MyPatient，MyStudy，MySeries和MyInstance）之后，可以使用Leadtools.Medical.DataAccessLayer.SqlProviderUtilities.GenerateWhereStatement（）方法来生成WHERE查询语句。
我们为本教程创建的MyStorageSqlDataAccessAgent类将覆盖许多用于创建SQL命令的StorageSqlDbDataAccessAgent方法。 下面显示了准备SQL查询（不包括SQL WHERE语句）的每个示例，以便您可以了解如何构建这些查询方式。
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'
了解更多
这是本系列的第五篇文章，本文介绍了LEAD医学存储服务器数据库的数据查询方式，我们将在《LEAD医疗存储服务器自定义数据库系列教程 –自定义医疗存储数据访问层》系列的第六篇文章中，着重介绍LEAD医用存储服务器数据库如何自定义医疗存储数据访问层。
