How to return a Datareader C#

Question

I know I’m doing it wrong.

But I would like to know if it is possible within what I am trying to return a dataReader.

See, I created a class called client that has the method "Searcher" the same has the connection class together, code:

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

namespace ProgramaGE
{
    class Clientes
    {
        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 Clientes()
        {
            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)");
                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;
                }
            }
        }

        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 NpgsqlDataReader BuscarCliente(ArrayList p_arr)
        {
            using (conn = new NpgsqlConnection(ConnString))
            {
                conn.Open();
                string cmdBuscar = String.Format("SELECT * FROM CLIENTES WHERE id_cliente = @ID");
                using (NpgsqlCommand cmd = new NpgsqlCommand(cmdBuscar, conn))
                {
                    cmd.Parameters.Add(new NpgsqlParameter("@ID", Convert.ToInt32(p_arr[0])));

                    NpgsqlDataReader reader = cmd.ExecuteReader();


                    if (reader.Read())
                    {
                        p_arr[1] = reader[0].ToString(); //txt.Text que vou passar e coluna que vou trazer dao db
                        p_arr[2] = reader[1].ToString();
                        p_arr[3] = reader[2].ToString();
                        p_arr[4] = reader[3].ToString();
                        p_arr[5] = reader[4].ToString();
                        p_arr[6] = reader[5].ToString();

                    }
                    else
                    {                
                        conn.Close();
                    }
                }
            }
        }
    }
}

The I method I’m doing the same as inserting, I used it this way on the search button:

 private void btnBuscarCliente_Click(object sender, EventArgs e)
        {
            Clientes buscar = new Clientes();
            ArrayList arr = new ArrayList();

            arr.Add(Convert.ToInt32(txtBuscarCliente.Text));
            arr.Add(txtId.Text);
            arr.Add(txtNomeCliente.Text);
            arr.Add(txtEndereco.Text);
            arr.Add(txtCidade.Text);
            arr.Add(txtEmail.Text);
            arr.Add(txtTelefone.Text);

            buscar.BuscarCliente(arr)
        }

No mistake, I just want to know if this is what I’m doing é possível if yes, how can I finish implementing.

Thank you!

Answer 1

Look, I don’t see how you could do this safely. If you use the using, the DataReader will be closed if you do not use the using, the connection will be open until someone closes it.

An alternative, is a pass Action<​Db​Data​Reader> and carry out all the necessary.

public static List<Cliente> BuscarCliente(int id, Function<NpgsqlDataReader, Cliente> callback) 
{
    var clientes = new List<Cliente>();
    using (conn = new NpgsqlConnection(ConnString))
    {
        conn.Open();
        string cmdBuscar = String.Format("SELECT * FROM CLIENTES WHERE id_cliente = @ID");
        using (NpgsqlCommand cmd = new NpgsqlCommand(cmdBuscar, conn))
        {
            cmd.Parameters.Add(new NpgsqlParameter("@ID", id));
            using (NpgsqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var cliente = callback(reader);
                    clientes.Add(cliente);
                }
            }
        }
    }
    return clientes;
}

public static Main()
{
    var idExterno = Guid.NewGuid();
    BuscarCliente(123, (reader) => {
        var cliente = new Cliente();
        cliente.IdExterno = idExterno;
        cliente.Prop1 = reader[0].ToString();
        cliente.Prop2 = reader[1].ToString();
        cliente.Prop3 = reader[2].ToString();
        cliente.Prop4 = reader[3].ToString();
        cliente.Prop5 = reader[4].ToString();
        cliente.Prop6 = reader[5].ToString();
        return cliente;
    });
}

You can even make the above method more generic by reading the header columns of the DbDataReader, assembling the return dynamically, etc.

In my view, this approach is far from the recommended one. If you can, I advise you to use at least the Dapper, or better yet Dapper + Dommel.

EDIT

In your specific case, the code below should suffice.:

public void BuscarCliente(int id, Action<NpgsqlDataReader> callback) 
{
    using (conn = new NpgsqlConnection(ConnString))
    {
        conn.Open();
        string cmdBuscar = String.Format("SELECT * FROM CLIENTES WHERE id_cliente = @ID");
        using (NpgsqlCommand cmd = new NpgsqlCommand(cmdBuscar, conn))
        {
            cmd.Parameters.Add(new NpgsqlParameter("@ID", id));
            using (NpgsqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    callback(reader);
                }
            }
        }
    }
}

private void btnBuscarCliente_Click(object sender, EventArgs e)
{
    var id = Convert.ToInt32(txtBuscarCliente.Text);
    BuscarCliente(id, (reader) => {
        txtId.Text = reader[0].ToString();
        txtNomeCliente.Text = reader[1].ToString();
        txtEndereco.Text = reader[2].ToString();
        txtCidade.Text = reader[3].ToString();
        txtEmail.Text = reader[4].ToString();
        txtTelefone.Text = reader[5].ToString();
    });
}