Datagrid show foreign key value instead of ID

Question

I’m showing the data from a table(TB_LIBERACAO) in a DataGrid, however this table has 2 foreign keys from the 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 id’s(numbers) will appear, but I would like the value of the board fields to appear(tbCarro) and name(tbMotorista).

I can do it in the database, but I’m not getting through 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 plays the objects to a list and returns this 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();
        }
    }

Answer 1

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 don’t know if the fields and table names are correct, but the most important is your sql that will return the values you are behind.

I hope I helped friend.

Answer 2

People 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 Releases model class,

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