ExecuteReader how to fill fields for search

1

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

This method runs the data reader but I'm not sure how to 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 have seen in some topics the staff uses this way:

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

How do I do this?

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;
                }
            }
        }
    }
 }
    
asked by anonymous 20.10.2017 / 19:32

1 answer

3

Without getting too deep, just grab the dataReader columns:

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 in the button event, from what I understand of 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 = "";
           }
    }

Note: I have not made any error handling.

    
20.10.2017 / 19:38