/// <summary> /// Add an Item to the columnlist /// </summary> /// <param name="attribute"></param> /// <param name="Operation"></param> private void AddSupportedMappingItem(AttributeMetadata attribute, IDTSInput100 input, int Operation) { MappingItem mi; if (SupportedTypes.isValidAttribute(attribute, Operation)) { mi = new MappingItem(); mi.InternalColumnName = attribute.LogicalName; mi.InternalColumnTypeName = attribute.AttributeType.ToString(); mi.InternalColumnType = attribute.AttributeType; mi.isPrimary = attribute.IsPrimaryId.HasValue ? (bool)attribute.IsPrimaryId : false; mi.isRequired = attribute.IsRequiredForForm.HasValue ? (bool)attribute.IsRequiredForForm : false; //Maps by name the Input collection with Dynamics CRM collection foreach (IDTSInputColumn100 inputcol in input.InputColumnCollection) { if (inputcol.Name == attribute.LogicalName) { mi.ExternalColumnName = inputcol.Name; mi.ExternalColumnType = inputcol.DataType; mi.ExternalColumnTypeName = inputcol.DataType.ToString(); } } columnList.Add(mi); } }
/// <summary> /// Gets information from Dynamics entity based on FetchXML query /// </summary> /// <param name="FetchXML">Quer</param> /// <param name="top">True for top 50 or all rows</param> /// <returns></returns> public DataTable GetData(String FetchXML, Boolean top) { if (string.IsNullOrEmpty(FetchXML)) { throw new Exception("FetchXML query is empty"); } try { if (service == null) { throw new Exception("No Organization Service Available"); } DataTable dTable = new DataTable(); EntityCollection result = new EntityCollection(); bool AddCol = true; int page = 1; AttributeMetadata mdta; System.Xml.Linq.XElement xe = XElement.Parse(FetchXML.Trim()); if (xe.Attribute("mapping") == null) { xe.SetAttributeValue("mapping", "logical"); } do { if (top) { if (xe.Attribute("distinct") == null) { xe.SetAttributeValue("distinct", "false"); } if (xe.Attribute("count") == null) { xe.SetAttributeValue("count", "50"); } else { xe.Attribute("count").SetValue("50"); } result = service.RetrieveMultiple(new FetchExpression(xe.ToString())); result.MoreRecords = false; } else { if (xe.Attribute("paging-cookie") == null) { xe.SetAttributeValue("paging-cookie", result.PagingCookie); } else { xe.Attribute("paging-cookie").SetValue(result.PagingCookie); } if (xe.Attribute("count") == null) { xe.SetAttributeValue("count", "5000"); } if (xe.Attribute("page") == null) { xe.SetAttributeValue("page", System.Convert.ToString(page)); } else { xe.Attribute("page").SetValue(System.Convert.ToString(page)); } page++; result = service.RetrieveMultiple(new FetchExpression(xe.ToString())); } RetrieveEntityRequest mdRequest = new RetrieveEntityRequest() { EntityFilters = EntityFilters.Attributes, LogicalName = result.EntityName, RetrieveAsIfPublished = false }; RetrieveEntityResponse entityResponse = (RetrieveEntityResponse)service.Execute(mdRequest); entMetadata = entityResponse.EntityMetadata; if (AddCol) { foreach (Entity entity in result.Entities) { for (int iElement = 0; iElement <= entity.Attributes.Count - 1; iElement++) { string columnName = entity.Attributes.Keys.ElementAt(iElement); if (!dTable.Columns.Contains(columnName)) { mdta = entMetadata.Attributes.FirstOrDefault(m => m.LogicalName == columnName); if (SupportedTypes.isValidAttribute(mdta)) { switch (mdta.AttributeType.Value) { // break; case AttributeTypeCode.BigInt: dTable.Columns.Add(columnName, typeof(Int64)); break; case AttributeTypeCode.Boolean: dTable.Columns.Add(columnName, typeof(bool)); break; case AttributeTypeCode.DateTime: dTable.Columns.Add(columnName, typeof(DateTime)); break; case AttributeTypeCode.Decimal: dTable.Columns.Add(columnName, typeof(decimal)); break; case AttributeTypeCode.Double: case AttributeTypeCode.Money: dTable.Columns.Add(columnName, typeof(float)); break; case AttributeTypeCode.Integer: case AttributeTypeCode.Picklist: case AttributeTypeCode.State: case AttributeTypeCode.Status: dTable.Columns.Add(columnName, typeof(Int32)); break; case AttributeTypeCode.Uniqueidentifier: case AttributeTypeCode.Customer: case AttributeTypeCode.Lookup: case AttributeTypeCode.PartyList: case AttributeTypeCode.Owner: dTable.Columns.Add(columnName, typeof(Guid)); break; default: dTable.Columns.Add(columnName, typeof(string)); break; } } } } } } else { AddCol = false; } foreach (Entity entity in result.Entities) { DataRow dRow = dTable.NewRow(); for (int i = 0; i <= entity.Attributes.Count - 1; i++) { string colName = entity.Attributes.Keys.ElementAt(i); mdta = entMetadata.Attributes.FirstOrDefault(m => m.LogicalName == colName); if (mdta != null) { switch (mdta.AttributeType.Value) { //case AttributeTypeCode.Boolean: // dRow[colName] = entity.Attributes.Values.ElementAt(i).ToString() == "1" || entity.Attributes.Values.ElementAt(i).ToString().Trim().ToLower() == "true"; // break; case AttributeTypeCode.Picklist: case AttributeTypeCode.State: case AttributeTypeCode.Status: dRow[colName] = ((Microsoft.Xrm.Sdk.OptionSetValue)entity.Attributes.Values.ElementAt(i)).Value; break; case AttributeTypeCode.Customer: case AttributeTypeCode.Lookup: case AttributeTypeCode.PartyList: case AttributeTypeCode.Owner: dRow[colName] = (Guid)((Microsoft.Xrm.Sdk.EntityReference)entity.Attributes.Values.ElementAt(i)).Id; break; case AttributeTypeCode.BigInt: dRow[colName] = (Int64?)entity.Attributes.Values.ElementAt(i); break; case AttributeTypeCode.Decimal: dRow[colName] = (decimal?)entity.Attributes.Values.ElementAt(i); break; case AttributeTypeCode.Double: dRow[colName] = (double?)entity.Attributes.Values.ElementAt(i); break; case AttributeTypeCode.Integer: dRow[colName] = (int?)entity.Attributes.Values.ElementAt(i); break; case AttributeTypeCode.Money: dRow[colName] = ((Microsoft.Xrm.Sdk.Money)entity.Attributes.Values.ElementAt(i)).Value; break; case AttributeTypeCode.DateTime: dRow[colName] = (DateTime?)entity.Attributes.Values.ElementAt(i); break; case AttributeTypeCode.Uniqueidentifier: dRow[colName] = (Guid?)entity.Attributes.Values.ElementAt(i); break; case AttributeTypeCode.Boolean: dRow[colName] = (bool?)entity.Attributes.Values.ElementAt(i); break; default: dRow[colName] = (string)entity.Attributes.Values.ElementAt(i); break; } } } dTable.Rows.Add(dRow); } }while (result.MoreRecords); return(dTable); } catch (Exception e) { throw e; } }