private void CreateQueryFieldWhereSql(ConditionValueGeneratorPara para, Dictionary <string, string> tableAliaNameDic, Dictionary <string, Dictionary <string, DBFieldInfo> > tableFieldInfoDic, ExpressionNodeCollection conditionExpressionNodes, DBFiledValueConverterCollection dbFiledValueConverterCollection) { if (conditionExpressionNodes == null || conditionExpressionNodes.Count == 0) { return; } ExpressionNodeCollection childrenConditionExpressionNodes; DBFieldInfo dbFieldInfo; CompareOperaterAttribute compareOperaterAttribute; int lastIndex = conditionExpressionNodes.Count - 1; for (int i = 0; i < conditionExpressionNodes.Count; i++) { var expressionNode = conditionExpressionNodes.ElementAt(i); childrenConditionExpressionNodes = expressionNode.Children; if (childrenConditionExpressionNodes == null || childrenConditionExpressionNodes.Count == 0) { //无子节点 dbFieldInfo = tableFieldInfoDic[expressionNode.TableName][expressionNode.FieldName]; para.DBFiledValueConverter = dbFiledValueConverterCollection.GetDBFiledValueConverter(dbFieldInfo); para.FieldInfo = dbFieldInfo; if (tableAliaNameDic.ContainsKey(expressionNode.TableName)) { para.TableAliaName = tableAliaNameDic[expressionNode.TableName]; } else { para.TableAliaName = expressionNode.TableName; } para.ValueList = expressionNode.ValueList; para.Operater = expressionNode.Operater; compareOperaterAttribute = CompareOperaterHelper.GetCompareOperaterAttributeByCompareOperater(expressionNode.Operater); compareOperaterAttribute.ConditionValueGenerator.Generate(para); if (i < lastIndex) { para.SqlStringBuilder.Append(DBConstant.BLACK_SPACE); para.SqlStringBuilder.Append(conditionExpressionNodes.LogicOperaters.ToString()); para.SqlStringBuilder.Append(DBConstant.BLACK_SPACE); } } else { //有子节点 para.SqlStringBuilder.Append('('); this.CreateQueryFieldWhereSql(para, tableAliaNameDic, tableFieldInfoDic, childrenConditionExpressionNodes, dbFiledValueConverterCollection); para.SqlStringBuilder.Append(')'); } } }
/// <summary> /// 转换为无参where语句 /// </summary> /// <param name="tableFieldInfoDic">表字段字典集合[key:表名;value:[key:字段名;value:DBFieldInfo]]</param> /// <param name="tableAliaNameDic">表别名字典集合[key:列名;value:别名]</param> /// <param name="filedValueConverterCollection">数据库字段值转换对象集合</param> /// <param name="fieldValueFormator">字段值格式化对象</param> /// <returns>where语句</returns> public string ToWhereNoParameter(Dictionary <string, Dictionary <string, DBFieldInfo> > tableFieldInfoDic, Dictionary <string, string> tableAliaNameDic, DBFiledValueConverterCollection filedValueConverterCollection, ISqlFieldValueFormator fieldValueFormator) { var sbSql = new StringBuilder(); var para = new ConditionValueNoSqlParaGeneratorPara(sbSql, fieldValueFormator); this.CreateQueryFieldWhereSqlNoParameter(para, tableAliaNameDic, tableFieldInfoDic, this, filedValueConverterCollection); return(sbSql.ToString()); }
/// <summary> /// 转换为带参数where语句 /// </summary> /// <param name="tableFieldInfoDic">表字段字典集合[key:表名;value:[key:字段名;value:DBFieldInfo]]</param> /// <param name="tableAliaNameDic">表别名字典集合[key:列名;value:别名]</param> /// <param name="filedValueConverterCollection">数据库字段值转换对象集合</param> /// <param name="paraSign">SQL参数符号字符串</param> /// <param name="parameterIndex">参数索引</param> /// <param name="parameterNameValueDic">参数名名称及对应的值字典集合[key:参数名称,含参数符号;value:参数值]</param> /// <returns>where语句</returns> public string ToWhere(Dictionary <string, Dictionary <string, DBFieldInfo> > tableFieldInfoDic, Dictionary <string, string> tableAliaNameDic, DBFiledValueConverterCollection filedValueConverterCollection, string paraSign, ref int parameterIndex, out Dictionary <string, object> parameterNameValueDic) { var sbSql = new StringBuilder(); parameterNameValueDic = new Dictionary <string, object>(); var para = new ConditionValueGeneratorPara(sbSql, paraSign, parameterIndex, parameterNameValueDic); this.CreateQueryFieldWhereSql(para, tableAliaNameDic, tableFieldInfoDic, this, filedValueConverterCollection); parameterIndex = para.GetParameterIndex(); return(sbSql.ToString()); }
private void btnDBQueryExpression_Click(object sender, EventArgs e) { try { // select treea.ID,treea.Name,treea.Age,treeb.City,treeb.Level From treea INNER JOIN treeb on treea.ID = treeb.AID //where treea.Age > 23 AND treeb.Level < 5 //select A.ID,A.Name,A.Age,B.City,B.Level From treea A INNER JOIN treeb B on A.ID = B.AID //where A.Age > 23 AND B.Level < 5 //string tblA = "TreeA"; //string tblB = "TreeB"; string tblA = "treea"; string tblB = "treeb"; var queryExpression = new DBQueryExpression(); queryExpression.QueryFields.Add(new DBQueryField(tblA, "ID")); queryExpression.QueryFields.Add(new DBQueryField(tblA, "Name")); queryExpression.QueryFields.Add(new DBQueryField(tblA, "Age")); queryExpression.QueryFields.Add(new DBQueryField(tblB, "City")); queryExpression.QueryFields.Add(new DBQueryField(tblB, "Level")); queryExpression.ConditionExpressionNodes.LogicOperaters = LogicOperaters.AND; queryExpression.ConditionExpressionNodes.Add(new ExpressionNode(CompareOperater.GreaterThan, tblA, "Age", 23)); queryExpression.ConditionExpressionNodes.Add(new ExpressionNode(CompareOperater.LessThan, tblB, "Level", 5)); var tableAliaNameDic = new Dictionary <string, string>(); tableAliaNameDic.Add("treea", "A"); tableAliaNameDic.Add("treeb", "B"); string fieldSql = queryExpression.QueryFields.ToQueryFieldSql(tableAliaNameDic); Loger.Info(fieldSql); var config = DropdownBoxHelper.GetGenericFromComboBox <DatabaseConfig>(comboBoxDB); var dbAccess = DBAccessManager.GetDBAccessInstance(config.DBID); List <DBTableInfo> dbTableInfos = dbAccess.Database.GetTableInfoList(true); var dbTableInfoDic = dbTableInfos.ToDictionary(k => { return(k.Name); }, v => { return(v.DbFieldInfos.ToDictionary(t => { return t.FieldName; })); }); var filedValueConverterCollection = new DBFiledValueConverterCollection(); Dictionary <string, object> parameterNameValueDic; int parameterIndex = 1; string where = queryExpression.ConditionExpressionNodes.ToWhere(dbTableInfoDic, tableAliaNameDic, filedValueConverterCollection, dbAccess.ParaSign, ref parameterIndex, out parameterNameValueDic); Loger.Info(where); ISqlFieldValueFormator fieldValueFormator = DBAccessManager.GetFieldValueFormator(config); string whereNoPara = queryExpression.ConditionExpressionNodes.ToWhereNoParameter(dbTableInfoDic, tableAliaNameDic, filedValueConverterCollection, fieldValueFormator); Loger.Info(whereNoPara); //------------------------------------------------------------------------------------------------------------------------------------- //A.Age > 23 AND (B.Level < 4 OR B.Level > 6) var expressionNodeCollection = new ExpressionNodeCollection(); expressionNodeCollection.LogicOperaters = LogicOperaters.AND; expressionNodeCollection.Add(new ExpressionNode(CompareOperater.GreaterThan, tblA, "Age", 23)); var orCondition = new ExpressionNode(); orCondition.Children.LogicOperaters = LogicOperaters.OR; orCondition.Children.Add(new ExpressionNode(CompareOperater.LessThan, tblB, "Level", 4)); orCondition.Children.Add(new ExpressionNode(CompareOperater.GreaterThan, tblB, "Level", 6)); expressionNodeCollection.Add(orCondition); parameterNameValueDic = null; parameterIndex = 1; string where2 = expressionNodeCollection.ToWhere(dbTableInfoDic, tableAliaNameDic, filedValueConverterCollection, dbAccess.ParaSign, ref parameterIndex, out parameterNameValueDic); Loger.Info(where2); string whereNoPara2 = expressionNodeCollection.ToWhereNoParameter(dbTableInfoDic, tableAliaNameDic, filedValueConverterCollection, fieldValueFormator); Loger.Info(whereNoPara2); tableAliaNameDic.Clear(); parameterNameValueDic = null; parameterIndex = 1; string where3 = expressionNodeCollection.ToWhere(dbTableInfoDic, tableAliaNameDic, filedValueConverterCollection, dbAccess.ParaSign, ref parameterIndex, out parameterNameValueDic); Loger.Info(where3); string whereNoPara3 = expressionNodeCollection.ToWhereNoParameter(dbTableInfoDic, tableAliaNameDic, filedValueConverterCollection, fieldValueFormator); Loger.Info(whereNoPara3); } catch (Exception ex) { Loger.Error(ex); } }