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);
}
}
/// <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>
/// 生成条件值
/// </summary>
/// <param name="sqlStringBuilder">sbSql</param>
/// <param name="fieldValueFormator">字段值格式化对象</param>
public ConditionValueNoSqlParaGeneratorPara(StringBuilder sqlStringBuilder, ISqlFieldValueFormator fieldValueFormator)
{
this.SqlStringBuilder = sqlStringBuilder;
this.FieldValueFormator = fieldValueFormator;
}
