C# ONSqlSelect.GenerateSQL方法代码示例

        /// <summary>Load the data retrieved from the Data Base to components of the application</summary>
        /// <param name="onContext">This parameter has the current context</param>
        /// <param name="oid">OID of the instance whose text attribute value is wanted to be loaded</param>
        public static ONText LoadTextOrigen(ONContext onContext, AeronaveOid oid)
        {
            ONSqlSelect lOnSql = new ONSqlSelect();

            lOnSql.CreateAlias(CtesBD.TBL_AERONAVE, null, "Aeronave");
            lOnSql.AddSelect(CtesBD.FLD_AERONAVE_ORIGEN);

            FixInstance(lOnSql, null, null, oid);

            // Create Data Component
            AeronaveData lData = new AeronaveData(onContext);

            // Execute query
            ArrayList lSqlParameters;
            ONText lReturn = new ONText((string) lData.ExecuteScalar(lOnSql.GenerateSQL(out lSqlParameters), lSqlParameters));
            return lReturn;
        }

        /// <summary>
        /// Replace Sql parameters with subqueries
        /// </summary>
        /// <param name="sql">Source sql sentence</param>
        /// <param name="numParameter">Parameter number</param>
        /// <param name="onSqlSubquery">Sql to insert</param>
        /// <returns></returns>
        private StringBuilder GenerateSQL_SubQueriesReplace(StringBuilder sql, int numParameter, ONSqlSelect onSqlSubquery, ref ArrayList parameters)
        {
            StringBuilder lTextBuilder = new StringBuilder();
            string lSql = sql.ToString();

            // Find ? number 'numParameter'
            int lIndex = 0;
            for (int i = 0; i <= numParameter; i++)
                lIndex = lSql.IndexOf("?", lIndex + 1);

            ArrayList lSubParameters;
            string lSubSql = onSqlSubquery.GenerateSQL(out lSubParameters);

            // Insert subquery parameters
            parameters.RemoveAt(numParameter);
            int j = numParameter;
            for (int i = 0; i < lSubParameters.Count; i++)
                parameters.Insert(j++, lSubParameters[i]);

            // Mix sql's
            lTextBuilder.Append(lSql.Substring(0, lIndex));
            lTextBuilder.Append("(");
            lTextBuilder.Append(lSubSql);
            lTextBuilder.Append(")");
            lTextBuilder.Append(lSql.Substring(lIndex + 1, sql.Length - lIndex - 1));

            return lTextBuilder;
        }

        /// <summary>
        /// Generates a sql as a Count
        /// </summary>
        /// <param name="onSql">Sql to generate</param>
        /// <param name="sqlParameters">Sql parameters</param>
        public static string GenerateSQLAsCount(ONSqlSelect onSql, out ArrayList sqlParameters)
        {
            StringBuilder lSql = new StringBuilder();
            List<string> mTempOrderBy = new List<string>();
            List<string> mTempSelectAttributes = onSql.mSelectAttributes;

            //The sql is generated with a single select attribute
            onSql.mSelectAttributes = new List<string>(1);
            onSql.mSelectAttributes.Add(mTempSelectAttributes[0]);

            lSql.Append("SELECT COUNT(*) FROM (");
            if (onSql.mOrderBy.Count > 0)
            {
                // Generate the sentence without the OrderBy part
                mTempOrderBy = onSql.mOrderBy;
                onSql.mOrderBy = new List<string>();
                lSql.Append(onSql.GenerateSQL(out sqlParameters));
                onSql.mOrderBy = mTempOrderBy;
            }
            else
                lSql.Append(onSql.GenerateSQL(out sqlParameters));
            lSql.Append(") lAux");

            // Restore the original select attribute list
            onSql.mSelectAttributes = mTempSelectAttributes;

            return lSql.ToString();
        }

        /// <summary>Load the data retrieved from the Data Base to components of the application</summary>
        /// <param name="onContext">This parameter has the current context</param>
        /// <param name="oid">OID of the instance whose text attribute value is wanted to be loaded</param>
        public static ONText LoadTextNombre(ONContext onContext, PasajeroOid oid)
        {
            ONSqlSelect lOnSql = new ONSqlSelect();

            lOnSql.CreateAlias(CtesBD.TBL_PASAJERO, null, "Pasajero");
            lOnSql.AddSelect(CtesBD.FLD_PASAJERO_NOMBRE);

            FixInstance(lOnSql, null, null, oid);

            // Create Data Component
            PasajeroData lData = new PasajeroData(onContext);

            // Execute query
            ArrayList lSqlParameters;
            ONText lReturn = new ONText((string) lData.ExecuteScalar(lOnSql.GenerateSQL(out lSqlParameters), lSqlParameters));
            return lReturn;
        }