/// <summary>
/// Generates a DELETE SQL query
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public ExecutionQuery GenerateDelete(QueryCriteria criteria)
{
try
{
generatorType = QueryCriteriaGeneratorType.Delete;
return(GenerateWithoutJoin(criteria));
}
catch
{
throw;
}
}
/// <summary>
/// Deletes the row by the specified primary key value.
/// </summary>
/// <param name="primaryKeyValue">The primary key value.</param>
/// <returns>Number of affected rows</returns>
public int Delete(object primaryKeyValue)
{
IQueryCriteriaGenerator queryCriteriaGenerator = null;
DataFactory factory = new DataFactory();
int affectedRows = 0;
ExecutionEngine exec = null;
try
{
queryCriteriaGenerator = factory.InitializeQueryCriteriaGenerator(database);
DatabaseField field = mappedObject.GetPrimaryKeyField();
field.fieldValue = primaryKeyValue;
QueryCriteria criteria = new QueryCriteria(mappedObject.TableName);
criteria.Add(CriteriaOperator.Equality, field, primaryKeyValue);
ExecutionQuery query = queryCriteriaGenerator.GenerateDelete(criteria);
if (contextSession != null)
{
if (contextSession.IsInTransaction)
{
contextSession.Queries.Add(query);
}
else
{
affectedRows = execEngine.ExecuteNonQuery(query);
}
}
else
{
exec = new ExecutionEngine();
affectedRows = exec.ExecuteNonQuery(query);
}
return(affectedRows);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public ExecutionQuery GenerateDelete(QueryCriteria criteria)
{
try
{
generatorType = QueryCriteriaGeneratorType.Delete;
//no joins so generate the normal query criteria
return(GenerateWithoutJoin(criteria));
}
catch
{
throw;
}
}
/// <summary>
/// Generates a UPDATE SQL query
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public ExecutionQuery GenerateUpdate(QueryCriteria criteria)
{
try
{
//set the generation type
generatorType = QueryCriteriaGeneratorType.Update;
return(GenerateWithoutJoin(criteria));
}
catch
{
throw;
}
}
/// <summary>
/// Get data as a TableMetadata array
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is selected</param>
/// <returns>A TableMetadata array containing all the data</returns>
public Array GetTableMetadata(QueryCriteria criteria)
{
if (criteria.TableName != mappedObject.TableName)
{
throw new ArgumentException("Invalid criteria query. Must be the same as current table metadata");
}
DataFactory factory = new DataFactory();
IQueryCriteriaGenerator iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
return(GetTableMetadata(selectQuery));
}
/// <summary>
/// Generates the query based on the specified query criteria.
/// </summary>
/// <param name="criteria">QueryCriteria upon which the query is generated </param>
/// <returns>Sql Query</returns>
public ExecutionQuery GenerateSelect(QueryCriteria criteria)
{
//set the generation type
generatorType = QueryCriteriaGeneratorType.Select;
if (criteria.JoinCriteriaConditions.Length == 0)
{
//no joins so generate the normal query criteria
return(GenerateWithoutJoin(criteria));
}
else
{
//generate the criteria with joins
return(GenerateWithJoin(criteria));
}
}
/// <summary>
/// Returns a single value from the database using the specified QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is selected</param>
/// <returns>The selected fieldValue</returns>
public object GetValue(QueryCriteria criteria)
{
IQueryCriteriaGenerator iql = null;
object result = null;
ExecutionEngine exec = null;
try
{
if (criteria.Fields.Length > 1)
{
throw new ArgumentException("Invalid fields length. Must have only one field");
}
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery selectQuery = iql.GenerateSelect(criteria);
if (contextSession != null)
{
result = execEngine.ExecuteScalar(selectQuery);
}
else
{
exec = new ExecutionEngine();
result = exec.ExecuteScalar(selectQuery);
}
return(result);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Delete multiple rows from the table using the specified criteria from a QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based on which data is deleted</param>
public int Delete(QueryCriteria criteria)
{
int affectedRows = 0;
DataFactory factory = new DataFactory();
ExecutionEngine exec = null;
try
{
IQueryCriteriaGenerator iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery query = iql.GenerateDelete(criteria);
if (contextSession != null)
{
if (contextSession.IsInTransaction)
{
contextSession.Queries.Add(query);
}
else
{
affectedRows = execEngine.ExecuteNonQuery(query);
}
}
else
{
exec = new ExecutionEngine();
affectedRows = exec.ExecuteNonQuery(query);
}
return(affectedRows);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
private object RunIntrinsecFunction(CriteriaOperator criteria, DatabaseField field)
{
IQueryCriteriaGenerator iql = null;
object result;
ExecutionEngine exec = null;
try
{
QueryCriteria qc = new QueryCriteria(mappedObject.TableName, field);
qc.Add(criteria, field);
DataFactory factory = new DataFactory();
iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery query = iql.GenerateSelect(qc);
if (contextSession != null)
{
result = execEngine.ExecuteScalar(query);
}
else
{
exec = new ExecutionEngine();
result = exec.ExecuteScalar(database, connectionString, query);
}
return(result);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Updates the specified entities using the specified QueryCriteria
/// </summary>
/// <param name="criteria">QueryCriteria based upon which data is updated</param>
/// <returns>Number of affected rows</returns>
public int Update(QueryCriteria criteria)
{
DataFactory factory = new DataFactory();
ExecutionEngine exec = null;
try
{
IQueryCriteriaGenerator iql = factory.InitializeQueryCriteriaGenerator(database);
ExecutionQuery query = iql.GenerateUpdate(criteria);
if (contextSession != null)
{
if (contextSession.IsInTransaction)
{
contextSession.Queries.Add(query);
return(0);
}
return(execEngine.ExecuteNonQuery(query));
}
exec = new ExecutionEngine();
return(exec.ExecuteNonQuery(query));
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// </summary>
/// <param name="criteria"></param>
/// <remarks>Generste</remarks>
/// <returns></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.MySQL, 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 joins now
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.MySQL, criteria.JoinCriteriaConditions[i].ForeignKeyFieldTableName) + " ON " +
generator.GetTableName(DatabaseServer.MySQL, criteria.JoinCriteriaConditions[i].PrimaryKeyFieldTableName) + "." +
criteria.JoinCriteriaConditions[i].PrimaryKey.fieldName + "=" +
generator.GetTableName(DatabaseServer.MySQL, criteria.JoinCriteriaConditions[i].Criteria.TableName) + "." +
criteria.JoinCriteriaConditions[i].ForeignKey.fieldName);
}
//add conditions
//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;
}
}
}
/// <summary>
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
internal ExecutionQuery GenerateWithoutJoin(QueryCriteria criteria)
{
ISqlGenerator isql = null;
ExecutionQuery execQuery;
SqlGenerator generator = new SqlGenerator();
DataFactory factory = new DataFactory();
List <IDataParameter> listParameters = new List <IDataParameter>();
StringBuilder sbuild = new StringBuilder();
execQuery = new ExecutionQuery();
if (generatorType == QueryCriteriaGeneratorType.Select)
{
execQuery = generator.GenerateSelectQuery(DatabaseServer.MySQL, criteria);
}
else if (generatorType == QueryCriteriaGeneratorType.Update)
{
execQuery = generator.GenerateUpdateQuery(DatabaseServer.MySQL, criteria.TableName, criteria.Fields, false);
}
else if (generatorType == QueryCriteriaGeneratorType.Delete)
{
execQuery = generator.GenerateDeleteQuery(DatabaseServer.MySQL, 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.MySQL);
//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);
}
/// <summary>
/// Generates a query based on a QueryCriteria. This function generates only the
/// query header without the citeria
/// </summary>
/// <param name="database">Database type for which we generate the query</param>
/// <param name="criteria">QueryCriteria based on which the query is generated</param>
/// <returns>Generated ExecutionQuery</returns>
public ExecutionQuery GenerateSelectQuery(DatabaseServer database, QueryCriteria criteria)
{
StringBuilder sqlBuilder = new StringBuilder();
DataFactory factory = new DataFactory();
ISqlGenerator isql = factory.InitializeSqlGenerator(database);
sqlBuilder.Append(" SELECT ");
//add field's name for the criteria mai9n table
for (int i = 0; i < criteria.Fields.Length; i++)
{
sqlBuilder.Append(" " + GetTableName(database, criteria.TableName) + "." + criteria.Fields[i].fieldName);
//check for alias
for (int j = 0; j < criteria.Aliases.Length; j++)
{
if (criteria.Fields[i].fieldName == criteria.Aliases[j].fieldName && criteria.TableName == criteria.Aliases[j].tableName)
{
sqlBuilder.Append(" AS " + criteria.Aliases[j].aliasName);
break;
}
}
if (i != criteria.Fields.Length - 1)
{
sqlBuilder.Append(" ,");
}
}
//check if the criteria has join criteria conditions. If it has then
//add comma.
if (criteria.JoinCriteriaConditions.Length > 0)
{
sqlBuilder.Append(" , ");
}
//now add the joins fields
for (int i = 0; i < criteria.JoinCriteriaConditions.Length; i++)
{
for (int j = 0; j < criteria.JoinCriteriaConditions[i].Criteria.Fields.Length; j++)
{
sqlBuilder.Append(GetTableName(DatabaseServer.PostgreSql, criteria.JoinCriteriaConditions[i].Criteria.TableName) + "." + criteria.JoinCriteriaConditions[i].Criteria.Fields[j].fieldName);
if (criteria.JoinCriteriaConditions[i].Criteria.Aliases.Length > 0)
{
for (int x = 0; x < criteria.JoinCriteriaConditions[i].Criteria.Aliases.Length; x++)
{
if (criteria.JoinCriteriaConditions[i].Criteria.Fields[j].fieldName == criteria.JoinCriteriaConditions[i].Criteria.Aliases[x].fieldName)
{
sqlBuilder.Append(" AS " + criteria.JoinCriteriaConditions[i].Criteria.Aliases[x].aliasName);
break;
}
}
}
sqlBuilder.Append(COMMA);
}
}
string temp = sqlBuilder.ToString().TrimEnd();
//remove the last comma if it exists
if (temp.EndsWith(COMMA))
{
temp = temp.Remove(temp.Length - COMMA.Length, COMMA.Length);
sqlBuilder.Remove(0, sqlBuilder.Length);
sqlBuilder.Append(temp);
}
sqlBuilder.Append(" FROM " + GetTableName(database, criteria.TableName));
ExecutionQuery execQuery = new ExecutionQuery();
execQuery.Query = sqlBuilder.ToString();
return(execQuery);
}
/// <summary>
/// GENERATE a SELECT query with joins
/// </summary>
/// <param name="criteria"></param>
/// <remarks>Generste</remarks>
/// <returns></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.Access, 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);
}
}
int joinsCount = criteria.JoinCriteriaConditions.Length;
StringBuilder parantheses = null;
if (joinsCount > 1)
{
parantheses = new StringBuilder(joinsCount);
for (int i = 0; i < parantheses.Capacity; i++)
{
parantheses.Append("(");
}
//HACK : here we need to add the colons for the joins.
//Because the name of the first table is already added we must
//add the parantheses before.
string generatedQuery = sbuild.ToString();
int lastIndex = generatedQuery.LastIndexOf(" ");
generatedQuery = generatedQuery.Insert(lastIndex, parantheses.ToString());
//clear the generated string so far
sbuild.Remove(0, sbuild.Length);
//add the new string
sbuild.Append(generatedQuery);
}
//add the JOINS
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.Access, criteria.JoinCriteriaConditions[i].ForeignKeyFieldTableName) + " ON " +
generator.GetTableName(DatabaseServer.Access, criteria.JoinCriteriaConditions[i].PrimaryKeyFieldTableName) + "." +
criteria.JoinCriteriaConditions[i].PrimaryKey.fieldName + "=" +
generator.GetTableName(DatabaseServer.Access, criteria.JoinCriteriaConditions[i].Criteria.TableName) + "." +
criteria.JoinCriteriaConditions[i].ForeignKey.fieldName);
if (parantheses != null)
{
sbuild.Append(")");
}
}
//add conditions
if (criteria.CriteriaConditions.Length > 0)
{
sbuild.Append(" WHERE ");
appendWhere = true;
sbuild.Append(GenerateCondition(criteria.TableName, criteria.CriteriaConditions, ref sbuild, ref listParameters));
}
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;
}
}
}
/// <summary>
/// Gets the data from a related table in a ManyToMany relation.
/// </summary>
/// <param name="relatedTableType">Type of the related entity</param>
/// <param name="intermediaryRelatedTableType">Type of the intermediary table</param>
/// <returns>TableMetadata array which contains the results</returns>
protected Array GetRelatedTableData(Type relatedTableType, Type intermediaryRelatedTableType)
{
PersistentObject persistent = null;
ManyToManyTableRelation relation = null;
DataSet ds = null;
try
{
//create a instance of the table so we can check the relations between out table and this
TableMetadata relatedTable = (TableMetadata)Activator.CreateInstance(relatedTableType);
TableMetadata intermediateTable = (TableMetadata)Activator.CreateInstance(intermediaryRelatedTableType);
object primaryKeyValue = GetPrimaryKeyField().fieldValue;
//first check the fieldValue of the primary key
if (GetPrimaryKeyField().fieldValue == null)
{
throw new InvalidOperationException("The primary key does not have a fieldValue");
}
//get the relation
foreach (TableRelation var in Relations)
{
if (var is ManyToManyTableRelation)
{
if (var.RelatedTableName == relatedTable.TableName)
{
relation = (ManyToManyTableRelation)var;
break;
}
}
}
//check if we got the relation
if (relation == null)
{
throw new ArgumentException("A relation cannot be found between the tables");
}
//we have the relation between the 2 tables.
persistent = new PersistentObject(this);
QueryCriteria qcThis = new QueryCriteria(TableName, GetPrimaryKeyField());
qcThis.Add(CriteriaOperator.Equality, GetPrimaryKeyField(), primaryKeyValue);
//generate here the inner join
QueryCriteria qcIntermediaryTable = new QueryCriteria(intermediateTable.TableName, intermediateTable.GetField(relation.IntermediaryKeyFieldFromParentTable));
QueryCriteria qcChild = new QueryCriteria(relatedTable);
qcThis.AddJoin(JoinType.Inner, TableName, GetPrimaryKeyField(), intermediateTable.TableName,
intermediateTable.GetField(relation.IntermediaryKeyFieldFromParentTable), qcIntermediaryTable);
qcThis.AddJoin(JoinType.Inner, intermediateTable.TableName, intermediateTable.GetField(relation.IntermediaryKeyFieldFromChildTable), relatedTable.TableName,
relatedTable.GetField(relation.IntermediaryKeyFieldFromChildTable), qcChild);
ds = persistent.GetDataSet(qcThis);
Array data = Array.CreateInstance(relatedTableType, ds.Tables[0].Rows.Count);
for (int i = 0; i < data.Length; i++)
{
object instance = Activator.CreateInstance(relatedTableType);
data.SetValue(instance, i);
//loop thru dataset
for (int j = START_INDEX_FIELD; j < ds.Tables[0].Columns.Count; j++)
{
Type tp = data.GetValue(i).GetType();
object[] args = new[] { ds.Tables[0].Columns[j].ColumnName, ds.Tables[0].Rows[i][j] };
tp.InvokeMember("SetFieldValue", BindingFlags.InvokeMethod | BindingFlags.Public | BindingFlags.Instance, null, instance, args);
}
}
return(data);
}
catch
{
throw;
}
finally
{
if (ds != null)
{
ds.Dispose();
}
}
}
