/// <summary>
/// 获取唯一值
/// </summary>
/// <param name="Table">数据表</param>
/// <param name="FieldName">字段名称</param>
/// <param name="JoinedTables">连接表</param>
/// <returns></returns>
public static IList <string> GimmeUniqeValuesForFieldname(ITable Table, string FieldName, IList <string> JoinedTables)
{
IList <string> alUniqueVal = new List <string>();
try
{
string cMember = "";
foreach (string tempLoopVar_cMember in JoinedTables)
{
cMember = tempLoopVar_cMember;
cMember = "," + cMember;
}
IDataset pDataset = Table as IDataset;
IFeatureWorkspace pFeatureWorkspace = pDataset.Workspace as IFeatureWorkspace;
IQueryDef pQueryDef = pFeatureWorkspace.CreateQueryDef();
pQueryDef.Tables = pDataset.Name + cMember;
pQueryDef.SubFields = "DISTINCT(" + FieldName + ")";
ICursor pCursor = pQueryDef.Evaluate();
IRow pRow = pCursor.NextRow();
while (!(pRow == null))
{
alUniqueVal.Add(pRow.Value[0].ToString());
pRow = pCursor.NextRow();
}
return(alUniqueVal);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " " + ex.Source + " " + ex.StackTrace);
return(alUniqueVal);
}
}
private byte[] GetLogicTreeContent(IFeatureDataSet dataset)
{
byte[] strContent = null;
try
{
IQueryDef qd = dataset.DataSource.CreateQueryDef();
qd.AddSubField("content");
qd.Tables = new String[] { "cm_logictree", "cm_group" };
qd.WhereClause = String.Format("cm_group.groupuid = cm_logictree.groupid "
+ " and cm_group.DataSet = '{0}'", dataset.Name);
IFdeCursor cursor = qd.Execute(false);
IRowBuffer row = null;
if ((row = cursor.NextRow()) != null)
{
//content
int nPose = row.FieldIndex("content");
if (nPose != -1)
{
IBinaryBuffer bb = row.GetValue(nPose) as IBinaryBuffer;
strContent = (byte[])bb.AsByteArray();
}
}
}
catch (COMException ex)
{
System.Diagnostics.Trace.WriteLine(ex.Message);
return(null);
}
return(strContent);
}
private void buttonGetUniqeValue_Click(object sender, System.EventArgs e)
{
//使用FeatureClass对象的IDataset接口来获取dataset和workspace的信息
IDataset dataset = (IDataset)mFeatureLayer.FeatureClass;
//使用IQueryDef接口的对象来定义和查询属性信息。通过IWorkspace接口的CreateQueryDef()方法创建该对象。
IQueryDef queryDef = ((IFeatureWorkspace)dataset.Workspace).CreateQueryDef();
//设置所需查询的表格名称为dataset的名称
queryDef.Tables = dataset.Name;
////设置查询的字段名称。可以联合使用SQL语言的关键字，如查询唯一值可以使用DISTINCT关键字。
queryDef.SubFields = "DISTINCT (" + currentFieldName + ")";
//执行查询并返回ICursor接口的对象来访问整个结果的集合
ICursor cursor = queryDef.Evaluate();
//使用IField接口获取当前所需要使用的字段的信息
IFields fields = mFeatureLayer.FeatureClass.Fields;
IField field = fields.get_Field(fields.FindField(currentFieldName));
//对整个结果集合进行遍历，从而添加所有的唯一值
//使用IRow接口来操作结果集合。首先定位到第一个查询结果。
IRow row = cursor.NextRow();
//如果查询结果非空，则一直进行添加操作
while (row != null)
{
listBoxValues.Items.Add(row.get_Value(0).ToString());
//继续执行下一个结果的添加
row = cursor.NextRow();
}
}
/// <summary>
/// get unique value
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
//IFeatureLayer.FeatureClass
IDataset dataSet = currentFeatureLayer.FeatureClass as IDataset;
//Iquerydef's class can't instantiate by its own
IQueryDef queryDef = ((IFeatureWorkspace)dataSet.Workspace).CreateQueryDef();
queryDef.Tables = dataSet.Name;
queryDef.SubFields = "DISTINCT(" + currentFieldName + ")"; //only one column
//IQueryDef.Evaluate: ICursor
ICursor pCursor = queryDef.Evaluate();
//ICursor.NextRow: IRow
IRow pRow = pCursor.NextRow();
//IFields.FindField(string)
IFields pFields = currentFeatureLayer.FeatureClass.Fields;
IField pField = pFields.get_Field(pFields.FindField(currentFieldName));
while (pRow != null)
{
//to distinguish string with single quotes
if (pField.Type == esriFieldType.esriFieldTypeString)
{
listBoxValues.Items.Add("\'" + pRow.get_Value(0).ToString() + "\'");
}
else
{
listBoxValues.Items.Add(pRow.get_Value(0).ToString());
}
//update pRow by ICursor.NextRow
pRow = pCursor.NextRow();
}
}
//获取下个最大的数据库类型ID值
private string getNextMaxCode(IWorkspace pW, out Exception ex)
{
string res = ""; ex = null;
IFeatureWorkspace pFW = pW as IFeatureWorkspace;
IQueryDef pQD = pFW.CreateQueryDef();
pQD.SubFields = "max(ID)";
pQD.Tables = "DATABASETYPEMD";
ICursor pCsr = null;
try
{
pCsr = pQD.Evaluate();
IRow pR = pCsr.NextRow();
string sCode = pR.get_Value(0).ToString();//DBNull在转换除字符串外的 类型会报错
if (sCode == "")
{
res = 1.ToString();
}
else
{
res = (Convert.ToInt32(sCode) + 1).ToString();
}
}
catch (Exception e)
{
ex = e;
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(pCsr);
}
return(res);
}
private void button15_Click(object sender, EventArgs e)
{
IDataset dataset = (IDataset)_currentFeatureLayer.FeatureClass;
IQueryDef queryDef = ((IFeatureWorkspace)dataset.Workspace).CreateQueryDef();
queryDef.Tables = dataset.Name;
queryDef.SubFields = "DISTINCT(" + _currentFieldName + ")";
ICursor cursor = queryDef.Evaluate();
IFields fields = _currentFeatureLayer.FeatureClass.Fields;
IField field = fields.Field[fields.FindField(_currentFieldName)];
IRow row = cursor.NextRow();
while (row != null)
{
if (field.Type == esriFieldType.esriFieldTypeString)
{
listBox2.Items.Add("\"" + row.Value[0] + "\"");
}
else
{
listBox2.Items.Add(row.Value[0].ToString());
}
row = cursor.NextRow();
}
}
// *---------------------------------------------------------------------------------------
// *开 发 者：chenyafei
// *开发时间：20110622
// *功能函数：根据执行的条件查询数据库中的信息
// *参 数：表名称（支持多表，以逗号隔开）、字段名称(支持多只段，以逗号隔开)、where字句，异常(输出)
// *返 回 值：返回查询表格的游标
public static ICursor GetCursor(IFeatureWorkspace pFeaWS, string tableName, string fieldName, string whereStr, out Exception outError)
{
outError = null;
ICursor pCursor = null;
IQueryDef pQueryDef = pFeaWS.CreateQueryDef();
pQueryDef.Tables = tableName;
pQueryDef.SubFields = fieldName;
pQueryDef.WhereClause = whereStr;
try
{
pCursor = pQueryDef.Evaluate();
if (pCursor == null)
{
outError = new Exception("查询数据库失败！");
return(null);;
}
}
catch
{
outError = new Exception("查询数据库失败！");
return(null);
}
return(pCursor);
}
//检查数据库类型名字的唯一性
private bool checkUnique()
{
IQueryDef pQueryDes = (ModuleData.TempWks as IFeatureWorkspace).CreateQueryDef();
try
{
//cyf 20110602
////数据库类型
pQueryDes.Tables = "DATABASETYPEMD";
pQueryDes.SubFields = "DATABASETYPE";
pQueryDes.WhereClause = "DATABASETYPE='" + txtDBType.Text + "'";
ICursor pCursor = null;
pCursor = pQueryDes.Evaluate();
if (pCursor == null)
{
return(true);
}
IRow pRow = pCursor.NextRow();
if (pRow != null)
{
SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "已存在此数据库类型！");
return(false);
}
return(true);
}
catch
{
return(true);
}
}
/// <summary>
/// 获取唯一值
/// </summary>
/// <param name="Table">数据表</param>
/// <param name="FieldName">字段名称</param>
/// <returns></returns>
public static IList <string> GimmeUniqeValuesForFieldname(ITable Table, string FieldName)
{
IList <string> tempList = new List <string>();
try
{
IDataset pDataset = Table as IDataset;
IFeatureWorkspace pFeatureWorkspace = pDataset.Workspace as IFeatureWorkspace;
IQueryDef pQueryDef = pFeatureWorkspace.CreateQueryDef();
pQueryDef.Tables = pDataset.Name;
pQueryDef.SubFields = "DISTINCT(" + FieldName + ")";
ICursor pCursor = pQueryDef.Evaluate();
IRow pRow = pCursor.NextRow();
while (!(pRow == null))
{
tempList.Add(pRow.Value[0].ToString());
pRow = pCursor.NextRow();
}
return(tempList);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(tempList);
}
}
/// <summary>
/// Joins the source table with the specified foreign table.
/// </summary>
/// <param name="source">The source.</param>
/// <param name="foreign">The foreign table.</param>
/// <param name="primaryKeyFieldName">Name of the primary key field.</param>
/// <param name="foreignKeyFieldName">Name of the foreign key field.</param>
/// <param name="subFields">The sub fields.</param>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
public static ITable Join(this ITable source, ITable foreign, string primaryKeyFieldName, string foreignKeyFieldName, string subFields, string tableName)
{
IQueryDef queryDef = source.Join(foreign, primaryKeyFieldName, foreignKeyFieldName, subFields);
IDataset ds = (IDataset)source;
return(queryDef.Evaluate(primaryKeyFieldName, true, ds.Workspace, tableName));
}
/// <summary>
/// Handles the specified query.
/// </summary>
/// <param name="queryType">The query type.</param>
/// <param name="queryDef">The query definition.</param>
/// <returns>The query response of the specified query type.</returns>
/// <remarks>
/// This method should handle the message. If the internal next handler is <c>null</c>, this method should also
/// return <c>null</c>; otherwise, it should return the result from the next internal handler's <see
/// cref="M:Service.IQueryHandler.Handle(System.String,Service.IQueryDef)" /> method.
/// </remarks>
public IQueryResponse Handle(string queryType, IQueryDef queryDef)
{
var queryResponse = queryType.Equals(this.supportedQueryType)
? this.response
: this.nextQueryHandler?.Handle(queryType, queryDef);
return(queryResponse);
}
/// <summary>
/// Gets the name of the delta (either the A or D) table for the versioned <paramref name="source" />.
/// </summary>
/// <param name="source">The versioned table or feature class.</param>
/// <param name="delta">The delta (indicate the A or D) table.</param>
/// <returns>
/// Returns a <see cref="string" /> representing the name of the delta table.
/// </returns>
/// <exception cref="System.ArgumentNullException">delta</exception>
/// <exception cref="System.ArgumentException">
/// The delta string must be 1 char long.
/// or
/// The delta string must contain only 'A' or 'D' chars.
/// or
/// The table must be versioned for it have a delta table.
/// </exception>
/// <exception cref="ArgumentException">
/// The delta string must be 1 char long.
/// or
/// The delta string must contain only 'A' or 'D' chars.
/// </exception>
public static string GetDeltaTableName(this ITable source, string delta)
{
if (source == null)
{
return(null);
}
if (delta == null)
{
throw new ArgumentNullException("delta");
}
if (delta.Length != 1)
{
throw new ArgumentException("The delta string must be 1 char long.");
}
if (delta.Any(@char => @char != 'A' && @char != 'D' && @char != 'a' && @char != 'd'))
{
throw new ArgumentException("The delta string must contain only 'A' or 'D' chars.");
}
IVersionedTable versionedTable = source as IVersionedTable;
if (versionedTable == null)
{
throw new ArgumentException("The table must be versioned for it have a delta table.");
}
string className = ((IDataset)source).Name;
int index = className.IndexOf('.');
if (index > 0)
{
string ownerName = source.GetSchemaName();
string tableName = source.GetTableName();
using (var cr = new ComReleaser())
{
IWorkspace workspace = ((IDataset)source).Workspace;
var fws = (IFeatureWorkspace)workspace;
var syntax = (ISQLSyntax)workspace;
string functionName = syntax.GetFunctionName(esriSQLFunctionName.esriSQL_UPPER);
IQueryDef queryDef = fws.CreateQueryDef();
queryDef.Tables = "sde.table_registry";
queryDef.SubFields = "registration_id";
queryDef.WhereClause = string.Format("{2}(table_name) = {2}('{0}') AND {2}(owner) = {2}('{1}')", tableName, ownerName, functionName);
ICursor cursor = queryDef.Evaluate();
cr.ManageLifetime(cursor);
IRow row = cursor.NextRow();
return((row != null) ? string.Format("{0}.{1}{2}", ownerName, delta, row.Value[0]) : null);
}
}
return(null);
}
//在点击“获取唯一属性值”按钮时触发事件，执行本函数
//对图层的某个字段进行唯一值获取操作，并将所有的唯一值显示在listBoxValues控件中
private void buttonGetUniqeValue_Click(object sender, EventArgs e)
{
// try
{
//使用FeatureClass对象的IDataset接口来获取dataset和workspace的信息
IDataset dataset = (IDataset)currentFeatureLayer.FeatureClass;
//使用IQueryDef接口的对象来定义和查询属性信息。通过IWorkspace接口的CreateQueryDef()方法创建该对象。
IQueryDef queryDef = ((IFeatureWorkspace)dataset.Workspace).CreateQueryDef();
//设置所需查询的表格名称为dataset的名称
queryDef.Tables = dataset.Name;
//设置查询的字段名称。可以联合使用SQL语言的关键字，如查询唯一值可以使用DISTINCT关键字。
//queryDef.SubFields = "DISTINCT (" + currentFieldName + ")";
queryDef.SubFields = currentFieldName;
//执行查询并返回ICursor接口的对象来访问整个结果的集合
ICursor cursor = queryDef.Evaluate();
//使用IField接口获取当前所需要使用的字段的信息
IFields fields = currentFeatureLayer.FeatureClass.Fields;
IField field = fields.get_Field(fields.FindField(currentFieldName));
//对整个结果集合进行遍历，从而添加所有的唯一值
//使用IRow接口来操作结果集合。首先定位到第一个查询结果。
IRow row = cursor.NextRow();
//如果查询结果非空，则一直进行添加操作
int index = listBoxFields.SelectedIndex;
while (row != null)
{
if (index == 0)
{
listBoxValues.Items.Add(row.get_Value(index).ToString());
}
else
{
//对String类型的字段，唯一值的前后添加'和'，以符合SQL语句的要求
//if (field.Type == esriFieldType.esriFieldTypeString)
if (field.Type == esriFieldType.esriFieldTypeString)
{
//listBoxValues.Items.Add("\'" + row.get_Value(index + 1).ToString() + "\'");
if (!listBoxValues.Items.Contains(row.get_Value(index + 1).ToString()))
{
listBoxValues.Items.Add("\'" + row.get_Value(index + 1).ToString() + "\'");
}
// listBoxValues.Items.Add("\'" + row.get_Value(2).ToString() + "\'");
}
else
{
listBoxValues.Items.Add(row.get_Value(index + 1).ToString());
}
}
//继续执行下一个结果的添加
row = cursor.NextRow();
}
}
//catch(Exception ex)
{
// MessageBox.Show("!");
}
}
private void LoadDataset()
{
IWorkspaceFactory pAccessWorkspaceFactory;
string pFullPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "\\data\\教室数据\\classroom.mdb";
pAccessWorkspaceFactory = new AccessWorkspaceFactory(); //using ESRI.ArcGIS.DataSourcesGDB;
//获取工作空间
IWorkspace pWorkspace = pAccessWorkspaceFactory.OpenFromFile(pFullPath, 0);
IEnumDataset pEnumDataset = pWorkspace.get_Datasets(ESRI.ArcGIS.Geodatabase.esriDatasetType.esriDTAny);
pEnumDataset.Reset();
//将Enum数据集中的数据一个个读到DataSet中
IDataset pDataset = pEnumDataset.Next();
//使用IQueryDef接口的对象来定义和查询属性信息。通过IWorkspace接口的CreateQueryDef()方法创建该对象。
IQueryDef queryDef = ((IFeatureWorkspace)pDataset.Workspace).CreateQueryDef();
//设置所需查询的表格名称为dataset的名称
queryDef.Tables = pDataset.Name;
string que = null;
//返回所有值
queryDef.WhereClause = que;
//执行查询并返回ICursor接口的对象来访问整个结果的集合
ICursor cursor = queryDef.Evaluate();
IRow row = cursor.NextRow();
dt.Columns.Add(new DataColumn("学部", typeof(string))); //在表中添加int类型的列
dt.Columns.Add(new DataColumn("教室", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("教学楼", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("人数", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("语音多媒体", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("周次", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("日期", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("1-2", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("3-4", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("3-5", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("6-7", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("8-9", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("8-10", typeof(string))); //在表中添加string类型的Name列
dt.Columns.Add(new DataColumn("11-13", typeof(string))); //在表中添加string类型的Name列
while (row != null)
{
DataRow dr1 = dt.NewRow();
for (int i = 1; i < 15; i++)
{
dr1[i - 1] = row.get_Value(i).ToString();
}
dt.Rows.Add(dr1);
row = cursor.NextRow();
}
dataGridView_classquery1.DataSource = dt;
dataGridView_classquery2.DataSource = dt;
}
/// <summary>
/// Joins the source table with the specified foreign table name.
/// </summary>
/// <param name="source">The source.</param>
/// <param name="foreignTableName">Name of the foreign table.</param>
/// <param name="primaryKeyFieldName">Name of the primary key field.</param>
/// <param name="foreignKeyFieldName">Name of the foreign key field.</param>
/// <param name="subFields">The sub fields.</param>
/// <returns></returns>
public static IQueryDef Join(this ITable source, string foreignTableName, string primaryKeyFieldName, string foreignKeyFieldName, string subFields)
{
IDataset ds = (IDataset)source;
IQueryDef queryDef = ((IFeatureWorkspace)ds.Workspace).CreateQueryDef();
queryDef.Tables = string.Format("{0}, {1}", ds.Name, foreignTableName);
queryDef.SubFields = subFields;
queryDef.WhereClause = string.Format("{0}.{1} = {2}.{3}", ds.Name, primaryKeyFieldName, foreignTableName, foreignKeyFieldName);
return(queryDef);
}
//QueryDef对象
private IQueryDef CreateQueryDef(IFeatureWorkspace pFeatureWorkspace)
{
//产生一个QueryDef对象
IQueryDef pQueryDef = pFeatureWorkspace.CreateQueryDef();
//基于连接的两个表必须是在一个工作空间内，设置它的各种属性
pQueryDef.Tables = "Counties,Cities";
pQueryDef.SubFields = "*";
pQueryDef.WhereClause = "Counties.STATE_FIPS =Cities.STFIPS";
return(pQueryDef);
}
/// <summary>
/// Implementation of Auto Updater Execute Ex method for derived classes.
/// </summary>
/// <param name="obj">The object that triggered the Auto Udpater.</param>
/// <param name="eAUMode">The auto updater mode.</param>
/// <param name="editEvent">The edit event.</param>
/// <exception cref="NotSupportedException">
/// The sequence generator is only supported on an ORACLE workspace (remote
/// geodatabase).
/// </exception>
/// <exception cref="ArgumentNullException">obj;@The field model name is not assigned on the object.</exception>
/// <remarks>
/// This method will be called from IMMSpecialAUStrategy::ExecuteEx
/// and is wrapped within the exception handling for that method.
/// </remarks>
protected override void InternalExecute(IObject obj, mmAutoUpdaterMode eAUMode, mmEditEvent editEvent)
{
if (obj == null)
{
return;
}
IDataset dataset = (IDataset)obj.Class;
IWorkspace workspace = dataset.Workspace;
if (workspace.IsDBMS(DBMS.Oracle))
{
throw new NotSupportedException("The sequence generator is only supported on an ORACLE workspace (remote geodatabase).");
}
string fieldName = obj.Class.GetFieldName(_FieldModelName);
if (string.IsNullOrEmpty(fieldName))
{
throw new ArgumentNullException("obj", @"The field model name is not assigned on the object.");
}
// Create a queryDef from the feature workspace
IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)workspace;
IQueryDef queryDef = featureWorkspace.CreateQueryDef();
// Set the query def to point to the sequence
queryDef.SubFields = _SequenceName + ".NEXTVAL";
queryDef.Tables = "SYS.DUAL";
// Define a cursor and row, for destroy in finally
using (ComReleaser cr = new ComReleaser())
{
// Fill the cursor via the query def
ICursor cursor = queryDef.Evaluate();
cr.ManageLifetime(cursor);
// Now get the row from the cursor
IRow row = cursor.NextRow();
if (row == null)
{
return;
}
// Store the formatted value if it's configured.
int val = TypeCast.Cast(row.get_Value(0), -1);
string formattedValue = this.Format(val, obj);
int pos = obj.Class.FindField(fieldName);
obj.set_Value(pos, formattedValue);
}
}
private static void LogQueryName([NotNull] IQueryName2 queryName)
{
IQueryDef queryDef = queryName.QueryDef;
_msg.DebugFormat("query table name: {0}",
((IDatasetName)queryName).Name);
_msg.DebugFormat("copy locally: {0}", queryName.CopyLocally);
_msg.DebugFormat("primary key: [{0}]", queryName.PrimaryKey);
_msg.DebugFormat("query:");
_msg.DebugFormat("SELECT {0} FROM {1} WHERE {2}",
queryDef.SubFields,
queryDef.Tables,
queryDef.WhereClause);
}
// Join the data.
private static void JoinTheTableToFeatureClass(string dataToJoin)
{
string featureClassName = "";
string featureClassJoinFieldName = "";
string tableNameJoinFieldName = "";
// table name.
if (dataToJoin == "AddrPoints")
{
featureClassName = "AddrPnts" + "_SGID";
featureClassJoinFieldName = ".UTAddPtID";
tableNameJoinFieldName = ".ADDR_UID";
}
else if (dataToJoin == "Roads")
{
featureClassName = "Roads" + "_SGID";
featureClassJoinFieldName = ".UNIQUE_ID";
tableNameJoinFieldName = ".ROAD_UID";
}
string tableName = "tbl_" + countyName + "Report";
string queryDefTables = tableName + "," + featureClassName;
string queryDefWhereClause = tableName + tableNameJoinFieldName + " = " + featureClassName + featureClassJoinFieldName;
IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)workspaceFGDB;
//create query definition
IQueryDef queryDef = featureWorkspace.CreateQueryDef();
//provide list of tables to join
//queryDef.Tables = "ROCKVILLE_Report, AddrPntsROCKVILLE_SGID";
queryDef.Tables = queryDefTables;
//retrieve the fields from all tables
//queryDef.SubFields = "sde.datesjoin.dt_field, sde.dudates.dt_field";
//set up join
//queryDef.WhereClause = "ROCKVILLE_Report.ADDR_UID = AddrPntsROCKVILLE_SGID.UTAddPtID";
queryDef.WhereClause = queryDefWhereClause;
//Create FeatureDataset. Note the use of .OpenFeatureQuery.
//The name "MyJoin" is the name of the restult of the query def and
//is used in place of a feature class name.
IFeatureDataset featureDataset = featureWorkspace.OpenFeatureQuery("MyJoin", queryDef);
//open layer to test against
IFeatureClassContainer featureClassContainer = (IFeatureClassContainer)featureDataset;
IFeatureClass featureClass = featureClassContainer.get_ClassByName("MyJoin");
// Export the joined feature class to the file geodatabase (at this point, it's just in memory)
ExportTheJoinedFeatureClass(featureClass, dataToJoin);
}
public IQueryDef CreateQuery(string whereClause)
{
IDataset dataset = this.FeatureClass as IDataset;
IFeatureWorkspace featureWorkspace = dataset.Workspace as IFeatureWorkspace;
IQueryDef query = featureWorkspace.CreateQueryDef();
query.Tables = dataset.Name;
whereClause = whereClause.Trim();
if (whereClause.Length != 0)
{
query.WhereClause = whereClause;
}
return(query);
}
/// <summary>
/// Create a table of the results of the query definition.
/// </summary>
/// <param name="source">The source.</param>
/// <param name="primaryKeys">The primary key field names.</param>
/// <param name="copyLocally">if set to <c>true</c> if the data must be copied locally.</param>
/// <param name="workspace">The workspace that contains the tables.</param>
/// <param name="tableName">Name of the table.</param>
/// <returns>Returns a <see cref="ITable" /> representing the results of the query definition.</returns>
public static ITable Evaluate(this IQueryDef source, string primaryKeys, bool copyLocally, IWorkspace workspace, string tableName)
{
IQueryName2 query = new TableQueryNameClass();
query.QueryDef = source;
query.PrimaryKey = (!copyLocally) ? primaryKeys : "";
query.CopyLocally = copyLocally;
IDatasetName ds = (IDatasetName)query;
ds.WorkspaceName = (IWorkspaceName)((IDataset)workspace).FullName;
ds.Name = tableName;
IName name = (IName)query;
return((ITable)name.Open());
}
public static IQueryDef CreateQueryDef(IFeatureWorkspace featureWorkspace,
String tables, String subFields, String whereClause)
{
// Create the query definition.
IQueryDef queryDef = featureWorkspace.CreateQueryDef();
// Provide a list of table(s) to join.
queryDef.Tables = tables;
// Declare the subfields to retrieve.
queryDef.SubFields = subFields; // must be qualified if multiple tables !!
// Assign a where clause to filter the results.
queryDef.WhereClause = whereClause;
return(queryDef);
}
/// <summary>
/// Reads database rows as a (lazily-evaluated) sequence of objects that are converted into the entity type.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="source">The source.</param>
/// <returns>
/// Returns a <see cref="IEnumerable{TEntity}" /> representing the entity object.
/// </returns>
public static IEnumerable <TEntity> Map <TEntity>(this IQueryDef source) where TEntity : Entity
{
var cursor = source.Evaluate();
try
{
foreach (var row in cursor.Map <TEntity>())
{
yield return(row);
}
}
finally
{
while (Marshal.ReleaseComObject(cursor) != 0)
{
}
}
}
//新建一个要素类
private void CreateFeatureclass(IQueryDef pQueryDef, IFeatureWorkspace pFeatureWorkspace)
{
IFeatureDataset pFeatureDataset = pFeatureWorkspace.OpenFeatureQuery("My counties join ", pQueryDef);
IFeatureClassContainer pFeatureClassContainer = pFeatureDataset as IFeatureClassContainer;
//判断IFeatureClassContainer中是否有要素类存在
if (pFeatureClassContainer.ClassCount != 1)
{
MessageBox.Show("Failed to create feature class by query！");
return;
}
IFeatureClass pFeatureClass = pFeatureClassContainer.get_Class(0);
IFeatureLayer pFeatureLayer = new FeatureLayerClass();
pFeatureLayer.FeatureClass = pFeatureClass;
pFeatureLayer.Name = pFeatureClass.AliasName;
pMap.AddLayer(pFeatureLayer);
}
public ArrayList GetUVByQueryDef(IFeatureClass pFeatureClass, string strField)
{
IDataset dataset = (IDataset)pFeatureClass;
IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)dataset.Workspace;
IQueryDef queryDef = featureWorkspace.CreateQueryDef();
queryDef.Tables = (dataset.Name);
queryDef.SubFields = ("DISTINCT(" + strField + ")");
ICursor cursor = queryDef.Evaluate();
IRow row = cursor.NextRow();
ArrayList arrayList = new ArrayList();
while (row != null)
{
object obj = row.get_Value(0);
arrayList.Add(obj.ToString());
row = cursor.NextRow();
}
return(arrayList);
}
/// <summary>
/// Executes a query using callbacks.
/// </summary>
/// <param name="queryType">The query type.</param>
/// <param name="queryDef">The query definition.</param>
public void Query(string queryType, IQueryDef queryDef)
{
var handler = this.QuerySubmitted;
if (handler == null)
{
return;
}
var callbackChannel = OperationContext.Current.GetCallbackChannel <IQueryCallbackContract>();
handler.Invoke(
this,
new QueryArgs
{
QueryType = queryType,
QueryDefinition = queryDef,
Callback = callbackChannel
});
}
/// <summary>
/// 创建查询表
/// </summary>
/// <param name="queryDef"></param>
/// <param name="str_pk"></param>
/// <param name="workspaceName"></param>
/// <param name="tableName"></param>
/// <returns></returns>
private ITable CreateQueryTable(IQueryDef queryDef, string str_pk, IWorkspaceName workspaceName, string tableName)
{
IQueryName2 queryName2 = (IQueryName2) new TableQueryNameClass();
queryName2.QueryDef = queryDef;
//queryName2.PrimaryKey = "streets.StreetID";
queryName2.PrimaryKey = str_pk;
queryName2.CopyLocally = true;
// Set the workspace and name of the new QueryTable.
IDatasetName datasetName = (IDatasetName)queryName2;
datasetName.WorkspaceName = workspaceName;
datasetName.Name = tableName;
// Open the virtual table.
IName name = (IName)queryName2;
ITable table = (ITable)name.Open();
return(table);
}
public static IFeatureClass GetFeatureClassWithQueryDef(IDataset arcDataSet, IFeatureWorkspace arcFeatureWS, string strDispatchEtlName, string strWhereClause)
{
try
{
// make idataset from the reverse geocode sde feature class
IDataset arcSDEDataSet_RevGeocoder;
arcSDEDataSet_RevGeocoder = (IDataset)arcDataSet;
//IFeatureClass arcETL_HwyRevGeocodeFC = arcFeatWorkspaceETL.OpenFeatureClass(strDispatchEtlName);
//IFeatureLayerDefinition arcFeatLayerDef = arcETL_HwyRevGeocodeFC;
IQueryDef queryDef = arcFeatureWS.CreateQueryDef();
//provide list of tables to join
queryDef.Tables = strDispatchEtlName;
//retrieve the fields from all tables
queryDef.SubFields = "*";
//set up join
queryDef.WhereClause = strWhereClause;
//Create FeatureDataset. Note the use of .OpenFeatureQuery.
//The name "MyJoin" is the name of the restult of the query def and
//is used in place of a feature class name.
IFeatureDataset featureDataset = arcFeatureWS.OpenFeatureQuery("FeatClassWithQueryDef", queryDef);
//open layer to test against
IFeatureClassContainer featureClassContainer = (IFeatureClassContainer)featureDataset;
IFeatureClass featureClass = featureClassContainer.get_ClassByName("FeatClassWithQueryDef");
return(featureClass);
}
catch (Exception ex)
{
return(null);
MessageBox.Show("Error Message: " + Environment.NewLine + ex.Message + Environment.NewLine + Environment.NewLine +
"Error Source: " + Environment.NewLine + ex.Source + Environment.NewLine + Environment.NewLine +
"Error Location:" + Environment.NewLine + ex.StackTrace,
"UTRANS Editor tool error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void button3_Click(object sender, EventArgs e)
{
string[] dataSourceArr = this.checkedComboBoxEdit1.EditValue.ToString().Split(',');
string sql = "(" + this.textBoxSql.Text.ToString() + ")";
CommonClass conClass = new CommonClass();
IFeatureWorkspace ifw = conClass.GetFeatureWorkspaceByName(dataSourceArr[0]);
IWorkspace iw = conClass.GetWorkspaceByName(dataSourceArr[0]);
IQueryDef pQueryDef = ifw.CreateQueryDef();
pQueryDef.Tables = sql;
pQueryDef.SubFields = "*";
try
{
ICursor pCur = pQueryDef.Evaluate();
MessageBox.Show("SQL通过检查");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
MessageBox.Show("SQL没有通过检查");
}
}
/// <summary>
/// 在点击“获取唯一属性值”按钮时触发事件执行本函数
/// 对图层的某个字段进行唯一值获取操作，并将所有的唯一值显示在listBoxValue控件中
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonGetUniqeValue_Click(object sender, EventArgs e)
{
// 先清除listBoxFields上面的信息，避免重复
listBoxValues.Items.Clear();
// 使用Feature对象的IDataset接口来获取dataset和workspace的信息
IDataset dataset = currentFeatureLayer.FeatureClass as IDataset;
// 使用IQueryDef接口的对象来定义和查询属性信息。通过IWorkspace接口的CreateQuertDef()方法创建该对象
IQueryDef queryDef = (dataset.Workspace as IFeatureWorkspace).CreateQueryDef();
// 设置所需查询的表格名称为dataset的名称
queryDef.Tables = dataset.Name;
// 设置查询的字段名称。可以联合使用SQL语言的关键字，如查询唯一值可以使用DISTINCT关键字
queryDef.SubFields = "DISTINCT(" + currentFileName + ")";
// 执行查询并返回ICursor接口的对象来访问整个结果的集合
ICursor cursor = queryDef.Evaluate();
// 使用IField接口获取当前所需要使用的字段的信息
IFields fields = currentFeatureLayer.FeatureClass.Fields;
IField field = fields.get_Field(fields.FindField(currentFileName));
// 对整个结果集合进行遍历，从而添加所有的唯一值
// 使用IRow接口来操作结果集合。首先定位到第一个查询结果
IRow row = cursor.NextRow();
// 如果查询结果非空，则一直进行添加操作
while (row != null)
{
// 对String类型的字段，唯一值的前后添加\和'，以符合SQL语句的要求
if (field.Type == esriFieldType.esriFieldTypeString)
{
listBoxValues.Items.Add("\'" + row.get_Value(0).ToString() + "\'");
}
else
{
listBoxValues.Items.Add(row.get_Value(0).ToString());
}
// 继续执行下一个结果的添加
row = cursor.NextRow();
}
}
private void SelectByQueryDef(IQueryDef queryDef, string oidColumnAlias)
{
// Evaluate queryDef to execute a database query and return a cursor.
ICursor selCursor = queryDef.Evaluate();
// check if the cursors contains the OID field
int oidOrdinalCursor = selCursor.FindField(oidColumnAlias);
// if OID field present select features on map
if (oidOrdinalCursor != -1)
{
IQueryFilter queryFilter = new QueryFilterClass();
queryFilter.WhereClause = ArcMapAppHelperClass.WhereClauseFromCursor(oidOrdinalCursor, oidColumnAlias, selCursor);
SelectByQueryFilter(queryFilter);
}
}
public IFeatureDataset OpenFeatureQuery(string QueryName, IQueryDef pQueryDef)
{
// See CreateQueryDef() for argument.
throw new NotImplementedException();
}
