/// <summary>
/// Get data from a child table based on the relation name and the primary key's fieldValue from the parent table.
/// </summary>
/// <param name="primaryKeyValue">The fieldValue of the primary key</param>
/// <returns>The selected TableMetadata </returns>
public TableMetadata GetTableMetadata(object primaryKeyValue)
{
SqlGenerator generator = new SqlGenerator();
DataFactory factory = new DataFactory();
//generate select statement
if (primaryKeyValue == null)
{
throw new ArgumentException("Invalid fieldValue for primary key");
}
ISqlGenerator isql = factory.InitializeSqlGenerator(database);
DatabaseField pkField = mappedObject.GetPrimaryKeyField();
pkField.fieldValue = primaryKeyValue;
//generate select query
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, mappedObject.TableFields, pkField);
TableMetadata table = (TableMetadata)Activator.CreateInstance(mappedObject.GetType());
ArrayList alList = MapDataReaderToTableMetadata(selectQuery, table);
table = (TableMetadata)alList[0];
return(table);
}
/// <summary>
/// Returns a dataset with the data from the specified fields.
/// </summary>
/// <param name="fields">DatabaseFields which will be included in the select</param>
/// <returns>DataSet with results</returns>
public DataSet GetDataSet(params DatabaseField[] fields)
{
DataSet ds = null;
SqlGenerator generator = new SqlGenerator();
if (fields.Length == 0)
{
throw new ArgumentException("Invalid fields number");
}
ds = new DataSet();
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, fields, null);
if (contextSession != null)
{
ds = execEngine.ExecuteDataSet(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
ds = e.ExecuteDataSet(selectQuery);
}
}
return(ds);
}
/// <summary>
/// Returns a dataset with all the data from our business object
/// </summary>
/// <returns>DataSet containing all the data</returns>
public DataSet GetDataSet()
{
DataSet dsTemp = null;
SqlGenerator generator = new SqlGenerator();
try
{
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject, false);
dsTemp = new DataSet();
//check for session
if (contextSession != null)
{
dsTemp = execEngine.ExecuteDataSet(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
dsTemp = e.ExecuteDataSet(database, connectionString, selectQuery);
}
}
return(dsTemp);
}
catch (Exception ex)
{
Log.LogMessage(ex.Message + ex.StackTrace);
throw;
}
}
/// <summary>
/// Returns a DataSet which contains data from the related table
/// </summary>
/// <param name="relatedTableName">Name of the related table</param>
/// <param name="foreignKeyValue">Value of the foreign key</param>
/// <returns>DataSet containing data from the related table</returns>
public DataSet GetDataSet(string relatedTableName, object foreignKeyValue)
{
SqlGenerator generator = new SqlGenerator();
DataSet ds = new DataSet();
ExecutionQuery selectQuery = new ExecutionQuery();
TableRelation[] relations = mappedObject.Relations;
for (int i = 0; i < relations.Length; i++)
{
if (relations[i].RelatedTableName == relatedTableName.Trim())
{
DatabaseField keyField;
//check if we have a ParentRelation or a ChildRelation
if (relations[i] is ParentTableRelation)
{
DatabaseField primaryKeyField = mappedObject.GetPrimaryKeyField();
//this is the parent so we select from the child table.
keyField = new DatabaseField(primaryKeyField.fieldType, ((ParentTableRelation)relations[i]).ForeignKeyName, false, false, foreignKeyValue);
}
else
{
//child relation
ChildTableRelation childRelation = (ChildTableRelation)relations[i];
//this is the child so get data from the parent
keyField = new DatabaseField(mappedObject.GetPrimaryKeyField().fieldType, childRelation.RelatedTableKeyName, true, false, foreignKeyValue);
}
selectQuery = generator.GenerateSelectQuery(database, relations[i].RelatedTableName, keyField);
break;
}
}
if (selectQuery.Query == string.Empty)
{
throw new ArgumentException("Invalid relation name");
}
//run the query in the associated context
if (contextSession != null)
{
ds = execEngine.ExecuteDataSet(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
ds = e.ExecuteDataSet(selectQuery);
}
}
return(ds);
}
/// <summary>
/// Checks if the specified fieldValue exists in the database. Returns true if the
/// fieldValue doesn't exists in the database and false if it exists
/// </summary>
/// <param name="field">The field to which the specified fieldValue belongs</param>
/// <param name="value">Value to search for</param>
/// <returns>Returns true if a field with the specified fieldValue is found</returns>
public bool IsUnique(DatabaseField field, object value)
{
SqlGenerator generator = new SqlGenerator();
ExecutionEngine exec = null;
try
{
//TODO: implement this with count
object oldValue = field.fieldValue; //save the initial fieldValue of the field
field.fieldValue = value; //set the new fieldValue to the field
//get the execution query
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, field);
object resultValue = null;
//check execution context
if (contextSession != null)
{
resultValue = execEngine.ExecuteScalar(selectQuery);
field.fieldValue = oldValue;
if (resultValue == null)
{
return(true);
}
return(false);
}
exec = new ExecutionEngine();
resultValue = exec.ExecuteScalar(database, connectionString, selectQuery);
//set the original fieldValue back
field.fieldValue = oldValue;
if (resultValue == null)
{
return(true);
}
return(false);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Return a sorted list with all the data from the specified 2 fields
/// </summary>
/// <param name="idField">First field</param>
/// <param name="descriptionField">Second field.</param>
/// <returns>StringDictionary which contains the selected data</returns>
public virtual SortedList GetFieldList(DatabaseField idField, DatabaseField descriptionField)
{
IDataReader iread = null;
SortedList scData = null;
SqlGenerator generator = new SqlGenerator();
ExecutionEngine exec = null;
try
{
DatabaseField[] fields = new DatabaseField[2];
fields[0] = idField;
fields[1] = descriptionField;
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, fields, null);
scData = new SortedList();
if (contextSession != null)
{
iread = execEngine.ExecuteReader(selectQuery);
}
else
{
exec = new ExecutionEngine(database, connectionString);
iread = exec.ExecuteReader(database, connectionString, selectQuery);
}
while (iread.Read())
{
scData.Add(iread.GetValue(0), iread.GetValue(1));
}
iread.Close();
return(scData);
}
finally
{
if (iread != null)
{
iread.Close();
}
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Returns a list with all the data from the specified field
/// </summary>
/// <param name="field">DatabaseField based upon which data is selected</param>
/// <returns>ArrayList which contains the selected data</returns>
public virtual ArrayList GetFieldList(DatabaseField field)
{
IDataReader iread = null;
ArrayList alData = null;
SqlGenerator generator = new SqlGenerator();
ExecutionEngine exec = null;
try
{
alData = new ArrayList();
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject.TableName, new DatabaseField[1] {
field
}, null);
if (contextSession != null)
{
iread = execEngine.ExecuteReader(selectQuery);
}
else
{
exec = new ExecutionEngine();
iread = exec.ExecuteReader(database, connectionString, selectQuery);
}
while (iread.Read())
{
alData.Add(iread.GetValue(0));
}
iread.Close();
return(alData);
}
finally
{
if (iread != null)
{
iread.Close();
}
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Get data as a TableMetadata array
/// </summary>
/// <returns>TableMetadata Array</returns>
public Array GetTableMetadata()
{
try
{
SqlGenerator generator = new SqlGenerator();
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject, false);
return(GetTableMetadata(selectQuery));
}
catch (Exception ex)
{
Log.LogMessage(ex.Message + ex.StackTrace);
throw new DataBlockException(ex.Message, ex);
}
}
/// <summary>
/// Gets a DataTable which contains all the data for current TableMetadata
/// </summary>
/// <returns>Resulting DataTable</returns>
public DataTable GetDataTable()
{
DataTable dsTemp = null;
SqlGenerator generator = new SqlGenerator();
ExecutionQuery selectQuery = generator.GenerateSelectQuery(database, mappedObject, false);
//check for session
if (contextSession != null)
{
dsTemp = execEngine.ExecuteDataTable(selectQuery);
}
else
{
using (ExecutionEngine e = new ExecutionEngine())
{
dsTemp = e.ExecuteDataTable(selectQuery);
}
}
return(dsTemp);
}
/// <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>
/// 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>
/// Get data from a related table (doen't matter if parent of child) based
/// on the relation name and the primary key's fieldValue from the related table.
/// </summary>
/// <param name="relatedTableName">The name of the related table class name</param>
/// <param name="classType">Class type of the related TableMetadata entity</param>
/// <param name="foreignKeyValue">Foreign key's fieldValue</param>
/// <returns>TableMetadata array which contains the specified data </returns>
public Array GetTableMetadata(string relatedTableName, Type classType, object foreignKeyValue)
{
ArrayList alList = null;
SqlGenerator generator = new SqlGenerator();
try
{
ExecutionQuery selectQuery = new ExecutionQuery();
//hold the table's relations.
TableRelation[] relations = mappedObject.Relations;
//loop and get the relation
for (int i = 0; i < relations.Length; i++)
{
if (relations[i].RelatedTableName == relatedTableName.Trim())
{
DatabaseField keyField;
//check if we habe a ParentRelation or a ChildRelation
if (relations[i] is ParentTableRelation)
{
DatabaseField primaryKeyField = mappedObject.GetPrimaryKeyField();
//this is the parent so we select from the child table.
keyField = new DatabaseField(primaryKeyField.fieldType, ((ParentTableRelation)relations[i]).ForeignKeyName, false, false, foreignKeyValue);
}
else
{
//child relation
ChildTableRelation childRelation = (ChildTableRelation)relations[i];
//this is the child so get data from the parent
keyField = new DatabaseField(mappedObject.GetPrimaryKeyField().fieldType, childRelation.RelatedTableKeyName, true, false, foreignKeyValue);
}
selectQuery = generator.GenerateSelectQuery(database, relations[i].RelatedTableName, keyField);
break;
}
}
if (selectQuery.Query == string.Empty)
{
throw new Exception("Invalid related table name");
}
object tableMetadata = Activator.CreateInstance(classType);
alList = MapDataReaderToTableMetadata(selectQuery, (TableMetadata)tableMetadata);
Array array = Array.CreateInstance(classType, alList.Count);
alList.CopyTo(array);
return(array);
}
finally
{
if (alList != null)
{
alList.Clear();
}
}
}