Loading the values of a select in my Gridview

Question

My DAL who does the Select and saved in the list type<>

 using System;
 using System.Data;
 using System.Collections.Generic;
 using System.Linq;
 using System.Web;
 using MySql.Data;
 using MySql.Data.MySqlClient;
 using Geax1.Model;

 namespace Geax1.DAL
{
public class ListaVeiculosDAL
{
    private static List<_Veiculos> lv = new List<_Veiculos>();
    public static void ListaVeiulos(_Clientes obj)
    {
        using (var conn = new MySqlConnection("server=127.0.0.1;Database=xpto;User ID=root;Password='';"))
        {
            try
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT * FROM tab_veiculo ORDER BY id;", conn);

                DataSet dataset = new DataSet();
                adapter.Fill(dataset);

                foreach (DataRow linha in dataset.Tables[0].Rows)
                {
                    _Veiculos veiculo = new _Veiculos();

                    veiculo.Modelo1 = Convert.ToString(linha["modelo"]);
                    veiculo.Placa1 = Convert.ToString(linha["placa"]);
                    veiculo.Quilometragem1 = Convert.ToString(linha["quilometragem"]);
                    veiculo.Cor1 = Convert.ToString(linha["cor"]);
                    veiculo.Chassi1 = Convert.ToString(linha["chassi"]);
                    veiculo.Quilometragem1 = Convert.ToString(linha["tipo"]);

                    lv.Add(veiculo);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }

    public static List<_Veiculos> retornaVeiculo()
    {
        return lv;
    }
  }
}

Calling my method into mine Gridview. However, when executing the page is empty, it does not return any value of the select.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Geax1.DAL;
using Geax1.Model;

namespace Geax1.Views
{
 public partial class ListagemVeiculos : System.Web.UI.Page
 {
    protected void Page_Load(object sender, EventArgs e)
    {
         GrdVeiculo.AutoGenerateColumns = true;
         GrdVeiculo.DataSource = ListaVeiculosDAL.retornaVeiculo();
         GrdVeiculo.DataBind();
    }
  }
}

Answer 1

The method ExecuteNonQuery is usually used when you want to manipulate the data in the database, for example to perform an UPDATE when you only need to know the number of affected lines.

Instead of using the MySqlCommand, utilize MySqlDataAdapter to read the lines returned by a SELECT.

Example:

MySqlConnection conn = new MySqlConnection(connection);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand("SELECT * FROM...", conn);

DataSet dataset = new DataSet();
adapter.Fill(dataset);

You can now scroll through the lines in dataset.Tables[0].Rows() and create objects to fill a list (List<SeuObjeto>).

Example:

List<Veiculo> veiculos = new List<Veiculo>();

foreach (DataRow linha in dataSet.Tables[0].Rows)
{
    Veiculo veiculo = new Veiculo();

    veiculo.Placa = linha["PLACA"];
    veiculo.Marca = linha["MARCA"];
    veiculo.Ano = linha["ANO"];

    veiculos.Add(veiculo);
}

Behold: Mysqldataadapter.

And more complete documentation on the DataAdapter of ADO.NET: Populating a Dataset from a Dataadapter.

Answer 2

We had to activate the AutoGenerateColumns, probably.

protected void Page_Load(object sender, EventArgs e)
{
    GrdVeiculo.AutoGenerateColumns = true;
    GrdVeiculo.DataSource = ListaVeiculosDAL.retornaVeiculo();
}

Answer 3

See the example of my code

    public Entities.Usuarios[] ListaUsuarios()
    {
        List<Entities.Usuarios> lstUsuarios = new List<Entities.Usuarios>();

        Data.Connection connection = new Data.Connection(this.ConnectionString);
        connection.AbrirConexao();

        StringBuilder sqlString = new StringBuilder();
        sqlString.AppendLine("select * from usuarios");

        IDataReader reader = connection.RetornaDados(sqlString.ToString());

        int idxId = reader.GetOrdinal("ID_USUARIO");
        int idxNome = reader.GetOrdinal("NOME_USUARIO");
        int idxEmail = reader.GetOrdinal("EMAIL_USUARIO");
        int idxLogin = reader.GetOrdinal("LOGIN_USUARIO");
        int idxSenha = reader.GetOrdinal("SENHA_USUARIO");
        int idxAtivo = reader.GetOrdinal("ATIVO_USUARIO");

        while (reader.Read())
        {
            Entities.Usuarios _Usuario = new Entities.Usuarios();
            _Usuario.Id = reader.GetInt32(idxId);
            _Usuario.Nome = reader.GetString(idxNome);
            _Usuario.Email = reader.GetString(idxEmail);
            _Usuario.Login = reader.GetString(idxLogin);
            _Usuario.Senha = reader.GetString(idxSenha);
            _Usuario.Ativo = reader.GetInt32(idxAtivo) == 1;

            lstUsuarios.Add(_Usuario);
        }

        connection.FechaConexao();

        return lstUsuarios.ToArray();
    }

Answer 4

Paul, I’ve seen the problem with your code. Include:

GrdVeiculo.DataBind()

Right after the call from the stretch

GrdVeiculo.DataSource = ListaVeiculosDAL.retornaVeiculo();

The result will be this

GrdVeiculo.DataSource = ListaVeiculosDAL.retornaVeiculo();
GrdVeiculo.DataBind();

I hope it helps.