/// <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> /// 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>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> /// 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> /// 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>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; }