How to select the foreign key id in the main table?

Question

public List Select() { List ListaCaminhao = new List();

        SqlConnection conexao = new SqlConnection(strCon);

        string sql = "Select * from Caminhao;";

        SqlCommand cmd = new SqlCommand(sql, conexao);

        conexao.Open();

        try
        {
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            while (reader.Read())
            {
                Model.Caminhao caminhao = new Model.Caminhao();

        caminhao.IDCaminhao = Convert.ToInt32(reader[0].ToString());

        caminhao.IDMotorista = convert.ToInt32(reader["IDMotorista"].ToString()); 

This second reader["IDMotorista"] is my secondary key, is the id I want to show the name.

Answer 1

What you need is a JOIN, to obtain the name of the Driver on the secondary table.

Select C*, M.NomeMotorista from Caminhao C -- Corrija o M.NomeMotorista para seu campo
join Motorista M
on M.IDMotorista = C.IDMotorista  -- verifique o nome correto das chaves.

You will also need to change your class caminhao with another property Nomemotorista .

caminhao.NomeMotorista = convert.ToInt32(reader["NomeMotorista "].ToString());

Answer 2

Replace the line:

string sql = "Select * from Caminhao;";

For:

string sql = "SELECT c.*, m.nome AS nomemotorista FROM caminhao AS c
JOIN motorista AS m ON m.idmotorista = c.idmotorista";

In your 'Model.Caminhao' add also the attribute 'nameMotorista';

On the line:

 caminhao.IDMotorista = convert.ToInt32(reader["IDMotorista"].ToString()); 

Change to:

caminhao.nomeMotorista = convert.ToInt32(reader["nomemotorista"].ToString()); 

In addition, you should also change from 'walking.Iddriver' to 'walking.nomeMotorista' where you upload your list to the graphic element.