LLBLGEN: Load a EntityCollection or List from a datatable

Question

How do I load an EntityCollection or List(Of Entity) from a DataTable using LLBLGen?

Answer 1

A datatable holds its values in rows and columns whereas a LLBLGen Collection class holds a collection of Entity objects that represent a table in your persistent storage. You can fetch a DataTable of fields that you define with a ResultsetFields via the TypedListDAO. However, going from a DataTable to an EntityCollection is not possible unless your Entity objects are stored in your DataTable.

More likely, you have some keys in your DataTable. If this is the case, you'll need to iterate over the rows of the DataTable, pull out the keys and create new Entity objects from these. Then you can add these Entity objects to your EntityCollection.

// Define the fields that we want to get
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(EmployeeFields.EmployeeId, 0);
fields.DefineField(EmployeeFields.Name, 1);

// Fetch the data from the db and stuff into the datatable
DataTable dt = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dt, 0, null, null, null, false, null, null, 0, 0);

// We now have a datatable with "EmployeeId | Name"
// Create a new (empty) collection class to hold all of the EmployeeEntity objects we'll create
EmployeeCollection employees = new EmployeeCollection();
EmployeeEntity employee;

foreach(DataRow row in dt.Rows)
{
    // Make sure the employeeId we are getting out of the DataTable row is at least a valid long
    long employeeId;
    if(long.TryParse(row["EmployeeId"].ToString(), out employeeId)) 
    {
        // ok-looking long value, create the Employee entity object
        employee = new EmployeeEntity(employeeId);

        // might want to check if this is .IsNew to check if it is a valid object
    }
    else
    {
        throw new Exception("Not a valid EmployeeId!");
    }

    // Add the object to the collection
    employees.Add(employee);
}

// now you have a collection of employee objects...
employees.DoStuff();