/// <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(); }