public void FromIn <K>(TQueryHelper <K> helper, string newTableName = null) where K : class
{
this.fromInTableName = TQueryReflectionHelper.GetTableName(typeof(K));
this.fromInNewTableName = newTableName;
string querySql = string.Format(TQueryHelperTemplateEnum.FROM_IN, helper.ToSql(), string.IsNullOrEmpty(newTableName) ? this.fromInTableName : newTableName);
this.queryList.Add(new ExpressionKeyValueItem(TQueryHelperTypeEnum.FROMIN, querySql));
}
public void WhereIn <K>(string fieldName, string charType, IList <K> fieldValueList, bool directUseFieldName = false)
{
string querySql = null;
if (!directUseFieldName)
{
querySql = string.Format(TQueryHelperTemplateEnum.WHERE_IN, TQueryReflectionHelper.GetTableName(typeof(T)), fieldName, charType, this.GetFieldValueString <K>(fieldValueList));
}
else
{
querySql = string.Format(TQueryHelperTemplateEnum.WHERE_IN_DIRECT, fieldName, charType, this.GetFieldValueString <K>(fieldValueList));
}
this.queryList.Add(new ExpressionKeyValueItem(TQueryHelperTypeEnum.WHERE, querySql));
}
public void Select(Type type, Expression lambda = null)
{
string querySql = null;
if (lambda != null)
{
querySql = new QueryTranslator().Translate(lambda);
}
else
{
querySql = string.Format(TQueryHelperTemplateEnum.TABLE_ALL, TQueryReflectionHelper.GetTableName(type));
}
this.queryList.Add(new ExpressionKeyValueItem(TQueryHelperTypeEnum.SELECT, querySql));
}
public void Init(int top, bool distinct, string queryType = TQueryHelperTypeEnum.QUERY_TYPE)
{
this.queryType = queryType;
this.queryList = new List <ExpressionKeyValueItem>();
if (queryType == TQueryHelperTypeEnum.QUERY_TYPE)
{
this.queryTableName = TQueryReflectionHelper.GetTableName(typeof(T), true);
}
else
{
this.queryTableName = TQueryReflectionHelper.GetTableName(typeof(T), false);
}
this.queryTop = top;
this.distinct = distinct;
}
public void Insert(Expression <Func <T, object> > lambda = null)
{
this.Init(0, false, TQueryHelperTypeEnum.INSERT_TYPE);
Dictionary <string, string> fieldMapperDict = null;
if (lambda == null)
{
fieldMapperDict = TQueryReflectionHelper.GetPropertyDict(typeof(T));
}
else
{
OperaterTranslator operaterTranslator = new OperaterTranslator();
operaterTranslator.Translate(lambda);
fieldMapperDict = operaterTranslator.MapperDict;
}
int propertyCount = fieldMapperDict.Count;
int propertyIndex = 0;
StringBuilder fieldStringBuilder = new StringBuilder();
StringBuilder paramStringBuilder = new StringBuilder();
foreach (var keyValueItem in fieldMapperDict)
{
fieldStringBuilder.Append(string.Format(TQueryHelperTemplateEnum.FIELD, keyValueItem.Key));
paramStringBuilder.Append("@");
paramStringBuilder.Append(keyValueItem.Value);
if (propertyIndex < propertyCount - 1)
{
fieldStringBuilder.Append(",");
paramStringBuilder.Append(",");
}
propertyIndex++;
}
string querySql = string.Format(TQueryHelperTemplateEnum.INSERT, this.queryTableName, fieldStringBuilder.ToString(), paramStringBuilder.ToString());
this.queryList.Add(new ExpressionKeyValueItem(TQueryHelperTypeEnum.INSERT, querySql));
}
private string CreateQuerySql()
{
string tableName = TQueryReflectionHelper.GetTableName(typeof(T));
#region 当无数据时
if (this.queryList == null || this.queryList.Count == 0)
{
string querySql = null;
if (this.distinct)
{
if (this.queryTop > 0)
{
querySql = string.Format(TQueryHelperTemplateEnum.QUERY_DISTINCT_TOP, tableName, this.queryTop);
}
else
{
querySql = string.Format(TQueryHelperTemplateEnum.QUERY_DISTINCT, tableName);
}
}
else
{
if (this.queryTop > 0)
{
querySql = string.Format(TQueryHelperTemplateEnum.QUERY_TOP, tableName, this.queryTop);
}
else
{
querySql = string.Format(TQueryHelperTemplateEnum.QUERY_DEFAULT, tableName);
}
}
return(querySql);
}
#endregion
StringBuilder stringBuilder = new StringBuilder();
int queryCount = this.queryList != null ? this.queryList.Count : 0;
if (queryCount > 0)
{
string expressionName = null;
ExpressionKeyValueItem keyValueItem = null;
#region 处理别名映射
if (!string.IsNullOrEmpty(this.fromInNewTableName))
{
foreach (var mapperKeyValueItem in this.queryList)
{
if (mapperKeyValueItem.Key == TQueryHelperTypeEnum.SELECT || mapperKeyValueItem.Key == TQueryHelperTypeEnum.WHERE || mapperKeyValueItem.Key == TQueryHelperTypeEnum.ORDERBY || mapperKeyValueItem.Key == TQueryHelperTypeEnum.GROUPBY)
{
mapperKeyValueItem.Value = mapperKeyValueItem.Value.Replace(this.fromInTableName, this.fromInNewTableName);
}
}
}
#endregion
#region 创建 Select 语句
stringBuilder.Append("select ");
if (this.distinct)
{
stringBuilder.Append(" distinct ");
}
if (this.queryTop > 0)
{
stringBuilder.Append(" top ");
stringBuilder.Append(this.queryTop);
stringBuilder.Append(" ");
}
List <ExpressionKeyValueItem> aggregateDataList = this.queryList.Where(p => p.Key == TQueryHelperTypeEnum.AGGREGATE).ToList();
List <ExpressionKeyValueItem> dataList = this.queryList.Where(p => p.Key == TQueryHelperTypeEnum.SELECT).ToList();
int dataCount = dataList != null ? dataList.Count : 0;
if ((dataList == null || dataCount == 0) && (aggregateDataList == null || aggregateDataList.Count == 0))
{
stringBuilder.Append(string.Format(TQueryHelperTemplateEnum.TABLE_ALL, tableName));
if (this.joinTableDict != null)
{
if (this.joinTableDict.Count > 0)
{
stringBuilder.Append(",");
}
int joinIndex = 0;
foreach (var joinKeyValueItem in this.joinTableDict)
{
stringBuilder.Append(string.Format(TQueryHelperTemplateEnum.TABLE_ALL, TQueryReflectionHelper.GetTableName(joinKeyValueItem.Value)));
if (joinIndex < this.joinTableDict.Count - 1)
{
stringBuilder.Append(",");
}
joinIndex++;
}
}
}
else
{
for (int index = 0; index < dataCount; index++)
{
stringBuilder.Append(dataList[index].Value);
if (index < dataCount - 1)
{
stringBuilder.Append(",");
}
}
}
if (aggregateDataList != null && aggregateDataList.Count > 0)
{
if (dataList != null && dataList.Count > 0)
{
stringBuilder.Append(",");
}
dataCount = aggregateDataList.Count;
for (int index = 0; index < dataCount; index++)
{
stringBuilder.Append(aggregateDataList[index].Value);
if (index < dataCount - 1)
{
stringBuilder.Append(",");
}
}
}
#endregion
#region From 语句
stringBuilder.Append(" from ");
// 判断子查询
keyValueItem = this.queryList.Where(p => p.Key == TQueryHelperTypeEnum.FROMIN).FirstOrDefault();
// 当没有子查询时，追加
if (keyValueItem == null)
{
stringBuilder.Append(this.queryTableName);
}
#endregion
#region 遍历
for (int index = 0; index < queryCount; index++)
{
keyValueItem = this.queryList[index];
if (keyValueItem.Key == TQueryHelperTypeEnum.SELECT || keyValueItem.Key == TQueryHelperTypeEnum.AGGREGATE || keyValueItem.Key == TQueryHelperTypeEnum.ORDERBY || keyValueItem.Key == TQueryHelperTypeEnum.GROUPBY || keyValueItem.Key == TQueryHelperTypeEnum.UNION)
{
continue;
}
if (keyValueItem.Key == TQueryHelperTypeEnum.WHERE && expressionName != TQueryHelperTypeEnum.WHERE)
{
stringBuilder.Append(" where ");
}
if (keyValueItem.Key == TQueryHelperTypeEnum.WHERE && expressionName == TQueryHelperTypeEnum.WHERE)
{
if (!keyValueItem.Value.StartsWith(" and ") && !keyValueItem.Value.StartsWith(" or "))
{
stringBuilder.Append(" and ");
}
}
if (keyValueItem.Key == TQueryHelperTypeEnum.JOINONAND)
{
if (!keyValueItem.Value.StartsWith(" and ") && !keyValueItem.Value.StartsWith(" or "))
{
stringBuilder.Append(" and ");
}
}
stringBuilder.Append(keyValueItem.Value);
expressionName = keyValueItem.Key;
}
#endregion
#region Group By 语句
dataList = this.queryList.Where(p => p.Key == TQueryHelperTypeEnum.GROUPBY).ToList();
dataCount = dataList != null ? dataList.Count : 0;
if (dataList != null && dataCount > 0)
{
stringBuilder.Append(" group by ");
for (int index = 0; index < dataCount; index++)
{
stringBuilder.Append(dataList[index].Value);
if (index < dataCount - 1)
{
stringBuilder.Append(",");
}
}
}
#endregion
#region Order By 语句
dataList = this.queryList.Where(p => p.Key == TQueryHelperTypeEnum.ORDERBY).ToList();
dataCount = dataList != null ? dataList.Count : 0;
if (dataList != null && dataCount > 0)
{
stringBuilder.Append(" order by ");
for (int index = 0; index < dataCount; index++)
{
stringBuilder.Append(dataList[index].Value);
if (index < dataCount - 1)
{
stringBuilder.Append(",");
}
}
}
#endregion
}
#region 处理 Union 语句
List <ExpressionKeyValueItem> unionDataList = this.queryList.Where(p => p.Key == TQueryHelperTypeEnum.UNION).ToList();
if (unionDataList == null || unionDataList.Count == 0)
{
return(stringBuilder.ToString());
}
else
{
StringBuilder unionStringBuilder = new StringBuilder();
unionStringBuilder.Append(string.Format(TQueryHelperTemplateEnum.BREAK, stringBuilder.ToString()));
foreach (ExpressionKeyValueItem keyValueItem in unionDataList)
{
unionStringBuilder.Append(" union ");
unionStringBuilder.Append(string.Format(TQueryHelperTemplateEnum.BREAK, keyValueItem.Value));
}
return(unionStringBuilder.ToString());
}
#endregion
}
public void Group(Type type, string groupFieldList)
{
this.queryList.Add(new ExpressionKeyValueItem(TQueryHelperTypeEnum.GROUPBY, TQueryReflectionHelper.GetFieldString(groupFieldList)));
}
public void Order(Type type, string orderFieldList)
{
this.queryList.Add(new ExpressionKeyValueItem(TQueryHelperTypeEnum.ORDERBY, TQueryReflectionHelper.GetFieldString(orderFieldList)));
}
public void JoinT <K>(Type type, Expression lambda, string joinType = "inner", bool isGeneric = false) where K : class
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" ");
if (isGeneric)
{
stringBuilder.Append(new JoinOnTranslator <K>().Translate(lambda, joinType, TQueryReflectionHelper.GetTableName(type)));
}
else
{
stringBuilder.Append(new JoinOnTranslator().Translate(lambda, joinType, TQueryReflectionHelper.GetTableName(type)));
}
stringBuilder.Append(" ");
this.queryList.Add(new ExpressionKeyValueItem(TQueryHelperTypeEnum.JOINON, stringBuilder.ToString()));
}
public void Select(Type type, string fieldNameList)
{
this.queryList.Add(new ExpressionKeyValueItem(TQueryHelperTypeEnum.SELECT, TQueryReflectionHelper.GetFieldString(fieldNameList)));
}
