/// <summary>
/// add where condition with list
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="Value"></param>
public void AddWhere(WhereRelation Relation, String ColumnName, CommandComparison Comparison, object[] Value)
{
String Condition;
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (!(Comparison == CommandComparison.In || Comparison == CommandComparison.NotIn))
{
throw new Exception("only support In comparison.");
}
Condition = "(";
foreach (object value in Value)
{
if (CommonService.CheckValueType(value))
{
Condition = Condition + "'" + value + "',";
}
else
{
Condition = Condition + value + ",";
}
}
Condition += ")";
Condition = Condition.Replace(",)", ")");
condition.Add(String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), Condition));
}
/// <summary>
/// delete data by key value
/// 删除指定条件数据
/// </summary>
/// <param name="KeyColumnName"></param>
/// <param name="KeyValue"></param>
/// <returns></returns>
public MongoDeleteData Where(string KeyColumnName, CommandComparison comparison,
object KeyValue)
{
filterDefinition = MongoDBOP.GetFilterOP(KeyColumnName, KeyValue, comparison);
return(this);
}
/// <summary>
/// get data by filter
/// 设定表中指定条件
/// </summary>
/// <param name="MatchColumn"></param>
/// <param name="Comparison"></param>
/// <param name="MatchValue"></param>
/// <returns></returns>
public DatabaseGetData Where(string MatchColumn,
CommandComparison Comparison, object[] MatchValue)
{
sql.AddWhere(WhereRelation.And, MatchColumn, Comparison, MatchValue);
return(this);
}
/// <summary>
/// get data by filter
/// 得到指定条件的表中所有数据
/// </summary>
/// <param name="ColumnName">筛选字段名 Filed</param>
/// <param name="Value">筛选值 Filter value</param>
/// <param name="Comparison">筛选条件 Filter where</param>
/// <returns></returns>
public MongoGetData Where(String ColumnName, Object Value,
CommandComparison Comparison)
{
filterDefinition = MongoDBOP.GetFilterOP(ColumnName, Value, Comparison);
return(this);
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="ColumnName"></param>
/// <param name="Value"></param>
/// <param name="comparison"></param>
/// <returns></returns>
public static FilterDefinition <BsonDocument> GetFilterOP(String ColumnName, Object Value,
CommandComparison comparison)
{
switch ((int)comparison)
{
case 1:
return(Builders <BsonDocument> .Filter.Eq(ColumnName, GetValueType(Value)));
case 2:
return(Builders <BsonDocument> .Filter.Ne(ColumnName, GetValueType(Value)));
case 5:
return(Builders <BsonDocument> .Filter.Gt(ColumnName, GetValueType(Value)));
case 6:
return(Builders <BsonDocument> .Filter.Gte(ColumnName, GetValueType(Value)));
case 7:
return(Builders <BsonDocument> .Filter.Lt(ColumnName, GetValueType(Value)));
case 8:
return(Builders <BsonDocument> .Filter.Lte(ColumnName, GetValueType(Value)));
case 13:
return(Builders <BsonDocument> .Filter.Eq(ColumnName, new ObjectId(Value.ToString())));
default:
return(new BsonDocument());
}
}
public static String ConvertComparison(CommandComparison Comparison)
{
switch (Comparison)
{
case CommandComparison.Equals:
return("=");
case CommandComparison.NotEquals:
return("<>");
case CommandComparison.Like:
return("like");
case CommandComparison.NotLike:
return("not like");
case CommandComparison.GreaterThan:
return(">");
case CommandComparison.GreaterOrEquals:
return(">=");
case CommandComparison.LessThan:
return("<");
case CommandComparison.LessOrEquals:
return("<=");
case CommandComparison.In:
return("in");
case CommandComparison.NotIn:
return("not in");
case CommandComparison.Between:
return("between");
case CommandComparison.NotBetween:
return("not between");
case CommandComparison.Is:
return("is");
case CommandComparison.IsNot:
return("is not");
case CommandComparison.Or:
return("or");
case CommandComparison.And:
return("and");
default:
throw new Exception("not found comparison.");
}
}
/// <summary>
/// add where condition with select query
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="SelectCommand"></param>
public void AddWhereBySelect(WhereRelation Relation, String ColumnName, CommandComparison Comparison, String SelectCommand)
{
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("ColumnName is null.");
}
condition.Add(String.Format(" {0} {1} {2} ({3})",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), SelectCommand));
}
/// <summary>
/// add Having condition
/// </summary>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="value"></param>
public void AddHaving(String ColumnName, CommandComparison Comparison, String SelectCommand)
{
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (groupcondition.Count == 0)
{
throw new Exception("Need set group by condition first.");
}
havingcondition.Add(String.Format("{0} {1} ({2})",
ColumnName, CommonService.ConvertComparison(Comparison), SelectCommand));
}
/// <summary>
/// add where condition
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="value"></param>
public void AddWhere(WhereRelation Relation, String ColumnName, CommandComparison Comparison, object value)
{
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (CommonService.CheckValueType(value))
{
condition.Add(String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), "'" + value + "'"));
}
else
{
condition.Add(String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), value));
}
}
/// <summary>
/// add Having condition
/// </summary>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="value"></param>
public void AddHaving(String ColumnName, CommandComparison Comparison, object value1, object value2)
{
String Condition;
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (groupcondition.Count == 0)
{
throw new Exception("Need set group by condition first.");
}
if (!(Comparison == CommandComparison.Between || Comparison == CommandComparison.NotBetween))
{
throw new Exception("only support Between comparison.");
}
if (CommonService.CheckValueType(value1))
{
Condition = String.Format("{0} {1} {2}",
ColumnName, CommonService.ConvertComparison(Comparison), "'" + value1 + "'");
}
else
{
Condition = String.Format("{0} {1} {2}",
ColumnName, CommonService.ConvertComparison(Comparison), value1);
}
if (CommonService.CheckValueType(value2))
{
Condition += "and '" + value2 + "'";
}
else
{
Condition += "and " + value2;
}
havingcondition.Add(Condition);
}
/// <summary>
/// add Having condition
/// </summary>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="value"></param>
public void AddHaving(String ColumnName, CommandComparison Comparison, object value)
{
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (groupcondition.Count == 0)
{
throw new Exception("Need set group by condition first.");
}
if (CommonService.CheckValueType(value))
{
havingcondition.Add(String.Format("{0} {1} {2}",
ColumnName, CommonService.ConvertComparison(Comparison), "'" + value + "'"));
}
else
{
havingcondition.Add(String.Format("{0} {1} {2}",
ColumnName, CommonService.ConvertComparison(Comparison), value));
}
}
/// <summary>
/// add Having condition
/// </summary>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="value"></param>
public void AddHaving(String ColumnName, CommandComparison Comparison, object[] Value)
{
String Condition;
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (groupcondition.Count == 0)
{
throw new Exception("Need set group by condition first.");
}
if (!(Comparison == CommandComparison.In || Comparison == CommandComparison.NotIn))
{
throw new Exception("only support In comparison.");
}
Condition = "(";
foreach (object value in Value)
{
if (CommonService.CheckValueType(value))
{
Condition = Condition + "'" + value + "',";
}
else
{
Condition = Condition + value + ",";
}
}
Condition += ")";
Condition = Condition.Replace(",)", ")");
havingcondition.Add(String.Format("{0} {1} {2}",
ColumnName, CommonService.ConvertComparison(Comparison), Condition));
}
/// <summary>
/// add where condition with between
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="value1"></param>
/// <param name="value2"></param>
public void AddWhere(WhereRelation Relation, String ColumnName, CommandComparison Comparison, object value1, object value2)
{
String Condition;
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (!(Comparison == CommandComparison.Between || Comparison == CommandComparison.NotBetween))
{
throw new Exception("only support Between comparison.");
}
if (CommonService.CheckValueType(value1))
{
Condition = String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), "'" + value1 + "'");
}
else
{
Condition = String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), value1);
}
if (CommonService.CheckValueType(value2))
{
Condition += " and '" + value2 + "'";
}
else
{
Condition += " and " + value2;
}
condition.Add(Condition);
}
/// <summary>
/// add Join condition
/// </summary>
/// <param name="type"></param>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="FromTableName"></param>
/// <param name="FromColumnName"></param>
public void AddJoin(SelectJoinType type, String TableName, String ColumnName, CommandComparison Comparison, String FromTableName, String FromColumnName)
{
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
joincondition.Add(String.Format(" {0} {1} ON {2}.{3} {4} {5}.{6}", CommonService.ConvertJoinComparison(type), TableName, FromTableName, FromColumnName
, CommonService.ConvertComparison(Comparison), TableName, ColumnName));
}
/// <summary>
/// add where condition with RelationShip
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="ColumnName"></param>
public void AddWhereByRelationShip(WhereRelation Relation, String LeftColumnName, CommandComparison Comparison, String RightColumnName)
{
if (String.IsNullOrEmpty(LeftColumnName) || String.IsNullOrEmpty(RightColumnName))
{
throw new Exception("Cloumn name is null.");
}
condition.Add(String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), LeftColumnName, CommonService.ConvertComparison(Comparison), RightColumnName));
}
/// <summary>
/// get data by filter
/// 设定表中指定条件
/// </summary>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="Value"></param>
/// <returns></returns>
public DatabaseGetData Where(string ColumnName, CommandComparison Comparison, object Value)
{
sql.AddWhere(WhereRelation.And, ColumnName, (CommandComparison)Comparison, Value);
return(this);
}