public SqlColumn(string name, SqlOperators Optr, object value, SqlWhereOperators whereOperator)
{
Name = name;
Value = value;
Operator = Optr;
WhereOperator = whereOperator;
}
/// <summary>
/// select where [name] [operator] [value]
/// </summary>
/// <param name="name">name as is in database</param>
/// <param name="op">operator</param>
/// <param name="value">value that is compared with operator</param>
/// <param name="lo">TODO add description</param>
public SqlFilter(string name, object value, SqlOperators op = SqlOperators.equals, SqlLogicOperators lo = SqlLogicOperators.and)
{
this.Name = name;
this.Value = value;
this.Operator = op;
this.LogicOperator = lo;
}
public void GetOperatorFormatTest()
{
String[] OperatorFormats = new String[] {
"{0} IS NULL",
"{0} IS NOT NULL",
"{0} = {1}",
"{0} <> {1}",
"{0} > {1}",
"{0} < {1}",
"{0} >= {1}",
"{0} <= {1}",
"{0} LIKE {1}",
"{0} NOT LIKE {1}",
"{0} BETWEEN {1} AND {2}",
"{0} NOT BETWEEN {1} AND {2}"
};
SqlOperator[] Operators = new SqlOperator[] {
SqlOperator.IsNull, //IS NULL
SqlOperator.IsNotNull, //IS NOT NULL
SqlOperator.Equal, //=
SqlOperator.NotEqual, //<>
SqlOperator.GreaterThan, //>
SqlOperator.LessThan, //<
SqlOperator.GreaterThanOrEqual, //>=
SqlOperator.LessThanOrEqual, //<=
SqlOperator.Like, //LIKE
SqlOperator.NotLike, //NOT LIKE
SqlOperator.Between, //BETWEEN
SqlOperator.NotBetween, //NOT BETWEEN
};
SqlOperator op = new SqlOperator(); // TODO: 初始化为适当的值
String expected = String.Empty;
String actual = String.Empty;
for (Byte i = 0; i < 12; i++)
{
op = Operators[i];
expected = OperatorFormats[i];
actual = SqlOperators.InternalGetOperatorFormat(op);
Assert.AreEqual(expected, actual);
}
}
private void AddFilterToGrid(object sender, EventArgs e)
{
DBMap column = (DBMap)cmbColumns.SelectedItem;
SqlOperators operation = (SqlOperators)Enum.Parse(typeof(SqlOperators), cmbChoices.SelectedItem.ToString());
string value = txtSearch1.Text;
if (dgvFilters.Rows.Count >= 1)
{
dgvFilters.Rows.Add(column, operation.ToString(), value, BooleanOps.AND);
}
else
{
dgvFilters.Rows.Add(column, operation.ToString(), value);
}
}
private string BuildFilterNoRows()
{
string fieldName = ((DBMap)cmbColumns.SelectedItem).InternalField;
string searchText = txtSearch1.Text;
SqlOperators operation = (SqlOperators)Enum.Parse(typeof(SqlOperators), cmbChoices.SelectedItem.ToString(), false);
if (tableName.Equals("Survey"))
{
if (fieldName.Equals("county_id"))
{
int countyID = RuntimeVars.Instance.Counties.Find(c => c.CountyName.ToLower().Contains(searchText.ToLower())).ID;
searchText = countyID.ToString();
operation = SqlOperators.Like;
}
}
filters = new ArrayList
{
new Filter(fieldName, operation, searchText)
};
return(((IFilter)filters[0]).FilterString);
}
/// <summary>
/// Create a new Filter statement with the specified name (column), <see cref="SqlOperators"/> operator, and value (search text).
/// </summary>
/// <param name="strFilterName">The name of the column this statement is for.</param>
/// <param name="sqlOperator">The <see cref="SqlOperators"/> that this statement applies.</param>
/// <param name="strFilterValue">The search text to apply to the column using the specified <see cref="SqlOperators"/>.</param>
public Filter(string strFilterName, SqlOperators sqlOperator, string strFilterValue)
{
m_strFilterName = strFilterName;
m_sqlOperator = sqlOperator;
m_strFilterValue = strFilterValue;
}
private string BuildFilter()
{
string fieldName = ((DBMap)dgvFilters.Rows[0].Cells["columnDBColumn"].Value).InternalField;
string searchText = (string)dgvFilters.Rows[0].Cells["columnCriteria"].Value;
SqlOperators operation = (SqlOperators)dgvFilters.Rows[0].Cells["columnOption"].Value;
if (tableName.Equals("Survey"))
{
if (fieldName.Equals("county_id"))
{
int countyID = RuntimeVars.Instance.Counties.Find(c => c.CountyName.ToLower().Contains(searchText.ToLower())).ID;
searchText = countyID.ToString();
operation = SqlOperators.Like;
}
}
//Always add the first row to the filter list.
filters = new ArrayList
{
new Filter(fieldName, operation, searchText)
};
//If there are more rows, continue adding filters that build on each other.
if (dgvFilters.Rows.Count > 1)
{
for (int i = 1; i < dgvFilters.Rows.Count; i++)
{
DataGridViewRow currentRow = dgvFilters.Rows[i];
fieldName = ((DBMap)currentRow.Cells["columnDBColumn"].Value).InternalField;
searchText = (string)currentRow.Cells["columnCriteria"].Value;
operation = (SqlOperators)currentRow.Cells["columnOption"].Value;
if (tableName.Equals("Survey"))
{
if (fieldName.Equals("county_id"))
{
int countyID = RuntimeVars.Instance.Counties.Find(c => c.CountyName.ToLower().Contains(searchText.ToLower())).ID;
searchText = countyID.ToString();
operation = SqlOperators.Like;
}
}
Filter f = new Filter(fieldName, operation, searchText);
BooleanOps op = (BooleanOps)currentRow.Cells["columnBoolean"].Value;
switch (op)
{
case BooleanOps.AND:
filters.Add(new ANDFilter((IFilter)filters[i - 1], f));
break;
case BooleanOps.OR:
filters.Add(new ORFilter((IFilter)filters[i - 1], f));
break;
}
}
}
//Return the final filter string in the FilterExpressionList.
return(((IFilter)filters[filters.Count - 1]).FilterString);
}
public DictCont(object Value)
{
this.Value = Value;
Operator = SqlOperators.Equal;
}
public DictCont(SqlOperators Operator, object Value)
{
this.Value = Value;
this.Operator = Operator;
}
public Where <T, TField> Where <TField>(Expression <Func <T, TField> > field, SqlOperators Optr, TField value)
{
return(new Where <T, TField>(field, Optr, value));
}
public static IQueryable <T> FullTextSearch <T>(this IQueryable <T> queryable, string searchKey, bool exactMatch, SqlOperators sqlOperators)
{
ParameterExpression parameter = Expression.Parameter(typeof(T), "c");
MethodInfo containsMethod = typeof(string).GetMethod("Contains", new Type[] { typeof(string) });
//MethodInfo toStringMethod = typeof(object).GetMethod("ToString", new Type[] { });
var publicProperties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly).Where(p => p.PropertyType == typeof(string));
Expression operatorExpressions = null;
string[] searchKeyParts;
if (exactMatch)
{
searchKeyParts = new[] { searchKey };
}
else
{
searchKeyParts = searchKey.Split(' ');
}
foreach (var property in publicProperties)
{
Expression nameProperty = Expression.Property(parameter, property);
foreach (var searchKeyPart in searchKeyParts.Select(p => p.Trim()))
{
Expression searchKeyExpression = Expression.Constant(searchKeyPart);
Expression callContainsMethod = Expression.Call(nameProperty, containsMethod, searchKeyExpression);
if (operatorExpressions == null)
{
operatorExpressions = callContainsMethod;
}
else
{
if (sqlOperators == SqlOperators.Or)
{
operatorExpressions = Expression.Or(operatorExpressions, callContainsMethod);
}
if (sqlOperators == SqlOperators.And)
{
operatorExpressions = Expression.And(operatorExpressions, callContainsMethod);
}
}
}
}
MethodCallExpression whereCallExpression = Expression.Call(
typeof(Queryable),
"Where",
new Type[] { queryable.ElementType },
queryable.Expression,
Expression.Lambda <Func <T, bool> >(operatorExpressions, new ParameterExpression[] { parameter }));
return(queryable.Provider.CreateQuery <T>(whereCallExpression));
}
public Filter(string strFilterName, SqlOperators sqlOperator, string strFilterValue)
{
m_strFilterName = strFilterName;
m_sqlOperator = sqlOperator;
m_strFilterValue = strFilterValue;
//Id tracks the original FilterName in case the value pairs are to change
m_strID = strFilterName;
}
public Where(Expression <Func <T, TField> > field, SqlOperators Optr, TField value)
: base((field.Body as MemberExpression).Member.Name, Optr, value)
{
}
public static SqlColumn Create(string name, SqlOperators optr, int value, SqlWhereOperators whereOperator)
{
return(new SqlColumn(name, optr, value, whereOperator));
}
public static SqlColumn Create(string name, SqlOperators optr, object value)
{
return(new SqlColumn(name, optr, value));
}
public SqlColumn(string name, SqlOperators Optr, int value)
{
Name = name;
Value = value;
Operator = Optr;
}
