private DetectionResults GetUsageFromFetchXMLQuery(EntityMetadata emd, Settings settings, BackgroundWorker worker = null)
{
QueryExpression query = ConvertFetchXMLtoQuery(settings.FetchXMLQuery);
query.NoLock = true;
query.PageInfo = new PagingInfo
{
Count = settings.RecordsReturnedPerTrip,
PageNumber = 1
};
DataCollection <string> attributes = query.ColumnSet.Columns;
EntityCollection ec;
Dictionary <string, int> attributesCount = new Dictionary <string, int>();
int total = 0;
do
{
ec = service.RetrieveMultiple(query);
total += ec.Entities.Count;
query.PageInfo.PageNumber++;
query.PageInfo.PagingCookie = ec.PagingCookie;
worker?.ReportProgress(0, string.Format("{0} records retrieved...", total));
foreach (var record in ec.Entities)
{
foreach (var attribute in attributes)
{
if (!record.Contains(attribute))
{
continue;
}
if (!attributesCount.ContainsKey(attribute))
{
attributesCount.Add(attribute, 0);
}
attributesCount[attribute] = attributesCount[attribute] + 1;
}
}
} while (ec.MoreRecords && Cancel == false);
DetectionResults result = new DetectionResults();
result.Total = total;
result.Entity = query.EntityName;
foreach (var key in attributesCount.Keys)
{
result.Results.Add(new DetectionResult
{
Attribute = MetadataHelper.GetMetadataAttribute(emd.Attributes, key),
NotNull = attributesCount[key],
Percentage = result.Total != 0 ? attributesCount[key] * 100 / (double)result.Total : 0
});
}
return(result);
}
public void AddEntity(EntityMetadata emd, DetectionResults data)
{
addEntitySummary(emd, data);
var sheet = AddWorkSheet(emd.DisplayName.UserLocalizedLabel != null ? emd.DisplayName.UserLocalizedLabel.Label : "N/A", emd.LogicalName);
if (data.Fault != null)
{
sheet.Cells[1, 1].Value = data.Fault.Message;
return;
}
int i = 1;
sheet.Cells[i, 1].Value = "Displayname";
sheet.Cells[i, 2].Value = "Logical name";
sheet.Cells[i, 3].Value = "Attribute Type";
sheet.Cells[i, 4].Value = "On Form(s)";
sheet.Cells[i, 5].Value = "Data usage";
sheet.Cells[i, 6].Value = "Data count";
foreach (var result in data.Results)
{
i++;
sheet.Cells[i, 1].Value = result.Attribute.DisplayName.UserLocalizedLabel?.Label;
sheet.Cells[i, 2].Value = result.Attribute.LogicalName;
sheet.Cells[i, 3].Value = result.Attribute.AttributeType.Value;
sheet.Cells[i, 4].Value = data.AttributeIsContainedInForms(result.Attribute.LogicalName);
sheet.Cells[i, 5].Value = result.Percentage / 100.0;
sheet.Cells[i, 5].Style.Numberformat.Format = "#0.00%";
sheet.Cells[i, 6].Value = result.NotNull;
}
sheet.Cells[1, 1, i, 6].AutoFitColumns();
}
private void addEntitySummary(EntityMetadata emd, DetectionResults data)
{
int rownum = ++entityCount + 1;
summaryWkSh.Cells[rownum, 1].Value = emd.DisplayName.UserLocalizedLabel != null ? emd.DisplayName.UserLocalizedLabel.Label : "N/A";
summaryWkSh.Cells[rownum, 2].Value = emd.LogicalName;
if (data.Fault != null)
{
summaryWkSh.Cells[rownum, 4].Value = data.Fault.Message;
}
else
{
summaryWkSh.Cells[rownum, 3].Value = data.Total;
}
}
private DetectionResults GetUsageStdQuery(EntityMetadata emd, Settings settings, BackgroundWorker worker = null)
{
var result = new DetectionResults();
List <AttributeMetadata> attributes = new List <AttributeMetadata>();
ColumnSet columnSet = null;
if (settings.Filters.ContainsKey(emd.LogicalName) &&
settings.Filters[emd.LogicalName].Attributes.Count > 0)
{
columnSet = new ColumnSet(settings.Filters[emd.LogicalName].Attributes.ToArray());
}
else
{
columnSet = new ColumnSet(MetadataHelper.FilterAttributes(emd.Attributes).Select(a => a.LogicalName).ToArray());
}
var query = new QueryExpression(emd.LogicalName)
{
PageInfo = new PagingInfo
{
Count = settings.RecordsReturnedPerTrip,
PageNumber = 1
},
ColumnSet = columnSet,
NoLock = true
};
EntityCollection ec;
Dictionary <AttributeMetadata, int> attributesCount = new Dictionary <AttributeMetadata, int>();
int total = 0;
do
{
ec = service.RetrieveMultiple(query);
total += ec.Entities.Count;
query.PageInfo.PageNumber++;
query.PageInfo.PagingCookie = ec.PagingCookie;
worker?.ReportProgress(0, string.Format("{0} records retrieved...", total));
foreach (var record in ec.Entities)
{
foreach (var attribute in MetadataHelper.FilterAttributes(emd.Attributes).OrderBy(a => a.LogicalName))
{
if (!record.Contains(attribute.LogicalName))
{
continue;
}
if (settings.FilterAttributes &&
settings.Filters.ContainsKey(record.LogicalName))
{
EntityFilterSetting fs = settings.Filters[record.LogicalName];
if (fs.Attributes.Count > 0 &&
!fs.Attributes.Contains(attribute.LogicalName))
{
continue;
}
if (fs.ShowOnlyCustom && !attribute.IsCustomAttribute.Value)
{
continue;
}
if (fs.ShowOnlyStandard && attribute.IsCustomAttribute.Value)
{
continue;
}
}
if (!attributesCount.ContainsKey(attribute))
{
attributesCount.Add(attribute, 0);
}
attributesCount[attribute] = attributesCount[attribute] + 1;
}
}
} while (ec.MoreRecords && Cancel == false);
result.Total = total;
result.Entity = emd.LogicalName;
foreach (var key in attributesCount.Keys)
{
result.Results.Add(new DetectionResult
{
Attribute = key,
NotNull = attributesCount[key],
Percentage = result.Total != 0 ? attributesCount[key] * 100 / (double)result.Total : 0
});
}
return(result);
}
private DetectionResults GetUsageSQLCount(EntityMetadata emd, Settings settings, BackgroundWorker worker = null)
{
var result = new DetectionResults();
if (!string.IsNullOrEmpty(settings.SQLConnectionString))
{
List <AttributeMetadata> attributes = new List <AttributeMetadata>();
foreach (var attribute in MetadataHelper.FilterAttributes(emd.Attributes).OrderBy(a => a.LogicalName))
{
if (settings.FilterAttributes &&
settings.Filters.ContainsKey(emd.LogicalName))
{
EntityFilterSetting fs = settings.Filters[emd.LogicalName];
if (fs.Attributes.Count > 0 &&
!fs.Attributes.Contains(attribute.LogicalName))
{
continue;
}
if (fs.ShowOnlyCustom && !attribute.IsCustomAttribute.Value)
{
continue;
}
if (fs.ShowOnlyStandard && attribute.IsCustomAttribute.Value)
{
continue;
}
}
attributes.Add(attribute);
}
string tablename = "dbo." + emd.SchemaName;
string sqlrequesttemplate = "SELECT count(*) as total{0} from {1}";
string fieldsquery = "";
if (attributes.Count() > 0)
{
fieldsquery = ", " + string.Join(",", attributes.Select(a => "count(" + a.SchemaName + ") as " + a.LogicalName));
}
string sqlrequest = string.Format(sqlrequesttemplate, fieldsquery, tablename);
using (SqlConnection connection = new SqlConnection(settings.SQLConnectionString))
{
SqlCommand command = new SqlCommand(sqlrequest, connection);
command.CommandTimeout = settings.SQLCommandTimeout;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Entity = emd.LogicalName;
result.Total = int.Parse(reader["total"].ToString());
foreach (var attribute in attributes)
{
double notNullValue = double.Parse(reader[attribute.LogicalName].ToString());
result.Results.Add(new DetectionResult
{
Attribute = attribute,
NotNull = Convert.ToInt32(notNullValue),
Percentage = result.Total != 0 ? (double)notNullValue * 100 / result.Total : 0
});
}
}
}
}
}
return(result);
}
private DetectionResults GetUsageFetchAggregate(EntityMetadata emd, Settings settings, BackgroundWorker worker = null)
{
var result = new DetectionResults();
List <AttributeMetadata> attributes = new List <AttributeMetadata>();
var emRequest = new ExecuteMultipleRequest
{
Settings = new ExecuteMultipleSettings
{
ContinueOnError = true,
ReturnResponses = true
},
Requests = new OrganizationRequestCollection()
};
var fetchXmlAttrPart = $"<attribute name=\"{emd.PrimaryIdAttribute}\" aggregate=\"count\" alias=\"count\"/>";
emRequest.Requests.Add(new RetrieveMultipleRequest
{
Query = new FetchExpression(string.Format(FetchXml, emd.LogicalName, fetchXmlAttrPart, "", settings.RecordsReturnedPerTrip))
});
var allResult = new ExecuteMultipleResponseItemCollection();
foreach (var attribute in MetadataHelper.FilterAttributes(emd.Attributes).OrderBy(a => a.LogicalName))
{
if (settings.FilterAttributes &&
settings.Filters.ContainsKey(emd.LogicalName))
{
EntityFilterSetting fs = settings.Filters[emd.LogicalName];
if (fs.Attributes.Count > 0 &&
!fs.Attributes.Contains(attribute.LogicalName))
{
continue;
}
if (fs.ShowOnlyCustom && !attribute.IsCustomAttribute.Value)
{
continue;
}
if (fs.ShowOnlyStandard && attribute.IsCustomAttribute.Value)
{
continue;
}
}
attributes.Add(attribute);
var fetchXmlConditionNotNullPart = $"<condition attribute=\"{attribute.LogicalName}\" operator=\"not-null\"/>";
emRequest.Requests.Add(new RetrieveMultipleRequest
{
Query =
new FetchExpression(string.Format(FetchXml, emd.LogicalName, fetchXmlAttrPart,
fetchXmlConditionNotNullPart, settings.RecordsReturnedPerTrip))
});
if (emRequest.Requests.Count == settings.AttributesReturnedPerTrip)
{
var tempResults = (ExecuteMultipleResponse)service.Execute(emRequest);
allResult.AddRange(tempResults.Responses);
emRequest = new ExecuteMultipleRequest
{
Settings = new ExecuteMultipleSettings
{
ContinueOnError = true,
ReturnResponses = true
},
Requests = new OrganizationRequestCollection()
};
}
}
var results = (ExecuteMultipleResponse)service.Execute(emRequest);
allResult.AddRange(results.Responses);
var allResponse = (RetrieveMultipleResponse)allResult[0].Response;
if (allResult[0].Fault != null)
{
result.IsAggregateQueryRecordLimitReached = allResult[0].Fault.ErrorCode == -2147164125;
result.Fault = allResult[0].Fault;
return(result);
}
var allCount = allResponse != null
? allResponse.EntityCollection.Entities.First().GetAttributeValue <AliasedValue>("count")
: null;
var allCountValue = allCount != null ? (int)allCount.Value : 0;
result.Total = allCountValue;
result.Entity = emd.LogicalName;
foreach (var attribute in attributes)
{
var index = attributes.IndexOf(attribute);
var resultNotNull = allResult[index + 1];
if (resultNotNull.Fault != null)
{
result.Fault = resultNotNull.Fault;
return(result);
}
var notNullValueAliased =
((RetrieveMultipleResponse)resultNotNull.Response).EntityCollection.Entities.First()
.GetAttributeValue <AliasedValue>("count");
var notNullValue = (int?)notNullValueAliased?.Value ?? 0;
result.Results.Add(new DetectionResult
{
Attribute = attribute,
NotNull = notNullValue,
Percentage = allCountValue != 0 ? (double)notNullValue * 100 / allCountValue : 0
});
}
return(result);
}
public void LoadDataUsage(bool useQueries)
{
dgvData.Rows.Clear();
lvEntities.Enabled = false;
tsbLoadEntities.Enabled = false;
tsbExportToExcel.Enabled = false;
pnlData.Visible = true;
pnlAggregateQueryRecordLimit.Visible = false;
tssCancel.Visible = useQueries;
tsbCancel.Visible = useQueries;
WorkAsync(new WorkAsyncInfo
{
Message = "Loading data usage...",
AsyncArgument =
new Tuple <EntityMetadata, bool>((EntityMetadata)lvEntities.SelectedItems[0].Tag, useQueries),
Work = (w, e) =>
{
de = new DetectiveEngine(Service);
var emd = ((Tuple <EntityMetadata, bool>)e.Argument).Item1;
var useStdQueries = ((Tuple <EntityMetadata, bool>)e.Argument).Item2;
DetectionResults result = de.GetUsage(emd, useStdQueries, settings, w);
w.ReportProgress(0, "Loading forms definitions...");
result.Forms = MetadataHelper.GetFormsDefinitions(emd.ObjectTypeCode.Value, Service);
e.Result = result;
},
PostWorkCallBack = e =>
{
SendMessageToStatusBar?.Invoke(this, new StatusBarMessageEventArgs(""));
lvEntities.Enabled = true;
tsbLoadEntities.Enabled = true;
if (e.Error != null)
{
MessageBox.Show(this, $@"An error occured: {e.Error.Message}", @"Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
return;
}
tsbExportToExcel.Enabled = true;
var results = (DetectionResults)e.Result;
var currentEntityResult = globalResults.FirstOrDefault(
r => r.Entity == ((EntityMetadata)lvEntities.SelectedItems[0].Tag).LogicalName);
if (currentEntityResult != null)
{
globalResults.Remove(currentEntityResult);
}
globalResults.Add(results);
if (results.Fault != null)
{
if (results.IsAggregateQueryRecordLimitReached)
{
pnlData.Visible = false;
pnlAggregateQueryRecordLimit.Visible = true;
lblWathNextOnPremise.Visible = !ConnectionDetail.UseOnline;
lblWhatNextOnline.Visible = ConnectionDetail.UseOnline;
llHowToUpdateAggregateQueryRecordLimit.Visible = !ConnectionDetail.UseOnline;
}
else
{
MessageBox.Show(results.Fault.Message);
lblCount.Text = results.Fault.Message;
}
return;
}
foreach (var result in results.Results)
{
dgvData.Rows.Add(result.Attribute.DisplayName.UserLocalizedLabel?.Label,
result.Attribute.LogicalName, result.Attribute.AttributeType.Value,
results.AttributeIsContainedInForms(result.Attribute.LogicalName), result.Percentage);
}
lblCount.Text = string.Format(lblCount.Tag.ToString(), results.Total);
},
ProgressChanged = e =>
{
SendMessageToStatusBar?.Invoke(this, new StatusBarMessageEventArgs(e.UserState.ToString()));
SetWorkingMessage($"Loading data usage...\n{e.UserState.ToString()}");
}
});
}
