This has been discussed in other postings within the "architecture" section but I thought I would start a new thread on this single topic:

DataTables or Collections of entities? (or both)

The business layer has to pass or else expose a collection to the PL. I am interested to know what people have used and how they have found it. I notice from some previous posts that some people (Cadium springs to mind) standardize on DataTables for collections at all times. Other people change depending to the scenario.

Personally, I am leaning towards DataTables in all cases, because:

1. In many cases the collection you need does not map to an existing entity (you might want most of tableX, but also some of tableY, for example if the data was coming from a SP). A datatable is easy, an entity collection is not (you need to create a new entity and then an entity collection of it).

2. If you were using custom collections rather than the ones LLBL generates for you to pass up to the PL, you need to take the data from LLBL (which is either a datatable for a SP or a LLBL EntityCollection), and stick it into the custom colection - which must be slow, non? Assuming that this is not desirable, you have no choice to to pass datatables in the case of SP's.

3. If using the Adaptor model in LLBL, then the entityCollection is not "strongly typed", so if you want to be able to do this: coll[2].Name = "bob"; ...you are forced to create your own entitycollections, and this goes back to problem number 2.

Thats my current viewpoint. I can't say yet whether it has worked/failed because the project is just starting! I'd love for someone to argue this with me, I probably haven't considered other options.....

Propably sick and tired to hear from me... Well tuff.

DataTable is cleaner - But not controllable - in the sense that i can not control what the developer put into it. Unless you are planning to pass the Datatable back into the BL and then do validation.

Your design has to be simular to a component design. It is a entity of a application that does a specific job and should have nothing todo with any other part of the app.

Other people change depending to the scenario.

I fall into this group...

I'd love for someone to argue this with me

Looking for a fight - are we? I hope that fishy and jeff will respond to this - they usually have very good opinions.

When dealing with an OOD scenario, there is never a "right way." Every task should stem from a requirement.

If you are dealing with collections in a thick client, you should know the rowID of the grid, so you can always cast the selected item in a grid to an entity.

You can always use the

 for each entity in collection

loop to get to the entity in question.

In regards to point #1, thats what a typed list is for.

If you really need a typed collection, using a generic datatable isnt any better than an untyped collection. So you could just create a helper class to create a typed collection.

If you are using a thin client browser app, if you dont want to hit the database again to find fetch the entity, then youre going to need to use viewstate, session, or cache to store the data and look it up from there. In this scenario, a generic data table still doesnt get you anything.

So, IMO, I dont really think that making everything into a datatable would benefit that much over what you already have.

Hi wayne! No, I appreciate your postings!! If I get sick and tried i'll stop posting!!

The trouble with using the adaptor model is that entitycollections you get are not strongly typed, so I can't do as much with 'em. Plus I have to send custom collections because I dont want the PL: to know about LLBLGen. So, my only non-DataTable alternative is to copy the data from an LLBL collection into my own - which must be bad!

Hehe, no I'm not looking for a fight Just a healthy debate about what people have used and what works for people.

Thanks devildog.

The typed list is a good point. In our application, a lot of the relationships aren't simple FK/PK affairs though, (they are 'Multi-class/single-table') so in some cases we can't use typed lists.

Devildog74 wrote:

If you really need a typed collection, using a generic datatable isnt any better than an untyped collection. So you could just create a helper class to create a typed collection.

In this case my helper class would have to take my LLBL collection, and loop thorugh it populating a custom collection. Wouldn't this really hit performance?

Wouldn't this really hit performance?

By gaining some thing you always lose somthing. There is always a traid off.

How long can a for loop take? surely you are not returing the whole table.

The traid off in speed may be worth the functionality advantage!

Matt

::you might want most of tableX, but also some of tableY, for example if the data was ::coming from a SP).

You can create 1 business object from TableXEntity and TableYEntity. For ex:

Table: Customer Columns: CustomerID, CustomerName

Table: Plan Columns: PlanID, PlanName

Table: CustomerPlan Cols: CustomerID, PlanID

So, we have the following entities which form the data layer:

CustomerEntity PlanEntity CustomerPlanEntity

In the business layer:

CustomerBusiness: CustomerEntity PlanBusiness: CustomerPlanEntity | --- Add an instance var/property PlanName which will return (PlanEntiy(PlanID)).PlanName and since it inherits CustomerPlanEntity, it already inherits PlanID, CustomerID

I hope make sense.

Thanks.

wayne wrote:

By gaining some thing you always lose somthing. There is always a traid off. How long can a for loop take? surely you are not returing the whole table. The traid off in speed may be worth the functionality advantage!

Hmmm, we might have a couple of hundred rows...

This looks like I might be staring at a limitation I have forced on myself by "passing business entities" in a service-oriented way, rather than exposing properties and code on the same class (as I think you have done).

if I wasn't passing the entity back to the PL, then I would store the EntityCollection within my business object, and expose it through properties defined on the custom business object, right?

E.g.

myBusinessObject { private EntityCollection _coll; ... some code that populates _coll when requested.... ... some properties that expose certain bits of _coll }

Therefore the client would not do: busObj._coll[0].name = 'bob';

But would do busObj.mycollection[0].name = 'bob; // with "mycollection" being some sort of property...

Does that make sense?

netLearner wrote:

Matt CustomerBusiness: CustomerEntity PlanBusiness: CustomerPlanEntity | --- Add an instance var/property PlanName which will return (PlanEntiy(PlanID)).PlanName and since it inherits CustomerPlanEntity, it already inherits PlanID, CustomerID

Thanks, but I got a bit lost on this bit. I'm not too sure what you are suggesting, and how this addresses dealing with collections (but hey, its getting later in the day, so my brain might have expired!! )

Morning guys (late getting in this morning...) Here's my experience thus far:

Using datatables is a pain in the arse, but I don't see any way around them simply because databinding grids to collections is interesting, and then you have that whole abstraction issue.

The big problem comes into play when your working with an entity collection that has not been persisted, or may only be partially persisted to the database. Then you've got to "sync" up the changes made via databinding (or other method) to the base collection since you can't just instantiate the same entity from both the datatable and the collection using the ID (I keep trying to get Frans to compromise his ideals and give me an in-memory object graph, but noooooo)

The other problem IS one of performance. If you're using collections (even if you're using datatables out in your presentation layer) you're pulling EVERY COLUMN of EVERY ENTITY that satisifies your predicate. That can be an insane amount of data to pull across the network if all you want to see is a name, for example.

I think the best possible compromise (since I've voiced my opposition to typed lists/views elsewhere) is the "Typed List Hack" (Copyright 2004) (ref: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=820&StartAtMessage=25) . Here's why:

1. No need to regenerate the LLBL everytime you need a new display collection

2. Can be retrieved as a datable for abstraction purposes

3. Is extremely light. Basically, you pass the method a collection of fields you want to see and that's all you get in return! This is really important because lookup-type collections typically don't have a predicate (you want all the records) and can be a big drain on the network, and if you are then trying to re-iterate through each entity in the collection, pull out just the fields you want, then stuff them into a datatable, to return to the caller, you probably have 2-3 times more overhead than you should have to deal with. Believe me, that's how we did it (don't get me started <grrr>) and we're paying the price, although at the time we really didn't have a good alternative.

The best way I can think to implement this is to use the Factory pattern to get whichever "sub-collection" you need for a given entity on demand. Smoooooth...

Then just expose this to your PL and you've got a quick, easy, and lightweight data structure that can be passed between the layers guilt-free. And low in carbs, too!

Jeff...

Have I mentioned that I really like the EntityCollection?

• You can cast it to an Entity
• You can iterate through it
• You can change propery values and use the dataadapter (via the bl) to update
• You have access to related tables

The bottom line is that it does so much for you. And, since I'm not using self-servicing I don't care that my pl has direct access to it. (the bl does all validation and updating)

Scary, what can I say, I'm a risk taker

So, if I were forced to choose between typed collections or typed data tables, I would use typed collections. If you are using the adapter model, there should not be any thing wrong with exposing entity objects to the client. IMO, it would be faster to roll your own typed collection vs rolling your own typed datatable. ADO.NET data objects have a huge overhead. In my experience, I have sent a lot of objects using MSMQ. In one case, I had a simple object, like an entity object. When serialized it was 250kb in size. The same data represented in the form of a generic data table with a singe row, serialized to 3000kb. This is obviously a huge difference.

In any case, I took some time to write you a sample that could meet your needs. So here goes (for best results, copy and paste into a vb.net application):

** Client Code** This is a windows form, with a datagrid and 2 buttons.

     Private Sub cmdLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLoadData.Click
  Dim svcs As New SeasonServices(True)
        mData = svcs.Seasons(True)
        DataGrid1.DataSource = mData
    End Sub

    Private Sub cmdEditItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEditItem.Click
        Dim bmGrid As BindingManagerBase
        bmGrid = BindingContext(mData)
        MessageBox.Show(("Current BindingManager Position: " & CType(bmGrid.Current, ISeason).SeasonID))
    End Sub

** Middle Tier Code:** A simple interface to ensure the contract

 Imports EDS.DNN2.Hotels.Framework
Imports SD.LLBLGen.Pro.ORMSupportClasses
Imports System.Collections
Imports System.Configuration

Public Interface ISeason
    'a simple interface to guarantee the contract
    Property SeasonID() As Integer
    Property SeasonStartDate() As Date
    Property SeasonEndDate() As Date
    Property SeasonName() As String
    Property PortalID() As Integer
End Interface

A class that derives from collection base. It stores objects that implement ISeason. It is very generic and should implement IEnumerable, IList, and ICollection to make it completely robust. It should probably contain IsDirty properties etc.

Public Class Seasons
    'a hand rolled, very basic collection
    Inherits CollectionBase

    Public Sub Add(ByVal item As ISeason)
        Me.List.Add(item)
    End Sub

    Public Sub Remove(ByVal index As Integer)
        If index > Count - 1 Or index < 0 Then
            Throw New ArgumentOutOfRangeException("No item found at the specified index")
        End If

        list.RemoveAt(index)

    End Sub

    Default Public Overloads Property Item(ByVal index As Integer) As ISeason
        Get
            Return CType(Me.List.Item(index), ISeason)
        End Get
        Set(ByVal Value As ISeason)
            Me.List.Item(index) = Value
        End Set
    End Property

End Class

This is a services layer object. It fetches data from the DB and maps it to ISeason objects. It also exposes the Seasons collection to the client.

Public Class SeasonServices
    'a sample services layer object 
    Dim mSeasons As HelperClasses.EntityCollection
    Dim mySeasons As Seasons

    Public Sub New()
        MyClass.New(False, Nothing)
    End Sub

    Public Sub New(ByVal fillData As Boolean)
        MyClass.New(fillData, Nothing)
    End Sub


    Public Sub New(ByVal fillData As Boolean, ByVal filter As IRelationPredicateBucket)
        If fillData Then
            mSeasons = GetSeasons(filter)
            If mSeasons.Count > 0 Then
                mySeasons = Seasons(False, filter)
            End If
        End If
    End Sub

    Public Function Seasons(ByVal forceFetch As Boolean, Optional ByVal filter As IRelationPredicateBucket = Nothing) As Seasons
        If mySeasons Is Nothing Then
            mySeasons = New Seasons
        End If

        If forceFetch Then
            mSeasons = GetSeasons(filter)
            If mySeasons.Count > 0 Then
                mySeasons.Clear()
            End If
        End If

        If mySeasons.Count = 0 Then
            Dim entity As EntityClasses.SeasonEntity
            For Each entity In mSeasons
                Dim tmpSeason As ISeason = New Season(entity.SeasonID, entity.PortalID, entity.SeasonEndDt, entity.SeasonStartDt, entity.SeasonName)
                mySeasons.Add(tmpSeason)
            Next

            If mySeasons.Count > 0 Then
                Return mySeasons
            End If
        End If
    End Function

    Private Function GetSeasons(ByVal filter As IRelationPredicateBucket) As HelperClasses.EntityCollection
        Dim myData As New DatabaseSpecific.DataAccessAdapter(ConfigurationSettings.AppSettings("ConnectionString"), False, ConfigurationSettings.AppSettings("CatalogNameUsageSetting"), ConfigurationSettings.AppSettings("CatalogNameToUse"))
        mSeasons = New HelperClasses.EntityCollection(New FactoryClasses.SeasonEntityFactory)
        myData.FetchEntityCollection(mSeasons, filter)
        Return mSeasons
    End Function
End Class

This is a generic Season object that implements ISeason. It can be used on the client side.

Public Class Season
    'a class that can be used on the client side
    Implements ISeason

    Private mSeasonID As Integer
    Private mPortalID As Integer
    Private mSeasonEndDate As Date
    Private mSeasonStartDate As Date
    Private mSeasonName As String

    Public Sub New()
        MyClass.New(-1, -1, Now, Now, String.Empty)
    End Sub

    Public Sub New(ByVal seasonID As Integer)
        MyClass.New(seasonID, -1, Now, Now, String.Empty)
    End Sub

    Public Sub New(ByVal seasonID As Integer, ByVal portalID As Integer, ByVal seasonEndDate As Date, ByVal seasonStartDate As Date, ByVal seasonName As String)
        mSeasonID = seasonID
        mPortalID = portalID
        mSeasonEndDate = seasonEndDate
        mSeasonStartDate = seasonStartDate
        mSeasonName = seasonName
    End Sub

    Public Property PortalID1() As Integer Implements ISeason.PortalID
        Get
            Return mPortalID
        End Get
        Set(ByVal Value As Integer)
            mPortalID = Value
        End Set
    End Property

    Public Property SeasonEndDate() As Date Implements ISeason.SeasonEndDate
        Get
            Return mSeasonEndDate
        End Get
        Set(ByVal Value As Date)
            mSeasonEndDate = Value
        End Set
    End Property

    Public Shadows Property SeasonID() As Integer Implements ISeason.SeasonID
        Get
            Return mSeasonID
        End Get
        Set(ByVal Value As Integer)
            mSeasonID = Value
        End Set
    End Property

    Public Shadows Property SeasonName() As String Implements ISeason.SeasonName
        Get
            Return mSeasonName
        End Get
        Set(ByVal Value As String)
            mSeasonName = Value
        End Set
    End Property

    Public Property SeasonStartDate() As Date Implements ISeason.SeasonStartDate
        Get
            Return mSeasonStartDate
        End Get
        Set(ByVal Value As Date)
            mSeasonStartDate = Value
        End Set
    End Property
End Class

Hi devildog.

Wow - thanks for the code snippet! It speaks volumes! You really demonstrate exactly how a collection can be easily created that fulfills most of the requirements put on it. The size of the object as you state is very important, in our old COM project we passed RecordSets and these were pretty big and slow in places!

Ok, back to the collection. My only reservations about collection classes now are:

1. We would want sort and filter capability. Although we can code this, we get a lot more flexibility with a DataView.
2. They don't often map to an entity. In some cases we might only want a list of 3 fields from a table, and its hard work to define entities for all these list scenarios.

Considering this Vs the benefits of collection classes, I think I am now of the opinion that its definately useful in some scenarios to create collections, but for more obscure/"reporty" needs a datatable may be the best way.

By the way, thanks a lot for this and for all other posts everyone have made to help me - I have been posting a helluva lot lately, and I have learnt a lot from all these replies - cheers!

1. We would want sort and filter capability. Although we can code this, we get a lot more flexibility with a DataView.

Implement IComparer and a sort method for simple sorting in your collection. Write a filter method that returns an arraylist of filtered data.

1. They don't often map to an entity. In some cases we might only want a list of 3 fields from a table, and its hard work to define entities for all these list scenarios.

Yes it is hard work, but thats what we get paid for. You should be able to take your usage scenarios and create 85% of these custom select lists into typed lists. Use codegen for the other 15%.

In my sample, it just so happens that my ISeason objects relate to an entity. This does not necessarily need to be the case. You could have a collection that holds objects that implement many interfaces. You might not need to use all members provided by the interface, but the point is that they would be provided by the interface and would always be accessible in the same fashion. For example, I could create an object, that implements ISeason, IRoom, and IPromoProgram that represents an individual reservation. I could also build a collection of these using certain fields from SeasonEntity, RoomEntity, and PromoProgramEntity. For these types of special cases, I often implement views, indexed views, and typed views.

Once again, your usage scenarios should help dictate your implementation.

Very nice and well thought-out Devildog. I can definitly see using this when an EntityCollection won't do.

I'm wondering if this can be code generated? Could the equivalent or perhaps more robust be created from a read/write version of a typed view or typed list? Especially now that Frans has added the ability to create additional fields?

Thanks again for your hard work.

I was wondering why you had an ISeason, rather than just define an entity, so thanks for that explanation!

I could create an object, that implements ISeason, IRoom, and IPromoProgram that represents an individual reservation. I could also build a collection of these using certain fields from SeasonEntity, RoomEntity, and PromoProgramEntity. For these types of special cases, I often implement views, indexed views, and typed views.

In this case do you create an object that implements the fields you need from for example ISeason and IRoom, and then build a collection around that?

Fishy, I have added some simple template mods so far to the current final release. I can generate the "custom entities", and also quite a bit of the collections. I think its really useful when the custom entitties are pretty much the same as the LLBL ones. I think it should be possible to make similar modifications simply enough to the typed list generation templates...

Thats me for the week!

Fishy asked:

Can this be code generated

, technically anything can, but this is really hand rolled code, so its difficult to code gen custom hand rolled logic. Sometimes, you just need to get your hands dirty.

Matt Asked:

In this case do you create an object that implements the fields you need from for example ISeason and IRoom, and then build a collection around that?

Pretty much. Assume that you need a list of Promotional Programs, for each RoomType, for the Spring 2005 season, and you have a typed list to do this. Also assume that you want to use the same design pattern as above, you want to hide llblgen from the client.

Declare an object called SeasonalRoomPromos that Implements ISeason, IRoom, and IPromoProgram. This will fill create all fields in the vb class (not sure if C# does the same thing.) Define your implementation for the fileds that you want to use. Create an object that derives from collectionBase, or one that implements the required collection interfaces. Except for in this collection, the Item property will return a SeasonlRoomPromo object. Create a method in the services layer to fetch the typed list, itereate through it, creating new SeasonalRoomPromo objects, and add them to the instance of the collection that you are going to return, and boom, youre done.

Great! I think we'll be using this technique for a number of places in the app. I'm not sure how much we will use this techniqure Vs. the datatable, but the code examples you gave are really useful - especially the but about defining an interface for each entity !!

Anyone else have thoughts on handling collections?

Thansk for the great conversation guys.....

Any devildog thanks for the code sample.

I do have one question, how do you guys deal with null values when you roll your own clases to pass between tiers?

Now DevilDog I am assuming that you are the same DevilDog I have seen on the DotNetNuke forums on asp.net? I also see that your namesapce is DNN2..

are you using the Null.VB class that the DotNetNuke core uses?

Bert

Devildog74 wrote:

(...)IMO, it would be faster to roll your own typed collection vs rolling your own typed datatable. ADO.NET data objects have a huge overhead. In my experience, I have sent a lot of objects using MSMQ. In one case, I had a simple object, like an entity object. When serialized it was 250kb in size. The same data represented in the form of a generic data table with a singe row, serialized to 3000kb. This is obviously a huge difference.(...)

Huumm strange ... I was a bit suprised when reading that so I made a small test here. 1st : I fetch a collection with 356 entities, writing xml 2nd : fill a datatable with the same data

here are the result:

- test 1 : collection -

String size : 19299369 (356 employees)

-- test 2 : dataset ---

Sting size : 72037 (356 employees)

Here is the (quite simple) code made in 5 min :

HRDataAccessAdapter da          = new HRDataAccessAdapter();
EntityCollection    ec          = new EntityCollection(new EmployeeFactory());
da.FetchEntityCollection(ec,null);

string              xmlCollection;
ec.WriteXml(out xmlCollection);
Console.WriteLine("-----------------------");
Console.WriteLine("- test 1 : collection -");
Console.WriteLine("-----------------------");
Console.WriteLine(string.Format("Size : {0} ({1} employees)",xmlCollection.Length, ec.Count));

Console.WriteLine("-----------------------");
Console.WriteLine("-- test 2 : dataset ---");
Console.WriteLine("-----------------------");
SqlConnection   sc  = new SqlConnection(da.ConnectionString);
SqlDataAdapter  sda = new SqlDataAdapter("SELECT * FROM EMPLOYEE",sc);
DataSet         ds  = new DataSet();
sda.Fill(ds);
System.IO.StringWriter sw = new System.IO.StringWriter();
ds.WriteXml(sw);
string              xmlDataSet = sw.ToString();
sw.Close();
Console.WriteLine(string.Format("Size : {0} ({1} employees)",xmlDataSet.Length, ds.Tables[0].Rows.Count));

So ... there is indeed a huge difference, but not in the same direction you told. Maybe I've made something wrong ?

Extended the test for 1 entity :

- test 3 : collection/1 -

Size : 55127 (1 employee)

- test 4 : dataset/1 --

Size : 226 (1 employee)

I'm very surprise that 1 entity take 55k once serialized ? I think there is something wrong in my code, but I'm too tired to see where (hard to work after 4 hours of sleep )

Bert Wrote:

Now DevilDog I am assuming that you are the same DevilDog I have seen on the DotNetNuke forums on asp.net? I also see that your namesapce is DNN2..

are you using the Null.VB class that the DotNetNuke core uses?

Yep, its me.

What sense do you mean "how do we use the null values" I will either check the original value or something like that. No, I dont use the DNN CORE Null.vb class.

I personally dont wrap entities into my own objects. I find it acceptable, since I write 95% of all code in all layers, that a client may reference ORMSupportObjects and my DatabaseGeneric namespace. So, hiding code servers me no purpose.

With regards to DNN, I write most of my moduels as follows: a UI module (written as a class library), a services facade with mostly static members, a framework assembly containing DB Generic Definitions, and a DBSpecific Assembly.

Fabrice wrote:

Extended the test for 1 entity :

---

- test 3 : collection/1 -

Size : 55127 (1 employee)

- test 4 : dataset/1 --

Size : 226 (1 employee)

I'm very surprise that 1 entity take 55k once serialized ? I think there is something wrong in my code, but I'm too tired to see where (hard to work after 4 hours of sleep )

You are not comparing apples to apples. You sample is using a typed object, i.e. an entity, that probably has relations, and comparing it to an untyped dataset,i.e. no relations or additional schema for the dependent objects.

Sure, an untyped DS will probably be smaller than a typed entity with relations. One of the key points of the original thread was that the developer wanted strong typing, so that they could say collection.item(0).FirstName.

Using a dataset to say dataset1.tables(0).rows(0).item("FirstName").ToString, is no more intuitive than using collection.item(0).GetCurrentFieldValue(EmployeeFieldIndex.FirstName)

Devildog74 wrote:

What sense do you mean "how do we use the null values" I will either check the original value or something like that. No, I dont use the DNN CORE Null.vb class.

In your Season object how would you handle it if SeasonEndDate or SesaonStartDate where nullable in the database?

I like to use adapter and pass the entity objects across ties. As entity objects have the ability to deal with nulls. If you are using your own custom classes you need to account for Null values in the database and how to persist them to the database

Thanks Bert

Devildog74 wrote:

I personally dont wrap entities into my own objects. I find it acceptable, since I write 95% of all code in all layers, that a client may reference ORMSupportObjects and my DatabaseGeneric namespace.

I wrote the quote above in a previous post. So, like you, I pass around entity objects and use their built in mechanisms to test for null. However, if you were going to wrap the object, you could define a property that did essentially the same thing as what the entity objects do. Infact, datasets do almost the same thing internally with null fields as LLBLGen does.

The only practical reason that I can see for wrapping entities into custom objects is when you either need to:

• add additional functionality to a few objects

• create a composite / aggregate object

• or to hide DAL code that is embeded into the entity, such as the case with the SelfServicing pattern

If you are using the adapter pattern, all that is really required is for the client to have a reference to the database generic library. If the type definitions were in the system.web or system.data namespace, would it make it ok to pass these objects around? All they are is type definitions, just like an interface.