// find entities in relations where given entity's primary key is being used as foreign key public List <string> GetChildEntities(MainTableRowSelectModel model) { List <string> allChildEntities = _relations.AsEnumerable() .Where(r => r.Field <string>(RelationDataColumns.PARENT_TABLE) == model.table) .Select(r => r.Field <string>(RelationDataColumns.CHILD_TABLE)).Distinct().OrderBy(o => o).ToList(); List <string> childEntities = new List <string>(); if (model.hideChilEntitiesWhenNoData) { // if need to hide child entities when no related data found, query each child entities to check if data exists foreach (string childEntity in allChildEntities) { RelatedDataSelectModel fakeModel = new RelatedDataSelectModel { topN = 1, fromEntity = model.table, toEntity = childEntity, toEntityType = RelationType.CHILD, keyVals = model.colNameVals }; List <JQDTFriendlyColumnInfo> columnsForFrontEnd = new List <JQDTFriendlyColumnInfo>(); DataTable dt = GetParentOrChildData(fakeModel, ref columnsForFrontEnd); if (dt.Rows.Count > 0) { childEntities.Add(childEntity); } } } else { childEntities = allChildEntities; } return(childEntities); }
//// returns jquery datatables friendly list of columns //private List<JQDTFriendlyColumnInfo> JQDTFriendlyColumnList(DataTable dt) //{ // List<JQDTFriendlyColumnInfo> list = new List<JQDTFriendlyColumnInfo>(); // foreach (DataColumn col in dt.Columns) // { // if (Type.GetTypeCode(col.DataType).IsNumericColumn()) // { // list.Add(new JQDTFriendlyColumnInfo(col.ColumnName, ColumnTypeToCategory(col.DataType) AppConst.JQDT_COL_ALIGN.RIGHT)); // } // else // { // list.Add(new JQDTFriendlyColumnInfo(col.ColumnName)); // } // } // return list; //} public DataTable GetParentOrChildData(RelatedDataSelectModel model, ref List <JQDTFriendlyColumnInfo> columnsForFrontEnd) { // set model.fromEntityForeignKey and remove fk from table name if needed (in case there are multiple fks for same parent) if (model.toEntityType == RelationType.PARENT && model.toEntity.Contains(PARENT_TABLE_FK_SEPARATOR)) { var elements = model.toEntity.Split(PARENT_TABLE_FK_SEPARATOR); model.toEntity = elements[1]; model.fromEntityForeignKey = elements[0]; } List <JQDTFriendlyColumnInfo> fromEntityCols = _dataAccess.GetColumns(model.fromEntity); List <JQDTFriendlyColumnInfo> toEntityCols = _dataAccess.GetColumns(model.toEntity); string criteria = _dataAccess.ColNameValToCriteria(model.keyVals, fromEntityCols); string sql = _dataAccess.BuildBasicSql("*", model.fromEntity, criteria, fromEntityCols[0].name, model.ascDesc, 1); DataTable dt = _dataAccess.GetData(sql); // get from table row for passed pk values List <ColNameValueModel> colVals; if (model.toEntityType == RelationType.PARENT) { colVals = ChildToParentColVals(model, dt); } else { colVals = ParentToChildColVals(model, dt); } string toCiteria = _dataAccess.ColNameValToCriteria(colVals, toEntityCols); sql = _dataAccess.BuildBasicSql("*", model.toEntity, toCiteria, toEntityCols[0].name, model.ascDesc, model.topN); DataTable toDt = EntitySqlToDtForFrontEnd(model.toEntity, sql, ref columnsForFrontEnd); return(toDt); }
private List <ColNameValueModel> ChildToParentColVals(RelatedDataSelectModel model, DataTable childTable) { List <ColNameValueModel> colVals = new List <ColNameValueModel>(); foreach (var match in _relations.AsEnumerable() .Where(r => r.Field <string>(RelationDataColumns.CHILD_TABLE) == model.fromEntity && r.Field <string>(RelationDataColumns.PARENT_TABLE) == model.toEntity)) { string cNm = match.Field <string>(RelationDataColumns.PARENT_KEY); string cVal = ""; if (String.IsNullOrEmpty(model.fromEntityForeignKey)) { cVal = childTable.Rows[0][match.Field <string>(RelationDataColumns.CHILD_KEY)].ToString(); } else { // just in case child table contains multiple fk columns (e.g. billing address id, shipping address id ) // assumed key is made of one column only (non composite) cVal = childTable.Rows[0][model.fromEntityForeignKey].ToString(); } if (!colVals.Any(o => o.colName == cNm && o.colValue == cVal)) { colVals.Add(new ColNameValueModel { colName = cNm, colValue = cVal }); } } return(colVals); }
private List <ColNameValueModel> ParentToChildColVals(RelatedDataSelectModel model, DataTable parentTable) { List <ColNameValueModel> colVals = new List <ColNameValueModel>(); foreach (var match in _relations.AsEnumerable() .Where(r => r.Field <string>(RelationDataColumns.PARENT_TABLE) == model.fromEntity && r.Field <string>(RelationDataColumns.CHILD_TABLE) == model.toEntity)) { // search by child key but set parent key because searching in parent colVals.Add(new ColNameValueModel { colName = match.Field <string>(RelationDataColumns.CHILD_KEY), colValue = parentTable.Rows[0][match.Field <string>(RelationDataColumns.PARENT_KEY)].ToString() }); } return(colVals); }
public IActionResult ParentOrChildGetData([FromBody] RelatedDataSelectModel model) { try { List <JQDTFriendlyColumnInfo> columnsForFrontEnd = new List <JQDTFriendlyColumnInfo>(); DataTable dt = _businessLayer.GetParentOrChildData(model, ref columnsForFrontEnd); return(Json(new { recordsFiltered = dt.Rows.Count, recordsTotal = dt.Rows.Count, data = dt.JQDTFriendlyTableData(), columns = columnsForFrontEnd, sortColIndex = 0, ascDesc = model.ascDesc.ToLower() })); } catch (Exception ex) { return(Json(new { error = ex.Message })); } }