C# MVC 5 - Run query with another query result

Question

Folks I’m coming from PHP and learning C# MVC while I migrate the applications I have. I came across the following problem that I can’t find a solution to:

This is my class that generates the menu dynamically in the system:

public class WebEstMenuAplicacao
{
    private ConexaoOracle bd;

    public List<WebEstMenu> MenuSistema(int gru_in_codigo)
    {
        using (bd = new ConexaoOracle())
        {
            var strQuery = "select " +
                             " t.men_in_codigo, " +
                             " t.men_st_descricao," +
                             " t.men_st_caminho," +
                             " t.men_st_icone," +
                             " t.men_st_status" +
                           " from " +
                             " MGESTOQUE.WEB_EST_SYSMENU t ";
            //strQuery += string.Format(" t.gru_in_codigo= ('{0}')", gru_in_codigo);
            var retorno = bd.ExecutaComandoComRetorno(strQuery);
            return ReaderEmListaMenu(retorno);
        }
    }

    public List<WebEstMenu> ReaderEmListaMenu(OracleDataReader reader)
    {
        var tabAplicacao = new List<WebEstMenu>();
        while (reader.Read())
        {
            var tempoObjeto = new WebEstMenu()
            {
                men_in_codigo = int.Parse(reader["men_in_codigo"].ToString()),
                men_st_descricao = reader["men_st_descricao"].ToString(),
                men_st_caminho = reader["men_st_caminho"].ToString(),
                men_st_icone = reader["men_st_icone"].ToString(),
                men_st_status = int.Parse(reader["men_st_status"].ToString())
            };
            tabAplicacao.Add(tempoObjeto);
        }
        reader.Close();
        return tabAplicacao;
    }
}

This is my controller:

public ActionResult Index()
{
    if (tempData.vORG_IN_CODIGO > 0)
    {
        var appWebEstMenu = new WebEstMenuAplicacao();
        var webEstMenu = appWebEstMenu.MenuSistema(tempData.vGRU_IN_CODIGO);

        if (webEstMenu == null)
        {
            return HttpNotFound();
        }

        return View(webEstMenu);

    } 
    else
    {
        TempData["msg"] = "<script>jAlert('Sua sessão expirou favor logar novamente.','Erro');</script>";
        return RedirectToAction("Index", "Login");
    }
}

This code generates the menu on my system by checking the employee access level. So far I’ve managed normally, but I’m unable to implement the SUBMENU check. Where it should scroll through the MENU LIST and search the submenu items. And then return the two models to my view.

Submenu Table

select t.sub_in_codigo,
       t.men_in_codigo,
       t.sub_st_descricao,
       t.sub_st_icone,
       t.sub_in_status,
       t.sub_st_url
from 
 MGESTOQUE.WEB_EST_SYSSUBMENU t

Answer 1

After I broke my head, I managed to find a solution:

I created a new class inheriting the MENU class:

MENU CLASS

    namespace Estoque.Dominio
{
    public class WebEstMenu
    {
        public int men_in_codigo { get; set; }
        public string men_st_descricao { get; set; }
        public string men_st_caminho { get; set; }        
        public string men_st_icone { get; set; }
        public int men_st_status { get; set; }
    }
}

Submenu Class

    namespace Estoque.Dominio
{
    public class WebEstSubMenu : WebEstMenu
    {
        public int Sub_in_codigo { get; set; }
        public string Sub_st_descricao { get; set; }
        public string Sub_st_icone { get; set; }
        public string Sub_st_url { get; set; }
    }

}

I created the application class where runs the MENU and SUBMENU querys, populating my domain classes.

   public class WebEstAplicacao
    {
        private ConexaoOracle bd;


       public List<WebEstSubMenu> MenuSistema(int gru_in_codigo)
               {
                   using (bd = new ConexaoOracle())
                   {
                       var strQuery = "select " +
                                        " t.men_in_codigo, " +
                                        " t.men_st_descricao," +
                                        " t.men_st_caminho," +
                                        " t.men_st_icone," +
                                        " t.men_st_status" +
                                      " from " +
                                        " MGESTOQUE.WEB_EST_SYSMENU t ";
                       //strQuery += string.Format(" t.gru_st_nome = LOWER('{0}')", gru_st_nome);
                       var retorno = bd.ExecutaComandoComRetorno(strQuery);
                       return ReaderEmListaMenu(retorno);
                   }
               }

               //
               public List<WebEstSubMenu> ReaderEmListaMenu(OracleDataReader reader)
               {
                   var tabAplicacaoMenu = new List<WebEstSubMenu>();
                   var appWebSubMenu = new WebEstSubMenuAplicacao();

                   while (reader.Read())
                   {
                       var tempoObjeto = new WebEstSubMenu()
                       {
                           men_in_codigo = int.Parse(reader["men_in_codigo"].ToString()),
                           men_st_descricao = reader["men_st_descricao"].ToString(),
                           men_st_caminho = reader["men_st_caminho"].ToString(),
                           men_st_icone = reader["men_st_icone"].ToString(),
                           men_st_status = int.Parse(reader["men_st_status"].ToString())
                       };
                       tabAplicacaoMenu.Add(tempoObjeto);
                        using (bd = new ConexaoOracle())
                        {
                            var strQuery = "select " +
                                          "  t.sub_in_codigo," +
                                          " t.men_in_codigo," +
                                          " t.sub_st_descricao," +
                                          " t.sub_st_icone," +
                                          " t.sub_in_status," +
                                          " t.sub_st_url" +
                                          " from MGESTOQUE.WEB_EST_SYSSUBMENU t";
                            strQuery += " where ";
                            strQuery += string.Format(" t.men_in_codigo = {0}", int.Parse(reader["men_in_codigo"].ToString()));
                            //strQuery += string.Format(" t.gru_st_nome = LOWER('{0}')", gru_st_nome);
                            var retorno = bd.ExecutaComandoComRetorno(strQuery);
                                while (retorno.Read())
                                {
                                    var tempoObjetoSub = new WebEstSubMenu()
                                    {
                                        men_in_codigo = int.Parse(reader["men_in_codigo"].ToString()),
                                        men_st_descricao = reader["men_st_descricao"].ToString(),
                                        men_st_caminho = reader["men_st_caminho"].ToString(),
                                        men_st_icone = reader["men_st_icone"].ToString(),
                                        men_st_status = int.Parse(reader["men_st_status"].ToString()),
                                        Sub_in_codigo = int.Parse(retorno["sub_in_codigo"].ToString()),
                                        Sub_st_descricao = retorno["sub_st_descricao"].ToString(),
                                        Sub_st_icone = retorno["Sub_st_icone"].ToString(),
                                        Sub_st_url = retorno["sub_st_url"].ToString()
                                    };
                                         tabAplicacaoMenu.Add(tempoObjetoSub);
                                }
                        }       
                   }
                   reader.Close();
                   return tabAplicacaoMenu;
               }
    }

Inside my _Layout.cshtml, I got a little bit on Razor, but I got the solution:

            @foreach (var item in Model)
            {
                var menu2 = 0;
                var menu = int.Parse(Html.DisplayFor(modelItem => item.Sub_in_codigo).ToString());
                menu2 = int.Parse(Html.DisplayFor(modelItem => item.men_in_codigo).ToString());

                if (menu == 0)
                {

                    @Html.Raw(" <li class='treeview'>");
                    @Html.Raw("<a href='#'> "+
                                "<i class='fa fa-random'></i>"+
                                "<span>"+ @Html.DisplayFor(modelItem => item.men_st_descricao).ToString() + "</span> "+
                                "<span class='pull-right-container'>"+
                                "<i class='fa fa-angle-left pull-right'></i>"+
                                "</span>"+
                                "</a>");
                    @Html.Raw("<ul class='treeview-menu'>");

                    foreach (var subemnuitem in Model)
                    {
                        var SubMenu = int.Parse(Html.DisplayFor(modelItem => subemnuitem.men_in_codigo).ToString());
                        var menu3 = int.Parse(Html.DisplayFor(modelItem => subemnuitem.Sub_in_codigo).ToString());

                        if (SubMenu == menu2 && menu3 > 0)
                        {
                            @Html.Raw("<li><a href='#' onclick='LoadingPage('Entradas','Index');'><i class='fa fa-sign-in'></i>"+ @Html.DisplayFor(modelItem => subemnuitem.Sub_st_descricao).ToString() + "</a></li>");
                        }
                        else
                        {
                            //@Html.Raw("</ul>");
                           // break;
                        }
                    }
                    @Html.Raw("</ul>");

                }
                else
                {

                    @Html.Raw("</li>");
                }
            }

Generating the menu dynamically.

Answer 2

Its object quotation is the list that contains your Menus. That way, if you need to scroll through it, you can do it this way:

Ex.:

var tabAplicacao = new List<WebEstMenu>();
var subMenusList = new List<SubMenu>();

foreach(WebEstMenu menu in tabAplicacao)
{
     // 1. Select na tabela de sub-menus, utilizando o código do menu em questão.
     menu.men_in_codigo
     // 2. Captura os sub-menus retornados pela busca.
     // 3. Adiciona-os em uma nova lista de sub-menus. (Este objeto deve ser passado para a sua View).
}