Fill all data from database in dataTable and build a list

Question

I have 2 objects like this:

public class Place : ICloneable, IEquatable<Place>
{
    public string Name{ get; set; }
    public float longitude{ get; set; }
    public float latitude{ get; set; }
    public Horizon thehorizon  { get; set; }
    ...
}
public class Horizon
{
    public List<PointF> points{ get; set; }
}

In my database I have 2 tables - "places" and "horizons", with a foreign key in horizons to know to which place the points belongs.

So the structure of the places is:

1. Name -nvarchar - primary key
2. longitude - real
3. latitude - real

and the structure of the horizons is

1. parent_key - nvarchar
2. pointX - real
3. poinY - real

I wrote the code below to select all the data and build a list of place. It's working, but it's very slow. If you have any suggestion how to make it faster (or any comment), please tell me.

DataTable TablePlaces;
DataTable TableHorizons;

public void init()
{
    TablePlaces = new DataTable();
    TablePlaces.Columns.Add("Name");
    TablePlaces.Columns.Add("longitude");
    TablePlaces.Columns.Add("latitude");

    TableHorizons = new DataTable();
    TableHorizons.Columns.Add("parent_key");
    TableHorizons.Columns.Add("pointX");
    TableHorizons.Columns.Add("pointY");

    System.Data.DataSet DS = new DataSet();
    DS.Tables.Add(TablePlaces);
    DS.Tables.Add(TableHorizons);
    DS.Relations.Add(TablePlaces.Columns["Name"],
        TableHorizons.Columns["parent_key"]);
}

public List<Place> BuilsListPlace()
{
    TableHorizons.Clear();
    TablePlaces.Clear();
    using (DbCommand Command = newConnectionNewCommand())
    {
        Command.CommandText = "SELECT * FROM places ORDER BY Name"
        fill(TablePlaces, Command);
        Command.CommandText = "SELECT * FROM horizons ORDER BY parent_key,pointX";
        fill(TableHorizons, Command);

        Command.Connection.Dispose();
    }
    return (from DataRow dr in TablePlaces.Rows
            select newPlace(dr)).ToList();
}

void fill(TableDB t ,DbCommand Command)
{
    using (var da = newDataAdapter())
    {
        da.SelectCommand = Command;
        da.MissingSchemaAction = MissingSchemaAction.Ignore;
        da.Fill(t);
    }
} 
Place newPlace(DataRow dr)
{
    Place result = new Place();
    result.longitude=(float)dr["longitude"];
    result.latitude=(float)dr["latitude"];
    result.Name=(string)dr["Name"];
    result.theHorizon=newHorizon(dr.GetChildRows(dr.Table.ChildRelations[0]));
    return result;
}

Horizon newHorizon(DataRow[] Rows)
{
    Horizon result = new Horizon();
    result.points = new List<PointF>();
    foreach(DataRow dr in Rows)
        result.points.Add(new PointF((float)dr["pointX"],(float)dr["pointY"]);
    return result;
}

Answer 1

This extension I found for converting datatable to list of objects using reflection & generic, but keep in mind that the property name has to be exact the same with the one in datatable

Hope it helps

public static List<T> DataTableToList<T>(this DataTable table) where T : class, new()
{
    try
    {
        List<T> list = new List<T>();

        foreach (var row in table.AsEnumerable())
        {
            T obj = new T();

            foreach (var prop in obj.GetType().GetProperties())
            {
                try
                {
                    PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                    propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                }
                catch
                {
                    continue;
                }
            }

            list.Add(obj);
        }

        return list;
    }
    catch
    {
        return null;
    }
}

Answer 2

I would try to select only the things that you really need. Although you say you have about 1800 places (which should not be a problem at all)... Try with the following

public IList GetPlaces()
{
  using (var conn = new SqlConnection("blahblahblah"))
  {
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT p.Name, h.pointX, h.pointY FROM places p LEFT OUTER JOIN horizons h ON h.parent_key = p.Name";

    DataTable tbl = new DataTable();

    using (var rdr = cmd.ExecuteReader())
      tbl.Load(rdr);

    return tbl.Rows
      .Cast<DataRow>()
      .Select(r => new
        {
          Name = r.Field<string>("Name"),
          HorizonX = r.Field<float>("pointX"),
          HorizonY = r.Field<float>("pointY")
        }).ToList();
  }
}

The "Select" part below is returning an anonymous type and not the types you were looking for. But it should not be hard to go from here, and converting it to you classes suing Select / SelectMany a.s.o.

Note that it is not really important how you return your data to a List, but it's pretty important how you read it from the database.