Пример #1
0
        /// <summary>
        /// format insert fields
        /// </summary>
        /// <param name="fields">fields</param>
        /// <param name="originParameters">origin parameters</param>
        /// <returns>first:fields,second:parameter fields,third:parameters</returns>
        Tuple <List <string>, List <string>, CmdParameters> FormatInsertFields(List <EntityField> fields, object parameters, int parameterSequence)
        {
            if (fields.IsNullOrEmpty())
            {
                return(null);
            }
            List <string> formatFields    = new List <string>(fields.Count);
            List <string> parameterFields = new List <string>(fields.Count);
            CmdParameters cmdParameters   = ParseParameters(parameters);
            string        key             = ((int)ServerType.MySQL).ToString();

            foreach (var field in fields)
            {
                //fields
                var formatValue = field.GetEditFormat(key);
                if (formatValue.IsNullOrEmpty())
                {
                    formatValue = string.Format("`{0}`", field.FieldName);
                    field.SetEditFormat(key, formatValue);
                }
                formatFields.Add(formatValue);

                //parameter name
                parameterSequence++;
                string parameterName = field.PropertyName + parameterSequence;
                parameterFields.Add("?" + parameterName);

                //parameter value
                cmdParameters?.Rename(field.PropertyName, parameterName);
            }
            return(new Tuple <List <string>, List <string>, CmdParameters>(formatFields, parameterFields, cmdParameters));
        }
        /// <summary>
        /// get insert execute DbCommand
        /// </summary>
        /// <param name="translator">translator</param>
        /// <param name="cmd">cmd</param>
        /// <returns></returns>
        DbExecuteCommand GetInsertExecuteDbCommand(IQueryTranslator translator, RdbCommand cmd)
        {
            string        cmdText     = string.Empty;
            CmdParameters parameters  = null;
            CommandType   commandType = GetCommandType(cmd);

            if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
            {
                cmdText    = cmd.CommandText;
                parameters = ParseParameters(cmd.Parameters);
            }
            else
            {
                string objectName         = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName);
                var    fields             = DataManager.GetEditFields(ServerType.SQLServer, cmd.EntityType);
                var    insertFormatResult = FormatInsertFields(fields, cmd.Parameters, translator.ParameterSequence);
                if (insertFormatResult == null)
                {
                    return(null);
                }
                cmdText = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2});"
                                        , objectName
                                        , string.Join(",", insertFormatResult.Item1)
                                        , string.Join(",", insertFormatResult.Item2));
                parameters = insertFormatResult.Item3;
                translator.ParameterSequence += fields.Count;
            }
            return(new DbExecuteCommand()
            {
                CommandText = cmdText,
                CommandType = commandType,
                ForceReturnValue = cmd.MustReturnValueOnSuccess,
                Parameters = parameters
            });
        }
Пример #3
0
        /// <summary>
        /// get delete execute command
        /// </summary>
        /// <param name="translator">translator</param>
        /// <param name="cmd">cmd</param>
        /// <returns></returns>
        DbExecuteCommand GetDeleteExecuteDbCommand(IQueryTranslator translator, RdbCommand cmd)
        {
            #region query translate

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

            #endregion

            string        cmdText    = string.Empty;
            CmdParameters parameters = ParseParameters(cmd.Parameters);
            if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
            {
                cmdText = cmd.CommandText;
            }
            else
            {
                string objectName = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
                cmdText = string.Format("{3}DELETE {0} FROM `{1}` AS {0}{4} {2};"
                                        , translator.ObjectPetName
                                        , objectName
                                        , conditionString
                                        , preScript
                                        , joinScript);
            }
            //combine parameters
            if (tranResult.Parameters != null)
            {
                var queryParameters = ParseParameters(tranResult.Parameters);
                if (parameters != null)
                {
                    parameters.Union(queryParameters);
                }
                else
                {
                    parameters = queryParameters;
                }
            }
            CommandType commandType = GetCommandType(cmd);
            return(new DbExecuteCommand()
            {
                CommandText = cmdText,
                CommandType = commandType,
                ForceReturnValue = cmd.MustReturnValueOnSuccess,
                Parameters = parameters,
                HasPreScript = !string.IsNullOrWhiteSpace(preScript)
            });
        }
Пример #4
0
        /// <summary>
        /// convert cmd parameters
        /// </summary>
        /// <param name="cmdParameters">cmd parameters</param>
        /// <returns></returns>
        DynamicParameters ConvertCmdParameters(CmdParameters cmdParameters)
        {
            if (cmdParameters?.Parameters.IsNullOrEmpty() ?? true)
            {
                return(null);
            }
            DynamicParameters dynamicParameters = new DynamicParameters();

            foreach (var item in cmdParameters.Parameters)
            {
                var parameter = item.Value;
                dynamicParameters.Add(parameter.Name, parameter.Value
                                      , parameter.DbType, parameter.ParameterDirection
                                      , parameter.Size, parameter.Precision
                                      , parameter.Scale);
            }
            return(dynamicParameters);
        }
Пример #5
0
        /// <summary>
        /// parse parameter
        /// </summary>
        /// <param name="originParameters">origin parameter</param>
        /// <returns></returns>
        CmdParameters ParseParameters(object originParameters)
        {
            if (originParameters == null)
            {
                return(null);
            }
            CmdParameters parameters = originParameters as CmdParameters;

            if (parameters != null)
            {
                return(parameters);
            }
            parameters = new CmdParameters();
            if (originParameters is IEnumerable <KeyValuePair <string, string> > )
            {
                var stringParametersDict = originParameters as IEnumerable <KeyValuePair <string, string> >;
                parameters.Add(stringParametersDict);
            }
            else if (originParameters is IEnumerable <KeyValuePair <string, dynamic> > )
            {
                var dynamicParametersDict = originParameters as IEnumerable <KeyValuePair <string, dynamic> >;
                parameters.Add(dynamicParametersDict);
            }
            else if (originParameters is IEnumerable <KeyValuePair <string, object> > )
            {
                var objectParametersDict = originParameters as IEnumerable <KeyValuePair <string, object> >;
                parameters.Add(objectParametersDict);
            }
            else if (originParameters is IEnumerable <KeyValuePair <string, IModifyValue> > )
            {
                var modifyParametersDict = originParameters as IEnumerable <KeyValuePair <string, IModifyValue> >;
                parameters.Add(modifyParametersDict);
            }
            else
            {
                var objectParametersDict = originParameters.ObjectToDcitionary();
                parameters.Add(objectParametersDict);
            }
            return(parameters);
        }
Пример #6
0
        /// <summary>
        /// execute command
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">server</param>
        /// <param name="executeOption">execute option</param>
        /// <param name="cmds">command</param>
        /// <returns>data numbers</returns>
        public async Task <int> ExecuteAsync(ServerInfo server, CommandExecuteOption executeOption, params ICommand[] cmds)
        {
            #region group execute commands

            IQueryTranslator        translator      = QueryTranslator.GetTranslator(server);
            List <DbExecuteCommand> executeCommands = new List <DbExecuteCommand>();
            var batchExecuteConfig   = DataManager.GetBatchExecuteConfig(server.ServerType) ?? BatchExecuteConfig.Default;
            var groupStatementsCount = batchExecuteConfig.GroupStatementsCount;
            groupStatementsCount = groupStatementsCount < 0 ? 1 : groupStatementsCount;
            var groupParameterCount = batchExecuteConfig.GroupParametersCount;
            groupParameterCount = groupParameterCount < 0 ? 1 : groupParameterCount;
            StringBuilder commandTextBuilder = new StringBuilder();
            CmdParameters parameters         = null;
            int           statementsCount    = 0;
            bool          forceReturnValue   = false;
            foreach (var cmd in cmds)
            {
                DbExecuteCommand executeCommand = GetExecuteDbCommand(translator, cmd as RdbCommand);
                if (executeCommand == null)
                {
                    continue;
                }
                if (executeCommand.PerformAlone)
                {
                    if (statementsCount > 0)
                    {
                        executeCommands.Add(new DbExecuteCommand()
                        {
                            CommandText      = commandTextBuilder.ToString(),
                            CommandType      = CommandType.Text,
                            ForceReturnValue = true,
                            Parameters       = parameters
                        });
                        statementsCount = 0;
                        translator.ParameterSequence = 0;
                        commandTextBuilder.Clear();
                        parameters = null;
                    }
                    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(new DbExecuteCommand()
                    {
                        CommandText      = commandTextBuilder.ToString(),
                        CommandType      = CommandType.Text,
                        ForceReturnValue = true,
                        Parameters       = parameters
                    });
                    statementsCount = 0;
                    translator.ParameterSequence = 0;
                    commandTextBuilder.Clear();
                    parameters = null;
                }
            }
            if (statementsCount > 0)
            {
                executeCommands.Add(new DbExecuteCommand()
                {
                    CommandText      = commandTextBuilder.ToString(),
                    CommandType      = CommandType.Text,
                    ForceReturnValue = true,
                    Parameters       = parameters
                });
            }

            #endregion

            return(await ExecuteCommandAsync(server, executeOption, executeCommands, executeOption?.ExecuteByTransaction ?? cmds.Length > 1).ConfigureAwait(false));
        }
Пример #7
0
        /// <summary>
        /// get update execute command
        /// </summary>
        /// <param name="translator">translator</param>
        /// <param name="cmd">cmd</param>
        /// <returns></returns>
        DbExecuteCommand GetUpdateExecuteDbCommand(IQueryTranslator translator, RdbCommand cmd)
        {
            #region query translate

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

            #endregion

            string        cmdText    = string.Empty;
            CmdParameters parameters = ParseParameters(cmd.Parameters);
            if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
            {
                cmdText = cmd.CommandText;
            }
            else
            {
                parameters = parameters ?? new CmdParameters();
                string        objectName        = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
                var           fields            = GetFields(cmd.EntityType, cmd.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 = 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 = GetCalculateChar(calculateModifyValue.Operator);
                                newValueExpression = string.Format("{0}.`{1}`{2}?{3}"
                                                                   , translator.ObjectPetName
                                                                   , field.FieldName
                                                                   , calChar
                                                                   , parameterName);
                            }
                        }
                    }
                    if (string.IsNullOrWhiteSpace(newValueExpression))
                    {
                        newValueExpression = "?" + parameterName;
                    }
                    updateSetArray.Add(string.Format("{0}.`{1}`={2}"
                                                     , translator.ObjectPetName
                                                     , field.FieldName
                                                     , newValueExpression));
                }
                cmdText = string.Format("{4}UPDATE `{2}` AS {0}{5} SET {1} {3};"
                                        , translator.ObjectPetName
                                        , string.Join(",", updateSetArray.ToArray())
                                        , objectName
                                        , conditionString
                                        , preScript
                                        , joinScript);
                translator.ParameterSequence = parameterSequence;
            }
            //combine parameters
            if (tranResult.Parameters != null)
            {
                var queryParameters = ParseParameters(tranResult.Parameters);
                if (parameters != null)
                {
                    parameters.Union(queryParameters);
                }
                else
                {
                    parameters = queryParameters;
                }
            }
            CommandType commandType = GetCommandType(cmd);
            return(new DbExecuteCommand()
            {
                CommandText = cmdText,
                CommandType = commandType,
                ForceReturnValue = cmd.MustReturnValueOnSuccess,
                Parameters = parameters,
                HasPreScript = !string.IsNullOrWhiteSpace(preScript)
            });
        }
Пример #8
0
        /// <summary>
        /// execute single command
        /// </summary>
        /// <param name="conn">db connection</param>
        /// <param name="cmd">command</param>
        /// <param name="tran">transaction</param>
        /// <returns></returns>
        int ExecuteSingleCommand(IQueryTranslator queryTranslator, IDbConnection conn, RdbCommand cmd, IDbTransaction tran = null)
        {
            int resultValue = 0;

            #region query object translate

            IDictionary <string, object> queryParameters = null;
            var    tranResult      = queryTranslator.Translate(cmd.Query);
            string conditionString = string.Empty;
            if (!tranResult.ConditionString.IsNullOrEmpty())
            {
                conditionString += "WHERE " + tranResult.ConditionString;
            }
            if (tranResult.Parameters != null)
            {
                if (tranResult.Parameters is IDictionary <string, object> )
                {
                    queryParameters = tranResult.Parameters as IDictionary <string, object>;
                }
                else
                {
                    queryParameters = tranResult.Parameters.ObjectToDcitionary();
                }
            }

            #endregion

            #region execute

            StringBuilder        cmdText          = new StringBuilder();
            object               newParameters    = cmd.Parameters;
            ExecuteCommandResult executeCmdResult = cmd.CommandResultType;
            if (cmd.ExecuteMode == CommandExecuteMode.CommandText)
            {
                cmdText.Append(cmd.CommandText);
            }
            else
            {
                switch (cmd.Operate)
                {
                case OperateType.Insert:
                    cmdText.AppendFormat("INSERT INTO `{0}` ({1}) VALUES ({2})", cmd.ObjectName, string.Join(",", FormatFields(cmd.Fields)), string.Join(",", cmd.Fields.Select(c => "?" + c)));
                    break;

                case OperateType.Update:
                    IDictionary <string, dynamic> oldCmdParameterDic = null;
                    IDictionary <string, dynamic> newCmdParameterDic = new Dictionary <string, dynamic>();
                    if (cmd.Parameters is IDictionary <string, dynamic> )
                    {
                        oldCmdParameterDic = cmd.Parameters as IDictionary <string, dynamic>;
                    }
                    else
                    {
                        oldCmdParameterDic = cmd.Parameters.ObjectToDcitionary();
                    }
                    List <string> updateSetArray = new List <string>();
                    foreach (var field in cmd.Fields)
                    {
                        if (oldCmdParameterDic == null || !oldCmdParameterDic.ContainsKey(field) || !(oldCmdParameterDic[field] is CalculateModify))
                        {
                            updateSetArray.Add(string.Format("{0}.`{1}`=?{2}", queryTranslator.ObjectPetName, field, field));
                            if (oldCmdParameterDic != null && oldCmdParameterDic.ContainsKey(field))
                            {
                                newCmdParameterDic.Add(field, oldCmdParameterDic[field]);
                            }
                        }
                        else if (oldCmdParameterDic[field] is CalculateModify)
                        {
                            CalculateModify calModify = oldCmdParameterDic[field] as CalculateModify;
                            newCmdParameterDic.Add(field, calModify.Value);
                            string calChar = GetCalculateChar(calModify.Calculate);
                            updateSetArray.Add(string.Format("{0}.`{1}`={0}.`{1}`{2}?{3}", queryTranslator.ObjectPetName, field, calChar, field));
                        }
                    }
                    newParameters = newCmdParameterDic;
                    cmdText.AppendFormat("{4}UPDATE `{2}` AS {0} SET {1} {3}", queryTranslator.ObjectPetName, string.Join(",", updateSetArray.ToArray()), cmd.ObjectName, conditionString, tranResult.PreScript);
                    break;

                case OperateType.Delete:
                    cmdText.AppendFormat("{3}DELETE {0} FROM `{1}` AS {0} {2}", queryTranslator.ObjectPetName, cmd.ObjectName, conditionString, tranResult.PreScript);
                    break;

                case OperateType.Exist:
                    cmdText.AppendFormat("SELECT EXISTS(SELECT {0}.`{1}` FROM `{2}` AS {0} {3})", queryTranslator.ObjectPetName, cmd.Fields.ElementAt(0), cmd.ObjectName, conditionString);
                    executeCmdResult = ExecuteCommandResult.ExecuteScalar;
                    break;

                default:
                    break;
                }
            }
            if (cmdText.Length <= 0)
            {
                return(0);
            }

            #endregion

            #region parameter opertion

            object parameters = queryParameters;
            if (newParameters != null)
            {
                CmdParameters cmdParameters = newParameters as CmdParameters;
                if (cmdParameters != null)
                {
                    DynamicParameters dynamicParameters = new DynamicParameters();
                    foreach (var item in cmdParameters.Parameters)
                    {
                        dynamicParameters.Add(item.Key, item.Value.Value, item.Value.DbType, item.Value.ParameterDirection, item.Value.Size, item.Value.Precision, item.Value.Scale);
                    }
                    if (queryParameters != null)
                    {
                        foreach (var parameter in queryParameters)
                        {
                            dynamicParameters.Add(parameter.Key, parameter.Value);
                        }
                    }
                    parameters = dynamicParameters;
                }
                else if (newParameters is IDictionary <string, dynamic> )
                {
                    IDictionary <string, dynamic> cmdParametersDic = newParameters as IDictionary <string, dynamic>;
                    if (queryParameters != null)
                    {
                        foreach (var queryParaItem in queryParameters)
                        {
                            cmdParametersDic.Add(queryParaItem.Key, queryParaItem.Value);
                        }
                    }
                    parameters = cmdParametersDic;
                }
                else
                {
                    IDictionary <string, object> cmdParametersDic = newParameters.ObjectToDcitionary();
                    if (queryParameters != null)
                    {
                        foreach (var queryParaItem in queryParameters)
                        {
                            cmdParametersDic.Add(queryParaItem.Key, queryParaItem.Value);
                        }
                    }
                    parameters = cmdParametersDic;
                }
            }

            #endregion

            switch (executeCmdResult)
            {
            case ExecuteCommandResult.ExecuteScalar:
                resultValue = conn.ExecuteScalar <int>(cmdText.ToString(), parameters, transaction: tran, commandType: GetCommandType(cmd));
                break;

            case ExecuteCommandResult.ExecuteRows:
                resultValue = conn.Execute(cmdText.ToString(), parameters, transaction: tran, commandType: GetCommandType(cmd));
                break;
            }
            return(resultValue);
        }