/// <summary> /// 生成连接表条件字符串 /// </summary> /// <returns></returns> public static string GetJoinConditionString(IEnumerable<NeedField> needFields, JoinCriterion joinCriterion) { StringBuilder result = new StringBuilder(); IDictionary<int, string> tableName = new Dictionary<int, string>(); int index = 0; foreach (var item in needFields) //去除重复的数据库表名 { if (!tableName.Values.Contains(item.TableName)) { tableName.Add(index, item.TableName); index++; } } if (tableName.Keys.Count() == 1) { return " FROM " + tableName[0]; } if (tableName.Keys.Count() > 1) { result.Append(" FROM ").Append(tableName[0]); if (joinCriterion.DefaultJoinFieldName == "" || joinCriterion.DefaultJoinFieldName == null) { if (tableName.Keys.Count() != joinCriterion.JoinFieldDictionary.Keys.Count()) { throw new KeyNotFoundException("连接的表数目和字段数目不一致"); } for (int itemNo = 1; itemNo < index; itemNo++) { result.Append(String.Format(" {0} {1} ON {2}.[{3}]={4}.[{5}]", GetJoinType(joinCriterion), tableName[itemNo], tableName[0], joinCriterion.JoinFieldDictionary[tableName[0]], tableName[itemNo], joinCriterion.JoinFieldDictionary[tableName[itemNo]])); } } else { for (int itemNo = 1; itemNo < index; itemNo++) { if (joinCriterion.DefaultJoinFieldName != "" || joinCriterion.DefaultJoinFieldName != null) result.Append(String.Format(" {0} {1} ON {2}.[{3}]={4}.[{5}]", GetJoinType(joinCriterion), tableName[itemNo], tableName[0], joinCriterion.DefaultJoinFieldName, tableName[itemNo], joinCriterion.DefaultJoinFieldName)); } } } else { throw new Exception("连接字符串生产错误"); } return result.ToString(); }
/// <summary> /// 获得连接方式 /// </summary> /// <param name="joinCriterion"></param> /// <returns></returns> private static string GetJoinType(JoinCriterion joinCriterion) { switch (joinCriterion.JoinType) { case JoinType.JOIN: return("JOIN"); case JoinType.INNER_JOIN: return("INNER JOIN"); case JoinType.LEFT_JOIN: return("LEFT JOIN"); case JoinType.RIGHT_JOIN: return("RIGHT JOIN"); case JoinType.FULL_JOIN: return("FULL JOIN"); default: throw new Exception("没有对应的连接方式"); } }
/// <summary> /// 生成连接表条件字符串 /// </summary> /// <returns></returns> public static string GetJoinConditionString(IEnumerable <NeedField> needFields, JoinCriterion joinCriterion) { StringBuilder result = new StringBuilder(); IDictionary <int, string> tableName = new Dictionary <int, string>(); int index = 0; foreach (var item in needFields) //去除重复的数据库表名 { if (!tableName.Values.Contains(item.TableName)) { tableName.Add(index, item.TableName); index++; } } if (tableName.Keys.Count() == 1) { return(" FROM " + tableName[0]); } if (tableName.Keys.Count() > 1) { result.Append(" FROM ").Append(tableName[0]); if (joinCriterion.DefaultJoinFieldName == "" || joinCriterion.DefaultJoinFieldName == null) { if (tableName.Keys.Count() != joinCriterion.JoinFieldDictionary.Keys.Count()) { throw new KeyNotFoundException("连接的表数目和字段数目不一致"); } for (int itemNo = 1; itemNo < index; itemNo++) { result.Append(String.Format(" {0} {1} ON {2}.[{3}]={4}.[{5}]", GetJoinType(joinCriterion), tableName[itemNo], tableName[0], joinCriterion.JoinFieldDictionary[tableName[0]], tableName[itemNo], joinCriterion.JoinFieldDictionary[tableName[itemNo]])); } } else { for (int itemNo = 1; itemNo < index; itemNo++) { if (joinCriterion.DefaultJoinFieldName != "" || joinCriterion.DefaultJoinFieldName != null) { result.Append(String.Format(" {0} {1} ON {2}.[{3}]={4}.[{5}]", GetJoinType(joinCriterion), tableName[itemNo], tableName[0], joinCriterion.DefaultJoinFieldName, tableName[itemNo], joinCriterion.DefaultJoinFieldName)); } } } } else { throw new Exception("连接字符串生产错误"); } return(result.ToString()); }
/// <summary> /// 返回最近两天的历史数据表 /// </summary> /// <param name="viewName"></param> /// <returns></returns> private DataTable GetHistoryDataTable(string viewName) { bool flag = false; DataTable result = new DataTable(); IList<NeedField> needFields = new List<NeedField>(); IEnumerable<DataSetInformation> dataSetInfor = GetDataSetInformation(viewName); foreach (var item in dataSetInfor) { if (flag == false) { needFields.Add(new NeedField { FieldName = "v_date", TableName = item.TableName, VariableName = "date" }); flag = true; } needFields.Add(new NeedField { FieldName = item.FieldName, TableName = item.TableName, VariableName = item.ViewId }); } JoinCriterion joinCriterion = new JoinCriterion { DefaultJoinFieldName = "v_date", JoinType = JoinType.FULL_JOIN }; ComplexQuery cmpquery = new ComplexQuery(needFields,joinCriterion); //cmpquery.AddCriterion("v_date", DateTime.Now.AddDays(1).Date, CriteriaOperator.LessThan); //cmpquery.AddCriterion("v_date", DateTime.Now.AddDays(-1).Date, CriteriaOperator.MoreThan); result = dataFactory.Query(cmpquery); return result; }
public ComplexQuery(JoinCriterion joinCriterion, IEnumerable<NeedField> needFields, IEnumerable<Criterion> criterions) { _joinCriterion = joinCriterion; _needFields = needFields.ToList(); _criterions = criterions.ToList(); }
public ComplexQuery(IEnumerable<NeedField> needFields, JoinCriterion joinCriterion) { _joinCriterion = joinCriterion; _needFields = needFields.ToList(); _criterions = new List<Criterion>(); }
public void AddJoinCriterion(string defaultJoinFieldName, JoinType joinType) { JoinCriterion join = new JoinCriterion { DefaultJoinFieldName = defaultJoinFieldName, JoinType = joinType }; _joinCriterion = join; }
/// <summary> /// 连接字段字典,键为表名,值为字段名 /// </summary> /// <param name="joinFieldDictionary"></param> /// <param name="joinType"></param> public void AddJoinCriterion(IDictionary<string, string> joinFieldDictionary, JoinType joinType) { JoinCriterion join = new JoinCriterion(); join.JoinFieldDictionary = joinFieldDictionary; join.JoinType = joinType; _joinCriterion = join; }
/// <summary> /// 获得连接方式 /// </summary> /// <param name="joinCriterion"></param> /// <returns></returns> private static string GetJoinType(JoinCriterion joinCriterion) { switch (joinCriterion.JoinType) { case JoinType.JOIN: return "JOIN"; case JoinType.INNER_JOIN: return "INNER JOIN"; case JoinType.LEFT_JOIN: return "LEFT JOIN"; case JoinType.RIGHT_JOIN: return "RIGHT JOIN"; case JoinType.FULL_JOIN: return "FULL JOIN"; default: throw new Exception("没有对应的连接方式"); } }
public AutoCreateView(string connectionString, IEnumerable<NeedField> needFields, JoinCriterion joinCriterion) { _connectionString = connectionString; _needFields = needFields; _joinCriterion = joinCriterion; }