/// <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));
        }
예제 #2
0
        /// <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);
        }
예제 #3
0
        /// <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);
        }
예제 #4
0
        /// <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);
        }
예제 #5
0
        /// <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());
            }
        }
예제 #6
0
        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));
        }
예제 #16
0
        /// <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);
        }