/// <summary>
/// Gets the graph list of a hierachical TableMetadata objects.
/// Also it adds the INSERT (FK) contraints into the hierachy.
/// </summary>
/// <param name="entity">Main TableMetadata which is the root of the graph</param>
/// <param name="list">List which contains the resulted data</param>
/// <param name="listTablesNames">List which contains the name of the tables</param>
public void BuildGraphList(TableMetadata entity, ref List <object> list, ref List <string> listTablesNames)
{
TableMetadata[] attachedData = entity.AttachedData;
//get the attached data based on relation type.
List <TableMetadata> dataParent = GetAttachedDataBasedOnRelationType(entity, true);
//the primary key field of the main table
DatabaseField primaryKey = entity.GetPrimaryKeyField();
bool insertConstraint = false;
string foreignKeyName;
if (dataParent.Count > 0)
{
//check to see if the value of the PK is autoassigned or not
if (!primaryKey.isValueAutogenerated)
{
object pkValue = primaryKey.fieldValue;
//first check the value of the primary key is set
if (pkValue == null)
{
throw new ArgumentException("The PK value is null for table " + entity + " when the PK is not autogenerated");
}
//set the value in the child tables
foreach (TableMetadata currentTable in dataParent)
{
foreignKeyName = MetadataManager.GetForeignKeyName(entity, currentTable);
currentTable.SetFieldValue(foreignKeyName, pkValue);
}
}
else
{
insertConstraint = true;
foreach (TableMetadata currentTable in dataParent)
{
foreignKeyName = MetadataManager.GetForeignKeyName(entity, currentTable);
currentTable.SetFieldValue(foreignKeyName, "???");
}
}
//check if we insert constraints to this table.
if (insertConstraint)
{
if (!listTablesNames.Contains(entity.TableName))
{
list.Add(ExecutionEngine.FOREIGN_KEY_CONSTRAINT + "//" + dataParent.Count.ToString() + "//" + entity.TableName + "//" + primaryKey.fieldName);
list.Add(entity);
//add it to the list of tables with constraints.
listTablesNames.Add(entity.TableName);
}
}
//now add the child tables
for (int i = 0; i < dataParent.Count; i++)
{
List <TableMetadata> temp = GetAttachedDataBasedOnRelationType(dataParent[i], true);
if (temp.Count == 0)
{
//add all the child tables.
list.Add(dataParent[i]);
}
}
//loop thru all the childs of the main table and add to the graph.
foreach (TableMetadata var in attachedData)
{
BuildGraphList(var, ref list, ref listTablesNames);
}
}
}
/// <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;
List <string> listParameterNames = null;
//temporary vars
string fieldName = string.Empty;
int index = -1;
string tempString = string.Empty;
SqlGenerator generator = new SqlGenerator();
DataConvertor converter = new DataConvertor();
DataFactory factory = new DataFactory();
try
{
listParameterNames = new List <string>();
//initialize generator
isql = factory.InitializeSqlGenerator(DatabaseServer.SqlServer);
//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
* -Distinct
*
*/
if ((conditions[i - 1].CriteriaOperator != CriteriaOperator.Distinct) && (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.SqlServer, tableName, field, ref listParameterNames);
IDataParameter paramBetweenSecond = converter.ConvertToDataParameter(DatabaseServer.SqlServer, 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.SqlServer, 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.SqlServer, tableName, field, ref listParameterNames);
listParameters.Add(paramDifferent);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + field.fieldName + "<>" + isql.GetValue(paramDifferent));
break;
case CriteriaOperator.Like:
field.fieldValue = "%" + conditions[i].Values[0] + "%";
IDataParameter paramLike = converter.ConvertToDataParameter(DatabaseServer.SqlServer, tableName, field, ref listParameterNames);
listParameters.Add(paramLike);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + field.fieldName + " LIKE " + isql.GetValue(paramLike));
break;
case CriteriaOperator.LikeEnd:
field.fieldValue = "%" + conditions[i].Values[0];
IDataParameter paramLikeEnd = converter.ConvertToDataParameter(DatabaseServer.SqlServer, tableName, field, ref listParameterNames);
listParameters.Add(paramLikeEnd);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + field.fieldName + " LIKE " + isql.GetValue(paramLikeEnd));
break;
case CriteriaOperator.LikeStart:
field.fieldValue = conditions[i].Values[0] + "%";
IDataParameter paramLikeStart = converter.ConvertToDataParameter(DatabaseServer.SqlServer, tableName, field, ref listParameterNames);
listParameters.Add(paramLikeStart);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + field.fieldName + " LIKE " + isql.GetValue(paramLikeStart));
break;
case CriteriaOperator.Equality:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramEquality = converter.ConvertToDataParameter(DatabaseServer.SqlServer, tableName, field, ref listParameterNames);
listParameters.Add(paramEquality);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + field.fieldName + "=" + isql.GetValue(paramEquality));
break;
case CriteriaOperator.IsNull:
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + field.fieldName + " is null");
break;
case CriteriaOperator.IsNotNull:
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, 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.SqlServer, tableName, field, ref listParameterNames);
listParameters.Add(paramSmaller);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + field.fieldName + " < " + isql.GetValue(paramSmaller));
break;
case CriteriaOperator.SmallerOrEqual:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramSmallerOrEqual = converter.ConvertToDataParameter(DatabaseServer.SqlServer, tableName, field, ref listParameterNames);
listParameters.Add(paramSmallerOrEqual);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + field.fieldName + " <= " + isql.GetValue(paramSmallerOrEqual));
break;
case CriteriaOperator.Higher:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramHigher = converter.ConvertToDataParameter(DatabaseServer.SqlServer, tableName, field, ref listParameterNames);
listParameters.Add(paramHigher);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + field.fieldName + " > " + isql.GetValue(paramHigher));
break;
case CriteriaOperator.HigherOrEqual:
field.fieldValue = conditions[i].Values[0];
IDataParameter paramHigherOrEqual = converter.ConvertToDataParameter(DatabaseServer.SqlServer, tableName, field, ref listParameterNames);
listParameters.Add(paramHigherOrEqual);
sbuild.Append(" " + generator.GetTableName(DatabaseServer.SqlServer, 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.SqlServer, tableName) + "." + field.fieldName + " " + conditions[i].Values[0]);
}
else
{
//add "," for the subsequent criterias
sbOrderByCriteria.Append(", " + generator.GetTableName(DatabaseServer.SqlServer, 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.SqlServer, 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();
//determine the position of the next coma after the distinct field
int comaLength = 0;
int startIndex = index + fieldName.Length;
for (int ff = startIndex; ff < tempString.Length; ff++)
{
if (tempString[ff] == ' ')
{
++comaLength;
}
else if (tempString[ff] == ',')
{
++comaLength;
break;
}
else
{
break;
}
}
tempString = tempString.Remove(index, fieldName.Length + comaLength);
//add it at the beginning of the select
tempString = tempString.Insert(SELECT_FIELD_LENGTH, " distinct " + generator.GetTableName(DatabaseServer.SqlServer, tableName) + "." + conditions[i].Field.fieldName + ",");
//remove the "," before "FROM" if it's the case
int iix = tempString.IndexOf("FROM");
while (--iix > 0)
{
if (tempString[iix] != ' ')
{
if (tempString[iix] == ',')
{
tempString = tempString.Remove(iix, 1);
break;
}
else
{
break;
}
}
}
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.SqlServer, 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.SqlServer, 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.SqlServer, 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.SqlServer, 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.SqlServer, 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.SqlServer, 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_KEYWORD))
{
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());
}
finally
{
if (listParameterNames != null)
{
listParameterNames.Clear();
}
}
}
/// <summary>
/// Inserts a new object into the database.
/// </summary>
/// <param name="metaTable">TableMatadata from which the object is created</param>
public int Create(TableMetadata metaTable)
{
//collection of queries which will be executed
List <ExecutionQuery> listQueries = null;
int resultCounter = 0;
DatabaseField field = metaTable.GetPrimaryKeyField();
SqlGenerator generator = new SqlGenerator();
ExecutionEngine exec = null;
try
{
listQueries = new List <ExecutionQuery>();
//get the attached tabels
TableMetadata[] attachedData = metaTable.AttachedData;
//check if the table has attached tables.
//If not generate the insert only for a single table.
if (attachedData.Length == 0)
{
//generate the sql command
ExecutionQuery insertQuery = generator.GenerateInsertQuery(database, metaTable);
//add PK constraint if necessary
if (field.isValueAutogenerated)
{
insertQuery.Query = ConstraintManager.GeneratePrimaryKeyConstraint(field.fieldName, metaTable.TableName, insertQuery.Query);
}
listQueries.Add(insertQuery);
}
else
{
//generate the multiple table's insert.
List <ExecutionQuery> multipleQueries = generator.GenerateMultipleInsertQueries(database, metaTable);
// containsSpecialModifications = true;
//add the queries to the
foreach (ExecutionQuery var in multipleQueries)
{
listQueries.Add(var);
}
}
//run in the current session
if (contextSession != null)
{
//the context is in a transaction so just cache the inserts.
if (contextSession.IsInTransaction)
{
foreach (ExecutionQuery var in listQueries)
{
contextSession.Queries.Add(var);
}
}
else
{
resultCounter = execEngine.ExecuteNonQuery(listQueries);
}
}
else
{
//
BeforeExecutingQueries(Operation.Create, ref listQueries);
exec = new ExecutionEngine();
//check if we need the PK or not
if (field.isValueAutogenerated && listQueries.Count == 1)
{
List <object> listPrimaryKeysValues = new List <object>();
resultCounter = exec.ExecuteNonQuery(listQueries, Configuration.DefaultTransactionIsolationLevel, out listPrimaryKeysValues);
if (listPrimaryKeysValues.Count > 0)
{
metaTable.SetFieldValue(field.fieldName, listPrimaryKeysValues[0]);
}
}
else
{
resultCounter = exec.ExecuteNonQuery(listQueries, Configuration.DefaultTransactionIsolationLevel);
}
}
return(resultCounter);
}
finally
{
if (exec != null)
{
exec.Dispose();
}
}
}
/// <summary>
/// Min
/// </summary>
/// <param name="field">The field.</param>
/// <returns></returns>
public object GetMin(DatabaseField field)
{
return((int)RunIntrinsecFunction(CriteriaOperator.Min, field));
}
/// <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();
}
}
}
/// <summary>
/// Add a new join to the QueryCriteria
/// </summary>
/// <param name="joinType">The join type</param>
/// <param name="primaryKeyFieldTableName">Name of the table to which the primary key belongs</param>
/// <param name="primaryKey">The Primary key field</param>
/// <param name="foreignKeyFieldTableName">Name of the table to which the foreign key belongs</param>
/// <param name="foreignKey">The foreign key field</param>
/// <param name="criteria">The query criteria with which we join</param>
public void AddJoin(JoinType joinType, string primaryKeyFieldTableName, DatabaseField primaryKey, string foreignKeyFieldTableName, DatabaseField foreignKey,
QueryCriteria criteria)
{
JoinCriteriaCondition joinCriteria = new JoinCriteriaCondition(joinType, primaryKeyFieldTableName, primaryKey, foreignKeyFieldTableName, foreignKey, criteria);
listJoinCriterias.Add(joinCriteria);
}
