Converting datareader to a list of Classes?

Question

User764021698 posted

Hi,

Following this post http://forums.asp.net/t/1654695.aspx?Converting+datareader+to+a+list that has been closed by Decker Dong.
When I try to use his "Marked as answer" I am receiving the error "CS0030 Cannot convert type 'object[]' to 'T'" on Build project.

How to fix it please?

Thanks,

AG

here is Decker Dong code:

Suppose you have a model class:

public class Model
{
 public int Id{get;set;}
 public string Name{get;set;}
}

public List<T> doSelect<T>(string sql)where T:new()  //Suppose your sql is select Id,Name from xxx. Your fields should match            //the public properties of your class instance.
        {
            DbCommand cmd;
            DbDataReader rs;
            object[] oo;
            List<T> res;
            res = new List<T>();
            cmd = MyConnection.CreateCommand(sql);
            rs = cmd.ExecuteReader();
             DataTable dt = rs.GetSchemaTable();

            while (rs.Read())
            {
                 int index = 0;
  T t = new T();
                
                   foreach(DataColumn col in dt.Columns)
   {
                    t.GetType().GetProperty(col.ColumnName).SetValue(t, rs.GetString(index++), null);
   }

                res.Add(oo);
            };
            rs.Close();
            return res;
        }

Answer 1

User1633621018 posted

It seems you are passing a collection in place of entity. Please recheck.

If you still get error, post your code here. 

Answer 2

User764021698 posted

Hi,

Do I need to pass Entity from Entity Framework?

What I like to do is:

I have a class that represent the names for the fields in the database table.

public class Model
    {
        public int actor_id { get; set; }
        public string first_name { get; set; }
        public string last_name { get; set; }
        public DateTime last_update { get; set; }
    }

When I select all rows from the table with this code...

private void button2_Click(object sender, EventArgs e)
        {
            using (var conn = new NpgsqlConnection("Host=localhost;Username=user;Password=password;Database=dvdrental"))
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;

                    // Retrieve all rows
                    cmd.CommandText = "SELECT * FROM Actor";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader.GetString(0));
                        }
                    }
                }
            }
        }

Instead of the "console.writeline..." I would like to automatically map each field from database table to the appropriate Model class with the value and to set Model in a List<Model>

Thanks for any help,

Asaf

Answer 3

User-271186128 posted

Hi Asaf,

You could refer to the following code:

            using (var conn = new NpgsqlConnection("Host=localhost;Username=user;Password=password;Database=dvdrental"))
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    //Create a List
                    List<Model> modelList = new List<Model>();

                    // Retrieve all rows
                    cmd.CommandText = "SELECT * FROM Actor";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Model model = new Model(); //create a new model.
                            model.actor_id = reader.GetString(0)); //Get the actor_id 
                            model.first_name = reader.GetString(1)); //Get the first_name 
                            model.last_name = reader.GetString(2)); //Get the last_name 
                            model.last_update = reader.GetString(3)); //Get the last_update

                            modelList.Add(model); //Add to list
                    
                        }
                    }
                }
            }

Best regards,
Dillion

Answer 4

User764021698 posted

Hi Dillion,

Thanks for your reply and sharing your code.

I am aware of this option of code but I am looking for an "Automatically" way that from the reader a field will be mapped to a "Model" class or any other provided class by the same name of DB table FieldName -> Class Property Name.

Kind Regards,

Asaf

Answer 5

User764021698 posted

Hi,

I have found the answer here http://stackoverflow.com/questions/1464883/how-can-i-easily-convert-datareader-to-listt by Ali YALÇIN using System.Reflection.

Thanks,

Asaf