Executereader how to fill in the fields by searching

Question

I’m having a problem understanding how to return the values within windows form (fill the textbox, etc).

This method executes the Reader data but I don’t know how I can use it to fill in the fields.

public bool BuscarCliente(int id)
{
    using (conn = new NpgsqlConnection(ConnString))
    {
        conn.Open();
        string cmdBuscar = String.Format("SELECT * FROM CLIENTES WHERE ID=@ID");

        using (NpgsqlCommand cmd = new NpgsqlCommand(cmdBuscar, conn))
        {
            cmd.Parameters.Add(new NpgsqlParameter("@ID", id));

            NpgsqlDataReader reader =  cmd.ExecuteReader();

            if (reader.Read())
            {
                //campos

            }
            else
            {
                //erro
            }
        }
    }
}

I saw in some topics the staff uses this way:

Example: txtBairro.Text = reader[0].ToString(); But in this case he used inside the button and in my case is in a class.

How do I do that?

Class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using System.Collections;

namespace GE
{
    class FDP
    {
        static string serverName = "localhost";
        static string port = "5432";
        static string userName = "postgres";
        static string password = "adm";
        static string databaseName = "GE";
        NpgsqlConnection conn = null;
        string ConnString = null;

        public FDP()
        {
            ConnString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
                                       serverName, port, userName, password, databaseName);

        }

        public bool InserirCliente(ArrayList p_arr)
        {
            using (conn = new NpgsqlConnection(ConnString))
            {
                conn.Open();
                string cmdInserir = String.Format("INSERT INTO CLIENTES (NOME, ENDERECO, CIDADE, EMAIL, TELEFONE) VALUES (@NOME, @ENDERECO, @CIDADE, @EMAIL, @TELEFONE)");

                try
                {
                    using (NpgsqlCommand cmd = new NpgsqlCommand(cmdInserir, conn))
                    {
                        cmd.Parameters.Add(new NpgsqlParameter("@NOME", p_arr[0]));
                        cmd.Parameters.Add(new NpgsqlParameter("@ENDERECO", p_arr[1]));
                        cmd.Parameters.Add(new NpgsqlParameter("@CIDADE", p_arr[2]));
                        cmd.Parameters.Add(new NpgsqlParameter("@EMAIL", p_arr[3]));
                        cmd.Parameters.Add(new NpgsqlParameter("@TELEFONE", p_arr[4]));

                        cmd.ExecuteNonQuery();
                        return true;
                    }
                }catch (NpgsqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
             }
         }

        public int UltimoRegistroCliente()
        {
            using (conn = new NpgsqlConnection(ConnString))
            {
                conn.Open();
                string cmdLast = String.Format("SELECT * FROM CLIENTES ORDER BY ID_CLIENTE DESC LIMIT 1;");

                using (NpgsqlCommand cmd = new NpgsqlCommand(cmdLast, conn))
                {
                    return Convert.ToInt32(cmd.ExecuteScalar());   
                }

            }
        }

        public bool BuscarCliente(int id)
        {
            using (conn = new NpgsqlConnection(ConnString))
            {
                conn.Open();
                string cmdBuscar = String.Format("SELECT * FROM CLIENTES WHERE ID=@ID");

                using (NpgsqlCommand cmd = new NpgsqlCommand(cmdBuscar, conn))
                {
                    cmd.Parameters.Add(new NpgsqlParameter("@ID", id));

                    NpgsqlDataReader reader =  cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        //campos

                    }
                    else
                    {
                        //erro
                    }
                }
            }
        } 

        public bool DeletarCliente(int id)
        {
            using (conn = new NpgsqlConnection(ConnString))
            {
                conn.Open();
                string cmdDeletar = String.Format("DELETE FROM CLIENTES WHERE ID_CLIENTE=@ID");

                using (NpgsqlCommand cmd = new NpgsqlCommand(cmdDeletar, conn))
                {
                    cmd.Parameters.Add(new NpgsqlParameter("@ID", id));

                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }
    }
 }

Answer 1

Without going too deep, just take the columns of the dataReader:

public bool BuscarCliente(int id)
{
    bool retorno = false;
    using (conn = new NpgsqlConnection(ConnString))
    {
        conn.Open();

        string cmdBuscar = String.Format("SELECT * FROM CLIENTES WHERE ID=@ID");

        using (NpgsqlCommand cmd = new NpgsqlCommand(cmdBuscar, conn))
        {
            cmd.Parameters.Add(new NpgsqlParameter("@ID", id));

            NpgsqlDataReader reader =  cmd.ExecuteReader();

            if (reader.Read())
            {
                this.Nome = reader[0].ToString(); //onde 0 é índice da coluna do resultado da Query
                //ou
                this.Nome = reader["coluna_nome"].ToString();

               ... outros campos...

              retorno = true;
            }
        }
        conn.Close();
    }

    return retorno;
}

There at the button event, so I understand from your code you will do the following:

    Cliente objCliente;
    private void buttonBuscarCliente_Click(object sender, EventArgs e)
    {
           objCliente = new Cliente();
           if (objCliente.BuscarCliente(1)) //onde 1 é o código que será buscado. Aqui você deve colocar o campo de busca, com uma conversão de string para inteiro
           {
               //Buscou o cliente com sucesso
               textBoxNome.Text = objCliente.Nome;
           }
           else
           {
             //Erro ao buscar cliente
             textBoxNome.Text = "";
           }
    }

BS.: I have not done any error handling.