/// <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(); } } }