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);
}
}
