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