locked
Converting datareader to a list of Classes? RRS feed

  • 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;
            }

    Sunday, January 17, 2016 10:03 AM

Answers

All replies

  • User1633621018 posted

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

    If you still get error, post your code here. 

    Sunday, January 17, 2016 10:39 AM
  • 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

    Sunday, January 17, 2016 3:01 PM
  • 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

    Monday, January 18, 2016 6:57 AM
  • 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

    Monday, January 18, 2016 8:36 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2016 1:17 PM