private void lvEntities_SelectedIndexChanged(object sender, EventArgs e)
{
if (lvEntities.SelectedItems.Count == 0)
{
return;
}
if (settings.FilterAttributes)
{
var dialog = new FilterAttributesDialog(Service,
((EntityMetadata)lvEntities.SelectedItems[0].Tag).LogicalName);
if (dialog.ShowDialog(this) != DialogResult.OK)
{
return;
}
EntityFilterSetting fs = new EntityFilterSetting();
fs.Attributes = dialog.Attributes;
fs.ShowOnlyCustom = dialog.ShowCustomOnly;
fs.ShowOnlyStandard = dialog.ShowStandardOnly;
settings.Filters[dialog.entityLogicalName] = fs;
}
else if (settings.UseFetchXMLQuery)
{
FetchXMLDialog fetchXMLDialog = new FetchXMLDialog(Service, settings, (EntityMetadata)lvEntities.SelectedItems[0].Tag, entities);
if (fetchXMLDialog.ShowDialog(this) != DialogResult.OK)
{
return;
}
settings.FetchXMLQuery = fetchXMLDialog.FetchXMLQuery;
}
ExecuteMethod(LoadDataUsage, false);
}
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);
}