/// <summary> /// Query datas /// </summary> /// <typeparam name="T">Data type</typeparam> /// <param name="server">Database server</param> /// <param name="command">Command</param> /// <returns>Return datas</returns> public async Task <IEnumerable <T> > QueryAsync <T>(DatabaseServer server, ICommand command) { if (command.Query == null) { throw new EZNEWException("ICommand.Query is null"); } #region query translate IQueryTranslator translator = SqlServerFactory.GetQueryTranslator(server); var tranResult = translator.Translate(command.Query); string preScript = tranResult.PreScript; string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; #endregion #region script string cmdText; switch (command.Query.QueryType) { case QueryCommandType.Text: cmdText = tranResult.ConditionString; break; case QueryCommandType.QueryObject: default: int size = command.Query.QuerySize; string objectName = DataManager.GetEntityObjectName(DatabaseServerType.SQLServer, command.EntityType, command.ObjectName); string orderString = string.IsNullOrWhiteSpace(tranResult.OrderString) ? string.Empty : $"ORDER BY {tranResult.OrderString}"; var queryFields = SqlServerFactory.GetQueryFields(command.Query, command.EntityType, true); string outputFormatedField = string.Join(",", SqlServerFactory.FormatQueryFields(translator.ObjectPetName, queryFields, true)); if (string.IsNullOrWhiteSpace(tranResult.CombineScript)) { cmdText = $"{preScript}SELECT {(size > 0 ? $"TOP {size}" : string.Empty)} {outputFormatedField} FROM {SqlServerFactory.WrapKeyword(objectName)} AS {translator.ObjectPetName} {joinScript} {(string.IsNullOrWhiteSpace(tranResult.ConditionString) ? string.Empty : $"WHERE {tranResult.ConditionString}")} {orderString}"; }
/// <summary> /// Execute Translate /// </summary> /// <param name="query">Query object</param> /// <param name="paras">Parameters</param> /// <param name="objectName">Query object name</param> /// <returns>Return translate result</returns> public TranslateResult ExecuteTranslate(IQuery query, CommandParameters paras = null, string objectName = "", bool subQuery = false, bool useOrder = true) { if (query == null) { return(TranslateResult.Empty); } StringBuilder conditionBuilder = new StringBuilder(); if (query.QueryType == QueryCommandType.QueryObject) { StringBuilder orderBuilder = new StringBuilder(); CommandParameters parameters = paras ?? new CommandParameters(); objectName = string.IsNullOrWhiteSpace(objectName) ? ObjPetName : objectName; List <string> withScripts = new List <string>(); string recurveTableName = string.Empty; string recurveTablePetName = string.Empty; #region query condition if (!query.Criterias.IsNullOrEmpty()) { int index = 0; foreach (var queryItem in query.Criterias) { var queryItemCondition = TranslateCondition(query, queryItem, parameters, objectName); if (!queryItemCondition.WithScripts.IsNullOrEmpty()) { withScripts.AddRange(queryItemCondition.WithScripts); recurveTableName = queryItemCondition.RecurveObjectName; recurveTablePetName = queryItemCondition.RecurvePetName; } conditionBuilder.Append($" {(index > 0 ? queryItem.Item1.ToString() : string.Empty)} {queryItemCondition.ConditionString}"); index++; } } #endregion #region sort if (useOrder && !query.Orders.IsNullOrEmpty()) { foreach (var orderItem in query.Orders) { orderBuilder.Append($"{ConvertOrderCriteriaName(query, objectName, orderItem)} {(orderItem.Desc ? DescKeyWord : AscKeyWord)},"); } } #endregion #region combine StringBuilder combineBuilder = new StringBuilder(); if (!query.CombineItems.IsNullOrEmpty()) { foreach (var combine in query.CombineItems) { if (combine?.CombineQuery == null) { continue; } var combineObjectPetName = GetNewSubObjectPetName(); string combineObjectName = DataManager.GetQueryRelationObjectName(DatabaseServerType.SQLServer, combine.CombineQuery); var combineQueryResult = ExecuteTranslate(combine.CombineQuery, parameters, combineObjectPetName, true, true); string combineConditionString = string.IsNullOrWhiteSpace(combineQueryResult.ConditionString) ? string.Empty : $"WHERE {combineQueryResult.ConditionString}"; combineBuilder.Append($" {GetCombineOperator(combine.CombineType)} SELECT {string.Join(",", SqlServerFactory.FormatQueryFields(combineObjectPetName, query, query.GetEntityType(), true, false))} FROM {SqlServerFactory.WrapKeyword(combineObjectName)} AS {combineObjectPetName} {(combineQueryResult.AllowJoin ? combineQueryResult.JoinScript : string.Empty)} {combineConditionString}"); if (!combineQueryResult.WithScripts.IsNullOrEmpty()) { withScripts.AddRange(combineQueryResult.WithScripts); recurveTableName = combineQueryResult.RecurveObjectName; recurveTablePetName = combineQueryResult.RecurvePetName; } } } #endregion #region join bool allowJoin = true; StringBuilder joinBuilder = new StringBuilder(); if (!query.JoinItems.IsNullOrEmpty()) { foreach (var joinItem in query.JoinItems) { if (joinItem == null || joinItem.JoinQuery == null) { continue; } if (joinItem.JoinQuery.GetEntityType() == null) { throw new EZNEWException("IQuery object must set entity type if use in join operation"); } string joinObjName = GetNewSubObjectPetName(); var joinQueryResult = ExecuteTranslate(joinItem.JoinQuery, parameters, joinObjName, true, true); if (string.IsNullOrWhiteSpace(joinQueryResult.CombineScript)) { var joinConnection = GetJoinCondition(query, joinItem, objectName, joinObjName); if (!string.IsNullOrWhiteSpace(joinQueryResult.ConditionString)) { if (joinQueryResult.AllowJoin && PositionJoinConditionToConnection(joinItem.JoinType)) { joinConnection += $"{(string.IsNullOrWhiteSpace(joinConnection) ? " ON" : " AND ")}{joinQueryResult.ConditionString}"; } else { conditionBuilder.Append($"{(conditionBuilder.Length == 0 ? string.Empty : " AND ")}{joinQueryResult.ConditionString}"); } } joinBuilder.Append($" {GetJoinOperator(joinItem.JoinType)} {SqlServerFactory.WrapKeyword(DataManager.GetQueryRelationObjectName(DatabaseServerType.SQLServer, joinItem.JoinQuery))} AS {joinObjName}{joinConnection}"); if (joinQueryResult.AllowJoin && !string.IsNullOrWhiteSpace(joinQueryResult.JoinScript)) { joinBuilder.Append($" {joinQueryResult.JoinScript}"); } } else { var combineJoinObjName = GetNewSubObjectPetName(); var joinConnection = GetJoinCondition(query, joinItem, objectName, combineJoinObjName); joinBuilder.Append($" {GetJoinOperator(joinItem.JoinType)} (SELECT {string.Join(",", SqlServerFactory.FormatQueryFields(joinObjName, joinItem.JoinQuery, joinItem.JoinQuery.GetEntityType(), true, false))} FROM {SqlServerFactory.WrapKeyword(DataManager.GetQueryRelationObjectName(DatabaseServerType.SQLServer, joinItem.JoinQuery))} AS {joinObjName} {(joinQueryResult.AllowJoin ? joinQueryResult.JoinScript : string.Empty)} {(string.IsNullOrWhiteSpace(joinQueryResult.ConditionString) ? string.Empty : "WHERE " + joinQueryResult.ConditionString)} {joinQueryResult.CombineScript}) AS {combineJoinObjName}{joinConnection}"); } if (!joinQueryResult.WithScripts.IsNullOrEmpty()) { withScripts.AddRange(joinQueryResult.WithScripts); recurveTableName = joinQueryResult.RecurveObjectName; recurveTablePetName = joinQueryResult.RecurvePetName; } } } string joinScript = joinBuilder.ToString(); #endregion #region recurve script string conditionString = conditionBuilder.ToString(); if (query.RecurveCriteria != null) { allowJoin = false; string nowConditionString = conditionString; EntityField recurveField = DataManager.GetField(DatabaseServerType.SQLServer, query, query.RecurveCriteria.Key); EntityField recurveRelationField = DataManager.GetField(DatabaseServerType.SQLServer, query, query.RecurveCriteria.RelationKey); var recurveTable = GetNewRecurveTableName(); recurveTablePetName = recurveTable.Item1; recurveTableName = recurveTable.Item2; conditionString = $"{objectName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)} IN (SELECT {recurveTablePetName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)} FROM {SqlServerFactory.WrapKeyword(recurveTableName)} AS {recurveTablePetName})"; string queryObjectName = DataManager.GetQueryRelationObjectName(DatabaseServerType.SQLServer, query); string withScript = $"{recurveTableName} AS (SELECT {objectName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)},{objectName}.{SqlServerFactory.WrapKeyword(recurveRelationField.FieldName)} FROM {SqlServerFactory.WrapKeyword(queryObjectName)} AS {objectName} {joinScript} {(string.IsNullOrWhiteSpace(nowConditionString) ? string.Empty : $"WHERE {nowConditionString}")} " + $"UNION ALL SELECT {objectName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)},{objectName}.{SqlServerFactory.WrapKeyword(recurveRelationField.FieldName)} FROM {SqlServerFactory.WrapKeyword(queryObjectName)} AS {objectName},{recurveTableName} AS {recurveTablePetName} " + $"WHERE {(query.RecurveCriteria.Direction == RecurveDirection.Up ? $"{objectName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)}={recurveTablePetName}.{SqlServerFactory.WrapKeyword(recurveRelationField.FieldName)}" : $"{objectName}.{SqlServerFactory.WrapKeyword(recurveRelationField.FieldName)}={recurveTablePetName}.{SqlServerFactory.WrapKeyword(recurveField.FieldName)}")})";