コード例 #1
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);
            }
        }
コード例 #2
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 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())));
            }
        }
コード例 #3
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 execute

            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())));
            }
        }
コード例 #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 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);
            string preScript  = tranResult.PreScript;

            #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("{4}SELECT {0} {3}.{1} FROM [{2}] AS {3}", size > 0 ? "TOP " + size : "", string.Join(fieldSplitChar, FormatFields(cmd.Fields)), cmd.ObjectName, queryTranslator.ObjectPetName, 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.Query <T>(cmdText.ToString(), tranResult.Parameters, commandType: GetCommandType(verifyResults.Item1.First())));
            }
        }
コード例 #5
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;
                }
            }
        }