/// <summary>
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
internal ExecutionQuery GenerateWithoutJoin(QueryCriteria criteria)
{
ISqlGenerator isql = null;
List <IDataParameter> listParameters = null;
ExecutionQuery execQuery;
SqlGenerator generator = new SqlGenerator();
DataFactory factory = new DataFactory();
try
{
listParameters = new List <IDataParameter>();
StringBuilder sbuild = new StringBuilder();
execQuery = new ExecutionQuery();
if (generatorType == QueryCriteriaGeneratorType.Select)
{
execQuery = generator.GenerateSelectQuery(DatabaseServer.Oracle, criteria);
}
else if (generatorType == QueryCriteriaGeneratorType.Update)
{
execQuery = generator.GenerateUpdateQuery(DatabaseServer.Oracle, criteria.TableName, criteria.Fields, false);
}
else if (generatorType == QueryCriteriaGeneratorType.Delete)
{
execQuery = generator.GenerateDeleteQuery(DatabaseServer.Oracle, criteria.TableName);
}
//add to the intermediary objects
if (execQuery.Parameters != null)
{
foreach (IDataParameter var in execQuery.Parameters)
{
listParameters.Add(var);
}
}
sbuild.Append(execQuery.Query);
//initialize generator
isql = factory.InitializeSqlGenerator(DatabaseServer.Oracle);
//append where clause
sbuild.Append(" WHERE ");
//generate the condition based on criteria
string condition = GenerateCondition(criteria.TableName, criteria.CriteriaConditions, ref sbuild, ref listParameters);
//more checks
if (sbuild.ToString().EndsWith(" WHERE ") && condition.StartsWith(" ORDER BY "))
{
if (condition.StartsWith(" ORDER BY"))
{
sbuild.Remove(sbuild.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH);
}
}
sbuild.Append(condition);
//last check to prevent invalid sql queries
if (sbuild.ToString().EndsWith(" WHERE "))
{
sbuild.Remove(sbuild.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH);
}
execQuery = new ExecutionQuery();
execQuery.Query = sbuild.ToString();
IDataParameter[] pmc = new IDataParameter[listParameters.Count];
listParameters.CopyTo(pmc);
execQuery.Parameters = pmc;
return(execQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (listParameters != null)
{
listParameters.Clear();
listParameters = null;
}
}
}
/// <summary>
/// Generates the sql query condition
/// </summary>
/// <param name="tableName">Name of the datbase table</param>
/// <param name="conditions">Criteria conditions </param>
/// <param name="sbSqlHeader">StringBuilder which contains the SELECT part of the sql query build so far</param>
/// <param name="listParameters">List with the IDataParameters used in the query</param>
/// <returns>The querie's condition </returns>
internal string GenerateCondition(string tableName, CriteriaCondition[] conditions, ref StringBuilder sbSqlHeader, ref List <IDataParameter> listParameters)
{
//keeps the order by part of the query
StringBuilder sbOrderByCriteria = new StringBuilder();
//holds the generated query
StringBuilder sbuild = new StringBuilder();
ISqlGenerator isql = null;
SqlGenerator generator = new SqlGenerator();
DataConvertor converter = new DataConvertor();
List <string> listParameterNames = null;
//temporary vars
string fieldName = string.Empty;
int index = -1;
string tempString = string.Empty;
DataFactory factory = new DataFactory();
try
{
listParameterNames = new List <string>();
//initialize generator
isql = factory.InitializeSqlGenerator(DatabaseServer.Oracle);
//generate conditions
for (int i = 0; i < conditions.Length; i++)
{
//check if we generate "AND" operator
if (i > 0)
{
/*excluse "AND for the following operators
*
* -Order by
* -Or
* -Not
* -Count
*
*/
if ((conditions[i].CriteriaOperator != CriteriaOperator.OrderBy) && (conditions[i].CriteriaOperator != CriteriaOperator.Or) &&
(conditions[i - 1].CriteriaOperator != CriteriaOperator.Count) && (conditions[i - 1].CriteriaOperator != CriteriaOperator.Or) &&
(conditions[i - 1].CriteriaOperator != CriteriaOperator.Not))
{
sbuild.Append(" AND ");
}
}
DatabaseField field = conditions[i].Field;
switch (conditions[i].CriteriaOperator)
{
case CriteriaOperator.Between:
//here we must have 2 parameters with two diffferent values and name. These
//parameters must be generated based on a single name.
IDataParameter paramBetweenFirst = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
IDataParameter paramBetweenSecond = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
paramBetweenFirst.ParameterName = paramBetweenFirst.ParameterName + "First";
paramBetweenSecond.ParameterName = paramBetweenSecond.ParameterName + "Second";
//set the parameter's value and add it to the list
paramBetweenFirst.Value = conditions[i].Values[0];
listParameters.Add(paramBetweenFirst);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + conditions[i].Field.fieldName + " BETWEEN " +
isql.GetValue(paramBetweenFirst));
sbuild.Append(" AND ");
//set the value of the second parameter
paramBetweenSecond.Value = conditions[i].Values[1];
listParameters.Add(paramBetweenSecond);
sbuild.Append(isql.GetValue(paramBetweenSecond));
break;
case CriteriaOperator.Not:
sbuild.Append(" NOT");
break;
case CriteriaOperator.Different:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramDifferent = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
listParameters.Add(paramDifferent);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + "<>" + isql.GetValue(paramDifferent));
break;
case CriteriaOperator.Like:
field.fieldValue = "%" + conditions[i].Values[0] + "%";
IDataParameter paramLike = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
listParameters.Add(paramLike);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " LIKE " + isql.GetValue(paramLike));
break;
case CriteriaOperator.LikeEnd:
field.fieldValue = "%" + conditions[i].Values[0];
IDataParameter paramLikeEnd = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
listParameters.Add(paramLikeEnd);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " LIKE " + isql.GetValue(paramLikeEnd));
break;
case CriteriaOperator.LikeStart:
field.fieldValue = conditions[i].Values[0] + "%";
IDataParameter paramLikeStart = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
listParameters.Add(paramLikeStart);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " LIKE " + isql.GetValue(paramLikeStart));
break;
case CriteriaOperator.Equality:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramEquality = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
listParameters.Add(paramEquality);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + "=" + isql.GetValue(paramEquality));
break;
case CriteriaOperator.IsNull:
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " is null");
break;
case CriteriaOperator.IsNotNull:
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " is not null");
break;
case CriteriaOperator.Or:
sbuild.Append(" OR");
break;
case CriteriaOperator.Smaller:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramSmaller = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
listParameters.Add(paramSmaller);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " < " + isql.GetValue(paramSmaller));
break;
case CriteriaOperator.SmallerOrEqual:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramSmallerOrEqual = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
listParameters.Add(paramSmallerOrEqual);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " <= " + isql.GetValue(paramSmallerOrEqual));
break;
case CriteriaOperator.Higher:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramHigher = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
listParameters.Add(paramHigher);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " > " + isql.GetValue(paramHigher));
break;
case CriteriaOperator.HigherOrEqual:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramHigherOrEqual = converter.ConvertToDataParameter(DatabaseServer.Oracle, tableName, field, ref listParameterNames);
listParameters.Add(paramHigherOrEqual);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " >= " + isql.GetValue(paramHigherOrEqual));
break;
case CriteriaOperator.OrderBy:
if (sbOrderByCriteria.Length == 0)
{
//add the operator for the first criteria
sbOrderByCriteria.Append("ORDER BY " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " " + conditions[i].Values[0]);
}
else
{
//add "," for the subsequent criterias
sbOrderByCriteria.Append(", " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + field.fieldName + " " + conditions[i].Values[0]);
}
break;
//NOTE : DISTICT requires modification of the sql header. Also
// DISTINCT clause requires that the distinct field should be
// the first one in the list.
case CriteriaOperator.Distinct:
//get the field
fieldName = generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + conditions[i].Field.fieldName;
//we have the field name now search for it in the fields list
index = sbSqlHeader.ToString().IndexOf(fieldName);
//now remove the field from the list.
if (index == -1)
{
throw new ArgumentException("Invalid Distinct clause");
}
tempString = sbSqlHeader.ToString();
tempString = tempString.Remove(index, fieldName.Length);
//add it at the beginning of the select
tempString = tempString.Insert(SELECT_FIELD_LENGTH, " distinct " + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + conditions[i].Field.fieldName);
sbSqlHeader.Remove(0, sbSqlHeader.Length);
sbSqlHeader.Append(tempString);
break;
//NOTE: MAX fields must be after SELECT statement
case CriteriaOperator.Max:
//get the field
fieldName = generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + conditions[i].Field.fieldName;
//we have the field name now search for it in the fields list
index = sbSqlHeader.ToString().IndexOf(fieldName);
//now remove the field from the list.
if (index == -1)
{
throw new ArgumentException("Invalid MAX clause");
}
tempString = sbSqlHeader.ToString();
tempString = tempString.Remove(index, fieldName.Length);
//add it at the beginning of the select
tempString = tempString.Insert(SELECT_FIELD_LENGTH, " max(" + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + conditions[i].Field.fieldName + ")");
sbSqlHeader.Remove(0, sbSqlHeader.Length);
sbSqlHeader.Append(tempString);
break;
//NOTE: MIN fields must be after SELECT statement
case CriteriaOperator.Min:
//get the field
fieldName = generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + conditions[i].Field.fieldName;
//we have the field name now search for it in the fields list
index = sbSqlHeader.ToString().IndexOf(fieldName);
//now remove the field from the list.
if (index == -1)
{
throw new ArgumentException("Invalid MIN clause");
}
tempString = sbSqlHeader.ToString();
tempString = tempString.Remove(index, fieldName.Length);
//add it at the beginning of the select
tempString = tempString.Insert(SELECT_FIELD_LENGTH, " min(" + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + conditions[i].Field.fieldName + ")");
sbSqlHeader.Remove(0, sbSqlHeader.Length);
sbSqlHeader.Append(tempString);
break;
//NOTE: COUNT fields must be after SELECT statement
case CriteriaOperator.Count:
//get the field
fieldName = generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + conditions[i].Field.fieldName;
//we have the field name now search for it in the fields list
index = sbSqlHeader.ToString().IndexOf(fieldName);
//now remove the field from the list.
if (index == -1)
{
throw new ArgumentException("Invalid count clause");
}
tempString = sbSqlHeader.ToString();
tempString = tempString.Remove(index, fieldName.Length);
//add it at the beginning of the select
tempString = tempString.Insert(SELECT_FIELD_LENGTH, " count(" + generator.GetTableName(DatabaseServer.Oracle, tableName) + "." + conditions[i].Field.fieldName + ")");
sbSqlHeader.Remove(0, sbSqlHeader.Length);
sbSqlHeader.Append(tempString);
break;
}
}
//last check to prevent invalid sql queries. If we don't have any
//conditions remove the "WHERE".
if (sbuild.ToString().EndsWith(" WHERE "))
{
sbuild.Remove(sbuild.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH);
}
//check if we have conditions which don't require a "WHERE" clause
if (sbuild.Length == 0 && sbOrderByCriteria.Length > 0)
{
//remove from query header
sbSqlHeader.Remove(sbSqlHeader.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH);
}
if (sbOrderByCriteria.Length > 0)
{
sbuild.Append(" " + sbOrderByCriteria);
}
return(sbuild.ToString());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (listParameterNames != null)
{
listParameterNames.Clear();
listParameterNames = null;
}
}
}
/// <summary>
/// Generates the query(including joins) based on the specified query criteria
/// </summary>
/// <param name="criteria">QueryCriteria based on which the QueryCriteria is generated</param>
/// <returns>Execution Query</returns>
internal ExecutionQuery GenerateWithJoin(QueryCriteria criteria)
{
StringBuilder sbuild = new StringBuilder();
bool appendWhere = false;
ExecutionQuery execQuery;
List <IDataParameter> listParameters = null;
SqlGenerator generator = new SqlGenerator();
try
{
listParameters = new List <IDataParameter>();
//generate the head of the SELECT query.
//we'll used this temporary structure (tempQuery) and pass it around
//to the generator functions.
execQuery = generator.GenerateSelectQuery(DatabaseServer.Oracle, criteria);
//add from the head query to the temporary objects.
sbuild.Append(execQuery.Query);
if (execQuery.Parameters != null)
{
foreach (IDataParameter var in execQuery.Parameters)
{
listParameters.Add(var);
}
}
//add the JOINS from the main tableMetadata
for (int i = 0; i < criteria.JoinCriteriaConditions.Length; i++)
{
switch (criteria.JoinCriteriaConditions[i].Join)
{
case JoinType.Inner:
sbuild.Append(" Inner Join ");
break;
case JoinType.Left:
sbuild.Append(" Left Join ");
break;
case JoinType.Right:
sbuild.Append(" Right Join ");
break;
}
sbuild.Append(generator.GetTableName(DatabaseServer.Oracle, criteria.JoinCriteriaConditions[i].ForeignKeyFieldTableName) + " ON " +
generator.GetTableName(DatabaseServer.Oracle, criteria.JoinCriteriaConditions[i].PrimaryKeyFieldTableName) + "." +
criteria.JoinCriteriaConditions[i].PrimaryKey.fieldName + "=" +
generator.GetTableName(DatabaseServer.Oracle, criteria.JoinCriteriaConditions[i].Criteria.TableName) + "." +
criteria.JoinCriteriaConditions[i].ForeignKey.fieldName);
}
//add "WHERE" condition from the first criteria
if (criteria.CriteriaConditions.Length > 0)
{
sbuild.Append(" WHERE ");
appendWhere = true;
sbuild.Append(GenerateCondition(criteria.TableName, criteria.CriteriaConditions, ref sbuild, ref listParameters));
}
//add the join criterias
for (int i = 0; i < criteria.JoinCriteriaConditions.Length; i++)
{
if ((i == 0) && (appendWhere == false))
{
sbuild.Append(" WHERE ");
}
if (criteria.JoinCriteriaConditions[i].Criteria.CriteriaConditions.Length > 0)
{
if (sbuild.ToString().EndsWith("WHERE "))
{
sbuild.Append(GenerateCondition(criteria.JoinCriteriaConditions[i].Criteria.TableName, criteria.JoinCriteriaConditions[i].Criteria.CriteriaConditions,
ref sbuild, ref listParameters));
}
else
{
sbuild.Append(" AND " +
GenerateCondition(criteria.JoinCriteriaConditions[i].Criteria.TableName, criteria.JoinCriteriaConditions[i].Criteria.CriteriaConditions,
ref sbuild, ref listParameters));
}
}
}
//checks for where and and
if (sbuild.ToString().EndsWith(" WHERE "))
{
sbuild.Remove(sbuild.Length - WHERE_FIELD_LENGTH, WHERE_FIELD_LENGTH);
}
execQuery.Query = sbuild.ToString();
IDataParameter[] pmc = new IDataParameter[listParameters.Count];
listParameters.CopyTo(pmc);
execQuery.Parameters = pmc;
return(execQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (listParameters != null)
{
listParameters.Clear();
listParameters = null;
}
}
}