/// <summary>
/// Append an array of <see cref="FilterField"/> to a Where Collection
/// </summary>
/// <param name="whereCollection">Reference to the WhereCollection</param>
/// <param name="filterFields">An array of <see cref="FilterField"/> containing the query string filters</param>
/// <param name="wrapIntoParenthesis">A flag to define whether to wrap the filters into a parenthesis</param>
public static void AppendQueryFilters(this WhereCollection whereCollection, FilterField[] filterFields, bool wrapIntoParenthesis)
{
// Make sure filters are not null
if (filterFields == null)
{
return;
}
if (filterFields.Length == 0)
{
return;
}
// Makre sure where collection is initialized
if (whereCollection == null)
{
throw new NullReferenceException("The 'WhereCollection' is empty. System is unable to Append Query Filters to a null collection");
}
// The Grouped Where Collection
var groupedWhereCollection = new GroupedFilterExpression(FieldAndOr.And);
// Loop thru all filters on the QueryFilters
foreach (var filter in filterFields)
{
// Creates the Expression
var filterExpression = new FilterExpression()
{
Field1 = new Field(filter.Field),
Field2 = filter.Value,
AndOr = FieldAndOr.And,
};
// Chooses the Operator
switch (filter.Operator)
{
case FilterFieldOperations.NotEqual: filterExpression.Operator = FieldOperators.NotEqual; break;
case FilterFieldOperations.In: filterExpression.Operator = FieldOperators.In; break;
case FilterFieldOperations.NotIn: filterExpression.Operator = FieldOperators.NotIn; break;
case FilterFieldOperations.LessThan: filterExpression.Operator = FieldOperators.LessThan; break;
case FilterFieldOperations.LessThanOrEqualTo: filterExpression.Operator = FieldOperators.LessThanOrEqual; break;
case FilterFieldOperations.GreaterThan: filterExpression.Operator = FieldOperators.GreaterThan; break;
case FilterFieldOperations.GreaterThanOrEqualTo: filterExpression.Operator = FieldOperators.GreaterThanOrEqual; break;
default:
filterExpression.Operator = FieldOperators.Equal;
break;
}
// Adds the Other Side of the Filter
//if (filter.Value.GetType().IsArray)
//{
// filterExpression.Field2 = filter.Value;
//}
//else
//{
// filterExpression.Field2 = filter.Value;
//}
// Includes it on the collection
if (wrapIntoParenthesis)
{
groupedWhereCollection.Add(filterExpression);
}
else
{
whereCollection.Add(filterExpression);
}
}
// When wrapping, adds the GroupedWhereCollection later
if (wrapIntoParenthesis)
{
whereCollection.Add(groupedWhereCollection);
}
}
private bool HasOneLine(string pFieldName, ref ChooseFromListEventArgs e)
{
var query = new TableQuery(Query)
{
Top = 2
};
var serarchField = new List <TableAdapterField>
{
query.Fields[pFieldName]
};
if (pFieldName == FieldNameDescription)
{
serarchField.AddRange(WhereColumns);
}
var where = new WhereCollection();
foreach (var column in serarchField)
{
where.Add(column.DbTypeIsNumeric()
? new QueryParam(column, e.SearchBy == null ? 0 : e.SearchBy.To <Int64>())
: new QueryParam(column, e.SearchBy ?? string.Empty));
where.Last().Relationship = eRelationship.erOr;
if (ConditionQuery != eCondition.ecNone)
{
where.Last().Condition = ConditionQuery;
}
}
query.Wheres.Add(where);
var script = new SqlServerScriptWizard(query);
var command = script.GetSelectStatment();
var data = Connection.Instance.SqlServerQuery(command.Item1, command.Item2);
if (data.Rows.Count == 1)
{
e.Record = data.Rows[0];
}
else
{
serarchField = new List <TableAdapterField> {
query.Fields[pFieldName]
};
if (pFieldName == FieldNameDescription)
{
serarchField.AddRange(WhereColumns);
}
where = new WhereCollection();
foreach (var column in serarchField)
{
@where.Add(column.DbTypeIsNumeric()
? new QueryParam(column, e.SearchBy == null ? 0 : e.SearchBy.To <Int64>())
: new QueryParam(column, eCondition.ecLike, e.SearchBy ?? String.Empty));
where.Last().Relationship = eRelationship.erOr;
}
query.Wheres.Add(where);
var comand = script.GetSelectStatment();
data = Connection.Instance.SqlServerQuery(comand.Item1, comand.Item2);
if (data.Rows.Count == 1)
{
e.Record = data.Rows[0];
}
}
return(data.Rows.Count == 1);
}
private void FillResult()
{
try
{
Cursor.Current = Cursors.WaitCursor;
var query = new TableQuery(Query);
if (!String.IsNullOrEmpty(SearchBy))
{
var where = new WhereCollection();
foreach (var item in SearchColumns)
{
QueryParam param;
if (item.DbTypeIsNumeric())
{
int intConvert;
if (!int.TryParse(SearchBy, out intConvert))
{
continue;
}
param = new QueryParam(item, intConvert);
}
else
{
param = new QueryParam(item, eCondition.ecLike, SearchBy);
}
param.Relationship = eRelationship.erOr;
where.Add(param);
query.OrderBy.Add(new OrderBy(param));
}
query.Wheres.Add(where);
}
var command = new SqlServerScriptWizard(query).GetSelectStatment();
grdResult.DataSource = Connection.Instance.SqlServerQuery(command.Item1, command.Item2);
ViewResult.BestFitColumns();
ViewResult.ClearSorting();
if (!SearchColumns.IsEmpty())
{
ViewResult.FocusedColumn = ViewResult.Columns[SearchColumns.First().Name];
ViewResult.FocusedColumn.SortOrder = DevExpress.Data.ColumnSortOrder.Ascending;
if (Record != null)
{
var fieldValue = Record[ViewResult.FocusedColumn.FieldName].ToString();
ViewResult.StartIncrementalSearch(fieldValue);
}
}
}
finally
{
Cursor.Current = Cursors.Default;
}
}