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