How to display data from 2 tables in a grid?

4

I am developing an application using Windows Forms and wanted to be able to display data from 2 tables in a ggrid, by the 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 would like to be able to do more of this code, so I can show the data of 2 tables on the grid, I can 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 has only shown 3 fields SubCategoriaId , Nome , CategoriaId I want to be able to show these 3 fields plus the category name.

    
asked by anonymous 21.06.2016 / 00:01

4 answers

2

Use Linq and enjoy join . Also create a class for your data return, where it receives the return of the query. There is an MVC staff who likes to call this type of ModelView class.

Example:

1 -

I create a class to get the typed result

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 find 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 - Enter the datasource of your DataGrid.

MinhaDataGrid.Datasource = Localizar("CategoriaTeste");

4 -

Seja feliz! :)

See this example I created at link

I tried to do something simple. Hope it helps.

    
21.06.2016 / 17:34
3

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

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

I'll assume that you have inside the object Subcategoria a property Categoria , ok?

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

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

Already where you called the Localizar method, it does the relationship of the result with the DataGridView . So:

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

Using the EditColumns option found in the properties list, you can manage the columns that will appear on your grid.     

21.06.2016 / 12:48
1

@Jessi J Santos I understood very well the method that you passed, but it is giving error in

return minhaQuery.ToList<ResultadoCategSubCategoriaGrid>();

    
21.06.2016 / 19:52
1

You just need to do 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; }
    }
}

Bank;

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)

    
21.06.2016 / 14:08