I'd like to convert (client-side) an EntityCollection to a DataTable... I know how to do this with a round trip back to the server but I'd like to do it client-side. I've heard rumors that other LLBLGen users are doing this... Any code to share?

Here's the code that I'm using with the Adapter templates. It requires that the entity collection has a defined factory, but so far that hasn't been a problem for me.

 public static DataTable CollectionToDataTable(EntityCollectionBase2 collection)
        {
    DataTable dt = new DataTable();
    IEntityFields2 fields = collection.EntityFactoryToUse.CreateFields();

    // Create the columns for the datatable
    for (int i = 0; i < fields.Count; i++)
    {
        dt.Columns.Add(fields[i].Name, fields[i].DataType);
    }
            
    // Add data into the datatable
    foreach (IEntity2 entity in collection)
    {
        DataRow row = dt.NewRow();
        for (int i = 0; i < fields.Count; i++)
        {
            row[entity.Fields[i].Name] = entity.Fields[i].CurrentValue;
        }
        dt.Rows.Add(row);
    }

    return dt;
        }

Backslash wrote:

Here's the code that I'm using with the Adapter templates. It requires that the entity collection has a defined factory, but so far that hasn't been a problem for me.

That's a pretty clean little function!

Backslash wrote:

Here's the code that I'm using with the Adapter templates. It requires that the entity collection has a defined factory, but so far that hasn't been a problem for me.

Can anyone tell me how this function could be converted to work with version 2.0?

Thanks, J.S.

Use projection from an EntityView into a dataTable.

Check the Projecting data inside an EntityView2 on another data-structure section in the LLBLGen Pro manual: "Using the generated code -> Adapter -> Using the EntityView2 class"

Examples of EntityView2 projections Projection to datatable.

 // C#
EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
adapter.FetchEntityCollection(customers, null); // fetch all customers
// create a view of all customers in germany
EntityView2 customersInGermanyView = new EntityView2( customers,
     (CustomerFields.Country == "Germany"), null );
// create projection of these customers of just the city and the customerid.
// for that, define 2 propertyprojectors, one for each field to project
ArrayList propertyProjectors= new ArrayList();
propertyProjectors.Add( new EntityPropertyProjector( CustomerFields.City, "City" ) );
propertyProjectors.Add( new EntityPropertyProjector( CustomerFields.CustomerId, "CustomerID" ) );
DataTable projectionResults = new DataTable();
// create the actual projection.
customersInGermanyView.CreateProjection( propertyProjectors, projectionResults );

How do you project all fields onto the datatable for the same example?

Is there an easy way to add all the fields collection for the entity, without having to add all fields one by one as EntityPropertyProjectors?

I have tried this but it does not work. The table dt has no columns or data after the CreateProjection, but the dataView has 3 rows of data

Dim dt As DataTable = New DataTable()
Dim dataView As IEntityView2 = GetEntityView(AccountTypeID)
Dim fields As IEntityFields2 = dataView.RelatedCollection.EntityFactoryToUse.CreateFields()
Dim propertyProjectors As New List(Of IEntityPropertyProjector)

Dim i As Integer = 0
Do While (i < fields.Count)
      propertyProjectors.Add(New EntityPropertyProjector(fields(i), fields(i).Name))
       i = (i + 1)
Loop

dataView.CreateProjection(propertyProjectors, dt)

On further investigation GetEntityView(AccountTypeID) does this, but the filter expression always results in a view with no data. The real issue is that I cannot filter entityview2 objects.

Dim filterThis As IPredicate 

Dim viewData As IEntityView2

'Create data collection
entityData = New EntityCollection(New MyTblAccountTypeEntityFactory())

'Retrieve data
adapter.FetchEntityCollection(entityData, Nothing)

viewData = entityData.DefaultView
'Filter the data
filterThis = New PredicateExpression(TblAccountTypeFields.AccountTypeId = Id)
viewData.Filter = filterThis

Thanks

sbense wrote:

On further investigation GetEntityView(AccountTypeID) does this, but the filter expression always results in a view with no data. The real issue is that I cannot filter entityview2 objects.

Dim filterThis As IPredicate

Dim viewData As IEntityView2

'Create data collection
entityData = New EntityCollection(New MyTblAccountTypeEntityFactory())

'Retrieve data
adapter.FetchEntityCollection(entityData, Nothing)

viewData = entityData.DefaultView
'Filter the data
filterThis = New PredicateExpression(TblAccountTypeFields.AccountTypeId = Id)
viewData.Filter = filterThis

What LLBLGenPro version and RuntimeLibraries version are you using? (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9076)

David,

Thank you for your response: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10809

The above post led to this one, and the issue was datatypes in the IPredicate express for DataView filtering. It is working now

What LLBLGenPro version and RuntimeLibraries version are you using? I am using LLBLGenPro version 2.

Hi,

I would like to convert entitycollection to DataTable.

Below function is not working for latest version of LLBLGen. Please advice.

public DataTable CollectionToDataTable(EntityCollectionBase2<IEntity2> collection)
{
    DataTable dt = new DataTable();
    IEntityFields2 fields = collection.EntityFactoryToUse.CreateFields();

     Create the columns for the datatable
    for (int i = 0; i < fields.Count; i++)
    {
        dt.Columns.Add(fields[i].Name, fields[i].DataType);
    }

     Add data into the datatable
    foreach (IEntity2 entity in collection)
    {
        DataRow row = dt.NewRow();
        for (int i = 0; i < fields.Count; i++)
        {
            row[entity.Fields[i].Name] = entity.Fields[i].CurrentValue;
        }
        dt.Rows.Add(row);
    }

    return dt;
}

Regards

What exactly isn't working? Do you receive an exception? compile error? you didn't get the expected results?

Hi,

I am trying to convert entitycollection to DataTable as per in below code.

        public EntityCollection<UserEntity> GetUserList()
        {
            EntityCollection<UserEntity> users = new EntityCollection<UserEntity>();

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(users, null);             
            }

        DataTable dt = this.CollectionToDataTable(users); //USING IT HERE

            return users;
        }

When I try to compile the WinForms Application I get error as per below.

Error 1 The type 'SD.LLBLGen.Pro.ORMSupportClasses.IEntity2' must be convertible to 'SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2' in order to use it as parameter 'TEntity' in the generic type or method 'Northwind.DAL.HelperClasses.EntityCollection<TEntity>' C:\projects\Northwind\Northwind.BO\UserBO.cs 19 20 Northwind.BO

C:\projects\Northwind\Northwind.BO\UserBO.cs 19 20 Northwind.BO

Which code line exactly that's causing the exception?

btw, Why don't you simply use projection? Please check Projecting data inside an EntityView2 on another data-structure in the LLBLGen manual's section "Using the generated code -> Adapter -> Using the EntityView2 class"

(EDIT) I think your method declaration should be changed to:

public DataTable CollectionToDataTable(EntityCollectionBase2<EntityBase2> collection)

Hi

After changing method signature to: public DataTable CollectionToDataTable(EntityCollectionBase2<EntityBase2> collection)

shows below errors.

Error 1 The best overloaded method match for 'Northwind.BO.UserBO.CollectionToDataTable(Northwind.DAL.HelperClasses.EntityCollection< SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2>)' has some invalid arguments C:\projects\UI\Northwind\Northwind.BO\UserBO.cs 59 22 Northwind.BO

Error 2 Argument '1': cannot convert from Northwind.DAL.HelperClasses.EntityCollection<Northwind.DAL.EntityClasses.UserEntity>' to 'Northwind.DAL.HelperClasses.EntityCollection<SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2>' C:\projects\UI\Northwind\Northwind.BO\UserBO.cs 59 49 Northwind.BO

Regards,

Oops that's a common mistake of me

The following should work: public DataTable CollectionToDataTable(EntityCollection<EntityBase2> collection)

Or: public DataTable CollectionToDataTable(EntityCollection<IEntity2> collection)

Hi,

Again the same error as posted before,

Could you please try above function code on a simple Entity collection and advice.

Regards

Sorry again,

The following does compile:

public DataTable CollectionToDataTable<TEntity>(EntityCollection<TEntity> collection) where TEntity : EntityBase2, IEntity2
{
...
}

Thanks Walaa.

Here is a little tweak that I extended from this thread that I thought might be helpful. It uses CreateProjection (and also sets the tablename ).

    public DataTable CollectionToDataTable<TEntity>(EntityCollectionBase<TEntity> collection, string TableName)
        where TEntity : EntityBase, IEntity
    {
        List<IEntityPropertyProjector> propertyProjectors =
            EntityFields.ConvertToProjectors(collection.EntityFactoryToUse.CreateFields());
        DataTable dt = new DataTable(TableName);
        collection.DefaultView.CreateProjection(propertyProjectors, dt);
        return dt;
    }

Thanks for sharing! Jennifer