Database query for a C#List

Question

I need to make a query at the bank, I made this code for connection, but I do not know how to proceed to put this information in a list<String>

SqlConnection conexao = new SqlConnection(_stringConexao);
SqlCommand comando = new SqlCommand("SELECT CodEstabelecimento 
                                     FROM TBEstabelecimentos", conexao);

List<String> teste = new List<string>();

I intend to use this List as a basis for a ComboBox, I don’t know if this is a good practice, if there is another more practical way I accept too.

Note: I am connecting in a Sqlserver bank.

Answer 1

If you just want to generate the list and turn around later, a way to do that would be like this:

public IEnumerable<string> PegaLista() {
    //cria a conexão garantindo que ela será fechada. A string é pega do arquivo de configuração
    //É possível a string de conexão pegar da fonte que você quiser
    using (var connection = new SqlConnection(Properties.Settings.Default.DBConnectionString))
    //cria uma *query* garantindo que ela será encerrada ao final
    using (var cmd = connection.CreateCommand()) {
        connection.Open(); //abre conexão
        cmd.CommandText = "select COUNT(CodEstabelecimento) as contagem from TBEstabelecimentos"; //define a query p/ o DB
        using (var reader = cmd.ExecuteReader()) { //cria um leitor do ADO.Net
            while (reader.Read()) { //vai lendo cada item do resultado do select
                //retorna sob demanda cada item encontrado
                yield return reader.GetString(reader.GetOrdinal("contagem"));
            }
        }
    }
}

I put in the Github for future reference.

If you need to generate the specific list you can do:

var teste = PegaLista();

You don’t need to generate the concrete list, you can get the data directly through a Binding achieving efficiency.

Read another answer to understand the yield.

If you prefer to generate the list in a traditional way you can do:

public List<string> PegaLista() {
    var lista = new List<string>();
    //cria a conexão garantindo que ela será fechada. A string é pega do arquivo de configuração
    //É possível a string de conexão pegar da fonte que você quiser
    using (var connection = new SqlConnection(Properties.Settings.Default.DBConnectionString))
    //cria uma *query* garantindo que ela será encerrada ao final
    using (var cmd = connection.CreateCommand()) {
        connection.Open(); //abre conexão
        cmd.CommandText = "select COUNT(CodEstabelecimento) as contagem from TBEstabelecimentos"; //define a query p/ o DB
        using (var reader = cmd.ExecuteReader()) { //cria um leitor do ADO.Net
            while (reader.Read()) { //vai lendo cada item do resultado do select
                //adiciona cada item encontrado na lista que será retornada
                lista.Add(reader.GetString(reader.GetOrdinal("contagem")));
            }
        }
    }
    return lista;
}

Calling the method:

var teste = PegaLista();

Something tells me you don’t want to use the COUNT() in this query, it will produce a unique result and not an effective list. But then you evaluate and exchange for a suitable field.

With this list you will still have to create a Binding with WPF. There are several ways to do this and the best one depends on the desired result.

In that answer in the OS has a more complete example to do the Binding. It is not showing how to take from the database but shows how to CollectionView can be populace with a generated list as I demonstrated above.

Using WPF is not something simple, you need to fully understand how it works. You can’t go around trying to do something on the basis of "Kiss my ox" like other technologies. Well, you’ll do it all wrong and you’ll learn to do it wrong. The best way to learn how to use this technology is by following a good book (which I know only exists in English) that details every aspect well. Fragmented knowledge in this case will hurt more than help.

This is still a naive way to create a WPF application. A real application will require more effort.

A little tutorial.