/// <summary>
/// 获得查询条件字符串
/// </summary>
/// <param name="stringBuilder"></param>
/// <param name="criterions"></param>
/// <param name="command"></param>
/// <param name="sqlOperator"></param>
public static void GetCriterionString(StringBuilder stringBuilder, IEnumerable<Criterion> criterions, SqlCommand command, SqlOperator sqlOperator)
{
bool _isNotfirstFilterClause = false;
if (criterions.Count() >= 1)
{
stringBuilder.Append(" WHERE ");
foreach (var item in criterions)
{
if (item.CriteriaOperator != CriteriaOperator.Like)
{
if (_isNotfirstFilterClause == true)
{
stringBuilder.Append(TranslateHelper.GetStringFromSqlOperator(sqlOperator));
}
stringBuilder.Append(TranslateHelper.GetStringFromCriterion(item));
command.Parameters.Add(ParameterDataNullHelper.ChangeNull("@" + item.ParameterName, item.ParameterValue));
_isNotfirstFilterClause = true;
}
else
{
if (_isNotfirstFilterClause == true)
{
stringBuilder.Append(TranslateHelper.GetStringFromSqlOperator(sqlOperator));
}
stringBuilder.Append(TranslateHelper.GetFuzzyStringFromCriterion(item));
//command.Parameters.Add(ParameterDataNullHelper.ChangeNull("@" + item.ParameterName, item.ParameterValue));
_isNotfirstFilterClause = true;
}
}
_isNotfirstFilterClause = false;
}
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="parameterOne">参数一</param>
/// <param name="parameterTwo">参数二</param>
/// <param name="op">条件运算符</param>
internal SqlBasicParameterCondition(AbstractSqlCommand baseCommand, SqlParameter parameterOne, SqlParameter parameterTwo, SqlOperator op)
: base(baseCommand)
{
this._parameterOne = parameterOne;
this._parameterTwo = parameterTwo;
this._operator = op;
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="parameterOne">参数一</param>
/// <param name="parameterTwo">参数二</param>
/// <param name="op">条件运算符</param>
private SqlBasicParameterCondition(AbstractSqlCommandWithWhere baseCommand, DataParameter parameterOne, DataParameter parameterTwo, SqlOperator op)
: base(baseCommand)
{
this._parameterOne = parameterOne;
this._parameterTwo = parameterTwo;
this._operator = op;
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="columnName">字段名称</param>
/// <param name="op">条件运算符</param>
/// <param name="command">选择语句</param>
internal SqlBasicCommandCondition(AbstractSqlCommand baseCommand, String columnName, SqlOperator op, SelectCommand command)
: base(baseCommand)
{
this._columnName = columnName;
this._operator = op;
this._command = command;
}
/// <summary>
/// ���캯��
/// </summary>
/// <param name="fieldName">�ֶ�����</param>
/// <param name="fieldValue">�ֶε�ֵ</param>
/// <param name="sqlOperator">�ֶε�Sql��������</param>
/// <param name="excludeIfEmpty">����ֶ�Ϊ�ջ���Null����Ϊ��ѯ����</param>
public SearchInfo(string fieldName, object fieldValue, string datatype,SqlOperator sqlOperator, bool excludeIfEmpty)
{
this.fieldName = fieldName;
this.fieldValue = fieldValue;
this.datatype = datatype;
this.sqlOperator = sqlOperator;
this.excludeIfEmpty = excludeIfEmpty;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段的值</param>
/// <param name="sqlOperator">字段的Sql操作符号</param>
/// <param name="excludeIfEmpty">如果字段为空或者Null则不作为查询条件</param>
/// <param name="groupName">分组的名称，如需构造一个括号内的条件 ( Test = "AA1" OR Test = "AA2"), 定义一个组名集中条件</param>
public SearchInfo(string fieldName, object fieldValue, SqlOperator sqlOperator, bool excludeIfEmpty, string groupName)
{
this.fieldName = fieldName;
this.fieldValue = fieldValue;
this.sqlOperator = sqlOperator;
this.excludeIfEmpty = excludeIfEmpty;
this.groupName = groupName;
}
public Query(string tableName, IList<Criterion> criterions,
SqlOperator sqlOperator, IList<OrderByClause> orderByClause)
{
_tableName = tableName;
_criterions = criterions;
_sqlOperator = sqlOperator;
_orderByClauses = orderByClause;
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="columnName">字段名称</param>
/// <param name="op">条件运算符</param>
/// <param name="tableName">查询的表名</param>
/// <param name="action">设置选择语句的方法</param>
private SqlBasicCommandCondition(AbstractSqlCommandWithWhere baseCommand, String columnName, SqlOperator op, String tableName, Action<SelectCommand> action)
: base(baseCommand)
{
this._columnName = columnName;
this._operator = op;
SelectCommand command = baseCommand.Database.InternalCreateSelectCommand((baseCommand.RootSource == null ? baseCommand : baseCommand.RootSource), tableName);
action(command);
this._command = command;
}
public static WhereTerm DefaultParam(DateTime value, string column, SqlOperator sqlOperator)
{
return new WhereTerm
{
Value = value,
TableName = "",
ParamDataType = EnumParamterDataTypes.DateTime,
Operator = sqlOperator,
ColumnName = column
};
}
public static WhereTerm DefaultParam(string value, string column, SqlOperator oSqlOperator)
{
return new WhereTerm
{
Value = value,
TableName = "",
ParamDataType = EnumParamterDataTypes.Character,
Operator = oSqlOperator,
ColumnName = column
};
}
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);
}
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="parameterOne">参数一</param>
/// <param name="parameterTwo">参数二</param>
/// <param name="op">条件运算符</param>
internal SqlBasicParameterCondition(SqlParameter parameterOne, SqlParameter parameterTwo, SqlOperator op)
{
this._parameterOne = parameterOne;
this._parameterTwo = parameterTwo;
this._operator = op;
}
/// <summary>
/// 初始化一个<see cref="WhereInfo"/>类型的实例
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="fieldValue">字段值</param>
/// <param name="sqlOperator">操作符</param>
public WhereInfo(string fieldName, object fieldValue, SqlOperator sqlOperator) : this(fieldName, fieldValue, sqlOperator, true)
{
}
/// <summary>
/// 添加条件
/// </summary>
/// <typeparam name="T">字段值类型</typeparam>
/// <param name="fieldName">字段名</param>
/// <param name="operator">操作符</param>
/// <param name="fieldValue">字段值，注：1、不可谓数组;2、Between时，此字段必须填两个值</param>
/// <returns></returns>
public virtual IConditionBuilder Append <T>(string fieldName, SqlOperator @operator, params T[] fieldValue)
{
return(Append <T>(RelationType.And, fieldName, @operator, fieldValue));
}
/// <summary>
/// 为查询添加条件
/// <example>
/// 用法一：
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual);
/// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like);
/// string conditionSql = searchObj.BuildConditionSql();
///
/// 用法二：AddCondition函数可以串起来添加多个条件
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual).AddCondition("Test2", "Test2Value", SqlOperator.Like);
/// string conditionSql = searchObj.BuildConditionSql();
/// </example>
/// </summary>
/// <param name="fielName">字段名称</param>
/// <param name="fieldValue">字段值</param>
/// <param name="sqlOperator">SqlOperator枚举类型</param>
/// <returns>增加条件后的Hashtable</returns>
//public SearchCondition AddCondition(string fielName, object fieldValue, SqlOperator sqlOperator)
//{
// this.conditionTable.Add(fielName, new SearchInfo(fielName, fieldValue, sqlOperator));
// return this;
//}
#region AddCondition(为查询添加条件)
/// <summary>
/// 为查询添加条件
/// <example>
/// 用法一：
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false);
/// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like, true);
/// string conditionSql = searchObj.BuildConditionSql();
///
/// 用法二：AddCondition函数可以串起来添加多个条件
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false).AddCondition("Test2", "Test2Value", SqlOperator.Like, true);
/// string conditionSql = searchObj.BuildConditionSql();
/// </example>
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段值</param>
/// <param name="sqlOperator">SqlOperator枚举类型</param>
/// <param name="IfExclude">是否排除，比如!string.isNullOrEmpty(key)</param>
/// <returns></returns>
public SearchCondition AddCondition(string fieldName, object fieldValue, SqlOperator sqlOperator, bool ifExclude)
{
this.conditionTable.Add(fieldName, new SearchInfo(fieldName, fieldValue, sqlOperator, ifExclude));
return(this);
}
/// <summary>
/// 创建双参数新的Sql条件语句
/// </summary>
/// <param name="cmd">Sql语句</param>
/// <param name="columnName">字段名</param>
/// <param name="op">条件运算符</param>
/// <param name="dataType">数据类型</param>
/// <param name="valueOne">数据一</param>
/// <param name="valueTwo">数据二</param>
/// <returns>Sql条件语句</returns>
internal static SqlBasicParameterCondition InternalCreate(AbstractSqlCommandWithWhere cmd, String columnName, SqlOperator op, DataType dataType, Object valueOne, Object valueTwo)
{
return new SqlBasicParameterCondition(cmd, cmd.CreateDataParameter(columnName, dataType, valueOne), cmd.CreateDataParameter(columnName, dataType, valueTwo), op);
}
/// <summary>
/// ת��ö������Ϊ��Ӧ��Sql����������
/// </summary>
/// <param name="sqlOperator">SqlOperatorö�ٶ���</param>
/// <returns><![CDATA[��Ӧ��Sql���������ţ��� ">" "<>" ">=")]]></returns>
private string ConvertSqlOperator(SqlOperator sqlOperator)
{
string stringOperator = " = ";
switch (sqlOperator)
{
case SqlOperator.Equal:
stringOperator = " = ";
break;
case SqlOperator.LessThan:
stringOperator = " < ";
break;
case SqlOperator.LessThanOrEqual:
stringOperator = " <= ";
break;
case SqlOperator.Like:
stringOperator = " Like ";
break;
case SqlOperator.MoreThan:
stringOperator = " > ";
break;
case SqlOperator.MoreThanOrEqual:
stringOperator = " >= ";
break;
case SqlOperator.NotEqual:
stringOperator = " <> ";
break;
case SqlOperator.Between:
stringOperator = " Between ";
break;
default:
break;
}
return stringOperator;
}
public static T Any <T>(this T query, Field field, SqlOperator sqlOperator, Select select)
where T : IConditionContainer
{
query.Add(new Any(field, sqlOperator, @select));
return(query);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段的值</param>
/// <param name="sqlOperator">字段的Sql操作符号</param>
public SearchInfo(string fieldName, object fieldValue, SqlOperator sqlOperator)
: this(fieldName, fieldValue, sqlOperator, true)
{ }
public static void Add(this IClauseCollection clauseCollection, string column, SqlOperator sqlOperator, object value)
{
Add(clauseCollection, null, column, sqlOperator, value);
}
public static IClauseCollection <T> Or <T, TProp>(this IClauseCollection <T> clauseCollection, Expression <Func <T, TProp> > property, SqlOperator sqlOperator, TProp value, Func <TProp, bool> applyPredicate)
{
if (applyPredicate(value))
{
clauseCollection.Or(property, sqlOperator, value);
}
return(clauseCollection);
}
public static void Add(this IClauseCollection clauseCollection, string @operator, string column, SqlOperator sqlOperator, object value)
{
Clause clause = new Clause(clauseCollection.Parameters, column, sqlOperator, value);
clauseCollection.Add(@operator, clause);
}
public Delete(string tableName, IList<Criterion> criterions, SqlOperator sqlOperator)
{
_tableName = tableName;
_criterions = criterions;
_sqlOperator = sqlOperator;
}
/// <summary>
/// 获取操作符字符串
/// </summary>
/// <param name="operator">操作符类型</param>
/// <returns></returns>
protected static string GetOperator(SqlOperator @operator)
{
string result;
switch (@operator)
{
case SqlOperator.Equal:
result = " = ";
break;
case SqlOperator.NotEqual:
result = " <> ";
break;
case SqlOperator.GreaterThan:
result = " > ";
break;
case SqlOperator.GreaterEqual:
result = " >= ";
break;
case SqlOperator.LessThan:
result = " < ";
break;
case SqlOperator.LessEqual:
result = " <= ";
break;
case SqlOperator.Contains:
case SqlOperator.Starts:
case SqlOperator.Ends:
result = " LIKE ";
break;
case SqlOperator.NotContains:
result = " NOT LIKE ";
break;
case SqlOperator.IsNull:
result = " IS NULL ";
break;
case SqlOperator.IsNotNull:
result = " IS NOT NULL ";
break;
case SqlOperator.In:
result = " IN ";
break;
case SqlOperator.NotIn:
result = " NOT IN ";
break;
case SqlOperator.Between:
result = " BETWEEN ";
break;
default:
throw new SqlBuilderException("条件未定义!");
}
return(result);
}
/// <summary>
/// 为查询添加条件
/// <example>
/// 用法一：
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false);
/// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like, true);
/// string conditionSql = searchObj.BuildConditionSql();
///
/// 用法二：AddCondition函数可以串起来添加多个条件
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false).AddCondition("Test2", "Test2Value", SqlOperator.Like, true);
/// string conditionSql = searchObj.BuildConditionSql();
/// </example>
/// </summary>
/// <param name="fielName">字段名称</param>
/// <param name="fieldValue">字段值</param>
/// <param name="sqlOperator">SqlOperator枚举类型</param>
/// <param name="excludeIfEmpty">如果字段为空或者Null则不作为查询条件</param>
/// <returns></returns>
public SearchCondition AddCondition(string fielName, object fieldValue, SqlOperator sqlOperator, bool excludeIfEmpty)
{
this.conditionTable.Add(System.Guid.NewGuid() /*fielName*/, new SearchInfo(fielName, fieldValue, sqlOperator, excludeIfEmpty));
return(this);
}
public static string ToSqlWhereClause(this List <string> filters, SqlOperator op)
{
return(filters.Count == 0
? (op == SqlOperator.And ? SqlTrue : SqlFalse)
: string.Join($" {op.ToString()} ", filters));
}
/// <summary>
/// 将多个条件分组归类作为一个条件来查询，
/// 如需构造一个括号内的条件 ( Test = "AA1" OR Test = "AA2")
/// </summary>
/// <param name="fielName">字段名称</param>
/// <param name="fieldValue">字段值</param>
/// <param name="sqlOperator">SqlOperator枚举类型</param>
/// <param name="excludeIfEmpty">如果字段为空或者Null则不作为查询条件</param>
/// <param name="groupName">分组的名称，如需构造一个括号内的条件 ( Test = "AA1" OR Test = "AA2"), 定义一个组名集中条件</param>
/// <returns></returns>
public SearchCondition AddCondition(string fielName, object fieldValue, SqlOperator sqlOperator,
bool excludeIfEmpty, string groupName)
{
this.conditionTable.Add(System.Guid.NewGuid()/*fielName*/, new SearchInfo(fielName, fieldValue, sqlOperator, excludeIfEmpty, groupName));
return this;
}
/// <summary>
/// 添加条件
/// </summary>
/// <param name="name">名称</param>
/// <param name="value">值</param>
/// <param name="sqlOperator">操作符</param>
/// <returns></returns>
public SearchCondition AddCondition(string name, string value, SqlOperator sqlOperator)
{
list.Add(new Condition(name, value, sqlOperator));
return(this);
}
/// <summary>
/// 通过SqlOperator获得操作符号（AND 和 OR）
/// </summary>
/// <param name="sqlOperator"></param>
/// <returns></returns>
public static string GetStringFromSqlOperator(SqlOperator sqlOperator)
{
switch (sqlOperator)
{
case SqlOperator.AND:
return " AND ";
case SqlOperator.OR:
return " OR ";
default:
throw new Exception("错误的连接操作符");
}
}
private IWhereClauseBuilder <TKey, TEntity> Render(SqlOperator @operator, Action write, Expression <Func <TEntity, object> > member, object value, object other)
{
string GetParameterName() => $"{this._dialectSettings.ParameterPrefix}{AppUtility.GetUniqueStringValue(10)}";
void InsertParameter(string key, object obj) => this._parameters.Insert(key, obj);
write?.Invoke(); // ???
//Func<string> getParameterName = () => $"{this._dialectSettings.ParameterPrefix}{AppUtility.GetUniqueStringValue(10)}";
//Action<string, object> insertParameter = (arg1, arg2) => this._parameters.Insert(arg1, arg2);
string pn = null;
object pv = null;
string spn = null;
object spv = null;
string mn = $"{this._storeMapping.GetEscapeTableName(_classMap.TableName, this._dialectSettings)}.{this._storeMapping.GetEscapeColumnName(_classMap.Properties.Get(member.GetMemberName()).ColumnName, this._dialectSettings)}";
switch (@operator)
{
case SqlOperator.Like:
if (value == null)
{
throw new ArgumentNullException(nameof(value));
}
if (value.GetType() != typeof(string))
{
throw new InvalidOperationException();
}
pn = GetParameterName();
pv = value;
InsertParameter(pn, pv);
this._builder.Append($"{mn} LIKE {pn}");
break;
case SqlOperator.NotLike:
if (value == null)
{
throw new ArgumentNullException(nameof(value));
}
if (value.GetType() != typeof(string))
{
throw new InvalidOperationException();
}
pn = GetParameterName();
pv = value;
InsertParameter(pn, pv);
this._builder.Append($"{mn} {this.NotStatement} LIKE {pn}");
break;
case SqlOperator.Between:
if (value == null)
{
throw new ArgumentNullException(nameof(value));
}
if (other == null)
{
throw new ArgumentNullException(nameof(other));
}
if (!value.GetType().IsSimpleType())
{
throw new InvalidOperationException();
}
if (!other.GetType().IsSimpleType())
{
throw new InvalidOperationException();
}
if (value.GetType() != other.GetType())
{
throw new InvalidOperationException();
}
pn = GetParameterName();
pv = value;
spn = GetParameterName();
spv = other;
InsertParameter(pn, pv);
InsertParameter(spn, spv);
this._builder.Append($"{mn} BETWEEN {pn} {this.AndStatement} {spn}");
break;
case SqlOperator.NotBetween:
if (value == null)
{
throw new ArgumentNullException(nameof(value));
}
if (other == null)
{
throw new ArgumentNullException(nameof(other));
}
if (!value.GetType().IsSimpleType())
{
throw new InvalidOperationException();
}
if (!other.GetType().IsSimpleType())
{
throw new InvalidOperationException();
}
if (value.GetType() != other.GetType())
{
throw new InvalidOperationException();
}
pn = GetParameterName();
pv = value;
spn = GetParameterName();
spv = other;
InsertParameter(pn, pv);
InsertParameter(spn, spv);
this._builder.Append($"{mn} {this.NotStatement} BETWEEN {pn} {this.AndStatement} {spn}");
break;
case SqlOperator.In:
if (value == null)
{
throw new ArgumentNullException(nameof(value));
}
if (
#if NetCore
value.GetType().GetTypeInfo().IsGenericType
#else
value.GetType().IsGenericType
#endif
)
{
if (!typeof(IEnumerable).IsAssignableFrom(value.GetType().GetGenericTypeDefinition()))
{
throw new InvalidOperationException();
}
if (!value.GetType().GetGenericArguments().Single().IsSimpleType())
{
throw new InvalidOperationException();
}
}
else if (value.GetType().IsArray)
{
if (!value.GetType().GetElementType().IsSimpleType())
{
throw new InvalidOperationException();
}
}
else
{
throw new InvalidOperationException();
}
pn = GetParameterName();
pv = value;
InsertParameter(pn, pv);
this._builder.Append($"{mn} IN {pn}");
break;
/// <summary>
/// 创建双参数新的Sql条件语句
/// </summary>
/// <param name="cmd">Sql语句</param>
/// <param name="columnName">字段名</param>
/// <param name="op">条件运算符</param>
/// <param name="columnNameTwo">字段名二</param>
/// <returns>Sql条件语句</returns>
internal static SqlBasicParameterCondition InternalCreateColumn(AbstractSqlCommandWithWhere cmd, String columnName, SqlOperator op, String columnNameTwo)
{
return new SqlBasicParameterCondition(cmd, cmd.CreateDataParameterCustomAction(columnName, GetFieldName(String.Empty, columnNameTwo)), op);
}
public SqlCompareFilter(ISqlTable table, string columnName, SqlOperator @operator, System.Data.DbType dataType, object value) : base(table, columnName, @operator, value)
{
this.DataType = dataType;
}
/// <summary>
/// 获取运算符字符串格式
/// </summary>
/// <param name="op">运算符</param>
/// <returns>字符串格式</returns>
internal static String InternalGetOperatorFormat(SqlOperator op)
{
Byte index = (Byte)op;
return SqlOperators.OperatorFormats[OperatorTypeTotalSum[index / 100] + index % 100];
}
public static UpdateStatement Where(this UpdateStatement update, string columnName, SqlOperator op, object value)
{
update.Conditions.Add(new Condition(columnName, op, value));
return(update);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段的值</param>
/// <param name="sqlOperator">字段的Sql操作符号</param>
/// <param name="excludeIfEmpty">如果字段为空或者Null则不作为查询条件</param>
public SearchEntity(string fieldName, object fieldValue, SqlOperator sqlOperator, bool excludeIfEmpty)
: this(fieldName, fieldValue, sqlOperator, excludeIfEmpty, null)
{
}
public static UpdateStatement Or(this UpdateStatement update, string columnName, SqlOperator op, object value)
{
update.Conditions.Add(new Condition(columnName, op, value)
{
Relationship = ConditionRelationship.Or
});
return(update);
}
/// <summary>
/// Adds a <see cref="SqlCompareFilter"/> to the given collection.
/// </summary>
/// <param name="collection"></param>
/// <param name="table"></param>
/// <param name="columnName"></param>
/// <param name="oper"></param>
/// <param name="dataType"></param>
/// <param name="value"></param>
/// <returns></returns>
public static SqlCompareFilter Add(this ICollection <ISqlFilter> collection, ISqlTable table, string columnName, SqlOperator oper, System.Data.DbType dataType, object value)
{
var item = new SqlCompareFilter(table, columnName, oper, dataType, value);
collection.Add(item);
return(item);
}
public Any(Field field, SqlOperator sqlOperator, Select select) : base(field, sqlOperator)
{
Select = @select;
}
public SqlColumnCompareFilter(ISqlTable leftTable, string columnName, SqlOperator @operator, ISqlTable rightTable)
: this(leftTable, columnName, @operator, rightTable, columnName)
{
}
/// <summary>
/// 添加条件
/// </summary>
/// <param name="operator">操作符</param>
/// <param name="conditionDict">条件字典，例如：A.Name 1</param>
/// <returns></returns>
public IConditionBuilder Append(SqlOperator @operator, Dictionary <string, object> conditionDict)
{
return(Append(RelationType.And, @operator, conditionDict));
}
/// <summary>
/// 初始化一个<see cref="WhereInfo"/>类型的实例
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="beginValue">开始字段值</param>
/// <param name="endValue">结束字段值</param>
/// <param name="sqlOperator">操作符</param>
public WhereInfo(string fieldName, object beginValue, object endValue, SqlOperator sqlOperator) : this(fieldName, beginValue, endValue, sqlOperator, true)
{
}
/// <summary>
/// 创建新的Sql条件语句
/// </summary>
/// <param name="cmd">Sql语句</param>
/// <param name="columnName">字段名称</param>
/// <param name="op">条件运算符</param>
/// <param name="action">设置选择语句的方法</param>
/// <exception cref="ArgumentNullException">设置语句的方法不能为空</exception>
/// <returns>Sql条件语句</returns>
internal static SqlBasicCommandCondition InternalCreate(AbstractSqlCommandWithWhere cmd, String columnName, SqlOperator op, Action<SelectCommand> action)
{
if (action == null)
{
throw new ArgumentNullException("action");
}
return new SqlBasicCommandCondition(cmd, columnName, op, cmd.TableName, action);
}
/// <summary>
/// 为查询添加条件
/// <example>
/// 用法一：
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual);
/// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like);
/// string conditionSql = searchObj.BuildConditionSql();
///
/// 用法二：AddCondition函数可以串起来添加多个条件
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual).AddCondition("Test2", "Test2Value", SqlOperator.Like);
/// string conditionSql = searchObj.BuildConditionSql();
/// </example>
/// </summary>
/// <param name="fielName">字段名称</param>
/// <param name="fieldValue">字段值</param>
/// <param name="sqlOperator">SqlOperator枚举类型</param>
/// <returns>增加条件后的Hashtable</returns>
public SearchCondition AddCondition(string fielName, object fieldValue, SqlOperator sqlOperator)
{
this.conditionTable.Add(System.Guid.NewGuid()/*fielName*/, new SearchInfo(fielName, fieldValue, sqlOperator));
return this;
}
/// <summary>
/// Adds an OR condition to the select statement.
/// </summary>
/// <param name="select">The select statement.</param>
/// <param name="column">The column.</param>
/// <param name="op">The op.</param>
/// <param name="value">The value.</param>
/// <returns>The select statement.</returns>
public static SelectStatement Or(this SelectStatement select, SourceExpression column, SqlOperator op, object value)
{
select.Conditions.Add(new Condition(column, op, value)
{
Relationship = ConditionRelationship.Or
});
return(select);
}
/// <summary>
/// ���캯��
/// </summary>
/// <param name="fieldName">�ֶ�����</param>
/// <param name="fieldValue">�ֶε�ֵ</param>
/// <param name="sqlOperator">�ֶε�Sql��������</param>
public SearchInfo(string fieldName, object fieldValue, string datatype, SqlOperator sqlOperator)
: this(fieldName, fieldValue,datatype,sqlOperator, false)
{
}
public void AddSqlOperator(SqlOperator sqlOperator)
{
_sqlOperator = sqlOperator;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段的值</param>
/// <param name="sqlOperator">字段的Sql操作符号</param>
/// <param name="excludeIfEmpty">如果字段为空或者Null则不作为查询条件</param>
public SearchInfo(string fieldName, object fieldValue, SqlOperator sqlOperator, bool excludeIfEmpty)
: this(fieldName, fieldValue, sqlOperator, excludeIfEmpty, null)
{ }
/// <summary>
/// 完成查询功能按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, EventArgs e)
{
string sqlstr = string.Empty;
SearchCondition searchObj = new SearchCondition();
for (int i = 0; i < SearchDgv.RowCount; i++)
{
SqlOperator typestr = new SqlOperator();
string dtype = SearchDgv.Rows[i].Cells["类型"].Value.ToString();
string fieldName = SearchDgv.Rows[i].Cells["column_name"].Value != null ? SearchDgv.Rows[i].Cells["column_name"].Value.ToString().ToUpper() : ""; ;
string fieldtype = (SearchDgv.Rows[i].Cells["sign"] as DataGridViewComboBoxCell).Value != null ? (SearchDgv.Rows[i].Cells["sign"] as DataGridViewComboBoxCell).Value.ToString() : "";
object value ;
string fieldvalue;
if (fieldName == "FLOWSTATUS")
{
value = SearchDgv.Rows[i].Cells["value"].Value;
if (value != null)
{
fieldvalue = value.ToString();
switch (fieldvalue)
{
case "初始":
fieldvalue = DBConnection.GetSpoolStatus("初始");
break;
case "审核中":
fieldvalue = DBConnection.GetSpoolStatus("审核中");
break;
case "审核通过":
fieldvalue = DBConnection.GetSpoolStatus("审核通过");
break;
case "审核退回":
fieldvalue = DBConnection.GetSpoolStatus("审核退回");
break;
case "下料完成":
fieldvalue = DBConnection.GetSpoolStatus("下料完成");
break;
case "装配完成":
fieldvalue = DBConnection.GetSpoolStatus("装配完成");
break;
case "焊接完成":
fieldvalue = DBConnection.GetSpoolStatus("焊接完成");
break;
case "待验":
fieldvalue = DBConnection.GetSpoolStatus("待验");
break;
case "检验通过":
fieldvalue = DBConnection.GetSpoolStatus("检验通过");
break;
case "检验不通过":
fieldvalue = DBConnection.GetSpoolStatus("检验不通过");
break;
case "处理完成":
fieldvalue = DBConnection.GetSpoolStatus("处理完成");
break;
case "接收完成":
fieldvalue = DBConnection.GetSpoolStatus("接收完成");
break;
case "发放完成":
fieldvalue = DBConnection.GetSpoolStatus("发放完成");
break;
case "安装完成":
fieldvalue = DBConnection.GetSpoolStatus("安装完成");
break;
default:
break;
}
}
else
{
fieldvalue = null;
}
}
else
{
fieldvalue = SearchDgv.Rows[i].Cells["value"].Value != null ? SearchDgv.Rows[i].Cells["value"].Value.ToString().ToUpper() : null;
}
if (string.IsNullOrEmpty(fieldtype))
{
typestr = SqlOperator.Like;
}
else
{
typestr = (SqlOperator)Enum.Parse(typeof(SqlOperator), fieldtype);
}
if (!string.IsNullOrEmpty(fieldvalue))
{
searchObj.AddCondition(fieldName, fieldvalue, dtype, typestr, true);
}
}
conditionSql = searchObj.BuildConditionSql();
StringBuilder sb = new StringBuilder();
for (int f = 0; f < this.SearchDgv.Rows.Count; f++)
{
string _selectValue = SearchDgv.Rows[f].Cells[5].EditedFormattedValue.ToString();
if (_selectValue == "True")
{
string FilterStr = SearchDgv.Rows[f].Cells[0].Value.ToString();
if (FilterStr == "FLOWSTATUS")
{
FilterStr = "(SELECT NAME FROM SP_FLOWSTATUS_TAB s WHERE s.ID=FLOWSTATUS)";
}
else if (FilterStr == "REMARK")
{
FilterStr = "replace(REMARK, chr(10), '')";
}
else if (FilterStr == "CONNECTORTYPE")
{
FilterStr = "decode(CONNECTORTYPE,'V','阀门','S','小票','O','其他',' ')";
}
else if (FilterStr == "WELDLENGTH")
{
FilterStr = "(case when WELDLENGTH > 10000 then 0 else round(WELDLENGTH,2) end) ";
}
if (table_name == "SP_WORKSHOPBLANKING_VIEW" || table_name == "SP_WORKSHOPASSEMBLY_VIEW" || table_name == "SP_WORKSHOPWELD_VIEW" || table_name == "SP_WORKSHOTOQC_VIEW" || table_name == "SP_WORKSHOPTOTREATMENT_VIEW" || table_name == "SP_WORKSHOPRECIEVE_VIEW" || table_name == "SP_WORKSHOPDELIVERY_VIEW" || table_name == "SP_WORKSHOPTRAYNOCLASS_VIEW" || table_name == "SP_NORMALPIPEWORKINGHOUR_VIEW")
{
sb.AppendFormat("{0},", FilterStr);
}
else
{
string NameStr = SearchDgv.Rows[f].Cells[1].Value.ToString();
sb.AppendFormat("{0} {1},", FilterStr, NameStr);
}
}
}
if (sb.Length<1)
{
MessageBox.Show("请选择要输出的字段！");
return;
}
sb.Remove(sb.Length-1,1);
this.Close();
if (MDIForm.pMainWin.ActiveMdiChild.Text.ToString() == "加工小票概览")
{
connector = " and ";
wheresql0 = string.Format("{0}{1}flowstatus in ({2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12})", conditionSql, connector, (int)FlowState.审核通过, (int)FlowState.下料完成, (int)FlowState.装配完成, (int)FlowState.焊接完成, (int)FlowState.待验, (int)FlowState.检验通过, (int)FlowState.检验不通过, (int)FlowState.处理完成, (int)FlowState.接收完成, (int)FlowState.发放完成, (int)FlowState.安装完成);
}
else
{
connector = " " + "and" + " " + " flag = 'Y'" + " " + "and" + " ";
wheresql0 = string.Format("{0}{1}flowstatus in ({2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15})", conditionSql, connector, (int)FlowState.初始, (int)FlowState.审核中, (int)FlowState.审核通过, (int)FlowState.审核退回, (int)FlowState.下料完成, (int)FlowState.装配完成, (int)FlowState.焊接完成, (int)FlowState.待验, (int)FlowState.检验通过, (int)FlowState.检验不通过, (int)FlowState.处理完成, (int)FlowState.接收完成, (int)FlowState.发放完成, (int)FlowState.安装完成);
}
string con = " " + "and" + " " + " flag = 'Y'";
string constr = " and " + "(materialname like '%主管%' OR materialname like '%支管%')" + " and " + " flag = 'Y'";
string wheresql6 = string.Format("{0}", conditionSql);
string wheresql8 = string.Format("{0}{1}", conditionSql,con);
string wheresql9 = string.Format("{0}{1}",conditionSql,constr);
int count = 0;
sqlstr = "SELECT " + sb + " FROM " + table_name;
if (MDIForm.pMainWin.ActiveMdiChild.Text == "设计小票概览")
{
User.DataBaseConnect(sqlstr + wheresql0 + " order by spoolname", dset);
ToolStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "加工小票概览")
{
User.DataBaseConnect(sqlstr + wheresql0 +" order by spoolname", dset);
ToolStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "阀门信息概览")
{
User.DataBaseConnect(sqlstr + wheresql6, dset);
ToolStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "质检小票概览")
{
User.DataBaseConnect(sqlstr + wheresql0 + " order by spoolname", dset);
ToolStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "管路统计信息")
{
User.DataBaseConnect(sqlstr + wheresql6 + " order by spoolname", dset);
try
{
((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).DataSource = dset.Tables[0].DefaultView;
((ContextMenuStrip)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"].ContextMenuStrip)).Enabled = true;
User.GetPipeMaterialString(wheresql6);
dset.Dispose();
count = ((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).Rows.Count;
((ToolStrip)(MDIForm.pMainWin.ActiveMdiChild.Controls["statusStrip1"])).Items["toolStripStatusLabel1"].Text = string.Format(" 当前总记录数：{0}个", count);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return;
}
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "管路附件统计信息")
{
User.DataBaseConnect(sqlstr + wheresql6 + " order by spoolname", dset);
try
{
((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).DataSource = dset.Tables[0].DefaultView;
((ContextMenuStrip)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"].ContextMenuStrip)).Enabled = true;
User.GetPipePartString(wheresql6);
dset.Dispose();
count = ((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).Rows.Count;
((ToolStrip)(MDIForm.pMainWin.ActiveMdiChild.Controls["statusStrip1"])).Items["toolStripStatusLabel1"].Text = string.Format(" 当前总记录数：{0}个", count);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return;
}
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "材料信息概览")
{
User.DataBaseConnect(sqlstr + wheresql6 + " order by spoolname", dset);
try
{
((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).DataSource = dset.Tables[0].DefaultView;
((ContextMenuStrip)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"].ContextMenuStrip)).Enabled = true;
dset.Dispose();
count = ((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).Rows.Count;
((ToolStrip)(MDIForm.pMainWin.ActiveMdiChild.Controls["statusStrip1"])).Items["toolStripStatusLabel1"].Text = string.Format(" 当前总记录数：{0}个", count);
if (UserSecurity.HavingPrivilege(User.cur_user, "SPOOLMACHINEUSERS"))
{
((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).EditMode = DataGridViewEditMode.EditOnEnter;
foreach (DataGridViewColumn dgvc in ((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).Columns)
{
if (dgvc.Name != "炉批号" && dgvc.Name != "证书号" && dgvc.Name != "焊接工艺号")
{
dgvc.ReadOnly = true;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return;
}
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "连接件信息概览")
{
User.DataBaseConnect(sqlstr + wheresql6 + " order by spoolname", dset);
ToolStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "加工信息概览")
{
sqlstr = "SELECT " + sb + " FROM " + table_name;
User.DataBaseConnect(sqlstr + wheresql8 + " order by spoolname", dset);
ToolStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "焊接信息概览")
{
User.DataBaseConnect(sqlstr + wheresql6 + " order by spoolname", dset);
try
{
((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).DataSource = dset.Tables[0].DefaultView;
((ContextMenuStrip)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"].ContextMenuStrip)).Enabled = true;
dset.Dispose();
count = ((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).Rows.Count;
((ToolStrip)(MDIForm.pMainWin.ActiveMdiChild.Controls["statusStrip1"])).Items["toolStripStatusLabel1"].Text = string.Format(" 当前总记录数：{0}个", count);
if (UserSecurity.HavingPrivilege(User.cur_user, "SPOOLMACHINEUSERS"))
{
((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).EditMode = DataGridViewEditMode.EditOnEnter;
foreach (DataGridViewColumn dgvc in ((DataGridView)(MDIForm.pMainWin.ActiveMdiChild.Controls["OverViewdgv"])).Columns)
{
if (dgvc.Name != "焊接人")
{
dgvc.ReadOnly = true;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return;
}
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "已发图纸材料定额表")
{
User.DataBaseConnect(sqlstr + wheresql8, dset);
ToolStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "下料工时定额")
{
User.DataBaseConnect(sqlstr + wheresql6 + " ORDER BY SPOOLNAME", dset);
GongShiStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "装配工时定额")
{
User.DataBaseConnect(sqlstr + wheresql8 + " order by spoolname", dset);
GongShiStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "焊接工时定额")
{
User.DataBaseConnect(sqlstr + wheresql8 + " order by spoolname", dset);
GongShiStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "料场工时定额")
{
User.DataBaseConnect(sqlstr + wheresql8 + " order by spoolname", dset);
GongShiStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "报验工时定额")
{
User.DataBaseConnect(sqlstr + wheresql8 + " order by spoolname", dset);
GongShiStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "压力试验工时定额")
{
User.DataBaseConnect(sqlstr + wheresql8 + " order by spoolname", dset);
GongShiStatusShow();
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "普通碳钢管(铜管)及普通不锈钢管总工时概览")
{
User.DataBaseConnect(sqlstr + wheresql6 + " order by spoolname", dset);
try
{
DataGridView dgv = (DataGridView)((MDIForm.pMainWin.ActiveMdiChild.Controls["dataGridView1"]));
dgv.DataSource = dset.Tables[0].DefaultView;
dset.Dispose();
count = dgv.Rows.Count;
((ToolStrip)(MDIForm.pMainWin.ActiveMdiChild.Controls["statusStrip1"])).Items["toolStripStatusLabel1"].Text = string.Format(" 当前总记录数：{0}个", count);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return;
}
}
#region 车间流程转换
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "下料信息")
{
templatestr = "BlankingCtl";
sqlfinal = sqlstr + wheresql6 + " ORDER BY SPOOLNAME";
WorkShopProcess(sqlfinal, templatestr);
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "装配信息")
{
templatestr = "AssembleCtl";
sqlfinal = sqlstr + wheresql6 + " ORDER BY SPOOLNAME";
WorkShopProcess(sqlfinal, templatestr);
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "焊接信息")
{
templatestr = "WeldCtl";
sqlfinal = sqlstr + wheresql6 + " ORDER BY SPOOLNAME";
WorkShopProcess(sqlfinal, templatestr);
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "报验信息")
{
templatestr = "QCCtl";
sqlfinal = sqlstr + wheresql6 + " ORDER BY SPOOLNAME";
WorkShopProcess(sqlfinal, templatestr);
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "处理信息")
{
templatestr = "TreatmentCtl";
sqlfinal = sqlstr + wheresql6 + " ORDER BY SPOOLNAME";
WorkShopProcess(sqlfinal, templatestr);
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "料场接收信息")
{
templatestr = "RevieveCtl";
sqlfinal = sqlstr + wheresql6 + " ORDER BY SPOOLNAME";
WorkShopProcess(sqlfinal, templatestr);
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "发放信息")
{
templatestr = "DeliveryCtl";
sqlfinal = sqlstr + wheresql6 + " ORDER BY SPOOLNAME";
WorkShopProcess(sqlfinal, templatestr);
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "安装信息")
{
templatestr = "InstallCtl";
sqlfinal = sqlstr + wheresql6 + " ORDER BY SPOOLNAME";
WorkShopProcess(sqlfinal, templatestr);
}
else if (MDIForm.pMainWin.ActiveMdiChild.Text == "托盘及分类信息")
{
templatestr = "TrayClassCtl";
sqlfinal = sqlstr + wheresql6 + " ORDER BY SPOOLNAME";
WorkShopProcess(sqlfinal, templatestr);
}
#endregion
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="parameterOne">参数一</param>
/// <param name="parameterTwo">参数二</param>
/// <param name="op">条件运算符</param>
internal SqlBasicParameterCondition(SqlParameter parameterOne, SqlParameter parameterTwo, SqlOperator op)
{
this._parameterOne = parameterOne;
this._parameterTwo = parameterTwo;
this._operator = op;
}
public Condition Or(Column column, SqlOperator op, object value = null)
{
Condition otherCondition = new Condition(column, op, value);
return(Append(Conditional.Or, otherCondition));
}
/// <summary>
/// 创建单参数新的Sql条件语句
/// </summary>
/// <param name="cmd">Sql语句</param>
/// <param name="columnName">字段名</param>
/// <param name="op">条件运算符</param>
/// <param name="action">赋值操作</param>
/// <returns>Sql条件语句</returns>
internal static SqlBasicParameterCondition InternalCreateAction(AbstractSqlCommandWithWhere cmd, String columnName, SqlOperator op, String action)
{
return new SqlBasicParameterCondition(cmd, cmd.CreateDataParameterCustomAction(columnName, action), op);
}
/// <summary>
/// Adds a NOT condition to the delete statement.
/// </summary>
/// <param name="delete">The delete statement.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="op">The operator.</param>
/// <param name="value">The value.</param>
/// <returns>The delete statement.</returns>
public static DeleteStatement WhereNot(this DeleteStatement delete, string columnName, SqlOperator op, object value)
{
delete.Conditions.Add(new Condition(columnName, op, value)
{
Not = true
});
return(delete);
}
/// <summary>
/// To create where term condition with default value TableName = string.Empty, ParamDataType = EnumParamterDataTypes.Character
/// </summary>
/// <param name="value">Fill string value of where term</param>
/// <param name="column">Fill string value of column name</param>
/// <param name="sqlOperator">Fill sql operator</param>
/// <returns>WhereTerm</returns>
public static WhereTerm Parameter(string value, string column, SqlOperator sqlOperator)
{
return new WhereTerm
{
Value = value,
TableName = String.Empty,
ParamDataType = EnumParamterDataTypes.Character,
Operator = sqlOperator,
ColumnName = column
};
}
/// <summary>
/// Adds an AND condition to the delete statement.
/// </summary>
/// <param name="delete">The delete statement.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="op">The operator.</param>
/// <param name="value">The value.</param>
/// <returns>The delete statement.</returns>
public static DeleteStatement And(this DeleteStatement delete, string columnName, SqlOperator op, object value)
{
delete.Conditions.Add(new Condition(columnName, op, value)
{
Relationship = ConditionRelationship.And
});
return(delete);
}
/// <summary>
/// Ϊ��ѯ�������
/// <example>
/// �÷�һ��
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false);
/// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like, true);
/// string conditionSql = searchObj.BuildConditionSql();
///
/// �÷�����AddCondition�������Դ�������Ӷ������
/// SearchCondition searchObj = new SearchCondition();
/// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false).AddCondition("Test2", "Test2Value", SqlOperator.Like, true);
/// string conditionSql = searchObj.BuildConditionSql();
/// </example>
/// </summary>
/// <param name="fielName">�ֶ�����</param>
/// <param name="fieldValue">�ֶ�ֵ</param>
/// <param name="sqlOperator">SqlOperatorö������</param>
/// <param name="excludeIfEmpty">����ֶ�Ϊ�ջ���Null����Ϊ��ѯ����</param>
/// <returns></returns>
public SearchCondition AddCondition(string fielName, object fieldValue, string datatype,SqlOperator sqlOperator, bool excludeIfEmpty)
{
this.conditionTable.Add(fielName, new SearchInfo(fielName, fieldValue,datatype,sqlOperator, excludeIfEmpty));
return this;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段的值</param>
/// <param name="sqlOperator">字段的Sql操作符号</param>
public SearchEntity(string fieldName, object fieldValue, SqlOperator sqlOperator)
: this(fieldName, fieldValue, sqlOperator, true)
{
}
public ConditionOnField(SqlOperator sqlOperator) : base(sqlOperator)
{
}
/// <summary>
/// 添加条件
/// </summary>
/// <typeparam name="T">字段值类型</typeparam>
/// <param name="relationType">关联运算符</param>
/// <param name="fieldName">字段名</param>
/// <param name="operator">操作符</param>
/// <param name="fieldValue">字段值，注：1、不可谓数组;2、Between时，此字段必须填两个值</param>
/// <returns></returns>
public virtual IConditionBuilder Append <T>(RelationType relationType, string fieldName, SqlOperator @operator,
params T[] fieldValue)
{
if (IsContinue(fieldValue))
{
return(this);
}
if (!this.ConditionAppendBuilder.ToString().Trim()
.EndsWith("WHERE", StringComparison.InvariantCultureIgnoreCase))
{
this.ConditionAppendBuilder.Append(GetRelation(relationType));
}
if (IsContinue(fieldValue))
{
return(this);
}
switch (@operator)
{
case SqlOperator.Equal:
case SqlOperator.NotEqual:
case SqlOperator.GreaterThan:
case SqlOperator.GreaterEqual:
case SqlOperator.LessThan:
case SqlOperator.LessEqual:
this.ConditionAppendBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName),
GetOperator(@operator), GetFieldValue(fieldValue[0]));
break;
case SqlOperator.IsNull:
case SqlOperator.IsNotNull:
this.ConditionAppendBuilder.AppendFormat("{0}{1}", GetFieldName(fieldName), GetOperator(@operator));
break;
case SqlOperator.Contains:
case SqlOperator.NotContains:
this.ConditionAppendBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName),
GetOperator(@operator), GetFieldValue(string.Format("%{0}%", fieldValue[0])));
break;
case SqlOperator.In:
case SqlOperator.NotIn:
this.ConditionAppendBuilder.AppendFormat("{0}{1}({2})", GetFieldName(fieldName),
GetOperator(@operator), GetArrayFieldValue(fieldValue));
break;
case SqlOperator.Starts:
this.ConditionAppendBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName),
GetOperator(@operator), GetFieldValue(string.Format("{0}%", fieldValue[0])));
break;
case SqlOperator.Ends:
this.ConditionAppendBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName),
GetOperator(@operator), GetFieldValue(string.Format("%{0}", fieldValue[0])));
break;
case SqlOperator.Between:
this.ConditionAppendBuilder.AppendFormat("{0}{1}{2} AND {3}", GetFieldName(fieldName),
GetOperator(@operator), GetFieldValue(fieldValue[0]), GetFieldValue(fieldValue[1]));
break;
default:
throw new SqlBuilderException("条件未定义!");
}
this.Length++;
return(this);
}
public ConditionOnField(Field field, SqlOperator sqlOperator, Field toField) : base(field, sqlOperator)
{
ToField = toField;
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="columnName">字段名称</param>
/// <param name="op">条件运算符</param>
/// <param name="command">选择语句</param>
internal SqlBasicCommandCondition(String columnName, SqlOperator op, SelectCommand command)
{
this._columnName = columnName;
this._operator = op;
this._command = command;
}
