public static List <SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp)
{
List <SqlFilterCondition> whereClause = new List <SqlFilterCondition>();
SqlFilterCondition cond = new SqlFilterCondition();
StringBuilder incidList = new StringBuilder();
var query = incidSelection.AsEnumerable().Select((r, index) => new
{
RowIndex = RecordIds.IncidNumber(r.Field <string>(0)) - index,
Incid = r.Field <string>(0)
}).ChunkBy(r => r.RowIndex);
List <string> inList = new List <string>();
foreach (var item in query)
{
if (item.Count() < 3)
{
inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
}
else
{
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidTable.incidColumn;
cond.Table = _incidTable;
cond.ColumnSystemType = _incidTable.incidColumn.DataType;
cond.Operator = ">=";
cond.Value = item.First().Incid;
cond.CloseParentheses = String.Empty;
whereClause.Add(cond);
cond = new SqlFilterCondition();
cond.BooleanOperator = "AND";
cond.OpenParentheses = String.Empty;
cond.Column = _incidTable.incidColumn;
cond.Table = _incidTable;
cond.ColumnSystemType = _incidTable.incidColumn.DataType;
cond.Operator = "<=";
cond.Value = item.Last().Incid;
cond.CloseParentheses = ")";
whereClause.Add(cond);
}
}
int i = 0;
while (i < inList.Count)
{
int numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
string[] oneList = new string[numElems];
inList.CopyTo(i, oneList, 0, numElems);
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidMMTable.incidColumn;
cond.Table = _incidMMTable;
cond.ColumnSystemType = _incidTable.incidColumn.DataType;
cond.Operator = "IN ()";
cond.Value = String.Join(",", oneList);
cond.CloseParentheses = ")";
whereClause.Add(cond);
i += numElems;
}
return(whereClause);
}
/// <summary>
/// Create a list of sql filter conditions using a table of selected Incids.
/// </summary>
/// <param name="incidSelection">A DataTable containing the selected Incids.</param>
/// <param name="gisApp">Which GIS system is being used.</param>
/// <returns>
/// A list of sql filter conditions.
/// </returns>
public static List <SqlFilterCondition> GisWhereClause(DataTable incidSelection, GISApp gisApp, bool useIncidTable)
{
List <SqlFilterCondition> whereClause = new List <SqlFilterCondition>();
SqlFilterCondition cond = new SqlFilterCondition();
//StringBuilder incidList = new StringBuilder();
// Split the table of selected Incids into chunks of continuous Incids so
// that each chunk contains a continuous series of one or more Incids.
var query = incidSelection.AsEnumerable().Select((r, index) => new
{
RowIndex = RecordIds.IncidNumber(r.Field <string>(0)) - index,
Incid = r.Field <string>(0)
}).ChunkBy(r => r.RowIndex);
// Create a temporary list for storing some of the Incids.
List <string> inList = new List <string>();
// Determine which table to base the conditions on.
DataTable condTable;
if (useIncidTable)
{
condTable = _incidTable;
}
else
{
condTable = _incidMMTable;
}
// Loop through each chunk/series of Incids. If there are at least three
// then it is worth processing them within ">=" and "<=" operators (as
// this keeps the filter conditions short). If there are only one or two
// Incids in the chunk then add them to the temporary 'inList' for
// processing later.
foreach (var item in query)
{
if (item.Count() < 3)
{
if (gisApp != null)
{
inList.AddRange(item.Select(t => gisApp.QuoteValue(t.Incid)));
}
else
{
inList.AddRange(item.Select(t => t.Incid));
}
}
else
{
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidMMTable.incidColumn;
cond.Table = condTable;
cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
cond.Operator = ">=";
cond.Value = item.First().Incid;
cond.CloseParentheses = String.Empty;
whereClause.Add(cond);
cond = new SqlFilterCondition();
cond.BooleanOperator = "AND";
cond.OpenParentheses = String.Empty;
cond.Column = _incidMMTable.incidColumn;
cond.Table = condTable;
cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
cond.Operator = "<=";
cond.Value = item.Last().Incid;
cond.CloseParentheses = ")";
whereClause.Add(cond);
}
}
// Any Incids that are not part of a continuous series of at least
// three Incid must be queried using an "=" or "IN" sql operator.
// So loop through all these Incids and lump them together into
// strings of 254 Incids at a time so that each string can be used
// in an "IN" statement.
int i = 0;
while (i < inList.Count)
{
int numElems = i < inList.Count - 254 ? 254 : inList.Count - i;
string[] oneList = new string[numElems];
inList.CopyTo(i, oneList, 0, numElems);
cond = new SqlFilterCondition();
cond.BooleanOperator = "OR";
cond.OpenParentheses = "(";
cond.Column = _incidMMTable.incidColumn;
cond.Table = condTable;
cond.ColumnSystemType = _incidMMTable.incidColumn.DataType;
//---------------------------------------------------------------------
// FIX: 001 Improve speed of 'Select current Incid on Map'
// Use " INCID =" in SQL statement instrad of "INCID IN ()"
// if there is only on item in the list (as it is much quicker)
if (inList.Count == 1)
{
cond.Operator = "=";
}
else
{
cond.Operator = "IN ()";
}
//---------------------------------------------------------------------
cond.Value = String.Join(",", oneList);
cond.CloseParentheses = ")";
whereClause.Add(cond);
i += numElems;
}
return(whereClause);
}