IQueryDef用于属性查询。它支持复杂的SQL查询并且能在同一工作空间内做连表查询。但是查询后的结果不支持编辑。且不能在Shapefile 和Coverage 数据中使用,而仅仅可以使用在GeoDatabase数据库中。若要执行空间查询或编辑查询后结果可用IQueryFilter或ISpatialFilter接口。官网地址:转至官网
测试数据准备:
使用QueryDef查询:
1、最普遍的查询例子
public static void UsingIQueryDef()
{
Type type = Type.GetTypeFromProgID("esriDataSourcesGDB.FileGDBWorkspaceFactory");
IWorkspaceFactory pWorkspaceFactory = Activator.CreateInstance(type) as IWorkspaceFactory;
IWorkspace pWorkspace = pWorkspaceFactory.OpenFromFile(@"F:\GIS测试数据\测试.gdb", 0);
IFeatureWorkspace pFeatureWorkspace = pWorkspace as IFeatureWorkspace;
//创建 QueryDef
IQueryDef2 pQueryDef2 = (IQueryDef2)pFeatureWorkspace.CreateQueryDef();
//指定表查询
pQueryDef2.Tables = "ABC_Point";
pQueryDef2.WhereClause = "OBJECTID=1";
using (ComReleaser comReleaser = new ComReleaser())
{
ICursor pCursor = pQueryDef2.Evaluate();
comReleaser.ManageLifetime(pCursor);
//字段——索引字典
Dictionary<string, int> dicField = new Dictionary<string, int>();
for (int i = 0; i < pCursor.Fields.FieldCount; i++)
dicField[pCursor.Fields.Field[i].Name] = i;
IRow pRow = null;
while ((pRow = pCursor.NextRow()) != null)
{
string str = string.Empty;
foreach (var it in dicField)
str += $"{it.Key}:{pRow.Value[it.Value]} ";
Console.WriteLine(str);
}
}
}
2、使用JOINS连表查询
pQueryDef2.Tables = "ABC INNER JOIN ABC_Point ON ABC.BSM =ABC_Point.BSM";
pQueryDef2.SubFields = "ABC.BSM,ABC.ADDRESS,ABC_Point.NAME";
/**
结果:
ABC.BSM:111222 ABC.ADDRESS:赵国 ABC_Point.NAME:张三
ABC.BSM:333444 ABC.ADDRESS:燕国 ABC_Point.NAME:李四
ABC.BSM:333444 ABC.ADDRESS:燕国 ABC_Point.NAME:王五
*/
3、DISTINCT
pQueryDef2.Tables = "ABC_Point";
pQueryDef2.SubFields = "bsm";
pQueryDef2.PrefixClause = "distinct";
//等同于 pQueryDef2.SubFields = "distinct bsm";
/**
结果:
bsm:555666
bsm:111222
bsm:333444
*/
4、ORDER BY 排序
pQueryDef2.Tables = "ABC_Point";
pQueryDef2.SubFields = "BSM,NAME as AAA"; //默认情况下它为“*”
pQueryDef2.PostfixClause = "ORDER BY BSM DESC";
/**
结果:
BSM:555666 AAA:钱六
BSM:333444 AAA:李四
BSM:333444 AAA:王五
BSM:111222 AAA:张三
*/
5、GROUP BY 和 HAVING
//pQueryDef2.Tables = "ABC_Point";
//pQueryDef2.SubFields = "BSM,SUM(OBJECTID)";
//pQueryDef2.PostfixClause = "GROUP BY BSM";
/**
结果:
BSM:111222 SUM( OBJECTID):1
BSM:333444 SUM( OBJECTID):6
BSM:555666 SUM( OBJECTID):3
*/
pQueryDef2.Tables = "ABC_Point";
pQueryDef2.SubFields = "BSM,SUM(OBJECTID)";
pQueryDef2.PostfixClause = "GROUP BY BSM HAVING SUM(OBJECTID)>1";
/**
结果:
BSM:333444 SUM( OBJECTID):6
BSM:555666 SUM( OBJECTID):3
*/
6、各种SQL函数
pQueryDef2.Tables = "ABC_Point";
pQueryDef2.SubFields = "BSM,CASE WHEN BSM='333444' THEN 'B' WHEN BSM='111222' THEN 'A' END AS bsm_LEVEL";
/**
结果:
BSM:111222 bsm_LEVEL:A
BSM:333444 bsm_LEVEL:B
BSM:555666 bsm_LEVEL:
BSM:333444 bsm_LEVEL:B
*/
//COUNT
//pQueryDef2.Tables = "ABC_Point";
//pQueryDef2.SubFields = "COUNT(*)";
//结果 : COUNT(*):4
QueryDef 可用于生成一个虚拟表或要素类,类似“视图”:
/// <summary>
/// QueryDef 可用于生成一个虚拟表或要素类。类似“视图”
/// </summary>
public static void UsingIQueryDefBuildTempTable()
{
Type type = Type.GetTypeFromProgID("esriDataSourcesGDB.FileGDBWorkspaceFactory");
IWorkspaceFactory pWorkspaceFactory = Activator.CreateInstance(type) as IWorkspaceFactory;
IWorkspace pWorkspace = pWorkspaceFactory.OpenFromFile(@"F:\GIS测试数据\测试.gdb", 0);
IFeatureWorkspace pFeatureWorkspace = pWorkspace as IFeatureWorkspace;
//创建 QueryDef
IQueryDef2 pQueryDef2 = (IQueryDef2)pFeatureWorkspace.CreateQueryDef();
//指定表和字段查询
pQueryDef2.Tables = $"ABC INNER JOIN ABC_Point ON ABC.BSM =ABC_Point.BSM";
pQueryDef2.SubFields = "ABC.BSM,ABC.ADDRESS,ABC_Point.NAME";
//构建表查询
IQueryName2 pQueryName2 = new TableQueryNameClass();
pQueryName2.PrimaryKey = string.Empty;
pQueryName2.QueryDef = pQueryDef2;
IDataset pDataset = (IDataset)pWorkspace;
IWorkspaceName pWorkspaceName = (IWorkspaceName)pDataset.FullName;
// 将表查询对象转为IDataset
IDatasetName pDatasetName = (IDatasetName)pQueryName2;
pDatasetName.WorkspaceName = pWorkspaceName;
pDatasetName.Name = "TABLE_ABC";
IName pName = (IName)pDatasetName;
//打开表
ITable pTable = (ITable)pName.Open();
Dictionary<string, int> dicField = new Dictionary<string, int>(); //字段——索引字典
for (int i = 0; i < pTable.Fields.FieldCount; i++)
dicField[pTable.Fields.Field[i].Name] = i;
using (ComReleaser comReleaser = new ComReleaser())
{
ICursor pCursor = pTable.Search(null, true);
comReleaser.ManageLifetime(comReleaser);
IRow pRow = null;
while ((pRow = pCursor.NextRow()) != null)
{
string str = string.Empty;
foreach (var it in dicField)
str += $"{it.Key}:{pRow.Value[it.Value]} ";
Console.WriteLine(str);
}
}
}
/**
结果:
ABC.BSM:111222 ABC.ADDRESS:赵国 ABC_Point.NAME:张三
ABC.BSM:333444 ABC.ADDRESS:燕国 ABC_Point.NAME:李四
ABC.BSM:333444 ABC.ADDRESS:燕国 ABC_Point.NAME:王五
*/