How to convert Datatable/Dataset/Datareader to List ?

Question

User539757411 posted

I have the following code:

public List<Parceiros> ListarParceiros()
        {
            Conexao conexao = new Conexao();
            DataTable dt = new DataTable();

            string sql = "SELECT * FROM parceiros ";
            MySqlConnection con = new MySqlConnection(conexao.StrinConexao());
            MySqlCommand cmd = new MySqlCommand(sql, con);
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            con.Open();
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                lista.Add((Parceiros)row);
            }
            con.Close();
            return lista;
        }

 

But it returns an error of casting , object type.
How can i return a list of PARCEIROS ?

is it better convert Datatable, Datareader or Dataset to a List ?

Thank you. 

Answer 1

User-1516073966 posted

Hi,
You should create an instance for Parceiros class and map the values in the row to the properties in the Parceiros class.

public List<Parceiros> ListarParceiros()
{
   Conexao conexao = new Conexao();
   DataTable dt = new DataTable();
   List<Parceiros> lista = new List<Parceiros>();
   string sql = "SELECT * FROM parceiros ";
   MySqlConnection con = new MySqlConnection(conexao.StrinConexao());
   MySqlCommand cmd = new MySqlCommand(sql, con);
   MySqlDataAdapter da = new MySqlDataAdapter(cmd);
   con.Open();
   da.Fill(dt);
   foreach (DataRow row in dt.Rows)
   {
	Parceiros obj = new Parceiros();
        obj.ID = Convert.ToInt32(row["IDColumn"]);	
        obj.Description = Convert.ToInt32(row["DescriptionColumn"]);	
        //Here add the obj to the lista. 
        lista.Add(obj);
   }
   con.Close();
   return lista;
}

Here is the sample structure of your Parceiros class with attribute/properties.

public class Parceiros
{
  public int ID{get; set;}
  public string Description {get; set;}
  ...
  ...
}

Answer 2

User539757411 posted

I did the Parceiros´ structure.

It returns to me a NullReferenceException.

Where should i declare the List ? 

Answer 3

User-1516073966 posted

Hi

Declare/create the "lista" instance in the method it self. By the way In which line you are getting the null reference exception? Is it at "lista.Add(..);"?

List<Parceiros> lista = new List<Parceiros>();

Answer 4

User539757411 posted

SUCCESS!
 

On the model i have used a Datatable and then converted on the Controller to a lista:

public ActionResult Index()
        {
            Parceiros parceiro = new Parceiros();
            DataTable dt = parceiro.ListarParceiros();
            foreach (DataRow row in dt.Rows)
            {
                Parceiros p = new Parceiros();
                p.id = Convert.ToInt32(row["id"].ToString());
                p.imagem = row["imagem"].ToString();
                p.titulo = row["titulo"].ToString();
                p.texto = row["texto"].ToString();
                p.url = row["url"].ToString();
                listinha.Add(p);
            }
            ViewData["parceiros"] = listinha;
            return View();
        }

Thank you !