/// <summary>
/// Generates a query using paging
/// </summary>
/// <param name="metadata">The metadata.</param>
/// <param name="numberOfItems">The number of items.</param>
/// <param name="pageNumber">The page number.</param>
/// <returns></returns>
public string GeneratePaginatedQuery(TableMetadata metadata, int numberOfItems, int pageNumber)
{
StringBuilder builder = new StringBuilder();
SqlGenerator generator = new SqlGenerator();
//SELECT TOP 5 * FROM items WHERE intitemid NOT IN (SELECT TOP 10 intitemid FROM items ORDER BY intitemid) ORDER BY intitemid asc
DatabaseField field = metadata.GetPrimaryKeyField();
string tableName = generator.GetTableName(DatabaseServer.SqlServer, metadata.TableName);
builder.Append("SELECT TOP ");
builder.Append(numberOfItems.ToString());
builder.Append(" FROM ");
builder.Append(tableName);
builder.Append(" WHERE ");
builder.Append(field.fieldName + " NOT IN (");
builder.Append(" SELECT TOP " + (pageNumber * numberOfItems).ToString());
builder.Append(" " + field.fieldName + " ");
builder.Append(" FROM ");
builder.Append(tableName);
builder.Append(" ORDER BY " + field.fieldName + ")");
builder.Append(" ORDER BY " + field.fieldName + " asc");
return(builder.ToString());
}
/// <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);
}
public int IndexOfTableMetadata(object primaryKeyValue, int startIndex)
{
//get the database field for this.
try
{
if (this.Count == 0)
{
return(-1);
}
TableMetadata meta = this[0];
DatabaseField field = meta.GetPrimaryKeyField();
int indexOfField = -1;
//get the index of the DatabaseField from TableMetadata
for (int i = 0; i < meta.TableFields.Length; i++)
{
if (field.fieldName == meta.TableFields[i].fieldName)
{
indexOfField = i;
break;
}
}
//get the index of the TableMetadata which contains the PK value
for (int i = startIndex; i < this.Count; i++)
{
meta = this[i];
if (meta.TableFields[indexOfField].fieldValue == primaryKeyValue)
{
return(i);
}
}
return(-1);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// Generates DELETE queries for the parent table and her related tables.
/// </summary>
/// <param name="database">Database server type</param>
/// <param name="table">TableMetadata based on which we generate the query</param>
/// <returns>List containing the queries</returns>
public List <ExecutionQuery> GenerateMultipleDeleteQueries(DatabaseServer database, TableMetadata table)
{
GraphBuilder builder = null;
List <ExecutionQuery> listQueries = null;
try
{
builder = new GraphBuilder();
listQueries = new List <ExecutionQuery>();
//generate delete for the main table.
listQueries.Add(GenerateDeleteQuery(database, table, true));
//get the PK of the main table.
DatabaseField primaryKeyField = table.GetPrimaryKeyField();
//get the relation where out table is parent
List <ParentTableRelation> listParent = MetadataManager.GetParentRelations(table);
foreach (ParentTableRelation relation in listParent)
{
//check the cascade delete flag
if (relation.CascadeDeleteChildren)
{
//reconstruct the foreign key.
DatabaseField field = new DatabaseField(primaryKeyField.fieldType, relation.ForeignKeyName, false, false, primaryKeyField.fieldValue);
listQueries.Add(GenerateDeleteQuery(database, GetTableName(database, relation.RelatedTableName), field));
}
}
return(listQueries);
}
catch
{
throw;
}
}
/// <summary>
/// Generates syncronization queries for the parent table and her related tables.
/// </summary>
/// <param name="database">Database server type</param>
/// <param name="mainTable">TableMetadata based on which we generate the queries</param>
/// <returns>List containing the queries</returns>
public List <ExecutionQuery> GenerateMultipleUpdateQueries(DatabaseServer database, TableMetadata mainTable)
{
List <ExecutionQuery> listQueries = null;
GraphBuilder builder = null;
try
{
listQueries = new List <ExecutionQuery>();
builder = new GraphBuilder();
List <object> listGraph = new List <object>();
List <string> listTableNames = new List <string>();
//generate
listQueries.Add(GenerateUpdateQuery(database, mainTable, true));
//generate the deletes rows.
TableMetadata[] removedChildTables = mainTable.RemovedData;
for (int i = 0; i < removedChildTables.Length; i++)
{
//generate DELETE by the primary key
try
{
//string foreigKeyName = builder.GetForeignKeyName(mappedObject, removedChildTables[i]);
//DatabaseField fkField = removedChildTables[i].GetField(foreigKeyName);
listQueries.Add(GenerateDeleteQuery(database, removedChildTables[i], true));
}
catch
{
continue;
}
}
//generate the insert/update rows.
TableMetadata[] attachedData = mainTable.AttachedData;
List <TableMetadata> listData = builder.GetAttachedDataBasedOnRelationType(mainTable, true);
string foreignKeyNsame = string.Empty;
for (int i = 0; i < attachedData.Length; i++)
{
//check for update or insert
DatabaseField field = MetadataManager.GetForeignKeyField(mainTable, attachedData[i]);
//check the value of the FK to determine if it's insert or update
//insert
if (field.fieldValue == null || field.fieldValue == DBNull.Value)
{
//set the value of the PK
////set the value of the PK
attachedData[i].SetFieldValue(field.fieldName, mainTable.GetPrimaryKeyField().fieldValue);
listQueries.Add(GenerateInsertQuery(database, attachedData[i]));
}
else
{
//update
listQueries.Add(GenerateUpdateQuery(database, attachedData[i], true));
}
}
return(listQueries);
}
catch
{
throw;
}
}
/// <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>
/// 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();
}
}
}
