1
\$\begingroup\$

I have very large method who insert child of TreeView, basically I do two different querys and set little changes I.E. firs foreach insert first child of parent and the second foreach insert child of child created before.

Foreach query just change conditional AND [ParentDesignKey] IS NULL, but inside it variables are too similar.

So here I have only two different foreach, but I need add 4 more childs of first foreach(second foreach), and I don't want very large method. Is there any way to optimize this?

Full method:

private void AssignDesign(DataTable designItemList, DataTable designFolioList, TreeNode parent, string designName, string legacyKey, int indexProject)
        {
            var iDesign = 0;


            foreach (var currentDesignItem in designItemList.Select($"[Design Name] = '{designName}' AND [Project Number] = {legacyKey}  AND [ParentDesignKey] IS NULL"))
            {
                var designItemName = currentDesignItem["DesignItemName"] as string;
                var designItemKey = currentDesignItem["DesignKey"] as int?;
                var designItemStatus = currentDesignItem["Design Status"] as string;
                var designItemIsLocked = currentDesignItem["IsLocked"] as bool?;
                var designItemStatusKey = currentDesignItem["StatusKey"] as int?;
                var designItemCreated = currentDesignItem["Design Created"] as DateTime?;
                var designItemRunCount = currentDesignItem["RunCount"] as int?;
                var designItemRunUnConCount = currentDesignItem["RunUnconCnt"] as int?;
                var designItemShopsStatus = currentDesignItem["ShopsStatus"] as int?;
                var designItemShopStatusExtended = currentDesignItem["ShopStatusExtended"] as string;
                var designItemFolio = designFolioList.Select($"[DesignKey] ={designItemKey}").FirstOrDefault()["Folio"].ToString();


                if (designItemName is null || designItemShopsStatus is null || designItemShopStatusExtended is null || designItemKey is null || designItemStatus is null || designItemStatusKey is null || designItemCreated is null || designItemRunCount is null || designItemRunUnConCount is null || designItemIsLocked is null) continue;



                var projectIndex = parent.Index;
                parent.Nodes[indexProject].Nodes.Add(designItemName);
                parent.Nodes[indexProject].Nodes[iDesign].Tag = $"{designItemKey.Value}|{projectIndex}|BDCD";
                parent.Nodes[indexProject].Nodes[iDesign].Name = "Design";
                parent.Nodes[indexProject].Nodes[iDesign].ToolTipText = $"Design: {designItemName} - Folio: {designItemFolio}";
                parent.Nodes[indexProject].Nodes[iDesign].ContextMenuStrip = contextMenuDesign;

                //load icon for design item
                this.LoadDesignStatusNodeIcon(parent.Nodes[indexProject].Nodes, iDesign, designItemStatusKey.Value, designItemIsLocked.Value);
                var parentNodes = parent.Nodes[indexProject].Nodes[iDesign].Nodes;
                parentNodes.Add(new TreeNode($"{designItemCreated.Value.ToLongDateString()} ({designItemCreated.Value.ToString("yyMMdd")})", 8, 8)
                {
                    Name = "Design Created",
                    ToolTipText = "Created Date"
                });
                parentNodes.Add(new TreeNode(designItemStatus, 18, 18)
                {
                    Name = "Design Status",
                    ToolTipText = "Design Status",
                    Tag = $"{designItemStatusKey.Value}|{designItemIsLocked}"
                });
                var imgKey = LoadShopsNodeIcon(designItemShopsStatus.Value);

                parentNodes.Add(new TreeNode(designItemShopStatusExtended, imgKey, imgKey)
                {
                    Name = "Shops Drawing Status",
                    ToolTipText = "Shops Drawing Status",
                    Tag = designItemShopsStatus.Value
                });

                if (designItemRunCount.Value == 0 && designItemRunUnConCount.Value > 0)
                {
                    parentNodes.Add(new TreeNode("Unconsolidated Runs: " + designItemRunUnConCount.Value)
                    {
                        Name = "Unconsolidated Runs",
                        ToolTipText = "Unconsolidated Runs",
                        Tag = designItemRunUnConCount.Value
                    });
                }
                else
                {
                    parentNodes.Add(new TreeNode("Run Count: " + designItemRunCount.Value)
                    {
                        Name = "Run Count",
                        ToolTipText = "Run Count",
                        Tag = designItemRunCount.Value
                    });
                }

                var iChangeOrderDesign = 0;
                foreach (var item in designItemList.Select($"[Design Name] = '{designName}' AND [Project Number] = {legacyKey} AND [ParentDesignKey] = {designItemKey}"))
                {
                    var changeOrderDesignItemName = item["DesignItemName"] as string;
                    var changeOrderDesignItemKey = item["DesignKey"] as int?;
                    var changeOrderDesignItemStatus = item["Design Status"] as string;
                    var changeOrderDesignItemIsLocked = item["IsLocked"] as bool?;
                    var changeOrderDesignItemStatusKey = item["StatusKey"] as int?;
                    var changeOrderDesignItemCreated = item["Design Created"] as DateTime?;
                    var changeOrderDesignItemRunCount = item["RunCount"] as int?;
                    var changeOrderDesignItemRunUnConCount = item["RunUnconCnt"] as int?;
                    var changeOrderDesignItemShopsStatus = item["ShopsStatus"] as int?;
                    var changeOrderDesignItemShopStatusExtended = item["ShopStatusExtended"] as string;
                    var changeOrderDesignItemFolio = designFolioList.Select($"[DesignKey] ={changeOrderDesignItemKey}").FirstOrDefault()["Folio"].ToString();
                    var changeOrderParentDesignKey = item["ParentDesignKey"] as int?;

                    if (changeOrderDesignItemName is null || changeOrderDesignItemShopsStatus is null || changeOrderDesignItemShopStatusExtended is null || changeOrderDesignItemKey is null || changeOrderDesignItemStatus is null || changeOrderDesignItemStatusKey is null || changeOrderDesignItemCreated is null || changeOrderDesignItemRunCount is null || changeOrderDesignItemRunUnConCount is null || changeOrderDesignItemIsLocked is null) continue;


                    //Create lowest level and assign their tree nodes

                    projectIndex = parent.Index;

                    TreeNode tn = parent.Nodes[indexProject].Nodes[iDesign].Nodes.Add(changeOrderDesignItemName);
                    parent.Nodes[indexProject].Nodes[iDesign].Nodes[tn.Index].Tag = $"{changeOrderDesignItemKey.Value}|{projectIndex}|CO";
                    parent.Nodes[indexProject].Nodes[iDesign].Nodes[tn.Index].Name = "Design";
                    parent.Nodes[indexProject].Nodes[iDesign].Nodes[tn.Index].ToolTipText = $"Change Order: {changeOrderDesignItemName} - Folio: {changeOrderDesignItemFolio}";
                    parent.Nodes[indexProject].Nodes[iDesign].Nodes[tn.Index].ContextMenuStrip = contextMenuDesign;


                    //load icon for changeOrderDesign item

                    var changeOrderparentNodes = parent.Nodes[indexProject].Nodes[iDesign].LastNode.Nodes;
                    changeOrderparentNodes.Add(new TreeNode($"{changeOrderDesignItemCreated.Value.ToLongDateString()} ({changeOrderDesignItemCreated.Value.ToString("yyMMdd")})", 8, 8)
                    {
                        Name = "Change Order Design Created",
                        ToolTipText = "Created Date"
                    });
                    changeOrderparentNodes.Add(new TreeNode(changeOrderDesignItemStatus, 18, 18)
                    {
                        Name = "Design Status",
                        ToolTipText = "Design Status",
                        Tag = $"{changeOrderDesignItemStatusKey.Value}|{changeOrderDesignItemIsLocked}"
                    });
                    var changeOrderimgKey = LoadShopsNodeIcon(changeOrderDesignItemShopsStatus.Value);

                    changeOrderparentNodes.Add(new TreeNode(changeOrderDesignItemShopStatusExtended, changeOrderimgKey, changeOrderimgKey)
                    {
                        Name = "Shops Drawing Status",
                        ToolTipText = "Shops Drawing Status",
                        Tag = changeOrderDesignItemShopsStatus.Value
                    });

                    if (changeOrderDesignItemRunCount.Value == 0 && changeOrderDesignItemRunUnConCount.Value > 0)
                    {
                        changeOrderparentNodes.Add(new TreeNode("Unconsolidated Runs: " + changeOrderDesignItemRunUnConCount.Value)
                        {
                            Name = "Unconsolidated Runs",
                            ToolTipText = "Unconsolidated Runs",
                            Tag = changeOrderDesignItemRunUnConCount.Value
                        });
                    }
                    else
                    {
                        changeOrderparentNodes.Add(new TreeNode("Run Count: " + changeOrderDesignItemRunCount.Value)
                        {
                            Name = "Run Count",
                            ToolTipText = "Run Count",
                            Tag = changeOrderDesignItemRunCount.Value
                        });
                    }

                    this.LoadChangeOrderNodeIcon(parent.Nodes[indexProject].Nodes[iDesign].Nodes[tn.Index], iChangeOrderDesign, changeOrderDesignItemStatusKey.Value, changeOrderDesignItemIsLocked.Value);
                }

                iDesign++;
            }
        }

UPDATE

In order to do a more simple method I do a recursion and set variables depending of item received:

private void AssignDesign(DataTable designItemList, DataTable designFolioList, TreeNode parent, string designName, string legacyKey, int indexProject, string DesignType, string Condition, int currDesign)
        {
            var iDesign = 0;


            foreach (var currentDesignItem in designItemList.Select($"[Design Name] = '{designName}' AND [Project Number] = {legacyKey}  AND [ParentDesignKey] {Condition}"))
            {
                var designItemName = currentDesignItem["DesignItemName"] as string;
                var designItemKey = currentDesignItem["DesignKey"] as int?;
                var designItemStatus = currentDesignItem["Design Status"] as string;
                var designItemIsLocked = currentDesignItem["IsLocked"] as bool?;
                var designItemStatusKey = currentDesignItem["StatusKey"] as int?;
                var designItemCreated = currentDesignItem["Design Created"] as DateTime?;
                var designItemRunCount = currentDesignItem["RunCount"] as int?;
                var designItemRunUnConCount = currentDesignItem["RunUnconCnt"] as int?;
                var designItemShopsStatus = currentDesignItem["ShopsStatus"] as int?;
                var designItemShopStatusExtended = currentDesignItem["ShopStatusExtended"] as string;
                var designItemFolio = designFolioList.Select($"[DesignKey] ={designItemKey}").FirstOrDefault()["Folio"].ToString();
                var changeOrderParentDesignKey = currentDesignItem["ParentDesignKey"] as int?;
                //var designConfirmDeliveryDate = currentDesignItem["ConfirmDeliveryDate"] as DateTime?;

                if (designItemName is null || designItemShopsStatus is null || designItemShopStatusExtended is null || designItemKey is null || designItemStatus is null || designItemStatusKey is null || designItemCreated is null || designItemRunCount is null || designItemRunUnConCount is null || designItemIsLocked is null) continue;



                //Create lowest level and assign their tree nodes
                var projectIndex = parent.Index;
                TreeNodeCollection parentNodes;
                if (DesignType == "BDCD")
                {
                    parent.Nodes[indexProject].Nodes.Add(designItemName);
                    parent.Nodes[indexProject].Nodes[iDesign].Tag = $"{designItemKey.Value}|{projectIndex}|BDCD";
                    parent.Nodes[indexProject].Nodes[iDesign].Name = "Design";
                    parent.Nodes[indexProject].Nodes[iDesign].ToolTipText = $"Design: {designItemName} - Folio: {designItemFolio}";
                    parent.Nodes[indexProject].Nodes[iDesign].ContextMenuStrip = contextMenuDesign;
                    this.LoadDesignStatusNodeIcon(parent.Nodes[indexProject].Nodes, iDesign, designItemStatusKey.Value, designItemIsLocked.Value);
                    parentNodes = parent.Nodes[indexProject].Nodes[iDesign].Nodes;

                }
                else
                {
                    TreeNode tn = parent.Nodes[indexProject].Nodes[currDesign].Nodes.Add(designItemName);
                    parent.Nodes[indexProject].Nodes[currDesign].Nodes[tn.Index].Tag = $"{designItemKey.Value}|{projectIndex}|CO";
                    parent.Nodes[indexProject].Nodes[currDesign].Nodes[tn.Index].Name = "Design";
                    parent.Nodes[indexProject].Nodes[currDesign].Nodes[tn.Index].ToolTipText = $"Change Order: {designItemName} - Folio: {designItemFolio}";
                    parent.Nodes[indexProject].Nodes[currDesign].Nodes[tn.Index].ContextMenuStrip = contextMenuDesign;
                    this.LoadChangeOrderNodeIcon(parent.Nodes[indexProject].Nodes[currDesign].Nodes[tn.Index], currDesign, designItemStatusKey.Value, designItemIsLocked.Value);
                    parentNodes = parent.Nodes[indexProject].Nodes[currDesign].LastNode.Nodes;
                }


                //load icon for design item
                var dateName = string.Empty;
                switch (DesignType)
                {
                    case "BDCD":
                        dateName = "Design Created";
                        break;
                    case "CO":
                        dateName = "Change Order Design Created";
                        break;


                }

                parentNodes.Add(new TreeNode($"{designItemCreated.Value.ToLongDateString()} ({designItemCreated.Value.ToString("yyMMdd")})", 8, 8)
                {
                    Name = dateName,
                    ToolTipText = "Created Date"
                });
                parentNodes.Add(new TreeNode(designItemStatus, 18, 18)
                {
                    Name = "Design Status",
                    ToolTipText = "Design Status",
                    Tag = $"{designItemStatusKey.Value}|{designItemIsLocked}"
                });
                var imgKey = LoadShopsNodeIcon(designItemShopsStatus.Value);

                parentNodes.Add(new TreeNode(designItemShopStatusExtended, imgKey, imgKey)
                {
                    Name = "Shops Drawing Status",
                    ToolTipText = "Shops Drawing Status",
                    Tag = designItemShopsStatus.Value
                });

                if (designItemRunCount.Value == 0 && designItemRunUnConCount.Value > 0)
                {
                    parentNodes.Add(new TreeNode("Unconsolidated Runs: " + designItemRunUnConCount.Value)
                    {
                        Name = "Unconsolidated Runs",
                        ToolTipText = "Unconsolidated Runs",
                        Tag = designItemRunUnConCount.Value
                    });
                }
                else
                {
                    parentNodes.Add(new TreeNode("Run Count: " + designItemRunCount.Value)
                    {
                        Name = "Run Count",
                        ToolTipText = "Run Count",
                        Tag = designItemRunCount.Value
                    });
                }

                var lastItem = string.Empty;
                foreach (var item in designItemList.Select($"[Design Name] = '{designName}' AND [Project Number] = {legacyKey} AND [ParentDesignKey] = {designItemKey}"))
                {
                    if (lastItem != parent.Text)
                    {
                    AssignDesign(designItemList, designFolioList, parent, designName, legacyKey, indexProject, "CO", $"= {designItemKey}", iDesign);
                    }
                    lastItem = parent.Text;
                }

                iDesign++;



            }
        }

But as you can see I send filter of query in method recursion:

First foreach:

AssignDesign(tvTable, designFolioList, parent, designName, currentProjectNumber, iDesign, "BDCD","IS NULL", 0);

Second foreach (recursion)

 AssignDesign(designItemList, designFolioList, parent, designName, legacyKey, indexProject, "CO", $"= {designItemKey}", iDesign);

In first one I send "IS NULL" and in second one $"= {designItemKey}"

Is there any way to do query one time only instead two times with different filters?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.