private void btnExecute_Click(object sender, EventArgs e) { if (m_dsProvider != null) { dgvResults.Rows.Clear(); try { // Comment the following line for versions less or equal v. 2.2 IExpressionEvaluationContext exprEvalContext = new DefaultExpressionEvaluationContext(); IFeatureExpressionFilter filterExpr = null; if (txtFilter.Text.Trim() != string.Empty) { filterExpr = new FeatureExpressionFilter(); filterExpr.Compile(txtFilter.Text, m_dsProvider.Information.Attributes); } Envelope env = m_dsProvider.GetExtent(); List <string> properties = m_dsProvider.Information.GetAttributeNames(); properties.Remove("_geom_"); DataSourceQuery query = new DataSourceQuery(env, new Tuple <double, double>(1.0, 1.0), null, null, properties); IFeatureDataReader dataReader = m_dsProvider.GetFeatureReader(query, FeatureFactory.Default); int i = 0; if (dataReader != null) { int rowsLimit = (int)nudRowsLimit.Value; while (dataReader.Read()) { Feature feature = dataReader.GetFeature(); if (rowsLimit > 0 && i >= rowsLimit) { break; } bool bFeaturePass = true; if (filterExpr != null) { // Apply a given expression to filter features from the data source. // Change the following line for versions less or equal v. 2.2 by replacing the expression with filterExpr.Pass(feature) bFeaturePass = filterExpr.Pass(feature, exprEvalContext); } if (bFeaturePass) { DataGridViewRow row = (DataGridViewRow)dgvResults.Rows[0].Clone(); object[] values = new object[feature.Attributes.Count]; int j = 0; // Enumerate feature properties and fill the grid view. foreach (string attr in feature.Attributes.EnumerateNames()) { object value = feature.GetPropertyValue(attr); if (attr == "_geom_") { value = value.ToString().Replace("MapSurfer.Geometries.", ""); } values[j] = value; row.Cells[j].Value = value; j++; } row.SetValues(values); dgvResults.Rows.Add(row); i++; } } DisposeUtility.Dispose(ref dataReader); } lblRows.Text = i.ToString() + " rows"; UpdateMessage(string.Format("{0} row(s) fetched.", i)); } catch (Exception ex) { UpdateMessage("Exception: " + ex.Message); } } }
private void btnExecute_Click(object sender, EventArgs e) { if (m_dsProvider != null) { dgvResults.Rows.Clear(); try { // Comment the following line for versions less or equal v. 2.2 IExpressionEvaluationContext exprEvalContext = new DefaultExpressionEvaluationContext(); IFeatureExpressionFilter filterExpr = null; if (txtFilter.Text.Trim() != string.Empty) { filterExpr = new FeatureExpressionFilter(); filterExpr.Compile(txtFilter.Text, m_dsProvider.Information.Attributes); } Envelope env = m_dsProvider.GetExtent(); List<string> properties = m_dsProvider.Information.GetAttributeNames(); properties.Remove("_geom_"); DataSourceQuery query = new DataSourceQuery(env, 1, null, null, properties); IFeatureDataReader dataReader = m_dsProvider.GetFeatureReader(query, FeatureFactory.Default); int i = 0; if (dataReader != null) { int rowsLimit = (int)nudRowsLimit.Value; while (dataReader.Read()) { Feature feature = dataReader.GetFeature(); if (rowsLimit > 0 && i >= rowsLimit) { break; } bool bFeaturePass = true; if (filterExpr != null) { // Apply a given expression to filter features from the data source. // Change the following line for versions less or equal v. 2.2 by replacing the expression with filterExpr.Pass(feature) bFeaturePass = filterExpr.Pass(feature, exprEvalContext); } if (bFeaturePass) { DataGridViewRow row = (DataGridViewRow)dgvResults.Rows[0].Clone(); object[] values = new object[feature.Attributes.Count]; int j = 0; // Enumerate feature properties and fill the grid view. foreach (string attr in feature.Attributes.EnumerateNames()) { object value = feature.GetPropertyValue(attr); if (attr == "_geom_") value = value.ToString().Replace("MapSurfer.Geometries.", ""); values[j] = value; row.Cells[j].Value = value; j++; } row.SetValues(values); dgvResults.Rows.Add(row); i++; } } DisposeUtility.Dispose(ref dataReader); } lblRows.Text = i.ToString() + " rows"; UpdateMessage(string.Format("{0} row(s) fetched.", i)); } catch (Exception ex) { UpdateMessage("Exception: " + ex.Message); } } }
private void LoadQueryWithDerivedTableAndCte() { UnionSubQuery unionSubQuery = _query.QueryRoot.FirstSelect(); // Derived Table SQLFromQuery fq = new SQLFromQuery(_query.SQLContext) { Alias = new SQLAliasObjectAlias(_query.SQLContext) { Alias = _query.QueryRoot.CreateUniqueSubQueryName() }, SubQuery = new SQLSubSelectStatement(_query.SQLContext) }; SQLSubQuerySelectExpression sqse = new SQLSubQuerySelectExpression(_query.SQLContext); fq.SubQuery.Add(sqse); sqse.SelectItems = new SQLSelectItems(_query.SQLContext); sqse.From = new SQLFromClause(_query.SQLContext); DataSourceQuery dataSourceQuery = (DataSourceQuery)_query.AddObject(unionSubQuery, fq, typeof(DataSourceQuery)); UnionSubQuery usc = dataSourceQuery.SubQuery.FirstSelect(); DataSource dsDerivedTable = _query.AddObject(usc, "MyDB.MySchema.SalesOrderHeader"); QueryColumnListItem ciDerivedTable1 = usc.QueryColumnList.AddField(dsDerivedTable, "OrderDate"); ciDerivedTable1.Selected = true; QueryColumnListItem ciDerivedTable2 = usc.QueryColumnList.AddField(dsDerivedTable, "SalesOrderID"); ciDerivedTable2.ConditionStrings[0] = "> 25"; // CTE SQLQualifiedName qn = new SQLQualifiedName(_query.SQLContext); if (!unionSubQuery.QueryRoot.SQLContext.SyntaxProvider.IsSupportCTE()) { return; } DataSource dataSourceCte = null; try { AstTokenIdentifier withClauseItemName = _query.QueryRoot.CreateUniqueCTEName("CTE"); qn.Add(withClauseItemName); SubQuery parentSubQuery = unionSubQuery.ParentSubQuery ?? unionSubQuery.QueryRoot as SubQuery; if (parentSubQuery.IsMainQuery) { _query.QueryRoot.AddNewCTE(null, withClauseItemName); } else { if (parentSubQuery.IsSubQueryCTE()) { int index = parentSubQuery.GetSubQueryCTEIndex(); parentSubQuery.InsertNewCTE(index, null, withClauseItemName); } else { parentSubQuery.AddNewCTE(null, withClauseItemName); } } if (_query.IsUniqueAlias(unionSubQuery, withClauseItemName)) { dataSourceCte = _query.AddObject(unionSubQuery, qn, null); } else { string withClauseItemNameStr = withClauseItemName.GetSQL(new SQLGenerationOptions()); using (AstTokenIdentifier alias = _query.CreateUniqueAlias(unionSubQuery, withClauseItemNameStr)) { dataSourceCte = _query.AddObject(unionSubQuery, qn, alias); } } } finally { qn.Dispose(); } SubQuery cte = _query.QueryRoot.GetSubQueryCTEList().FirstOrDefault(); if (cte == null) { return; } UnionSubQuery unionSubQueryCte = cte.FirstSelect(); DataSource ds1 = _query.AddObject(unionSubQueryCte, "MyDB.MySchema.Customers"); // create output column QueryColumnListItem ci1 = unionSubQueryCte.QueryColumnList.AddField(ds1, "CustomerName"); ci1.Selected = true; // create output column QueryColumnListItem ci2 = unionSubQuery.QueryColumnList.AddField(dataSourceCte, "CustomerName"); ci2.Selected = true; }