Static Datatable, how to load?

Question

I need to create a method, that when the user logs into the system, when the initial menu is loaded, it loads a Static DataTable, so that I can use the DataTable, for some popular ComboBox, without having to make several connections to my database.

So I created a Static class:

public static class Paises
{
    private static DataTable _tablePais;

    public static DataTable Tablepais
    {
        get { return Paises._tablePais; }
        set { Paises._tablePais = value; }
    }
}

I also created a class, to connect to my database:

 class Consulta_pais
{
    conexao_bd conexao = new conexao_bd();

    public void consulta()
    {
        conexao.bd_string();
        SqlConnection sqlconn = new SqlConnection(conexao.sqlconn);

        try
        {
            conexao._sql = @"SELECT code,descricao FROM Paises";
            SqlCommand cmd = new SqlCommand(conexao._sql, sqlconn);

            sqlconn.Open();

            Paises.Tablepais.Load(cmd.ExecuteReader());
        }
        catch
        {

        }
        finally
        {
            sqlconn.Close();
        }
    }
}

And then in the initial menu load, I call my class to bring the information from the bank:

 private void Menu_Inicial_Load(object sender, EventArgs e)
    {
        Consulta_pais load_pais = new Consulta_pais();
        load_pais.consulta();
    }

However, when I use to popular a combobox, the same goes blank, I’m populating the combobox so:

 private void carrega_cb_pais()
    {
        comboBox5.DataSource = Paises.Tablepais;
        comboBox5.ValueMember = "code";
        comboBox5.DisplayMember = "descricao";
    }

Answer 1

@Thomas Eric Pimentel, was looking at your code and I believe that in the class that you populate the Datatable _tablePais, you do not need to pass the class name in front of the object, because they are in the same context. I remade it with some modifications and it was like this:

public static class Paises
{
    private static DataTable _tablePais;

    public static DataTable Tablepais
    {
        get { return _tablePais; }
        private set { _tablePais = value; }
    }
}

As you are only populating your Datatable within the Countries class, I left the set as private so as not to take the risk of this Datatable receiving value outside the class that was declared, and also to use the concepts of encapsulation.

I created an example of the connection class with the database. Follow the example:

public class conexao_bd
{
    private SqlConnection sqlconn;

    public SqlConnection bd_string(out string pstrMsg)
    {
        pstrMsg = default(String);

        try
        {
            // Conexão definida no no arquvio App.Config da aplicação
            sqlconn= new SqlConnection(ConfigurationManager.ConnectionStrings["NOME_DA_CONEXAO"].ConnectionString);

            if (sqlconn.State == ConnectionState.Closed)
                sqlconn.Open();
        }
        catch (Exception ex)
        {
            pstrMsg = string.Format("Erro no método 'bd_string'\nDetalhes: {0}", ex.Message);
        }

        return sqlconn;
    }
}

The query method I set up an example, I believe that this way the code is leaner:

public class Consulta_pais
{
    conexao_bd conexao = new conexao_bd();

    public void consulta()
    {
        string strMsg = default(String);

        try
        {
            using (SqlConnection sqlconn = conexao.bd_string(out strMsg))
            {
                string strComando = @"SELECT code, descricao FROM Paises";

                using (SqlCommand cmd = new SqlCommand(strComando, sqlconn))
                {
                    Paises.Tablepais.Load(cmd.ExecuteReader());
                }
            }
        }
        catch (Exception ex)
        {
            // Tratamento da exceção
        }
    }
}

I believe that the Load of the initial menu, will not have problems in loading the combobox, I would leave as you did.

private void Menu_Inicial_Load(object sender, EventArgs e)
{
    Consulta_pais load_pais = new Consulta_pais();
    load_pais.consulta();
}

And in the end, to load the combobox would do so:

private void carrega_cb_pais()
{
    comboBox5.ValueMember = "code";
    comboBox5.DisplayMember = "descricao";
    comboBox5.DataSource = Paises.Tablepais;
    comboBox5.Update();
    comboBox5.Refresh();
}

Answer 2

Next place the line that fills the combo after informing the values and display Member

private void carrega_cb_pais()
    {

        comboBox5.ValueMember = "code";
        comboBox5.DisplayMember = "descricao";
        comboBox5.DataSource = Paises.Tablepais;
    }