public static DataTable RetrieveDataTableReferenceSimpleDcutValue(IEnumerable <string> referenceIds, List <PdmBlockSubItemClrDto> subItemList, bool isShowLookUpItem = true) { Dictionary <int, int> dictSuibtemIdControType = new Dictionary <int, int>(); foreach (var blockSubitem in subItemList) { dictSuibtemIdControType.Add(blockSubitem.SubItemId, blockSubitem.ControlType); } Dictionary <int, Dictionary <int, object> > toReturn = new Dictionary <int, Dictionary <int, object> >(); //if (referenceIds == null || referenceIds.Count() == 0) // return simpleDcuTable; var subitemIds = subItemList.Select(o => o.SubItemId).ToList(); if (subitemIds.Count == 0) { return(new DataTable()); } string referenceInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(referenceIds, GridColumnConstantName.ProductReferenceID, true); if (referenceIds.Count() == 1 && referenceIds.ElementAt(0) == "-2") { referenceInClause = string.Empty; } string subitemIdInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(subitemIds, GridColumnConstantName.SubItemID, false); StringBuilder aQuery = new StringBuilder(); // aQuery.Append( string" SELECT ProductReferenceID,BlockID, GridID, RowID, RowValueGUID ,Sort,"); aQuery.Append(" SELECT " + GridColumnConstantName.ProductReferenceID + ", " + GridColumnConstantName.SubItemID + ", " + GridColumnConstantName.ValueText ); string queryString = aQuery.ToString() + " from pdmSearchSimpleDcuValue where " + subitemIdInclause + referenceInClause; DataTable dtSimpleValue; var allSubitemeEntityIds = subItemList.Where(o => o.EntityId.HasValue).Select(o => o.EntityId.Value).ToList(); Dictionary <int, Dictionary <object, string> > dictKeyEntityDictKeyIdDisplayString = PLMSEntityClrBL.GetDictEntityDictDisplayString(allSubitemeEntityIds); using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString)) { conn.Open(); DataAcessHelper.ExecuteReadUnCommmited(conn); dtSimpleValue = DataAcessHelper.GetDataTableQueryResult(conn, queryString); // dictKeyEntityDictKeyIdDisplayString = PLMSEntityClrBL.GetDictEntityDictDisplayString(allSubitemeEntityIds, conn); DataAcessHelper.ExecuteReadCommmited(conn); } var subItemQuery = from row in dtSimpleValue.AsDataRowEnumerable() group row by new { ProductReferenceID = (int)row[GridColumnConstantName.ProductReferenceID] } into grp orderby grp.Key.ProductReferenceID select new { Key = grp.Key.ProductReferenceID, SubitemIDAndValueList = grp.Select(r => new { id = (int)r[GridColumnConstantName.SubItemID], value = r[GridColumnConstantName.ValueText] }), }; DataTable roReturnDcuTable = CreateDataTableStrcutureFromBlockSubitem(subItemList); foreach (var o in subItemQuery) { //Dictionary<int, object> dictSubitemValue = new Dictionary<int, object>(); DataRow dataRow = roReturnDcuTable.NewRow(); dataRow[GridColumnConstantName.ProductReferenceID] = o.Key; foreach (var subitem in o.SubitemIDAndValueList) { object value = ControlTypeValueConverter.ConvertValueToObject(subitem.value, dictSuibtemIdControType[subitem.id]); if (value == null) { dataRow[subitem.id.ToString()] = DBNull.Value; } else { // CLROutput.Output("convervalue=" + value); dataRow[subitem.id.ToString()] = value; // CLROutput.Output("dataRow[subitem.id.ToString()]" + dataRow[subitem.id.ToString()]); } } roReturnDcuTable.Rows.Add(dataRow); } if (isShowLookUpItem) { List <PdmBlockSubItemClrDto> dDLSubitemDtoList = subItemList.Where(o => o.ControlType == (int)EmControlType.DDL && o.EntityId.HasValue).ToList(); foreach (var dictDDLSubitemDto in dDLSubitemDtoList) { int entityId = dictDDLSubitemDto.EntityId.Value; string stringSubItemId = dictDDLSubitemDto.SubItemId.ToString(); if (dictKeyEntityDictKeyIdDisplayString.ContainsKey(entityId)) { Dictionary <object, string> lookUpItem = dictKeyEntityDictKeyIdDisplayString[entityId]; foreach (DataRow row in roReturnDcuTable.Rows) { int?lookupid = ControlTypeValueConverter.ConvertValueToInt(row[stringSubItemId]); if (lookupid.HasValue) { int idvalue = lookupid.Value; if (lookUpItem.ContainsKey(idvalue)) { row[stringSubItemId] = lookUpItem[idvalue];; CLROutput.OutputDebug("entityID_idvalue+dislayvalue=" + entityId.ToString() + "_" + idvalue.ToString() + "_" + "_" + lookUpItem[idvalue]); } } } } } } return(roReturnDcuTable); } // key1: blockId, key2:referecne, value
internal static void SetupDataTableDDLLookupValue(List <PdmGridMetaColumnClrDto> columnList, DataTable roReturnDcuTable) { var allSubitemeEntityIds = columnList.Where(o => o.EntityId.HasValue).Select(o => o.EntityId.Value).ToList(); Dictionary <int, Dictionary <object, string> > dictKeyEntityDictKeyIdDisplayString = PLMSEntityClrBL.GetDictEntityDictDisplayString(allSubitemeEntityIds); var dDLColumnDtoList = columnList.Where(o => o.ColumnTypeId == (int)EmControlType.DDL).ToList(); foreach (var aDdlColumnDto in dDLColumnDtoList) { int columnId = aDdlColumnDto.GridColumnId; if (aDdlColumnDto.ColumnTypeId == (int)EmControlType.DDL && aDdlColumnDto.EntityId.HasValue) { int entityId = aDdlColumnDto.EntityId.Value; if (dictKeyEntityDictKeyIdDisplayString.ContainsKey(entityId)) { Dictionary <object, string> lookUpItem = dictKeyEntityDictKeyIdDisplayString[entityId]; foreach (DataRow row in roReturnDcuTable.Rows) { string stringColumnId = columnId.ToString(); // CLROutput.Output("stringColumnId=" + stringColumnId); SqlInt32 lookupid = Converter.ToDDLSqlInt32(row[stringColumnId]); if (!lookupid.IsNull) { int idvalue = lookupid.Value; if (lookUpItem.ContainsKey(idvalue)) { row[stringColumnId] = lookUpItem[idvalue];; } } } } } } }