DataGrid show foreign key value instead of ID

Question

DataGrid show foreign key value instead of ID

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

I am displaying the data of a table ( TB_LIBERACAO ) in a DataGrid , but this table has 2 foreign keys coming from tables TB_CARROS and TB_MOTORISTAS

TB_LIBERACAO
idLib
idCarro(FK)
idMotorista(FK)

TB_CARRO
idCarro
placa

TB_MOTORISTAS
idMotorista
nome

In the fields idCarro and idMotorista will appear the id's, but I would like the value of the fields pla ( tbCarro ) and name ( tbMotorista ) to appear.

I can do it in the database, but I can not get to C #

public void atualizarGrid() {
     LiberacoesControle liberacoesControle = new 
     LiberacoesControle();

     dataGridView1.DataSource = null;
     dataGridView1.DataSource = liberacoesDAL.preencherGrid();

            dataGridView1.Update();
            dataGridView1.Refresh();
 }

I have a method that throws objects to a list and returns that list

(dataGridView1.DataSource = liberacoesDAL.preencherGrid();)

public List<LiberacoesModelo> preencherGrid()
    {
        LiberacoesModelo liberacao = null;
        MySqlCommand comandoSql = null;

        try
        {
            abrirConexao();

            List<LiberacoesModelo> listaLiberacao = new List<LiberacoesModelo>();

            String sql = "Select * from liberacoes";
            comandoSql = new MySqlCommand(sql, conexao);

            comandoSql.Parameters.Clear();
            //comandoSql.Parameters.Add("@id", MySqlDbType.String).Value = id;

            MySqlDataReader dr = comandoSql.ExecuteReader();

            while (dr.Read())
            {
                liberacao = new LiberacoesModelo();

                liberacao.idliberacoes = dr.GetInt32(dr.GetOrdinal("idLiberacao"));
                liberacao.idcarro = dr.GetInt32(dr.GetOrdinal("idCarro"));
                liberacao.idmotorista = dr.GetInt32(dr.GetOrdinal("idMotorista"));
                liberacao.dataLiberacoes = dr.GetString(dr.GetOrdinal("dataLiberacao"));                    
                listaLiberacao.Add(liberacao);
            }
            dr.Close();
            return listaLiberacao;
        }
        catch (Exception erro)
        {
            throw erro;
        }
        finally
        {
            fecharConexao();
        }
    }

c#

asked by anonymous 06.09.2018 / 18:05

2 answers

1

Personal follow the method that worked for me here:

    public List<LiberacoesModelo> preencherGrid()
    {
        LiberacoesModelo liberacao = null;
        MySqlCommand comandoSql = null;

        try
        {
            abrirConexao();

            List<LiberacoesModelo> listaLiberacao = new List<LiberacoesModelo>();

            String sql = "Select * from liberacoes L join carros C on C.idCarro = L.idCarro join motoristas M on M.idMotorista = L.idMotorista";
            comandoSql = new MySqlCommand(sql, conexao);

            comandoSql.Parameters.Clear();
            //comandoSql.Parameters.Add("@id", MySqlDbType.String).Value = id;

            MySqlDataReader dr = comandoSql.ExecuteReader();

            while (dr.Read())
            {
                liberacao = new LiberacoesModelo();

                liberacao.idliberacoes = dr.GetInt32(dr.GetOrdinal("idLiberacao"));
                liberacao.placaCarro = dr.GetString(dr.GetOrdinal("placa"));
                liberacao.nomeMotorista = dr.GetString(dr.GetOrdinal("nome"));
                liberacao.dataLiberacoes = dr.GetString(dr.GetOrdinal("dataLiberacao"));                    
                listaLiberacao.Add(liberacao);
            }
            dr.Close();
            return listaLiberacao;
        }
        catch (Exception erro)
        {
            throw erro;
        }
        finally
        {
            fecharConexao();
        }
    }

Changes I've made:

Maycon
liberacao.placacarro = dr.GetString(dr.GetOrdinal("C.placa"));
liberacao.idmotorista = dr.GetString(dr.GetOrdinal("M.nome"));

Alterado para: 
liberacao.placacarro = dr.GetString(dr.GetOrdinal("placa"));
liberacao.idmotorista = dr.GetString(dr.GetOrdinal("nome"));

I had to add two more geters / seter that returned string instead of int in the class MovesModel,

public string placaCarro { get; set; }
public string nomeMotorista{ get; set; }

06.09.2018 / 19:01

How to replace certain letters in a sentence? Copy value that is enclosed in quotation marks C #

score 2 · Accepted Answer

Friend in your sql string put:

Select * from TB_LIBERACAO L
join TB_CARRO C on C.idCarro = L.idCarro
join TB_MOTORISTAS M on M.idMotorista = L.idMotorista

and in your while populating DataSource put:

while (dr.Read())
        {
            liberacao = new LiberacoesModelo();

            liberacao.idliberacoes = dr.GetInt32(dr.GetOrdinal("L.idLiberacao"));
            liberacao.placacarro = dr.GetString(dr.GetOrdinal("C.placa"));
            liberacao.idmotorista = dr.GetString(dr.GetOrdinal("M.nome"));
            liberacao.dataLiberacoes = dr.GetString(dr.GetOrdinal("L.dataLiberacao"));                    
            listaLiberacao.Add(liberacao);
        }

I do not know if the fields and table names are correct, but the most important is your sql that will return the values you are after.

I hope to have helped my friend.