Recommended way to populate variables with bank data

Question

Recommended way to populate variables with bank data

Navigation

#1 by (4 votes)

8

Assuming this dummy method to get data from the database, let's 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 popularizing variables? A more correct and optimized way? Is this way that I structured the method is the best or is there another way?

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

sql c# .net ado.net

asked by anonymous 01.08.2014 / 15:39

1 answer

Attribute dir, html or css How to apply! important in a property with multiple values

score 4 · Answer 1

Paul, I do not know if there is a more optimized way, but in your code example, there are some things that can be protected to ensure lower memory consumption and code optimization.

I made a pseudo code in Notepad and I do not know if it has any errors, if any, sorry.

I'll give you a brief explanation:

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

In this example I consulted the values of the select using the index of the result column. This is the fastest way, but it is the worst for maintenance, because if you change the order of the columns in the query command or insert a new field in the middle, it will change all 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 sword. The yield causes your command to run late. But what would that be. When you run the GetData method it will "prepare" the method to be executed and return you an IEnumerable object but as long as you do not access it will not trigger 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 the moment that data is actually read. But what is the other side of the coin? The method will fire every time you access the property. With that instead of a single query, you will do several. There are a number of well-rounded examples of how to get around this on the Internet.

Follow 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();
    }
}