private string GetValueFromForeignKeyColumns(Guid tenantId, string tableName, string prefix, string primaryKey, string columnsName, string resourceId) { var targetValue = string.Empty; var selectQueryBuilder = new SelectQueryBuilder(); selectQueryBuilder.SelectFromTable(tableName, prefix); selectQueryBuilder.AddWhere(columnsName, Comparison.Equals, resourceId, 1); var selectQuery = selectQueryBuilder.BuildQuery(); IQueryReview review = new QueryReview(); var targetResult = review.GetResult(tenantId, tableName, selectQuery); if (targetResult != null && targetResult.Rows.Count > 0) { var targetColumn = primaryKey.TrimStart('[').TrimEnd(']'); if (!targetResult.Columns.Contains(targetColumn)) { return(targetValue); } targetValue = targetResult.Rows[0][targetColumn].ToString(); } return(targetValue); }
//tested on //batchtype (26th July 2019), public dynamic BuildDeleteQuery(Guid tenantId, Guid userId, Guid itemId, string entityName) { IMetadataManager entityManager = new MetadataManager.Contracts.MetadataManager(); var columns = entityManager.GetColumnNameByEntityName(entityName, null); //@todo need to add options based column methods. if (!columns.Any()) { return(string.Empty); } var query = ""; var tableName = entityManager.GetTableNameByEntityname(entityName); var primaryKey = entityManager.GetPrimaryKeyByEntityname(entityName); var selectQueryBuilder = new SelectQueryBuilder(); selectQueryBuilder.SelectFromTable(tableName, string.Empty); selectQueryBuilder.AddWhere(primaryKey, Comparison.Equals, itemId.ToString(), 1); var selectQuery = selectQueryBuilder.BuildQuery(); IQueryReview review = new QueryReview(); var targetResult = review.GetResult(tenantId, entityName, selectQuery); if (targetResult == null || targetResult.Rows.Count == 0) { return(""); } //delete entity query += GetDeleteQuery(tableName, primaryKey, itemId.ToString()); //delete item //@todo need to change business constat isPicklist.. var isItem = entityManager.EntityIsAnItem(entityName, BusinessConstant.IsPickList); if (isItem) { query += GetDeleteQuery(ItemHelper.ItemTableName, ItemHelper.ItemTablePrimarykey, itemId.ToString()); } foreach (var col in columns) { if (col.DataType.ToString().ToLower().Equals("picklist") || col.DataType.ToString().ToLower().Equals("lookup")) { continue; } if (!col.EntityFullName.ToLower().Equals(entityName.ToLower())) { continue; } var targetColum = col.ColumnName.TrimStart('[').TrimEnd(']'); var targetValue = targetResult.Rows[0][targetColum].ToString(); //inverse matching column if (!string.IsNullOrEmpty(col.InverseColumnName) && !string.IsNullOrEmpty(col.InverseTableName)) { query += GetDeleteQuery(col.InverseTableName, col.InverseColumnName, targetValue); } //foreign key matching column if (!string.IsNullOrEmpty(col.ReferenceColumnName) && !string.IsNullOrEmpty(col.ReferenceTableName)) { query += GetDeleteQuery(col.InverseTableName, col.InverseColumnName, targetValue); } } return(TransactionHelper.BuildQuery(query)); }