How to show data from 2 tables in a grid?

Question

I am developing an application using Windows Forms and I wanted to be able to show the data of 2 tables in a gGrid, by SQL command I can do this

SELECT SU.SUBCATEGORIAID, SU.NOME, SU.CATEGORIAID, C.NOME 
FROM SUBCATEGORIA SU 
INNER JOIN CATEGORIA C 
ON SU.CATEGORIAID = C.CATEGORIAID

I wish I could do more than this code, so I could show in the grid the data of 2 tables, I could only do this

public List<SubCategoria> Localizar(string valor)
{
    if (valor != string.Empty)
    {
        return _contexto.SubCategorias
        .Where(c => c.Nome.ToUpper().Contains(valor.ToUpper()))
        .ToList();

    }
    else
    {
        return _contexto.SubCategorias.ToList();
    }
}

Here it is only shown 3 fields SubCategoriaId, Nome, CategoriaId I want to be able to show these 3 fields plus the category name.

Answer 1

If I understand correctly, you already have the method Localizar that returns you a object list of the kind Subcategoria.

What you need to do is relate this list to a DataGridView.

I’ll assume you have inside the object Subcategoria a property Categoria, okay?

First, let’s create a property of Subcategoria that returns the name of Categoria.

public string NomeCategoria {
   return this.Categoria.Nome;
}

Already in the place where you called the method Localizar, makes the relationship of the result with the DataGridView. Thus:

List<Subcategoria> result = Localizar();
MeuDataGrid.DataSource = result;

Through the option Editcolumns, that you find in the list of properties, it is possible to manage the columns that will be displayed in your grid.

Answer 2

Use Linq and enjoy the join . Also create a class for your data return, where you get the query return. There are some MVC people who like to call this kind of class Modelview.

Example :

1 -

I create a class to have the result typed

public class ResultadoParaMinhaDataGrid
{
    public int SubCategoriaId {get;set;}
    public string SubCategoriaNome {get;set;}
    public int CategoriaId {get;set;}
    public string CategoriaNome {get;set;}
}

2 - I changed your tracking method to the following :

public List<ResultadoParaMinhaDataGrid> Localizar(string valor)
{
    if (valor != string.Empty)
    {
        /*
        SELECT SU.SUBCATEGORIAID, SU.NOME, SU.CATEGORIAID, C.NOME 
        FROM SUBCATEGORIA SU 
        INNER JOIN CATEGORIA C 
        ON SU.CATEGORIAID = C.CATEGORIAID
        */

        //Traduzindo em Linq seria algo assim, onde _contexto é seu DbContext.                  
        var minhaQuery = from su in _contexto.SubCategoria
            join c in _contexto.Categoria on su.CATEGORIAID equals c.CATEGORIAID   //Assim que fazemos o inner join no Linq atenção para usar "equals" ao invés do operador "=".
            where c.Nome.ToUpper().Contains(valor.ToUpper())
            select new ResultadoParaMinhaDataGrid { SubCategoriaId = su.SUBCATEGORIAID, SubCategoriaNome = su.Nome,CategoriaId = su.CATEGORIAID, CategoriaNome = c.NOME };//Perceba que o select é um tipo anonimo onde coloco os nomes dos retornos como se fossem "alias" do SQL. Assim os nomes e tipos sendo compatíveis com a classe ResultadoParaMinhaDataGrid


        return minhaQuery.ToList(); //Aqui quando transformo em lista informo que tipo de objetos tenho na lista.

    }
    else
    {
        //Aqui simplesmente repeti por questões de simplicidade e removi o where.
        var minhaQuery = from su in _contexto.SubCategoria
            join c in _contexto.Categoria on su.CATEGORIAID equals c.CATEGORIAID                
            select new ResultadoParaMinhaDataGrid { SubCategoriaId = su.SUBCATEGORIAID, SubCategoriaNome = su.Nome,CategoriaId = su.CATEGORIAID, CategoriaNome = c.NOME };

        return minhaQuery.ToList();
    }
}

}

3 - Inform the datasource of your Datagrid.

MinhaDataGrid.Datasource = Localizar("CategoriaTeste");

4 -

Seja feliz! :)

Look at this example I created in https://dotnetfiddle.net/yftz5C

I tried to do something simple. I hope to help you.

Answer 3

You only need to make a sub query with the categories.

using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web.UI.WebControls;

namespace stackoverflow
{
    public partial class BuscarCategoriaSubCategorias : System.Web.UI.Page
    {
        protected void Button1_Click(object sender, EventArgs e)
        {
            String valor = TextBox1.Text;

            using (var ctx = new stackoverflowEntities())
            {
                var qrInSubCategoria = ctx.SubCategoria.AsQueryable();

                if (!string.IsNullOrEmpty(valor))
                {
                    qrInSubCategoria = qrInSubCategoria
                        .Where(C => C.Nome.ToUpper().Contains(valor.ToUpper()));
                }

                var qrInCategoria = qrInSubCategoria
                    .Select(SC => new
                    {
                        SubCategoria = SC,
                        Categoria = ctx.Categoria.FirstOrDefault(C => C.CategoriaId == SC.CategoriaId)
                    })
                    .Select(SC => new ResultadoCategSubCategoriaGrid
                    {
                        SubCategoriaId = SC.SubCategoria.SubCategoriaId,
                        SubCategoriaNome = SC.SubCategoria.Nome,
                        CategoriaId = SC.Categoria.CategoriaId,
                        CategoriaNome = SC.Categoria.Nome,
                    }).ToList();

                GridView1.DataSource = qrInCategoria;
                GridView1.DataBind();
            }
        }
    }

    [Serializable, NotMapped]
    public class ResultadoCategSubCategoriaGrid
    {
        public int SubCategoriaId { get; set; }
        public string SubCategoriaNome { get; set; }
        public int CategoriaId { get; set; }
        public string CategoriaNome { get; set; }

    }
}

Aspx;

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="BuscarCategoriaSubCategorias.aspx.cs" Inherits="stackoverflow.BuscarCategoriaSubCategorias" %>

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
    <table>
        <tr>
            <td>
                <asp:Label ID="Label1" runat="server" Text="Sub Categoria"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            </td>
        </tr>

        <tr>
            <td></td>
            <td>
                <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Buscar" />
            </td>
        </tr>
    </table>
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
</asp:Content>

Entity model;

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace stackoverflow
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;

    public partial class stackoverflowEntities : DbContext
    {
        public stackoverflowEntities()
            : base("name=stackoverflowEntities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public virtual DbSet<Categoria> Categoria { get; set; }
        public virtual DbSet<SubCategoria> SubCategoria { get; set; }
    }
}

Bench;

CREATE TABLE [dbo].[Categoria](
    [CategoriaId] [int] IDENTITY(1,1) NOT NULL,
    [Nome] [varchar](100) NOT NULL,
    [DtInclusao] [datetime] NOT NULL,
 CONSTRAINT [PK_Categoria] PRIMARY KEY CLUSTERED 
(
    [CategoriaId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert into Categoria values
('Calçados', getdate()),
('Roupas', getdate())



CREATE TABLE [dbo].[SubCategoria](
    [SubCategoriaId] [int] IDENTITY(1,1) NOT NULL,
    [Nome] [varchar](100) NOT NULL,
    [CategoriaId] [int] NOT NULL,
 CONSTRAINT [PK_SubCategoria] PRIMARY KEY CLUSTERED 
(
    [SubCategoriaId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into SubCategoria values
('havaianas',1),
('botas dakota',1),
('botas ramarim',1),
('botas jacomo',1),
('Blusa Inverno',2),
('Blusa Regata',2),
('Vestido',2)

Answer 4

@Guilherme J Santos understood very well the method you passed, but you are giving error in

return minhaQuery.ToList<ResultadoCategSubCategoriaGrid>();