C# SQL.ONSqlSelect类代码示例

        public override void FilterInData(ONSqlSelect onSql, ONDBData data)
        {
            if (InLegacy)
                return;

            //Fix Instance
            data.InhFixInstance(onSql, null, null, mOid);
        }

        public override void FilterInData(ONSqlSelect onSql, ONDBData data)
        {
            if (InLegacy)
            return;

               	data.InhAddPath(onSql, mRelatedOid.ClassName, ONPath, "");

            //Fix Instance
              	ONDBData lData = ONContext.GetComponent_Data(mRelatedOid.ClassName, data.OnContext) as ONDBData;
               	lData.InhFixInstance(onSql, null, ONPath, mRelatedOid);
        }

        public override void FilterInData(ONSqlSelect onSql, ONDBData data)
        {
            if (IsUnableToFilterInData(data.OnContext))
                return;

            #region Horizontal visibility for agent 'Administrador'
            if(data.OnContext.LeafActiveAgentFacets.Contains("Administrador"))
            {
                // No Horizontal Visibility formula
            }
            #endregion Horizontal visibility for agent 'Administrador'
        }

        /// <summary>This method adds to the SQL statement any path that appears in a formula</summary>
        /// <param name="onSql">This parameter has the current SQL statement</param>
        /// <param name="joinType">This parameter has the type of join</param>
        /// <param name="facet">First class, the beginning of the path</param>
        /// <param name="onPath">Path to add to SQL statement</param>
        /// <param name="processedOnPath">Path pocessed until the call of this method</param>
        /// <param name="initialClass">Domain of the object valued argument, object valued filter variables or AGENT when it should be necessary</param>
        /// <param name="forceLastAlias">Create almost the last alias in the sql</param>
        public static string AddPath(ONSqlSelect onSql, JoinType joinType, string facet, ONPath onPath, ONPath processedOnPath, string initialClass, bool forceLastAlias)
        {
            // initialClass is used for Object-valued arguments, object-valued filter variables, agent instance, ...
            ONPath lProcessedOnPath = new ONPath(processedOnPath);
            ONPath lOnPath = new ONPath(onPath);

            // Calculate processed path
            string lRole = lOnPath.RemoveHead() as string;
            lProcessedOnPath += lRole;

            // Search Path
            if (lOnPath.Count == 0)
            {
                string lAlias = onSql.GetAlias(facet, lProcessedOnPath);
                if ((lAlias != "") && (!forceLastAlias))
                    return (lAlias);
            }
             			// Create path
            if (initialClass == "") // Simple paths
            {
                if (string.Compare(lRole, "NaveNodriza", true) == 0)
                    return NaveNodrizaData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
                if (string.Compare(lRole, "Aeronave", true) == 0)
                    return AeronaveData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
                if (string.Compare(lRole, "Pasajero", true) == 0)
                    return PasajeroData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
                if (string.Compare(lRole, "PasajeroAeronave", true) == 0)
                    return PasajeroAeronaveData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
                if (string.Compare(lRole, "Revision", true) == 0)
                    return RevisionData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
                if (string.Compare(lRole, "RevisionPasajero", true) == 0)
                    return RevisionPasajeroData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
                if (string.Compare(lRole, "Administrador", true) == 0)
                    return AdministradorData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias);
            }

            // Solve path with initialPath
            object[] lParameters = new object[6];
            lParameters[0] = onSql;
            lParameters[1] = facet;
            lParameters[2] = lOnPath;
            lParameters[3] = lProcessedOnPath;
            lParameters[4] = "";
            lParameters[5] = forceLastAlias;

            return ONContext.InvoqueMethod(ONContext.GetType_Data(initialClass), "AddPath", lParameters) as string;
        }

 public override string InhAddPath(ONSqlSelect onSql, string facet, ONPath onPath, string initialClass, bool isLinkedTo)
 {
     return AddPath(onSql, JoinType.InnerJoin, facet, onPath, null, initialClass, false, isLinkedTo);
 }

 public virtual void InhFixInstance(ONSqlSelect onSql, ONPath onPath, ONPath processedOnPath, ONOid oid, bool isLinkedTo)
 {
 }

        private static string PasajeroAeronaveRoleAddSql(ONSqlSelect onSql, JoinType joinType, string facet, ONPath onPath, ONPath processedPath, string role, bool force, bool isLinkedTo)
        {
            ONPath lOnPath = new ONPath(processedPath);
            lOnPath += role;

            //Source table
            string lAliasProcessed = onSql.GetAlias("Aeronave", processedPath, isLinkedTo);
            if (lAliasProcessed == "")
            {
                force = false;
                lAliasProcessed = onSql.CreateAlias(joinType, lAliasProcessed, CtesBD.TBL_AERONAVE, processedPath, "Aeronave", force, isLinkedTo);
            }

            //Target table
            string lAlias = onSql.GetAlias("PasajeroAeronave", lOnPath, isLinkedTo);
            if (lAlias == "")
            {
                force = false;
                lAlias = onSql.CreateAlias(joinType, lAliasProcessed, CtesBD.TBL_PASAJEROAERONAVE, lOnPath, "PasajeroAeronave", force, isLinkedTo);
                onSql.AddAliasWhere(lAlias, lAliasProcessed + "." + CtesBD.FLD_AERONAVE_ID_AERONAVE + "=" + lAlias + "." + CtesBD.FLD_PASAJEROAERONAVE_FK_AERONAVE_1);
            }
            //Target path
            if ((((object) onPath == null) || (onPath.Count == 0)) && (string.Compare("PasajeroAeronave", facet, true) == 0) && (!force))
                return lAlias;

            return PasajeroAeronaveData.AddPath(onSql, joinType, facet, onPath, lOnPath, "", force, isLinkedTo);
        }

 private static string AeronaveFacetAddSql(JoinType joinType, ONSqlSelect onSql, ONPath onPath, bool force)
 {
     return AeronaveFacetAddSql(joinType, onSql, onPath, force, false);
 }

        ///<summary> This method adds the order criteria to the SQL statement </summary>
        ///<param name = "onSql"> This parameter represents the SQL component </param>
        ///<param name = "comparer"> This parameter has all the information refering to the order criteria to add to SQL statement</param>
        /// <param name="startRowOid">This parameter has the OID necessary to start the search</param>
        /// <param name="blockSize">This parameter represents the number of instances to be returned</param>
        protected override void AddOrderCriteria(ONSqlSelect onSql, ONOrderCriteria comparer, ONOid startRowOid, int blockSize, ONPath initialPath)
        {
            //Initilizate StartRow
            AeronaveInstance lInstance = null;
            if (startRowOid != null)
            {
                lInstance = new AeronaveInstance(OnContext);
                lInstance.Oid = startRowOid as AeronaveOid;
            }

            //Default OrderCriteria
            if (comparer == null)
            {
                string lAlias = onSql.GetAlias("Aeronave", initialPath);
                if (lInstance != null)
                {
                    onSql.AddOrderBy(lAlias, CtesBD.FLD_AERONAVE_ID_AERONAVE, OrderByTypeEnumerator.Asc, lInstance.Oid.Id_AeronaveAttr);
                }
                else
                {
                    onSql.AddOrderBy(lAlias, CtesBD.FLD_AERONAVE_ID_AERONAVE, OrderByTypeEnumerator.Asc, null);
                }
                return;
            }

            //Add OrderCriteria
            bool lUseStartRow = (!comparer.InMemory);
            foreach (ONOrderCriteriaItem lOrderCriteriaItem in comparer.OrderCriteriaSqlItem)
            {
                ONPath lPath = new ONPath(lOrderCriteriaItem.OnPath);
                if((lInstance != null) && (lUseStartRow))
                {
                    ONSimpleType lAttrStartRow = null;

                    if (lPath.Path == "")
                        lAttrStartRow = lInstance[lOrderCriteriaItem.Attribute] as ONSimpleType;
                    else
                    {
                        ONCollection lCollection = (lInstance[lPath.Path] as ONCollection);
                        if((lCollection != null) && (lCollection.Count > 0))
                            lAttrStartRow = lCollection[0][lOrderCriteriaItem.Attribute] as ONSimpleType;
                    }
                    onSql.AddOrderBy(AeronaveData.AddPath(onSql, JoinType.LeftJoin, lOrderCriteriaItem.Facet, lPath, null, lOrderCriteriaItem.DomainArgument, false), lOrderCriteriaItem.Attribute, lOrderCriteriaItem.Type, lAttrStartRow);
                    lUseStartRow = (lAttrStartRow != null);
                }
                else
                    onSql.AddOrderBy(AeronaveData.AddPath(onSql, JoinType.LeftJoin, lOrderCriteriaItem.Facet, lPath, null, lOrderCriteriaItem.DomainArgument, false), lOrderCriteriaItem.Attribute, lOrderCriteriaItem.Type, null);
            }
            return;
        }

 public override string InhRetrieveInstances(ONSqlSelect onSql, ONDisplaySet displaySet, ONPath onPath, ONContext onContext)
 {
     return RetrieveInstances(onSql, displaySet, onPath, onContext);
 }

        /// <summary>This method adds to the SQL statement any path that appears in a formula</summary>
        /// <param name="onSql">This parameter has the current SQL statement</param>
        /// <param name="joinType">This parameter has the type of join</param>
        /// <param name="facet">First class, the beginning of the path</param>
        /// <param name="onPath">Path to add to SQL statement</param>
        /// <param name="processedOnPath">Path pocessed until the call of this method</param>
        /// <param name="initialClass">Domain of the object valued argument, object valued filter variables or AGENT when it should be necessary</param>
        /// <param name="forceLastAlias">Create almost the last alias in the sql</param>
        /// <param name="isLinkedTo">The alias belongs to a role in a linked To element</param>
        public static string AddPath(ONSqlSelect onSql, JoinType joinType, string facet, ONPath onPath, ONPath processedOnPath, string initialClass, bool forceLastAlias, bool isLinkedTo)
        {
            // initialClass is used for Object-valued arguments, object-valued filter variables, agent instance, ...
            ONPath lProcessedOnPath = new ONPath(processedOnPath);
            ONPath lOnPath = new ONPath(onPath);
            bool lOnPathExist = true;
            object[] lParameters = new object[8];

            if (initialClass != "")
            {
                string lRol = lOnPath.RemoveHead();
                lProcessedOnPath += lRol;
                // Solve path with initialPath
                lParameters[0] = onSql;
                lParameters[1] = joinType;
                lParameters[2] = facet;
                lParameters[3] = lOnPath;
                lParameters[4] = lProcessedOnPath;
                lParameters[5] = "";
                lParameters[6] = forceLastAlias;
                lParameters[7] = isLinkedTo;

                return ONContext.InvoqueMethod(ONContext.GetType_Data(initialClass), "AddPath", lParameters) as string;

            }

            // Search max solved path
            ONPath lMaxSolvedPath = new ONPath(onPath);
            string lMaxSolvedPathDomain = facet;
            while ((lMaxSolvedPath.Count > 0) && (onSql.GetAlias(lMaxSolvedPathDomain, lProcessedOnPath + lMaxSolvedPath, isLinkedTo) == ""))
            {
                lMaxSolvedPath.RemoveTail();
                lMaxSolvedPathDomain = GetTargetClassName(lMaxSolvedPath);
            }
            if (lMaxSolvedPath.Count > 0)
            {
                lProcessedOnPath += lMaxSolvedPath;
                for (int i = 0; i < lMaxSolvedPath.Count; i++)
                    lOnPath.RemoveHead();

                lParameters[0] = onSql;
                lParameters[1] = joinType;
                lParameters[2] = facet;
                lParameters[3] = lOnPath;
                lParameters[4] = lProcessedOnPath;
                lParameters[5] = "";
                lParameters[6] = forceLastAlias;
                lParameters[7] = isLinkedTo;

                return ONContext.InvoqueMethod(ONContext.GetType_Data(lMaxSolvedPathDomain), "AddPath", lParameters) as string;
            }

            // Create inheritance path
            if ((onPath == null) || (onPath.Count == 0))
            {
                if (forceLastAlias)
                    return AeronaveFacetAddSql(joinType, onSql, processedOnPath, forceLastAlias, isLinkedTo);

                if ((processedOnPath == null) || (processedOnPath.Count == 0))
                    return (onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, null, "Aeronave", false, isLinkedTo));
                else
                    return (onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, processedOnPath, "Aeronave", false, isLinkedTo));
            }

            // Calculate processed path
            string lRole = lOnPath.RemoveHead() as string;
            lProcessedOnPath += lRole;

            // Search Path
            if (lOnPath.Count == 0)
            {
                string lAlias = onSql.GetAlias(facet, lProcessedOnPath, isLinkedTo);
                if ((lAlias != "") && (!forceLastAlias))
                    return (lAlias);
                else
                    lOnPathExist = false;
            }
            else
            {
                string lTargetClass = GetTargetClassName(new ONPath(lRole));

                // Agent & OV Argument Control
                if ((lTargetClass == "") && (initialClass != ""))
                    lTargetClass = initialClass;

                string lAlias = onSql.GetAlias(lTargetClass, lProcessedOnPath, isLinkedTo);
                if (lAlias == "")
                    lOnPathExist = false;
            }

            // Create path
//.........这里部分代码省略.........

        /// <summary>
        /// Retrieve all the instances of a determinate class that fulfil a determinate formula of searching
        /// </summary>
        /// <param name="linkedTo">List to reach the class to retrieve the related instance</param>
        /// <param name="filters">Formula to search concrete instances</param>
        /// <param name="comparer">Order Criteria that must be followed by the query</param>
        /// <param name="startRowOid">OID frontier</param>
        /// <param name="blockSize">Number of instances to be returned</param>
        /// <returns>Instances that check the filter list</returns>
        protected virtual ONCollection SolveQuery(ONLinkedToList linkedTo, ONFilterList onFilterList, ONDisplaySet displaySet, ONOrderCriteria comparer, ONOid startRowOid, int blockSize)
        {
            ONSqlSelect lOnSql = new ONSqlSelect();
            try
            {
            // Create select and first table
            InhRetrieveInstances(lOnSql, displaySet, null, OnContext);

                // Fix related instance
                if (!AddLinkedTo(lOnSql, linkedTo))
                return ONContext.GetComponent_Collection(ClassName, OnContext);

            // Add filter formula
            if (onFilterList != null)
                onFilterList.FilterInData(lOnSql, this);

            // Retrieve query instance number
            int lTotalNumInstances = -1;
            if (OnContext.CalculateQueryInstancesNumber)
            {
                if ((onFilterList == null) || (!onFilterList.InMemory))
                {
                    ArrayList lSqlParameters;
                    string lNumInstancesSqlSentence = ONSqlSelect.GenerateSQLAsCount(lOnSql, out lSqlParameters);
                    lTotalNumInstances = Convert.ToInt32(ExecuteScalar(lNumInstancesSqlSentence, lSqlParameters));
                }
                OnContext.CalculateQueryInstancesNumber = false;
            }

            // OrderCriteria
            AddOrderCriteria(lOnSql, comparer, startRowOid, blockSize);

            // Execute
            ONCollection lONCollection = ExecuteSql(lOnSql, onFilterList, displaySet, comparer, startRowOid, blockSize);

            // Set Query instance number
            if (lTotalNumInstances > -1)
                lONCollection.totalNumInstances = lTotalNumInstances;

            return lONCollection;
            }
            catch (Exception e)
            {
            string ltraceItem = "Method: SolveQuery, Component: ONDBData";
            if (e is ONSystemException)
              			{
                ONSystemException lException = e as ONSystemException;
                lException.addTraceInformation(ltraceItem);
                throw lException;
            }
              			throw new ONSystemException(e, ltraceItem);
              		}
        }

 /// <summary> This method adds the order criteria to the SQL statement </summary>
 /// <param name="onSql"> This parameter represents the SQL component </param>
 /// <param name="comparer"> This parameter has all the information refering to the order criteria to add to SQL statement</param>
 /// <param name="startRowOid">This parameter has the OID necessary to start the search</param>
 /// <param name="blockSize">This parameter represents the number of instances to be returned</param>
 /// <param name="initialPath"> This parameter has the path of the instances reached in a For All</param>
 protected virtual void AddOrderCriteria(ONSqlSelect onSql, ONOrderCriteria comparer, ONOid startRowOid, int blockSize, ONPath initialPath)
 {
 }

        /// <summary>
        /// Fix related instance
        /// </summary>
        /// <param name="onSql">Sentence SQL to be executed</param>
        /// <param name="linkedTo">List to reach the class to retrieve the related instance</param>
        protected bool AddLinkedTo(ONSqlSelect onSql, ONLinkedToList linkedTo)
        {
            // Fix related instance
            foreach(KeyValuePair<ONPath, ONOid> lDictionaryEntry in linkedTo)
            {
                ONPath lPath = lDictionaryEntry.Key as ONPath;
                ONOid lOID = lDictionaryEntry.Value as ONOid;

                string lAliasRelated = InhAddPath(onSql, lOID.ClassName, lPath, "", true);

                // Check Visibility
                if (!ONInstance.IsVisibleInv(ONContext.GetType_Instance(ClassName), lPath, OnContext))
                    return false;

                ONDBData lData = ONContext.GetComponent_Data(lOID.ClassName, OnContext) as ONDBData;
                lData.InhFixInstance(onSql, null, lPath, lOID, true);
            }

            return (true);
        }

        /// <summary>
        /// Returns the instance with the Oid
        /// </summary>
        /// <param name="oid">OID to search the instance</param>
        /// <param name="onFilterList">Filters to theck</param>
        /// <returns>The instance searched</returns>
        public override ONInstance Search(ONOid oid, ONFilterList onFilterList, ONDisplaySet displaySet)
        {
            ONSqlSelect lOnSql = new ONSqlSelect();

            // Create select and first table
            InhRetrieveInstances(lOnSql, displaySet, null, OnContext);

            // Fix instance
            InhFixInstance(lOnSql, null, null, oid);

            // Add filter formula
            onFilterList.FilterInData(lOnSql, this);

            // Execute
            ONCollection lCollection = ExecuteSql(lOnSql, onFilterList, displaySet, null, null, 0);

            if (lCollection.Count > 0)
                return lCollection[0] as ONInstance;
            else
                return null;
        }