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

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

LEADTOOLS Recognition Imaging SDK试用版

概述

• 为每个表创建一个临时表的主键（#PrimaryKeys）
• 插入#PrimaryKeys所有满足SELECT语句的键
• 可以包含一个WHERE语句
• 从#PrimaryKeys中选择MyPatientTable中的所有字段
• 从#PrimaryKeys中选择来自MyStudyTable的所有字段
• 从#PrimaryKeys中选择来自MySeriesTable的所有字段
• 从#PrimaryKeys中选择来自MyInstanceTable的所有字段
• 删除临时表

示例查询如下所示：

假设您想构建一个查询，该查询返回包含“Smith”的所有患者姓名，其中PatientSex为“M”。 查询与上面的示例查询完全相同，但增加了WHERE语句：

protected override void PreparePatientsQueryCommand

BEGINSET NOCOUNT ONCREATE TABLE #PrimaryKeys ( PatientId [int], StudyId [int], SeriesId [int],ImageId [int] )INSERT INTO #PrimaryKeysSELECT MyPatientTable.PatientId, MyStudyTable.StudyId, MySeriesTable.SeriesId,MyInstanceTable.ImageIdFROM MyPatientTableLEFT OUTER JOIN MyStudyTable ON MyStudyTable.StudyPatientId =MyPatientTable.PatientIdLEFT OUTER JOIN MySeriesTable ON MySeriesTable.SeriesStudyId =MyStudyTable.StudyIdLEFT OUTER JOIN MyInstanceTable ON MyInstanceTable.ImageSeriesId =MySeriesTable.SeriesIdSET NOCOUNT OFFSELECT * FROM MyPatientTable WHERE PatientId IN ( SELECT PatientId FROM#PrimaryKeys )DROP TABLE #PrimaryKeysEND**protected override void PrepareStudiesQueryCommand**BEGINSET NOCOUNT ONCREATE TABLE #PrimaryKeys ( PatientId [int], StudyId [int], SeriesId [int],ImageId [int] )INSERT INTO #PrimaryKeysSELECT MyPatientTable.PatientId, MyStudyTable.StudyId, MySeriesTable.SeriesId,MyInstanceTable.ImageIdFROM MyStudyTableLEFT OUTER JOIN MyPatientTable ON MyPatientTable.PatientId =MyStudyTable.StudyPatientIdLEFT OUTER JOIN MySeriesTable ON MySeriesTable.SeriesStudyId =MyStudyTable.StudyIdLEFT OUTER JOIN MyInstanceTable ON MyInstanceTable.ImageSeriesId =MySeriesTable.SeriesIdSET NOCOUNT OFFSELECT * FROM MyPatientTable WHERE PatientId IN ( SELECT PatientId FROM#PrimaryKeys )SELECT * FROM MyStudyTable WHERE StudyId IN ( SELECT StudyId FROM #PrimaryKeys)DROP TABLE #PrimaryKeysEND

protected override void PrepareSeriesQueryCommand

BEGINSET NOCOUNT ONCREATE TABLE #PrimaryKeys ( PatientId [int], StudyId [int], SeriesId [int],ImageId [int] )INSERT INTO #PrimaryKeysSELECT MyPatientTable.PatientId, MyStudyTable.StudyId, MySeriesTable.SeriesId,MyInstanceTable.ImageIdFROM MySeriesTableLEFT OUTER JOIN MyStudyTable ON MyStudyTable.StudyId =MySeriesTable.SeriesStudyIdLEFT OUTER JOIN MyPatientTable ON MyPatientTable.PatientId =MyStudyTable.StudyPatientIdLEFT OUTER JOIN MyInstanceTable ON MyInstanceTable.ImageSeriesId =MySeriesTable.SeriesIdSET NOCOUNT OFFSELECT * 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 #PrimaryKeysEND

protected override void PrepareInstanceQueryCommand

BEGINSET NOCOUNT ONCREATE TABLE #PrimaryKeys ( PatientId [int], StudyId [int], SeriesId [int],ImageId [int] )INSERT INTO #PrimaryKeysSELECT MyPatientTable.PatientId, MyStudyTable.StudyId, MySeriesTable.SeriesId,MyInstanceTable.ImageIdFROM MyInstanceTableLEFT OUTER JOIN MySeriesTable ON MySeriesTable.SeriesId =MyInstanceTable.ImageSeriesIdLEFT OUTER JOIN MyStudyTable ON MyStudyTable.StudyId =MySeriesTable.SeriesStudyIdLEFT OUTER JOIN MyPatientTable ON MyPatientTable.PatientId =MyStudyTable.StudyPatientIdSET NOCOUNT OFFSELECT * 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 #PrimaryKeysEND

protected override void PrepareDeletePatientsCommand

DELETEFROM MyPatientTableWHERE ( MyPatientTable.PatientId IN( SELECT MyPatientTable.PatientIdFROM MyInstanceTableLEFT OUTER JOIN MySeriesTable ON MySeriesTable.SeriesId =MyInstanceTable.ImageSeriesIdLEFT OUTER JOIN MyStudyTable ON MyStudyTable.StudyId =MySeriesTable.SeriesStudyIdLEFT OUTER JOIN MyPatientTable ON MyPatientTable.PatientId =MyStudyTable.StudyPatientId ))

protected override void PrepareDeleteStudiesCommand

DELETEFROM MyStudyTableWHERE ( MyStudyTable.StudyId IN( SELECT MyStudyTable.StudyIdFROM MyInstanceTableLEFT OUTER JOIN MySeriesTable ON MySeriesTable.SeriesId =MyInstanceTable.ImageSeriesIdLEFT OUTER JOIN MyStudyTable ON MyStudyTable.StudyId =MySeriesTable.SeriesStudyIdLEFT OUTER JOIN MyPatientTable ON MyPatientTable.PatientId =MyStudyTable.StudyPatientId ))

protected override void PrepareDeleteSeriesCommand

DELETEFROM MySeriesTableWHERE ( MySeriesTable.SeriesId IN( SELECT MySeriesTable.SeriesIdFROM MyInstanceTableLEFT OUTER JOIN MySeriesTable ON MySeriesTable.SeriesId =MyInstanceTable.ImageSeriesIdLEFT OUTER JOIN MyStudyTable ON MyStudyTable.StudyId =MySeriesTable.SeriesStudyIdLEFT OUTER JOIN MyPatientTable ON MyPatientTable.PatientId =MyStudyTable.StudyPatientId ))

protected override void PrepareDeleteInstanceCommand

DELETEFROM MyInstanceTableWHERE ( MyInstanceTable.SOPInstanceUID IN( SELECT MyInstanceTable.SOPInstanceUIDFROM MyInstanceTableLEFT OUTER JOIN MySeriesTable ON MySeriesTable.SeriesId =MyInstanceTable.ImageSeriesIdLEFT OUTER JOIN MyStudyTable ON MyStudyTable.StudyId =MySeriesTable.SeriesStudyIdLEFT OUTER JOIN MyPatientTable ON MyPatientTable.PatientId =MyStudyTable.StudyPatientId ))

protected override void PrepareDeletePatientsNoChildStudiesCommand

DELETEFROM MyPatientTableWHERE MyPatientTable.PatientId NOT IN ( SELECT MyStudyTable.StudyPatientId FROMMyStudyTable )**protected override void PrepareDeleteStudiesNoChildSeriesCommand**DELETEFROM MyStudyTableWHERE MyStudyTable.StudyId NOT IN ( SELECT MySeriesTable.SeriesStudyId FROMMySeriesTable )

protected override void PrepareDeleteSeriesNoChildInstancesCommand

DELETEFROM MySeriesTableWHERE MySeriesTable.SeriesId NOT IN ( SELECT MyInstanceTable.ImageSeriesId FROMMyInstanceTable )

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′

了解更多

试用版下载>>>

LEADTOOLS 使用教程>>>

想要购买LEADTOOLS正版授权，或了解更多产品信息请点击【咨询在线客服】

标签：