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);
        }
示例#7
0
        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()}");
                }
            });
        }