Recommended form for popular variables with database data

Question

Assuming this fictitious method to obtain database data, say SQL Server:

  public List<DadosDTO> ObterDados()
    {
        try
        {
            comand = connectionFactory.CreateCommand();
            comand.CommandText = "SELECT intDado, stringDado, dateDado, doubleDado FROM TB_DADOS";
            comand.Connection = connection;
            dataReader = comand.ExecuteReader();

            List<DadosDTO> LstDados = new List<DadosDTO>();

            while (dataReader.Read())
            {
                DadosDTO dados = new DadosDTO();

                //Popular variáveis aqui.                     

                LstDados.Add(dados);
            }

            return LstDados;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            connection.Close();
        }
    }

Is there a recommended standard for popular variables? A more correct and optimized form? This way that I structured the method is the best or is there another way?

My question aims to learn the best way to do and why, so if anyone knows and can explain...

Answer 1

Paulo, I don’t know if there is a more optimized way, but in your example of code, there are some things that can be protected to ensure a lower consumption of memory and optimization of code.

I made a pseudo code in the notebook and I don’t know if he has any mistakes, in case you have, I’m sorry.

I will give a brief explanation:

Use the clause using whenever possible. It will ensure that the object created in the declaration of the same, is used within its scope and that when leaving it, this object is marked to be destroyed.

In this example I consulted the values of select using the index of the result column. This is the fastest way, however it is the worst for maintenance, because if you change the order of the columns in the query command or if you insert a new field in the middle, it will change all the indexes. That is, use carefully and guide your team to only add fields at the end.

The Yield Return data; is used in methods whose return is Ienumerable, it is a double-edged knife. Yield makes your command run late. But what would that be. The moment you execute the method of Obtain data it will "prepare" the method to be executed and return you an object of Ienumerable but while you do not access it, it will not fire the query itself. That is, in a case where you have an object that has a property that is a list of objects, it will only execute the query at the moment that this data is actually read. But what is the other side of the coin? The method will be fired every time you access the property. With this instead of a single query, you will do several. There are several very complete examples on the Internet of how to get around this.

Follows my code:

---

public IEnumerable<DadosDTO> ObterDados()
{
    try
    {
        using (IDbCommand dbCommand = connectionFactory.CreateCommand())
        {
            dbCommand.CommandText = "SELECT intDado, stringDado, dateDado, doubleDado FROM TB_DADOS";
            dbCommand.Connection = connection;
            using (IDataReader dataReader = dbCommand.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    var dados = new DadosDTO {
                        ValorInteiro = dataReader.GetInt32(0),
                        ValorString = dataReader.GetString(1)
                    };

                    yield return dados;
                }
            }
        }
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        connection.Close();
    }
}