/// <summary>
        /// Get insert execute DbCommand
        /// </summary>
        /// <param name="translator">Translator</param>
        /// <param name="command">Command</param>
        /// <returns>Return insert execute command</returns>
        DatabaseExecuteCommand GetInsertExecuteDbCommand(IQueryTranslator translator, RdbCommand command)
        {
            string objectName         = DataManager.GetEntityObjectName(DatabaseServerType.SQLServer, command.EntityType, command.ObjectName);
            var    fields             = DataManager.GetEditFields(DatabaseServerType.SQLServer, command.EntityType);
            var    fieldCount         = fields.GetCount();
            var    insertFormatResult = SqlServerFactory.FormatInsertFields(fieldCount, fields, command.Parameters, translator.ParameterSequence);

            if (insertFormatResult == null)
            {
                return(null);
            }
            string            cmdText    = $"INSERT INTO {SqlServerFactory.WrapKeyword(objectName)} ({string.Join(",", insertFormatResult.Item1)}) VALUES ({string.Join(",", insertFormatResult.Item2)});";
            CommandParameters parameters = insertFormatResult.Item3;

            translator.ParameterSequence += fieldCount;
            return(new DatabaseExecuteCommand()
            {
                CommandText = cmdText,
                CommandType = SqlServerFactory.GetCommandType(command),
                ForceReturnValue = command.MustReturnValueOnSuccess,
                Parameters = parameters
            });
        }
        /// <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 command
        /// </summary>
        /// <param name="server">Server</param>
        /// <param name="executeOption">Execute option</param>
        /// <param name="commands">Commands</param>
        /// <returns>Return effect data numbers</returns>
        public async Task <int> ExecuteAsync(DatabaseServer server, CommandExecuteOptions executeOption, IEnumerable <ICommand> commands)
        {
            #region group execute commands

            IQueryTranslator translator = SqlServerFactory.GetQueryTranslator(server);
            List <DatabaseExecuteCommand> executeCommands = new List <DatabaseExecuteCommand>();
            var batchExecuteConfig   = DataManager.GetBatchExecuteConfiguration(server.ServerType) ?? BatchExecuteConfiguration.Default;
            var groupStatementsCount = batchExecuteConfig.GroupStatementsCount;
            groupStatementsCount = groupStatementsCount < 0 ? 1 : groupStatementsCount;
            var groupParameterCount = batchExecuteConfig.GroupParametersCount;
            groupParameterCount = groupParameterCount < 0 ? 1 : groupParameterCount;
            StringBuilder     commandTextBuilder = new StringBuilder();
            CommandParameters parameters         = null;
            int  statementsCount  = 0;
            bool forceReturnValue = false;
            int  cmdCount         = 0;

            DatabaseExecuteCommand GetGroupExecuteCommand()
            {
                var executeCommand = new DatabaseExecuteCommand()
                {
                    CommandText      = commandTextBuilder.ToString(),
                    CommandType      = CommandType.Text,
                    ForceReturnValue = forceReturnValue,
                    Parameters       = parameters
                };

                statementsCount = 0;
                translator.ParameterSequence = 0;
                commandTextBuilder.Clear();
                parameters       = null;
                forceReturnValue = false;
                return(executeCommand);
            }

            foreach (var cmd in commands)
            {
                DatabaseExecuteCommand executeCommand = GetExecuteDbCommand(translator, cmd as RdbCommand);
                if (executeCommand == null)
                {
                    continue;
                }

                //Trace log
                SqlServerFactory.LogExecuteCommand(executeCommand);

                cmdCount++;
                if (executeCommand.PerformAlone)
                {
                    if (statementsCount > 0)
                    {
                        executeCommands.Add(GetGroupExecuteCommand());
                    }
                    executeCommands.Add(executeCommand);
                    continue;
                }
                commandTextBuilder.AppendLine(executeCommand.CommandText);
                parameters        = parameters == null ? executeCommand.Parameters : parameters.Union(executeCommand.Parameters);
                forceReturnValue |= executeCommand.ForceReturnValue;
                statementsCount++;
                if (translator.ParameterSequence >= groupParameterCount || statementsCount >= groupStatementsCount)
                {
                    executeCommands.Add(GetGroupExecuteCommand());
                }
            }
            if (statementsCount > 0)
            {
                executeCommands.Add(GetGroupExecuteCommand());
            }

            #endregion

            return(await ExecuteCommandAsync(server, executeOption, executeCommands, executeOption?.ExecuteByTransaction ?? cmdCount > 1).ConfigureAwait(false));
        }
        /// <summary>
        /// Get update execute command
        /// </summary>
        /// <param name="translator">Translator</param>
        /// <param name="command">Command</param>
        /// <returns>Return update execute command</returns>
        DatabaseExecuteCommand GetUpdateExecuteDbCommand(IQueryTranslator translator, RdbCommand command)
        {
            #region query translate

            var    tranResult      = translator.Translate(command.Query);
            string conditionString = string.Empty;
            if (!string.IsNullOrWhiteSpace(tranResult.ConditionString))
            {
                conditionString = $"WHERE {tranResult.ConditionString}";
            }
            string preScript  = tranResult.PreScript;
            string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;

            #endregion

            #region script

            CommandParameters parameters    = SqlServerFactory.ParseParameters(command.Parameters) ?? new CommandParameters();
            string            objectName    = DataManager.GetEntityObjectName(DatabaseServerType.SQLServer, command.EntityType, command.ObjectName);
            var           fields            = SqlServerFactory.GetFields(command.EntityType, command.Fields);
            int           parameterSequence = translator.ParameterSequence;
            List <string> updateSetArray    = new List <string>();
            foreach (var field in fields)
            {
                var    parameterValue     = parameters.GetParameterValue(field.PropertyName);
                var    parameterName      = field.PropertyName;
                string newValueExpression = string.Empty;
                if (parameterValue != null)
                {
                    parameterSequence++;
                    parameterName = SqlServerFactory.FormatParameterName(parameterName, parameterSequence);
                    parameters.Rename(field.PropertyName, parameterName);
                    if (parameterValue is IModifyValue)
                    {
                        var modifyValue = parameterValue as IModifyValue;
                        parameters.ModifyValue(parameterName, modifyValue.Value);
                        if (parameterValue is CalculateModifyValue)
                        {
                            var    calculateModifyValue = parameterValue as CalculateModifyValue;
                            string calChar = SqlServerFactory.GetCalculateChar(calculateModifyValue.Operator);
                            newValueExpression = $"{translator.ObjectPetName}.{SqlServerFactory.WrapKeyword(field.FieldName)}{calChar}{SqlServerFactory.ParameterPrefix}{parameterName}";
                        }
                    }
                }
                if (string.IsNullOrWhiteSpace(newValueExpression))
                {
                    newValueExpression = $"{SqlServerFactory.ParameterPrefix}{parameterName}";
                }
                updateSetArray.Add($"{translator.ObjectPetName}.{SqlServerFactory.WrapKeyword(field.FieldName)}={newValueExpression}");
            }
            string cmdText = $"{preScript}UPDATE {translator.ObjectPetName} SET {string.Join(",", updateSetArray)} FROM {SqlServerFactory.WrapKeyword(objectName)} AS {translator.ObjectPetName} {joinScript} {conditionString};";
            translator.ParameterSequence = parameterSequence;

            #endregion

            #region parameter

            var queryParameters = SqlServerFactory.ParseParameters(tranResult.Parameters);
            parameters.Union(queryParameters);

            #endregion

            return(new DatabaseExecuteCommand()
            {
                CommandText = cmdText,
                CommandType = SqlServerFactory.GetCommandType(command),
                ForceReturnValue = command.MustReturnValueOnSuccess,
                Parameters = parameters,
                HasPreScript = !string.IsNullOrWhiteSpace(preScript)
            });
        }
        /// <summary>
        /// Execute commands
        /// </summary>
        /// <param name="server">db server</param>
        /// <param name="executeCommands">execute commands</param>
        /// <param name="useTransaction">use transaction</param>
        /// <returns>Return effect data numbers</returns>
        async Task <int> ExecuteCommandAsync(DatabaseServer server, CommandExecuteOptions executeOption, IEnumerable <DatabaseExecuteCommand> executeCommands, bool useTransaction)
        {
            int  resultValue = 0;
            bool success     = true;

            using (var conn = SqlServerFactory.GetConnection(server))
            {
                IDbTransaction transaction = null;
                if (useTransaction)
                {
                    transaction = SqlServerFactory.GetExecuteTransaction(conn, executeOption);
                }
                try
                {
                    foreach (var cmd in executeCommands)
                    {
                        var cmdDefinition      = new CommandDefinition(cmd.CommandText, SqlServerFactory.ConvertCmdParameters(cmd.Parameters), transaction: transaction, commandType: cmd.CommandType, cancellationToken: executeOption?.CancellationToken ?? default);
                        var executeResultValue = await conn.ExecuteAsync(cmdDefinition).ConfigureAwait(false);

                        success      = success && (cmd.ForceReturnValue ? executeResultValue > 0 : true);
                        resultValue += executeResultValue;
                        if (useTransaction && !success)
                        {
                            break;
                        }
                    }
                    if (!useTransaction)
                    {
                        return(resultValue);
                    }
                    if (success)
                    {
                        transaction.Commit();
                    }
                    else
                    {
                        resultValue = 0;
                        transaction.Rollback();
                    }
                    return(resultValue);
                }
                catch (Exception ex)
                {
                    resultValue = 0;
                    transaction?.Rollback();
                    throw ex;
                }
            }
        }
示例#6
0
        /// <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)}")})";