DDL at runtime with LLBLGen

Posts   
1  /  2
 
    
sgilroy
User
Posts: 3
Joined: 28-Nov-2006
# Posted on: 28-Nov-2006 19:03:09   

Is anyone using LLBLGen in combination with DDL? I am under the impression that LLBLGen does not support any changes to your database schema at runtime, such as adding new columns to a table.

Two possible solutions might be: 1) Run LLBLGen at runtime to dynamically recompile the data access code used in my application any time the schema is changed (when DDL is used). 2) Extend LLBLGen to support changes to columns at runtime. This would mean (among other things) having any dynamic columns represented in the fields collection of an entity, but the dynamic columns would not have associated C# properties defined for them, and the fields would not be included in the corresponding *FieldIndex enum in ConstantsEnums.cs.

So is anyone doing this? Does LLBLGen have any sort of support for DDL out of the box? Is there another way to use LLBLGen and DDL together?

For reference, here is some info on DDL:

http://www.w3schools.com/sql/sql_intro.asp

SQL Data Definition Language (DDL)

The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.

The most important DDL statements in SQL are:

* CREATE TABLE - creates a new database table
* ALTER TABLE - alters (changes) a database table
* DROP TABLE - deletes a database table
* CREATE INDEX - creates an index (search key)
* DROP INDEX - deletes an index
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Nov-2006 09:41:35   

sgilroy wrote:

Is anyone using LLBLGen in combination with DDL? I am under the impression that LLBLGen does not support any changes to your database schema at runtime, such as adding new columns to a table.

Correct, because how are you going to setup the mappings in that case or better: how are you going to add a property like CustomerEntity.CompanyName to the CustomerEntity class if you've added the CompanyName column at runtime?

Two possible solutions might be: 1) Run LLBLGen at runtime to dynamically recompile the data access code used in my application any time the schema is changed (when DDL is used). 2) Extend LLBLGen to support changes to columns at runtime. This would mean (among other things) having any dynamic columns represented in the fields collection of an entity, but the dynamic columns would not have associated C# properties defined for them, and the fields would not be included in the corresponding *FieldIndex enum in ConstantsEnums.cs.

So is anyone doing this? Does LLBLGen have any sort of support for DDL out of the box? Is there another way to use LLBLGen and DDL together?

Why do you want to create tables at runtime? I think that's the first question to start with, as the reason why, that problem, might have more than one solution simple_smile

Frans Bouma | Lead developer LLBLGen Pro
sgilroy
User
Posts: 3
Joined: 28-Nov-2006
# Posted on: 29-Nov-2006 19:13:39   

Otis wrote:

sgilroy wrote:

Is anyone using LLBLGen in combination with DDL? I am under the impression that LLBLGen does not support any changes to your database schema at runtime, such as adding new columns to a table.

Correct, because how are you going to setup the mappings in that case or better: how are you going to add a property like CustomerEntity.CompanyName to the CustomerEntity class if you've added the CompanyName column at runtime?

Assuming we were to add DDL support to LLBLGen, a dynamic (runtime added) column would not have associated property, but would be accessible from the Fields collection. Original fields (such as a column CustomerId that existed on the Customer table when at the time of code generation) could be accessed either via a property (CustomerEntity.CustoemrId) or via the Fields (CustomerEntity.Fields["CustomerId"]. After the CompanyName column has been added at rutime, the only way to access it would be via the Fields, such as CustomerEntity.Fields["CompanyName"].

Why do you want to create tables at runtime? I think that's the first question to start with, as the reason why, that problem, might have more than one solution simple_smile

I want to add columns at runtime to support custom fields which a user would define at runtime to extend an existing entity type. For example, Joe (of XYZ Hair Styles) might decide that all Customer entities need to have a PreferedShampoo field added.

Creating tables at runtime is needed to support creating new entity subtypes at runtime. For example, Joe might want to have a new subtype of the Contract entity called ResearchContract which has the added field TypeOfStudy.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-Nov-2006 10:36:26   

sgilroy wrote:

Otis wrote:

sgilroy wrote:

Is anyone using LLBLGen in combination with DDL? I am under the impression that LLBLGen does not support any changes to your database schema at runtime, such as adding new columns to a table.

Correct, because how are you going to setup the mappings in that case or better: how are you going to add a property like CustomerEntity.CompanyName to the CustomerEntity class if you've added the CompanyName column at runtime?

Assuming we were to add DDL support to LLBLGen, a dynamic (runtime added) column would not have associated property, but would be accessible from the Fields collection. Original fields (such as a column CustomerId that existed on the Customer table when at the time of code generation) could be accessed either via a property (CustomerEntity.CustoemrId) or via the Fields (CustomerEntity.Fields["CustomerId"]. After the CompanyName column has been added at rutime, the only way to access it would be via the Fields, such as CustomerEntity.Fields["CompanyName"].

That's not possible out of the box, you've to do some work to be able to do so, as the entityfieldfactories don't know about your new field so they won't add it nor fill it at runtime.

Take a look at this article: http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

Under step 6 you'll see an example how to extend the factory and append a field to the fieldslist.

Why do you want to create tables at runtime? I think that's the first question to start with, as the reason why, that problem, might have more than one solution simple_smile

I want to add columns at runtime to support custom fields which a user would define at runtime to extend an existing entity type. For example, Joe (of XYZ Hair Styles) might decide that all Customer entities need to have a PreferedShampoo field added.

Creating tables at runtime is needed to support creating new entity subtypes at runtime. For example, Joe might want to have a new subtype of the Contract entity called ResearchContract which has the added field TypeOfStudy.

How would you create an instance of that subtype at runtime if it doesn't exist at compile time?

Frans Bouma | Lead developer LLBLGen Pro
sgilroy
User
Posts: 3
Joined: 28-Nov-2006
# Posted on: 02-Dec-2006 01:53:01   

Otis wrote:

That's not possible out of the box, you've to do some work to be able to do so, as the entityfieldfactories don't know about your new field so they won't add it nor fill it at runtime.

Take a look at this article: http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

Under step 6 you'll see an example how to extend the factory and append a field to the fieldslist.

Thanks, I am investigating this now.

How would you create an instance of that subtype at runtime if it doesn't exist at compile time?

The new subtype scenario is more complex, and not as high of a priority for me. I think you would need to build in support for dynamic subtyping, and when you want to instantiate an instance of a subtype, you would use the base entity class but do some run-time customization. I imagine this would be rather tricky to support.

For now, lets just assume we want to create new fields on an existing entity at runtime. This seems to be quite possible without a lot of rework to LLBLGen as long as we are storing these new fields as columns on an existing table. However, for performance reasons, we may need to create a separate table for the custom fields, so we might create (at design time) Employee and Employee_Custom tables, both with an EmployeeId column. The structure of the Employee_Custom table would get modified at runtime to hold the custom fields of Employee. The structure of the Employee table would never change. When an Employee entity instance is read, it should have all of the original fields from the Employee table as well as the custom fields from the Employee_Custom table. It looks to me like trying to create, update, or delete entity instances with such a scenario could be difficult to implement in LLBLGen. Breaking the assumption of "one record per entity instance" is probably not very practical. Any suggestions?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Dec-2006 10:18:53   

Creating custom types at runtime is indeed something not supported in full. You can get far with tricks but it will be awkward because the process is based on the fact to create teh mappings before compilation so at compile time everything is known.

Frans Bouma | Lead developer LLBLGen Pro
Backslash
User
Posts: 21
Joined: 21-Jun-2005
# Posted on: 21-Dec-2006 01:23:39   

I'm working on a new distributed CRM application, and we have similar requirements. Basically we want to dynamically alter the schema, by letting the end user add fields and create new objects (tables) at runtime. We also store metadata for every table and field in our system. Each new object will come with some predefined fields, such as a GUID PK, Created Date, Modified Date, and so on. At this point, all we need are basic CRUD operations.

I was able to make a working prototype by making some changes to the ORMSupportClasses library. The entities already have built-in support for defining a new field (by creating a new FieldInfo and adding it to the entity Field collection), but I couldn't find a way to add a corresponding FieldPersistenceInfo. In my prototype, I changed AddElementMapping and AddElementFieldMapping from protected to public in PersistenceInfoProviderBase. I also wrote a new public method to remove an element mapping, to support updating or deleting a field at runtime.

For the user-defined objects, I created a new class that inherits from our generated 'base table' entity. Then I added a new constructor with a string parameter for the user-defined object name, which then looks up the fields and adds them to the Fields collection. It also modifies the ContainingObjectName of the inherited fields. I also updated the constructors for the generated entities to add the extra fields. Finally, I inherited from DataAccessAdapter and overrode GetFieldPersistenceInfos to pass in the right entity name for user defined objects. A little extra work will need to be done to handle cases where someone is editing a record, while someone else has changed the schema. But this should be straight-forward to detect and handle gracefully.

The end result is quite nice! For working with user-defined objects and fields, I can use code like the following:

CustomObjectEntity entity = new CustomObjectEntity(objectName);
entity.ID = id;

using (MyDataAdapter adapter = new MyDataAdapter())
{
    adapter.FetchEntity(entity);
    object oldData = entity.Fields[fieldName].CurrentValue;
    entity.SetNewFieldValue(fieldName, newData);
    adapter.SaveEntity(entity);
}

Anyways, that's about as far as I've gotten so far. It would be nice to see better built-in support for a dynamic schema, so I wouldn't have to alter the ORMSupportClasses. Maybe in a future version? wink

Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 21-Dec-2006 10:13:15   

We also need to develop an application that need to support custom fields added by the customers at runtime. The customers need to be able to add some fields to some tables.

We decided to don't change the DB schema, so for each fields that the customer want to add, we will save column/data type/ExtendedTable in some "structure table"; and the added data will go in a predefined "generic extra column data table". This generic ExtraDataTable will have an ID that will refer to the original entity to wich this extra data is linked. For simplicity we assume that each "extendible" table has a single integer(32 bit) key column.

Now, the only problem is that in LLBLGen I can't add a new EntityRelation at runtime.cry If this were possible (for example) I will be able to add, at runtime, a properly defined relation between the "Extendible table 'Customers'" and the "generic extra data table"; linking Customers.IDCustomers to ExtraData.ID. So I will be able to load these extra data using a properly filtered prefetch path that extract from ExtraData only the row really linked to Customers table (I can automatically build that prefetch path, because the software know the structure of extended extra data).

Will LLBLGen ever support dynamically added Relation? please please pleeeaseeee....... simple_smile simple_smile simple_smile simple_smile simple_smile simple_smile

I think that a similar functionality imply that when I dynamically add a new relation between 2 entities, this new relation go in some static/shared "Dynamic entityRelation container". And so these new relation are visible/usable/used everywhere in the client code that has added them. And so thes new relation need to be added only one time, when the program start.

Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 21-Dec-2006 10:47:59   

Max wrote:

We also need to develop an application that need to support custom fields added by the customers at runtime. The customers need to be able to add some fields to some tables.

We decided to don't change the DB schema, so for each fields that the customer want to add, we will save column/data type/ExtendedTable in some "structure table"; and the added data will go in a predefined "generic extra column data table". This generic ExtraDataTable will have an ID that will refer to the original entity to wich this extra data is linked. For simplicity we assume that each "extendible" table has a single integer(32 bit) key column.

Now, the only problem is that in LLBLGen I can't add a new EntityRelation at runtime.cry If this were possible (for example) I will be able to add, at runtime, a properly defined relation between the "Extendible table 'Customers'" and the "generic extra data table"; linking Customers.IDCustomers to ExtraData.ID. So I will be able to load these extra data using a properly filtered prefetch path that extract from ExtraData only the row really linked to Customers table (I can automatically build that prefetch path, because the software know the structure of extended extra data).

Will LLBLGen ever support dynamically added Relation? please please pleeeaseeee....... simple_smile simple_smile simple_smile simple_smile simple_smile simple_smile

I think that a similar functionality imply that when I dynamically add a new relation between 2 entities, this new relation go in some static/shared "Dynamic entityRelation container". And so these new relation are visible/usable/used everywhere in the client code that has added them. And so thes new relation need to be added only one time, when the program start.

Using a similar approach I can add a new EntityRelation beteween 2 entities. But because of the fact that the relation is added at runtime, I can't have a real property that link the object on the entity classes. But, for example, the Customers entity.GetMemberEntityCollections can return a Collection containing the ExtraData row linked trough the dynamically added relation. And I believe it's also possible to trick the binding, and made the binding believe that exist property that expose the dynamically added collection.

(Some week ago I've created a class that trick the binding sunglasses . This class is a datasource that can hold an arbitrary large number of IEtityCollection2; and these entityCollection can come from different database simple_smile . This class "expose" these collection to de binding making the binding believe that exist an EntityCollection2 property for each entityCollection added to this datasource. And properly overriding the Get/Set value of PropertyDescriptor made the binding works. It's not simple, it's not linear, but it works! Ah... I was able to create that class only because I read the fantastic article about binding writed by Frans, thanks Frans! simple_smile

Ah... knowing how the binding works made me able to do some nice tricks. For example a developed a personalyzed BindingSource class that, holding an EntityCollection, and using the prefetch path that loaded this entitycollection --> hide from the binding the propertyDescriptor representind the entityCollection that where not loaded in the used prefetch path.

And now my EntityCollection binded to my UltraGrid generate only 5 band, instead on 80 frowning bands (of wich, obviously, only 5 contained rows). And the binding operation (ultragrid.Datasource = EntityCollection) now only take 0.1 second, instead of 3 seconds.sunglasses sunglasses sunglasses And I didn't need to limit the MaxBandDepth of ultragrid simple_smile simple_smile simple_smile simple_smile

Another function of my tweaked BindingSource, is that the property representing the entitySubcollection loaded by the prefetchpath, are exposed in the order in wich they were found in the prefetch path. So I can define the order in wich my ultragrid expose my subCollection.

Frans, why didn't you include a similar tweaked BindingSource class in LLBLGen library? I think this can be usefull for speedup/tweaking the binding operation of windows form controls. And it isn't tied to UltraGrid, it even works with .Net datagrid, or .Net binding in general. )

BertS
User
Posts: 89
Joined: 28-Jul-2005
# Posted on: 21-Dec-2006 15:32:16   

Max wrote:

We decided to don't change the DB schema, so for each fields that the customer want to add, we will save column/data type/ExtendedTable in some "structure table"; and the added data will go in a predefined "generic extra column data table". This generic ExtraDataTable will have an ID that will refer to the original entity to wich this extra data is linked. For simplicity we assume that each "extendible" table has a single integer(32 bit) key column.

Now, the only problem is that in LLBLGen I can't add a new EntityRelation at runtime.cry If this were possible (for example) I will be able to add, at runtime, a properly defined relation between the "Extendible table 'Customers'" and the "generic extra data table"; linking Customers.IDCustomers to ExtraData.ID. So I will be able to load these extra data using a properly filtered prefetch path that extract from ExtraData only the row really linked to Customers table (I can automatically build that prefetch path, because the software know the structure of extended extra data).

You can also create CustomersExtraData, SuppliersExtraData instead of one ExtraData. In that case, you can create all desired relations at designtime. This will also make ('all') your queries easier, because you eliminiate the check for EntityType.

Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 21-Dec-2006 17:56:14   

BertS wrote:

Max wrote:

We decided to don't change the DB schema, so for each fields that the customer want to add, we will save column/data type/ExtendedTable in some "structure table"; and the added data will go in a predefined "generic extra column data table". This generic ExtraDataTable will have an ID that will refer to the original entity to wich this extra data is linked. For simplicity we assume that each "extendible" table has a single integer(32 bit) key column.

Now, the only problem is that in LLBLGen I can't add a new EntityRelation at runtime.cry If this were possible (for example) I will be able to add, at runtime, a properly defined relation between the "Extendible table 'Customers'" and the "generic extra data table"; linking Customers.IDCustomers to ExtraData.ID. So I will be able to load these extra data using a properly filtered prefetch path that extract from ExtraData only the row really linked to Customers table (I can automatically build that prefetch path, because the software know the structure of extended extra data).

You can also create CustomersExtraData, SuppliersExtraData instead of one ExtraData. In that case, you can create all desired relations at designtime. This will also make ('all') your queries easier, because you eliminiate the check for EntityType.

I can, but in that case I also need to double the table... And in that case it's harder to build an engine that will take care of these extra data at the gui level.

Thanks anyway

BertS
User
Posts: 89
Joined: 28-Jul-2005
# Posted on: 21-Dec-2006 18:48:31   

Max wrote:

I can, but in that case I also need to double the table...

Yes, but why should that be a problem? Ok, you've two tables with the same fields, but with different constraints. So they're not the same.

And in that case it's harder to build an engine that will take care of these extra data at the gui level.

Why? You could give the propertyname in the base-entity that refers to the ExtraData-EntityCollection a fixed name. Eventually let the ExtraData-entities implement an interface that makes it easier to your gui. But in what way do you think it should be harder?

Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 22-Dec-2006 09:20:20   

BertS wrote:

Max wrote:

I can, but in that case I also need to double the table...

Yes, but why should that be a problem? Ok, you've two tables with the same fields, but with different constraints. So they're not the same.

And in that case it's harder to build an engine that will take care of these extra data at the gui level.

Why? You could give the propertyname in the base-entity that refers to the ExtraData-EntityCollection a fixed name. Eventually let the ExtraData-entities implement an interface that makes it easier to your gui. But in what way do you think it should be harder?

mmmh... My DB now has about 80 tables, of wich 30 are "extedable". So with you approach I need to add 30 new table. And I need to add 30 new relation. and these 30 new table + 30 new relatione need to be maintained. And these 30 new table will have the same structure, because I want that the DB structure is the same over customers, and because I don't want to recompile the software when I need a new extra column.

I believe that a more generic approach, using only 1 extraDataTable, is simpler and better maintainable. Naturally it's less performing, and need runtime-added/generated relation. But to me, in our case, this seem a better approach than 30 new table of wich, maybe that only 2 or 3 are used by the same customers.

These "ExtraData" are not so common... it surely exist in each of our customers... buy maybe from 1 to 10 extra column for each customers, on 2 or 3 tables.

There is nothing wrong in your approach, and surely it's better performing, and better normilized respect to DB structure. But i believe that it will requre more time to be maintained thar my approach. We need a solution that is simple and fast to maintain, ad we don't need the better performace possible. Our approach is a trade-off, but I think it's a good trade-off in our situation.

simple_smile

BertS
User
Posts: 89
Joined: 28-Jul-2005
# Posted on: 22-Dec-2006 11:02:12   

Max wrote:

...truncated...

Ok, then I can understand your choise for a generic way. Although you then reach the limits of LLBLGen with your dynamic relations simple_smile Good luck!

sami
User
Posts: 93
Joined: 28-Oct-2005
# Posted on: 27-Dec-2006 09:41:10   

Max wrote:

These "ExtraData" are not so common... it surely exist in each of our customers... buy maybe from 1 to 10 extra column for each customers, on 2 or 3 tables. simple_smile

I am sure this option has also crossed your mind, but here goes nothing. If you are after a simple solution, why not just add a few spare columns to each might-be-extended tables.

Anyway, we are also using custom columns in our solution (using a bit similar approach as your extradata tables), and it would be great if LLBLGen had a better support for custom fields.

greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 20-Jun-2007 03:52:31   

Hi Backslash,

I enjoyed reading about your solution for dynamically creating/accessing the database columns/tables. Quite clever! I just starting looking at LLBLGenPro...what a great product to work with!

Have you completed your work on the dynamic database column/table creating? Any more issues/guidance you'd be able to share?

Thanks!

Andy

Backslash
User
Posts: 21
Joined: 21-Jun-2005
# Posted on: 29-Jun-2007 05:19:19   

Hi Greenstone, thanks for your comments!

Since my last post, I've made some more progress on these customizations, such as supporting serialization and using SMO to manage the actual database schema. It's been running pretty solid so far, so I'm happy about that.

It is quite a bit of extra work to support a dynamic schema, because now you need to build a framework to let the user create new fields and tables, dynamically generate the user interface, handle navigation, manage permissions, and create queries and reports at runtime. Once you have all those, you can actually start building an application on top of the framework. In my case that will be a very flexible CRM application. simple_smile

I did make a couple assumptions while working on this problem, such as only supporting SQL Server and the adapter templates. But let me know if you have any more questions, I'd be happy to offer more of the specifics of the implementation.

greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 30-Jun-2007 06:25:16   

Hi Backslash,

Great to hear back from you about your application!

It sounds like you've made great progress on what sounds like a quite slick application! sunglasses

For your application, it seems that you've worked through all the issues: from the configuration-user data-definition to the run-time-user running reports/queries.

I can envision what you mean about the extra work to support the dynamic-data. But the need for this dynamic-data is (with my inventory-type application) like it sounds with your application: a really important feature.

For my app, I'm envisioning that on a portion of our standard UI form (representing an object currently selected for display) there is a part where the “configuration-user” is allowed to add fields of his/her choice to the UI form.

To define the (customizable dynamic-data part of the) UI form, the configuration-user would use a built-in (to my app) form-builder to define the added custom data-fields for the selected object.

Then a run-time-user could go into "run" mode and get/set information in that UI form template.

Here's the general scheme I'm proposing:

Dynamic Data Metadata Schema Generation: 1.) Implement a form-builder in the UI/Business layers--where the user creates UI form field-definitions (and specifies the database column type/form-design-surface position for each). The form-builder would generate a form xml-metadata document describing the form layout (in the for UI) and the SQL Server database metadata (database schema)

2.) Parse of this xml-metadata document, in the data-layer, would generate an alteration of the database schema.

3.) Parse of this xml-metadata document, in the UI-layer, by the form-builder-tool run-time component, would generate the form UI.

Dynamic Data Reading/Writing: 1.) The read (from the database) of UI form data (via the modified …ORMSupportClasses.dll) gets dynamic-data via LLBLGenPro.

2.) The write (to the database) of the UI form data (via the modified …ORMSupportClasses.dll) sets the dynamic-data via LLBLGenPro.

Here are some questions going through my mind about your implementation...

  • Does your application have a “configuration-user” defining UI forms by locating form-fields on a design-surface? Did you come across any good libraries/tools for help here?

  • Did you have your “configuration-user” mode define an xml-metadata document specifying the added dynamic-data fields & table/column mapping parameters to the database tables/columns?
    ...Or maybe your database-mapping metadata was contained as xml attributes in the xml-data document...assuming you passed the dynamic metadata and dynamic data as xml?

  • You mentioned you were able to get the serialization working well (I assume the serialization of your dynamic-data...by making modifications in the LLBLGenPro ...ORMSupportClasses.dll). Any more code chunks you'd be able to share from your customization of the LLBLGenPro ORMSupportClasses?

*You also mentioned using the LLBLGenPro “adapter” mode (versus the “self-service” mode). I don’t yet understand all the intricacies of the best mode to choose. What drove you to choose that mode?

*I may have a need for a given dynamic-data field to be (in some cases) a collection: Meaning a 1:m relationship between my “main” (dynamically-generated) table –and- a “secondary” (dynamically-generated) table. That given dynamic-data field would be shown in a little grid (showing the columns of the “secondary” table as the grid’s columns). Did your dynamically-generated data sit in a single-table, or did you have cases where a secondary table was also dynamically-generated & set/get data at run-time?

Any comments on my proposed scheme…and on these questions would be greatly appreciated!

Backslash
User
Posts: 21
Joined: 21-Jun-2005
# Posted on: 04-Jul-2007 10:19:48   

It looks like you're on the right track. I'll try to answer your questions in corresponding order...

  1. Yes, I needed to create a form builder for the end users so they can control the location and properties of the fields they create. I wasn't able to find any helpful libraries for this, partly because we're using WPF for the user interface, and there's not a lot of third party controls for WPF yet. But building a simple interface wasn't too difficult, there's quite a few WPF samples out there, and WPF has support for drag-and-drop. One site that came in handy for 'research purposes' was a trial account at www.salesforce.com. Once you have an account, you can go through the process of adding a new object, add fields and relationships, and change the page layout. (Go to Setup -> App Setup -> Build -> Custom Objecs) It can take a while to get to know their system, but it's nice to have a working demo to play with.

  2. I'm storing the metadata for user-defined tables and fields in the database in their own actual tables. For example, I have a table in the database called 'Object' to store data for user-defined tables, with a 1:M relationship to a table called 'Field' to store the user-defined field definitions. The Field table also has a relationship to a FieldType table, which contains all the supported field types in the application. Within the application, I have generic field types such as Date/Time, Text, Number, along with specialized field types such as Email, Phone Number, and URL.

It shouldn't really matter how the metadata is stored, just as long as you can manage the data at runtime. I like having the Object and Field tables in the database, so I can use LLBLGen to generate ObjectEntity and FieldEntity classes, and use those like any other entity.

  1. Serialization... Yikes! I might have to start a 3-part series to go though all the details. It was a challenge to get serialization working properly with WCF. If you're not using WCF, it should simplify things a bit. Give me some time and I'll see if I can throw something together into an attachment.

  2. Choosing the adapter templates was a simple choice for me, because it gives more control over when data is accessed from the database, and has better support for remoting and web services. There's a section in the LLBLGen documentation called "Templates and Template Groups" under Concepts that explains the differences in more detail.

  3. Yes, you can let users create 1:M relationships between tables. Whenever a user creates a new object, we actually create that table in the database at runtime to store the records. Now it's pretty much up to you how to design the user interface to let users view, add, and edit records in the new table. In my app, users can browse directly to a list view of all records in the object, or they can view and edit the related records under the details of the parent object. I'm sure you could allow users to edit related records within a grid, but that could make things difficult if you wanted to support multiple or nested 1:M relationships.

Hope that helps! simple_smile

greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 04-Jul-2007 21:13:53   

Hi Backslash,

Thanks for the note. Some great help!

I got online and looked at the SalesForce.com. Quite interesting how they approached creating on the custom objects and fields. They seem to have done a nice job--with the difficult limitation of an HTML-based UI. I'm not overly familiar with the new WPF. Have you found WPF to have enough flexibility to allow you to build a good UI for your application? Is your app using WPF for both desktop and browser versions of you app?

I'd love to hear about your experiences with serialization using the WCF...so look forward to the possibility of the "series". simple_smile

In a previous post, you were able to post a code-snippet that showed a modification to the LLBLGenPro's run-time library code. Any chance you'd be able to post any updated or other such (dynamically mapped data) code-snippets of your changes to the LLBLGenPro's run-time?

Hope you have a great weekend,

Greenstone

Backslash
User
Posts: 21
Joined: 21-Jun-2005
# Posted on: 07-Jul-2007 04:38:48   

Yes, we're using WPF for both the desktop and browser based version. We recently updated our project to use a flexible application template, so we can switch between running in a browser versus the desktop just by changing modes in the debug config settings. This has the advantage of only maintaining one codebase for both the web and desktop. We're also trying to support running in partial trust on the web, but some functionality will be limited by the browser sandbox. There's an article about the template here: http://scorbs.com/2006/06/04/vs-template-flexible-application/

So far I've found WPF to be pretty nice, but I'm not the one designing the graphics, animations, and so on. There's definitely a lot to learn in WPF, but it is very flexible.

Also, I forgot to mention that I no longer need to make any changes to the LLBLGen runtime libraries. Instead, I made some small adjustments to the code generator templates, and use reflection in a couple spots to access private fields. It's a bit of a hack, but it makes updating to the latest version of LLBLGen easier.

I've started to review and clean up the code for the serialization process (can't post messy code!), and found some ways to simplify things. First of all, no serialization code is needed to support dynamic fields on the generated entites. For user-defined tables, some code is needed for XML serialization, which is used by WCF. Most of the complexity is related to the user-defined tables, which is handled by the CustomObjectEntity and CustomObjectEntityFactory classes that I wrote. Even then, there isn't any particularly complex code in one spot. The basic idea is to add the dynamic field definitions to the entity when a new instance of an entity is created.

The other part is to manage the field definitions dynamically. Take a look at PersistenceInfoProvider.cs and FieldInfoProvider.cs in the generated code. The first file manages the mapping from the database fields to the entity fields, and the second defines the actual entity fields. When the application starts up, I load all the metadata for the user defined fields and tables, and make calls to AddElementMapping, AddElementFieldMapping, and AddElementFieldInfo as in those files. When a user adds, edits, or deletes a field or object at runtime, then I simply remove the mappings for that object and recreate them.

Anyways, I still want to review and clean up more of the code before posting anything. I should have something ready for next week. simple_smile

greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 08-Jul-2007 15:16:48   

Hi Backslash,

Thanks for the thoughts.

The WPF sounds like a nice environment, so will look into that and the flexible templates more.

Quite interesting that you were able to able to modify the LLBLGen code generation templates...

Yes, very nice that don't need to support modified LLBLGen runtime libraries. Are you using reflection to add the methods/properties (to an "empty" entity class) for the user-defined tables/fields?

Definitely some very helpful info on the serialization and managing the field defintions dynamically.

I'm Looking forward to the serialization process info.

Backslash
User
Posts: 21
Joined: 21-Jun-2005
# Posted on: 26-Jul-2007 04:13:45   

Ok, finally have the first piece of code ready. Attached are the template changes, and bits of code to support dynamic fields on the generated entity classes. This is still a work-in-progress, so I'm sure there's still bugs to be worked out!

As for the user defined fields and tables, there is no need to use reflection to access their properties. All the actual values are stored in the entity's Fields collection. To read a value, you can use entity.Fields[fieldName].CurrentValue; and to set a value, use entity.SetNewFieldValue(fieldName, "New value"); You just have need to have a list of field names handy, which comes from the metadata that's saved when a field is added.

Next up is support for dynamic tables!

Attachments
Filename File size Added on Approval
DynamicFields.txt 12,741 26-Jul-2007 04:14.43 Approved
greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 27-Jul-2007 02:29:54   

Quite nice!

Looking forward to hearing about dynamic tables!

greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 18-Oct-2007 22:56:19   

Hi Backslash,

If you find time, would love to see how you approached the dynamic tables.

Cheers,

Greenstone

1  /  2