LEADTOOLS医疗存储服务器自定义数据库系列教程（五）

LEADTOOLS医疗存储服务器自定义数据库系列教程（五）— 数据库查询

转帖|使用教程|编辑：杨鹏连|2021-01-29 13:52:52.133|阅读 134 次

概述：本文将主要介绍LEAD存储服务器数据库的数据查询方式，LEAD存储服务器数据库和教程数据库的数据库查询都遵循相同的模式。

相关链接：

LEADTOOLS Recognition Imaging SDK是精选的LEADTOOLS SDK功能集，旨在在企业级文档自动化解决方案中构建端到端文档成像应用程序，这些解决方案需要OCR，MICR，OMR，条形码，表单识别和处理，PDF，打印捕获，档案，注释和图像查看功能。这套功能强大的工具利用LEAD屡获殊荣的图像处理技术，智能识别可用于识别和提取任何类型的扫描或传真形式图像数据的文档功能。

点击下载LEADTOOLS Recognition Imaging SDK试用版

概述

本文将主要介绍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医用存储服务器数据库如何自定义医疗存储数据访问层。

试用版下载>>>

LEADTOOLS 使用教程>>>

想要购买LEADTOOLS正版授权，或了解更多产品信息请点击

标签：

本站文章除注明转载外，均为本站原创或翻译。欢迎任何形式的转载，但请务必注明出处、不得修改原文相关链接，如果存在内容上的异议请邮件反馈至chenjj@pclwef.cn

上一篇：LEADTOOLS医疗存储服务器自定义数据库系列教程（四）— 存储目录下一篇：BI工具 Qlik 教程：Qlik Sense 入门知识

彩票走势图

在线采购

中文文档库

试用/Demo下载

开发社群