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