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;
}
