示例#1
0
        /// <summary>
        /// determine whether data has existed
        /// </summary>
        /// <param name="server">server</param>
        /// <param name="cmd">command</param>
        /// <returns>data has existed</returns>
        public async Task <bool> QueryAsync(ServerInfo server, ICommand cmd)
        {
            var translator = QueryTranslator.GetTranslator(server);

            #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

            var    field      = DataManager.GetDefaultField(ServerType.MySQL, cmd.EntityType);
            string objectName = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
            string cmdText    = string.Format("{5}SELECT EXISTS(SELECT {0}.`{1}` FROM `{2}` AS {0}{4} {3})"
                                              , translator.ObjectPetName
                                              , field.FieldName
                                              , objectName
                                              , conditionString
                                              , joinScript
                                              , preScript);
            using (var conn = DbServerFactory.GetConnection(server))
            {
                var tran  = GetQueryTransaction(conn, cmd.Query);
                int value = await conn.ExecuteScalarAsync <int>(cmdText, tranResult.Parameters, transaction : tran).ConfigureAwait(false);

                return(value > 0);
            }
        }
        /// <summary>
        /// query data list offset the specified numbers
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">database server</param>
        /// <param name="cmd">command</param>
        /// <param name="offsetNum">offset num</param>
        /// <param name="size">query size</param>
        /// <returns></returns>
        public async Task <IEnumerable <T> > QueryOffsetAsync <T>(ServerInfo server, ICommand cmd, int offsetNum = 0, int size = int.MaxValue)
        {
            if (cmd.Query == null)
            {
                throw new EZNEWException("ICommand.Query is null");
            }

            #region query object translate

            IQueryTranslator translator = QueryTranslator.GetTranslator(server);
            var tranResult = translator.Translate(cmd.Query);

            #endregion

            #region execute

            string        joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
            StringBuilder cmdText    = new StringBuilder();
            switch (cmd.Query.QueryType)
            {
            case QueryCommandType.Text:
                cmdText.Append(tranResult.ConditionString);
                break;

            case QueryCommandType.QueryObject:
            default:
                string        objectName        = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName);
                string        defaultFieldName  = string.Empty;
                List <string> formatQueryFields = FormatQueryFields(translator.ObjectPetName, cmd.Query, cmd.EntityType, out defaultFieldName);
                cmdText.AppendFormat("{4}SELECT COUNT({3}.[{0}]) OVER() AS QueryDataTotalCount,{1} FROM [{2}] AS {3}{5}"
                                     , defaultFieldName
                                     , string.Join(",", formatQueryFields)
                                     , objectName
                                     , translator.ObjectPetName
                                     , tranResult.PreScript
                                     , joinScript);
                if (!tranResult.ConditionString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
                }
                if (!tranResult.OrderString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
                }
                else
                {
                    cmdText.AppendFormat(" ORDER BY {0}.[{1}] DESC", translator.ObjectPetName, defaultFieldName);
                }
                cmdText.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offsetNum, size);
                break;
            }

            #endregion

            using (var conn = DbServerFactory.GetConnection(server))
            {
                var tran = GetQueryTransaction(conn, cmd.Query);
                return(await conn.QueryAsync <T>(cmdText.ToString(), tranResult.Parameters, transaction : tran, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
            }
        }
        /// <summary>
        /// Aggregate Function
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">database server</param>
        /// <param name="cmd">command</param>
        /// <returns></returns>
        async Task <T> AggregateFunctionAsync <T>(ServerInfo server, ICommand cmd)
        {
            cmd.Query = cmd.Query ?? QueryFactory.Create();

            #region query object translate

            IQueryTranslator translator = QueryTranslator.GetTranslator(server);
            var tranResult = translator.Translate(cmd.Query);

            #endregion

            #region execute

            StringBuilder cmdText    = new StringBuilder();
            string        joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
            switch (cmd.Query.QueryType)
            {
            case QueryCommandType.Text:
                cmdText.Append(tranResult.ConditionString);
                break;

            case QueryCommandType.QueryObject:
            default:
                string funcName = GetAggregateFunctionName(cmd.Operate);
                if (funcName.IsNullOrEmpty() || cmd.Fields.IsNullOrEmpty())
                {
                    return(default(T));
                }
                string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName);
                if (cmd.Query.QueryFields.IsNullOrEmpty())
                {
                    throw new EZNEWException("must set one field");
                }
                var field = DataManager.GetField(ServerType.SQLServer, cmd.EntityType, cmd.Query.QueryFields[0]);
                cmdText.AppendFormat("{4}SELECT {0}({3}.[{1}]) FROM [{2}] AS {3}{5}"
                                     , funcName
                                     , field.FieldName
                                     , objectName
                                     , translator.ObjectPetName
                                     , tranResult.PreScript
                                     , joinScript);
                if (!tranResult.ConditionString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
                }
                if (!tranResult.OrderString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
                }
                break;
            }

            #endregion

            using (var conn = DbServerFactory.GetConnection(server))
            {
                return(await conn.ExecuteScalarAsync <T>(cmdText.ToString(), tranResult.Parameters, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
            }
        }
示例#4
0
        /// <summary>
        /// query data list
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">database server</param>
        /// <param name="cmd">command</param>
        /// <returns>data list</returns>
        public async Task <IEnumerable <T> > QueryAsync <T>(ServerInfo server, ICommand cmd)
        {
            if (cmd.Query == null)
            {
                throw new EZNEWException("ICommand.Query is null");
            }

            #region query object translate

            IQueryTranslator translator = QueryTranslator.GetTranslator(server);
            var    tranResult           = translator.Translate(cmd.Query);
            string joinScript           = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;

            #endregion

            #region execute

            StringBuilder cmdText = new StringBuilder();
            switch (cmd.Query.QueryType)
            {
            case QueryCommandType.Text:
                cmdText.Append(tranResult.ConditionString);
                break;

            case QueryCommandType.QueryObject:
            default:
                int    size       = cmd.Query.QuerySize;
                string objectName = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
                cmdText.AppendFormat("{3}SELECT {1} FROM `{2}` AS {0}{4}"
                                     , translator.ObjectPetName
                                     , string.Join(",", FormatQueryFields(translator.ObjectPetName, cmd.Query, cmd.EntityType, out var defaultFieldName))
                                     , objectName
                                     , tranResult.PreScript
                                     , joinScript);
                if (!tranResult.ConditionString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
                }
                if (!tranResult.OrderString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
                }
                if (size > 0)
                {
                    cmdText.AppendFormat(" LIMIT 0,{0}", size);
                }
                break;
            }

            #endregion

            using (var conn = DbServerFactory.GetConnection(server))
            {
                var tran = GetQueryTransaction(conn, cmd.Query);
                return(await conn.QueryAsync <T>(cmdText.ToString(), tranResult.Parameters, transaction : tran, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
            }
        }
        /// <summary>
        /// query data list
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">database server</param>
        /// <param name="cmd">command</param>
        /// <returns>data list</returns>
        public async Task <IEnumerable <T> > QueryAsync <T>(ServerInfo server, ICommand cmd)
        {
            cmd.Query = cmd.Query ?? QueryFactory.Create();

            #region query object translate

            IQueryTranslator translator = QueryTranslator.GetTranslator(server);
            var    tranResult           = translator.Translate(cmd.Query);
            string preScript            = tranResult.PreScript;
            string joinScript           = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;

            #endregion

            #region execute

            StringBuilder cmdText = new StringBuilder();
            switch (cmd.Query.QueryType)
            {
            case QueryCommandType.Text:
                cmdText.Append(tranResult.ConditionString);
                break;

            case QueryCommandType.QueryObject:
            default:
                int    size       = cmd.Query == null ? 0 : cmd.Query.QuerySize;
                string objectName = DataManager.GetEntityObjectName(ServerType.SQLServer, cmd.EntityType, cmd.ObjectName);
                cmdText.AppendFormat("{4}SELECT {0} {1} FROM [{2}] AS {3}{5}"
                                     , size > 0 ? "TOP " + size : string.Empty
                                     , string.Join(",", FormatQueryFields(translator.ObjectPetName, cmd.Query, cmd.EntityType, out var defaultFieldName))
                                     , objectName
                                     , translator.ObjectPetName
                                     , preScript
                                     , joinScript);
                if (!tranResult.ConditionString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
                }
                if (!tranResult.OrderString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
                }
                break;
            }

            #endregion

            using (var conn = DbServerFactory.GetConnection(server))
            {
                return(await conn.QueryAsync <T>(cmdText.ToString(), tranResult.Parameters, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
            }
        }
示例#6
0
        /// <summary>
        /// execute by nonee transaction
        /// </summary>
        /// <param name="serverInfo">database server</param>
        /// <param name="cmds">command</param>
        /// <returns></returns>
        int ExecuteByNoneTransaction(ServerInfo serverInfo, IEnumerable <RdbCommand> cmds)
        {
            IQueryTranslator queryTranslator = QueryTranslator.GetTranslator(serverInfo);

            using (var conn = DbServerFactory.GetConnection(serverInfo))
            {
                int executeRows = 0;
                foreach (var cmd in cmds)
                {
                    executeRows += ExecuteSingleCommand(queryTranslator, conn, cmd);
                }
                return(executeRows);
            }
        }
示例#7
0
        /// <summary>
        /// query data list
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">database server</param>
        /// <param name="cmd">command</param>
        /// <returns>data list</returns>
        public IEnumerable <T> Query <T>(ServerInfo server, ICommand cmd)
        {
            var verifyResults = VerifyCommand(cmd);

            cmd.Query = cmd.Query ?? QueryFactory.Create();

            #region query object translate

            IQueryTranslator queryTranslator = QueryTranslator.GetTranslator(server);
            var tranResult = queryTranslator.Translate(cmd.Query);

            #endregion

            #region execute

            StringBuilder cmdText = new StringBuilder();
            switch (cmd.Query.QueryType)
            {
            case QueryCommandType.Text:
                cmdText.Append(tranResult.ConditionString);
                break;

            case QueryCommandType.QueryObject:
            default:
                int    size           = cmd.Query == null ? 0 : cmd.Query.QuerySize;
                string fieldSplitChar = string.Format(",{0}.", queryTranslator.ObjectPetName);
                cmdText.AppendFormat("{3}SELECT {0}.{1} FROM `{2}` AS {0}", queryTranslator.ObjectPetName, string.Join(fieldSplitChar, FormatFields(cmd.Fields)), cmd.ObjectName, tranResult.PreScript);
                if (!tranResult.ConditionString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
                }
                if (!tranResult.OrderString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
                }
                if (size > 0)
                {
                    cmdText.AppendFormat(" LIMIT 0,{0}", size);
                }
                break;
            }

            #endregion

            using (var conn = DbServerFactory.GetConnection(server))
            {
                return(conn.Query <T>(cmdText.ToString(), tranResult.Parameters, commandType: GetCommandType(verifyResults.Item1.First())));
            }
        }
        /// <summary>
        /// query data list offset the specified numbers
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">database server</param>
        /// <param name="cmd">command</param>
        /// <param name="offsetNum">offset num</param>
        /// <param name="size">query size</param>
        /// <returns></returns>
        public IEnumerable <T> QueryOffset <T>(ServerInfo server, ICommand cmd, int offsetNum = 0, int size = int.MaxValue)
        {
            var verifyResults = VerifyCommand(cmd);

            cmd.Query = cmd.Query ?? QueryFactory.Create();

            #region query object translate

            IQueryTranslator queryTranslator = QueryTranslator.GetTranslator(server);
            var tranResult = queryTranslator.Translate(cmd.Query);

            #endregion

            #region execute

            StringBuilder cmdText = new StringBuilder();
            switch (cmd.Query.QueryType)
            {
            case QueryCommandType.Text:
                cmdText.Append(tranResult.ConditionString);
                break;

            case QueryCommandType.QueryObject:
            default:
                string fieldSplitChar = string.Format(",{0}.", queryTranslator.ObjectPetName);
                cmdText.AppendFormat("{4}SELECT COUNT({3}.[{0}]) OVER() AS PagingTotalCount,{3}.{1} FROM [{2}] AS {3}", cmd.Fields.ElementAt(0), string.Join(fieldSplitChar, FormatFields(cmd.Fields)), cmd.ObjectName, queryTranslator.ObjectPetName, tranResult.PreScript);
                if (!tranResult.ConditionString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
                }
                if (!tranResult.OrderString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
                }
                else
                {
                    cmdText.AppendFormat(" ORDER BY {0}.[{1}] DESC", queryTranslator.ObjectPetName, cmd.Fields.ElementAt(0));
                }
                cmdText.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offsetNum, size);
                break;
            }

            #endregion

            using (var conn = DbServerFactory.GetConnection(server))
            {
                return(conn.Query <T>(cmdText.ToString(), tranResult.Parameters, commandType: GetCommandType(verifyResults.Item1.First())));
            }
        }
示例#9
0
        /// <summary>
        /// Aggregate Function
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">database server</param>
        /// <param name="cmd">command</param>
        /// <returns></returns>
        T AggregateFunction <T>(ServerInfo server, ICommand cmd)
        {
            var verifyResults = VerifyCommand(cmd);

            cmd.Query = cmd.Query ?? QueryFactory.Create();

            #region query object translate

            IQueryTranslator queryTranslator = QueryTranslator.GetTranslator(server);
            var tranResult = queryTranslator.Translate(cmd.Query);

            #endregion

            #region query

            StringBuilder cmdText = new StringBuilder();
            switch (cmd.Query.QueryType)
            {
            case QueryCommandType.Text:
                cmdText.Append(tranResult.ConditionString);
                break;

            case QueryCommandType.QueryObject:
            default:
                string funcName = GetAggregateFunctionName(cmd.Operate);
                if (funcName.IsNullOrEmpty() || cmd.Fields.IsNullOrEmpty())
                {
                    return(default(T));
                }
                cmdText.AppendFormat("{4}SELECT {0}({3}.`{1}`) FROM `{2}` AS {3}", funcName, cmd.Fields.ElementAt(0), cmd.ObjectName, queryTranslator.ObjectPetName, tranResult.PreScript);
                if (!tranResult.ConditionString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
                }
                if (!tranResult.OrderString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
                }
                break;
            }

            #endregion

            using (var conn = DbServerFactory.GetConnection(server))
            {
                return(conn.ExecuteScalar <T>(cmdText.ToString(), tranResult.Parameters, commandType: GetCommandType(verifyResults.Item1.First())));
            }
        }
示例#10
0
        /// <summary>
        /// execute by transaction
        /// </summary>
        /// <param name="serverInfo">database server</param>
        /// <param name="cmds">commands</param>
        /// <returns></returns>
        int ExecuteByTransaction(ServerInfo serverInfo, IEnumerable <RdbCommand> cmds)
        {
            IQueryTranslator queryTranslator = QueryTranslator.GetTranslator(serverInfo);

            using (var conn = DbServerFactory.GetConnection(serverInfo))
            {
                conn.Open();
                var  tran        = conn.BeginTransaction();
                bool result      = true;
                int  resultValue = 0;
                try
                {
                    foreach (var cmd in cmds)
                    {
                        int cmdResult = ExecuteSingleCommand(queryTranslator, conn, cmd, tran);
                        resultValue += cmdResult;
                        if (cmd.VerifyResult != null)
                        {
                            result = result && cmd.VerifyResult(cmdResult);
                        }
                    }
                    if (result)
                    {
                        tran.Commit();
                    }
                    else
                    {
                        tran.Rollback();
                        resultValue = 0;
                    }
                    return(resultValue);
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
        }
示例#11
0
        /// <summary>
        /// query single value
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">database server</param>
        /// <param name="cmd">command</param>
        /// <returns>query data</returns>
        public async Task <T> AggregateValueAsync <T>(ServerInfo server, ICommand cmd)
        {
            if (cmd.Query == null)
            {
                throw new EZNEWException("ICommand.Query is null");
            }

            #region query object translate

            IQueryTranslator translator = QueryTranslator.GetTranslator(server);
            var tranResult = translator.Translate(cmd.Query);

            #endregion

            #region query

            StringBuilder cmdText    = new StringBuilder();
            string        joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
            switch (cmd.Query.QueryType)
            {
            case QueryCommandType.Text:
                cmdText.Append(tranResult.ConditionString);
                break;

            case QueryCommandType.QueryObject:
            default:
                string funcName = GetAggregateFunctionName(cmd.Operate);
                if (funcName.IsNullOrEmpty())
                {
                    return(default(T));
                }

                #region field

                EntityField field = null;
                if (AggregateOperateMustNeedField(cmd.Operate))
                {
                    if (cmd.Query.QueryFields.IsNullOrEmpty())
                    {
                        throw new EZNEWException(string.Format("You must specify the field to perform for the {0} operation", funcName));
                    }
                    else
                    {
                        field = DataManager.GetField(ServerType.MySQL, cmd.EntityType, cmd.Query.QueryFields[0]);
                    }
                }
                else
                {
                    field = DataManager.GetDefaultField(ServerType.MySQL, cmd.EntityType);
                }

                #endregion

                string objectName = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
                cmdText.AppendFormat("{4}SELECT {0}({1}) FROM `{2}` AS {3}{5}"
                                     , funcName
                                     , FormatField(translator.ObjectPetName, field)
                                     , objectName
                                     , translator.ObjectPetName
                                     , tranResult.PreScript
                                     , joinScript
                                     );
                if (!tranResult.ConditionString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" WHERE {0}", tranResult.ConditionString);
                }
                if (!tranResult.OrderString.IsNullOrEmpty())
                {
                    cmdText.AppendFormat(" ORDER BY {0}", tranResult.OrderString);
                }
                break;
            }

            #endregion

            using (var conn = DbServerFactory.GetConnection(server))
            {
                var tran = GetQueryTransaction(conn, cmd.Query);
                return(await conn.ExecuteScalarAsync <T>(cmdText.ToString(), tranResult.Parameters, transaction : tran, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
            }
        }
示例#12
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));
        }
示例#13
0
        /// <summary>
        /// query data list offset the specified numbers
        /// </summary>
        /// <typeparam name="T">data type</typeparam>
        /// <param name="server">database server</param>
        /// <param name="cmd">command</param>
        /// <param name="offsetNum">offset num</param>
        /// <param name="size">query size</param>
        /// <returns></returns>
        public async Task <IEnumerable <T> > QueryOffsetAsync <T>(ServerInfo server, ICommand cmd, int offsetNum = 0, int size = int.MaxValue)
        {
            if (cmd.Query == null)
            {
                throw new EZNEWException("ICommand.Query is null");
            }

            #region query object translate

            IQueryTranslator translator = QueryTranslator.GetTranslator(server);
            var tranResult = translator.Translate(cmd.Query);

            #endregion

            #region execute

            string        joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty;
            StringBuilder cmdText    = new StringBuilder();
            switch (cmd.Query.QueryType)
            {
            case QueryCommandType.Text:
                cmdText.Append(tranResult.ConditionString);
                break;

            case QueryCommandType.QueryObject:
            default:
                string        conditionString   = string.Empty;
                string        orderString       = string.Empty;
                string        limitString       = string.Empty;
                string        objectName        = DataManager.GetEntityObjectName(ServerType.MySQL, cmd.EntityType, cmd.ObjectName);
                string        defaultFieldName  = string.Empty;
                List <string> formatQueryFields = FormatQueryFields(translator.ObjectPetName, cmd.Query, cmd.EntityType, out defaultFieldName);
                if (!tranResult.ConditionString.IsNullOrEmpty())
                {
                    conditionString = string.Format(" WHERE {0}", tranResult.ConditionString);
                }
                if (!tranResult.OrderString.IsNullOrEmpty())
                {
                    orderString = string.Format(" ORDER BY {0}", tranResult.OrderString);
                }
                limitString = string.Format(" LIMIT {0},{1}", offsetNum, size);
                cmdText.AppendFormat("{7}SELECT (SELECT COUNT({3}.`{0}`) FROM `{2}` AS {3}{8} {4}) AS PagingTotalCount,{1} FROM `{2}` AS {3}{8} {4} {5} {6}"
                                     , defaultFieldName
                                     , string.Join(",", formatQueryFields)
                                     , objectName
                                     , translator.ObjectPetName
                                     , conditionString
                                     , orderString
                                     , limitString
                                     , tranResult.PreScript
                                     , joinScript);
                break;
            }

            #endregion

            using (var conn = DbServerFactory.GetConnection(server))
            {
                return(await conn.QueryAsync <T>(cmdText.ToString(), tranResult.Parameters, commandType : GetCommandType(cmd as RdbCommand)).ConfigureAwait(false));
            }
        }