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