/// <summary> /// Order 的设置 /// </summary> /// <param name="CurrentDataFilter"></param> /// <param name="SqlContent"></param> private static void SetQueryOrder(DataFilter CurrentDataFilter, String SqlContent) { //如果获得了内容,应该是这个样子的 By A ASC,B DES //1.删除By By A ASC,B DES -> A Asc,B Des SqlContent = SqlContent.Substring(3); //2.通过逗号分隔列表 //A Asc , B Des -> A Asc // B Des String[] SortFieldLst = SqlContent.Split(",".ToCharArray()); //3.分出 Field 和 Order foreach (String SortField in SortFieldLst) { String[] Sortfld = SortField.Trim().Split(" ".ToCharArray()); for (int i = 0; i < CurrentDataFilter.QueryFieldList.Count; i++) { if (CurrentDataFilter.QueryFieldList[i].ColName.ToLower() == Sortfld[0].ToLower()) { //无参数时候,默认是升序[Can't Modify]QueryFieldList是一个结构体 DataFilter.QueryFieldItem queryfld = CurrentDataFilter.QueryFieldList[i]; if (Sortfld.Length == 1) { queryfld.sortType = DataFilter.SortType.Ascending; } else { queryfld.sortType = Sortfld[1].ToLower().StartsWith("d") ? DataFilter.SortType.Descending : DataFilter.SortType.Ascending; } CurrentDataFilter.QueryFieldList[i] = queryfld; break; } } } }
/// <summary> /// Aggregation用的$project和$order /// </summary> /// <returns></returns> public BsonDocument GetAggregation() { var Aggregation = new BsonDocument(); var project = new BsonDocument(); var sort = new BsonDocument(); foreach (DataFilter.QueryFieldItem item in mQueryFieldList) { DataFilter.QueryFieldItem ctl = ((ctlFieldInfo)Controls.Find(item.ColName, true)[0]).QueryFieldItem; if (ctl.ColName == MongoDbHelper.KEY_ID) { if (!ctl.IsShow) { project.Add(new BsonElement(MongoDbHelper.KEY_ID, 0)); } } else { if (ctl.IsShow) { project.Add(string.IsNullOrEmpty(ctl.ProjectName) ? new BsonElement(ctl.ColName, 1) : new BsonElement(ctl.ProjectName, "$" + ctl.ColName)); } } switch (ctl.sortType) { case DataFilter.SortType.NoSort: break; case DataFilter.SortType.Ascending: sort.Add(new BsonElement(ctl.ColName, 1)); break; case DataFilter.SortType.Descending: sort.Add(new BsonElement(ctl.ColName, -1)); break; default: break; } } //Note The $sort cannot begin sorting documents until previous operators in the pipeline have returned all output. //如果先$project,再$sort的话,全字段输出 if (sort.ElementCount > 0) { Aggregation.Add(new BsonElement("$sort", sort)); } Aggregation.Add(new BsonElement("$project", project)); return(Aggregation); }
/// <summary> /// 获取Group的ID /// </summary> /// <returns></returns> public BsonDocument GetAggregationGroup() { var Aggregation = new BsonDocument(); var project = new BsonDocument(); foreach (DataFilter.QueryFieldItem item in mQueryFieldList) { DataFilter.QueryFieldItem ctl = ((ctlFieldInfo)Controls.Find(item.ColName, true)[0]).QueryFieldItem; if (ctl.IsShow) { project.Add(string.IsNullOrEmpty(ctl.ProjectName) ? new BsonElement(ctl.ColName, ctl.ColName) : new BsonElement(ctl.ProjectName, "$" + ctl.ColName)); } } Aggregation.Add("_id", project); return(Aggregation); }
/// <summary> /// GroupID /// </summary> /// <returns></returns> internal BsonDocument getGroupID() { // { _id : { author: '$author', pageViews: '$pageViews', posted: '$posted' } } var id = new BsonDocument(); var member = new BsonDocument(); foreach (DataFilter.QueryFieldItem item in mQueryFieldList) { DataFilter.QueryFieldItem ctl = ((ctlFieldInfo)Controls.Find(item.ColName, true)[0]).QueryFieldItem; if (ctl.IsShow && ctl.ColName != "_id") { member.Add(string.IsNullOrEmpty(ctl.ProjectName) ? new BsonElement(ctl.ColName, "$" + ctl.ColName) : new BsonElement(ctl.ProjectName, "$" + ctl.ColName)); } } id.Add("_id", member); return(id); }
/// <summary> /// </summary> /// <param name="mIsShow"></param> public void InitByCurrentCollection(bool mIsShow) { List <String> ColumnList = MongoDbHelper.GetCollectionSchame(SystemManager.GetCurrentCollection()); var FieldList = new List <DataFilter.QueryFieldItem>(); foreach (String item in ColumnList) { //输出配置的初始化 var queryFieldItem = new DataFilter.QueryFieldItem(); queryFieldItem.ColName = item; queryFieldItem.IsShow = mIsShow; queryFieldItem.sortType = DataFilter.SortType.NoSort; if (queryFieldItem.ColName == MongoDbHelper.KEY_ID) { queryFieldItem.IsShow = true; } FieldList.Add(queryFieldItem); } mQueryFieldList = FieldList; SetFieldList(); }
/// <summary> /// </summary> /// <param name="mIsShow"></param> public void InitByCurrentCollection(bool mIsShow) { var columnList = MongoHelper.GetCollectionSchame(RuntimeMongoDbContext.GetCurrentCollection()); var fieldList = new List <DataFilter.QueryFieldItem>(); foreach (var item in columnList) { //输出配置的初始化 var queryFieldItem = new DataFilter.QueryFieldItem(); queryFieldItem.ColName = item; queryFieldItem.IsShow = mIsShow; queryFieldItem.SortType = DataFilter.SortType.NoSort; if (queryFieldItem.ColName == ConstMgr.KeyId) { queryFieldItem.IsShow = true; } fieldList.Add(queryFieldItem); } _mQueryFieldList = fieldList; SetFieldList(); }
/// <summary> /// 输出配置字典 /// </summary> private void frmQuery_Load(object sender, EventArgs e) { ColumnList = MongoDBHelper.GetCollectionSchame(_mongoCol); foreach (String item in ColumnList) { //输出配置的初始化 DataFilter.QueryFieldItem queryFieldItem = new DataFilter.QueryFieldItem(); queryFieldItem.ColName = item; if (!CurrentDataViewInfo.IsUseFilter) { queryFieldItem.IsShow = true; queryFieldItem.sortType = DataFilter.SortType.NoSort; } else { DataFilter.QueryFieldItem find = CurrentDataViewInfo.mDataFilter.QueryFieldList.Find ( (x) => { return(x.ColName == item); } ); if (find.ColName != String.Empty) { queryFieldItem.IsShow = find.IsShow; queryFieldItem.sortType = find.sortType; } } if (queryFieldItem.ColName == MongoDBHelper.KEY_ID) { queryFieldItem.IsShow = true; } //动态加载控件 ctlFieldInfo ctrItem = new ctlFieldInfo(); ctrItem.Name = item; ctrItem.Location = _conditionPos; ctrItem.QueryFieldItem = queryFieldItem; tabFieldInfo.Controls.Add(ctrItem); //纵向位置的累加 _conditionPos.Y += ctrItem.Height; } _conditionPos = new Point(5, 20); ctlQueryCondition firstQueryCtl = new ctlQueryCondition(); firstQueryCtl.Init(ColumnList); firstQueryCtl.Location = _conditionPos; firstQueryCtl.Name = "Condition" + _conditionCount.ToString(); panFilter.Controls.Add(firstQueryCtl); if (CurrentDataViewInfo.mDataFilter.QueryConditionList.Count > 0) { PutQueryToUI(CurrentDataViewInfo.mDataFilter); } if (!SystemManager.IsUseDefaultLanguage) { this.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_Title); tabFieldInfo.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_FieldInfo); tabCondition.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_Filter); cmdAddCondition.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_Filter_AddCondition); cmdLoad.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_Action_Load); cmdSave.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Common_Save); cmdOK.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Common_OK); cmdCancel.Text = SystemManager.mStringResource.GetText(StringResource.TextType.Common_Cancel); } }
private void PutQueryToUI(DataFilter NewDataFilter) { String strErrMsg = String.Empty; List <String> ShowColumnList = new List <String>(); foreach (String item in ColumnList) { ShowColumnList.Add(item); } //清除所有的控件 tabFieldInfo.Controls.Clear(); foreach (DataFilter.QueryFieldItem queryFieldItem in NewDataFilter.QueryFieldList) { //动态加载控件 if (!ColumnList.Contains(queryFieldItem.ColName)) { strErrMsg += queryFieldItem.ColName + "Display Field is not exist in current collection any more" + System.Environment.NewLine; } else { ctlFieldInfo ctrItem = new ctlFieldInfo(); ctrItem.Name = queryFieldItem.ColName; ctrItem.Location = _conditionPos; ctrItem.QueryFieldItem = queryFieldItem; tabFieldInfo.Controls.Add(ctrItem); //纵向位置的累加 _conditionPos.Y += ctrItem.Height; ShowColumnList.Remove(queryFieldItem.ColName); } } //新增字段 _conditionPos = new Point(5, 0); foreach (String item in ShowColumnList) { strErrMsg += "New Field" + item + "Is Append" + System.Environment.NewLine; //输出配置的初始化 DataFilter.QueryFieldItem queryFieldItem = new DataFilter.QueryFieldItem(); queryFieldItem.ColName = item; queryFieldItem.IsShow = true; queryFieldItem.sortType = DataFilter.SortType.NoSort; //动态加载控件 ctlFieldInfo ctrItem = new ctlFieldInfo(); ctrItem.Name = item; _conditionPos.Y += ctrItem.Height; ctrItem.Location = _conditionPos; ctrItem.QueryFieldItem = queryFieldItem; tabFieldInfo.Controls.Add(ctrItem); } panFilter.Controls.Clear(); _conditionPos = new Point(5, 0); _conditionCount = 0; foreach (DataFilter.QueryConditionInputItem queryConditionItem in NewDataFilter.QueryConditionList) { ctlQueryCondition newCondition = new ctlQueryCondition(); newCondition.Init(ColumnList); _conditionPos.Y += newCondition.Height; newCondition.Location = _conditionPos; newCondition.ConditionItem = queryConditionItem; _conditionCount++; newCondition.Name = "Condition" + _conditionCount.ToString(); panFilter.Controls.Add(newCondition); if (!ColumnList.Contains(queryConditionItem.ColName)) { strErrMsg += queryConditionItem.ColName + "Query Condition Field is not exist in collection any more" + System.Environment.NewLine; } } if (strErrMsg != String.Empty) { MyMessageBox.ShowMessage("Load Exception", "A Exception is happened when loading", strErrMsg, true); } }
//http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart(旧网址) //http://docs.mongodb.org/manual/reference/sql-comparison/ /// <summary> /// Convert Query Sql To DataFilter /// </summary> /// <param name="Sql"></param> /// <returns></returns> public static DataFilter ConvertQuerySql(String Sql) { var rtnQuery = new DataFilter(); Sql = Sql.Trim(); //引号中的空格用 代替,引号以外的东西小写 Sql = Regular(Sql); //先将字符串里面的空格统一成单个空格 //Select A,B From C -> //Select A,B From C while (Sql.Contains(" ")) { Sql = Sql.Replace(" ", " "); } //找出Select ,From , Group String[] SqlToken = Sql.Split(" ".ToCharArray()); int SelectStartIndex = -1; int FromStartIndex = -1; int WhereStartIndex = -1; int GroupByStartIndex = -1; int OrderByStartIndex = -1; for (int i = 0; i < SqlToken.Length; i++) { switch (SqlToken[i].ToLower()) { case "select": SelectStartIndex = i; break; case "from": FromStartIndex = i; break; case "where": WhereStartIndex = i; break; case "group": GroupByStartIndex = i; break; case "order": OrderByStartIndex = i; break; default: break; } } String[] KeyWords = { "select", "from", "where", "group", "order" }; //From - > CollectionName GetKeyContent(FromStartIndex, SqlToken, KeyWords); MongoCollection mongoCol; //if ((strFrom != String.Empty) & SystemManager.GetCurrentDataBase().CollectionExists(strFrom)) //{ // mongoCol = SystemManager.GetCurrentDataBase().GetCollection(strFrom); //} //else //{ // return null; //} //From:由于当前肯定是从固定表里面取数据 mongoCol = SystemManager.GetCurrentCollection(); //Select 设定 必须项 //Select - > FieldList String strSelect = GetKeyContent(SelectStartIndex, SqlToken, KeyWords); if (strSelect == String.Empty) { return(null); } List <String> ColumnNameLst = MongoDbHelper.GetCollectionSchame(mongoCol); if (strSelect == "*") { //Select * foreach (String item in ColumnNameLst) { var field = new DataFilter.QueryFieldItem(); field.ColName = item; field.IsShow = true; field.sortType = DataFilter.SortType.NoSort; rtnQuery.QueryFieldList.Add(field); } } else { //Select A,B,C foreach (String item in strSelect.Split(",".ToCharArray())) { var field = new DataFilter.QueryFieldItem(); field.ColName = item; field.IsShow = true; field.sortType = DataFilter.SortType.NoSort; rtnQuery.QueryFieldList.Add(field); } } //Where 设定,可选项 String strWhere = GetKeyContent(WhereStartIndex, SqlToken, KeyWords); if (strWhere != String.Empty) { rtnQuery.QueryConditionList = SetQueryCondition(strWhere, ColumnNameLst); } //Order 设定,可选项 String strOrder = GetKeyContent(OrderByStartIndex, SqlToken, KeyWords); if (strOrder != String.Empty) { SetQueryOrder(rtnQuery, strOrder); } //Group 设定,可选项 String strGroup = GetKeyContent(GroupByStartIndex, SqlToken, KeyWords); if (strGroup != String.Empty) { //TODO:Group } return(rtnQuery); }