Hi,

I have a table called "CustomBase" which has columns LLBLGen doesn't know about (e.g. dynamically added columns by a user).

I would now like to add these columns as EntityFields to CustomBaseEntity at runtime. Once they are added LLBLGen should treat them like all other fields and include them in fetch and update operations.

To access the fields I would use code like this: object oldData = entity.Fields[fieldName].CurrentValue; entity.SetNewFieldValue(fieldName, newData);

It seems to be possible when looking at this article (http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=59404&ThreadID=8232) but I couldn't get it to work so far.

Any tips are appreciated, Thank you, Patrick

LLBLGen 2.5; SQL Server 2005; C#; VS2005; Adapter Template

Backslash is the only one I know who has proposed a solution for dynamic DDL (in the mentioned thread).

Walaa wrote:

a solution for dynamic DDL

Thanks Walaa I wanted to limit the question though to not confuse it with other complicated topics. It's only about adding EntityFields?, FieldInfo? at runtime which are used for retrieval and updates and not dynamic tables etc. It seems to be possible from the mentioned article but I couldn't get it to work so far.

So I would be grateful if somebody could answer this question: When and where can I add dynamically EntityFields?, FieldInfo? (not sure what is needed) etc. to an entity or entity factory in code so that they are always used when retrieving and persisting this entity (with single entity fetches and collections).

Thank you very much, Patrick

Ok it is possible to add dynamic fields to the LLBLGen entities and it actually works very well.

As a test I changed this in the templates: FieldInfoProvider.Custom.cs

public partial class FieldInfoProviderCore : FieldInfoProviderBase
{
    public void AddElementFieldInfo(string elementName, string elementFieldName, Type dataType, bool isPrimaryKey, bool isForeignKey, bool isReadOnly, bool isNullable, int fieldIndex, int maxLength, byte scale, byte precision)
    {
        base.AddElementFieldInfo(elementName, elementFieldName, dataType, isPrimaryKey, isForeignKey, isReadOnly, isNullable, fieldIndex, maxLength, scale, precision);
    }
    public void ConstructElementFieldStructures()
    {
        base.ConstructElementFieldStructures(InheritanceInfoProviderSingleton.GetInstance());
    }
}

PersistenceInfoProvider.Custom.cs

internal partial class PersistenceInfoProviderCore
{
    public new void AddElementFieldMapping(string elementName, string elementFieldName, string sourceColumnName, bool isSourceColumnNullable, int sourceColumnDbType, int sourceColumnMaxLength, byte sourceColumnScale, byte sourceColumnPrecision, bool isIdentity, string identityValueSequenceName, TypeConverter typeConverterToUse, Type actualDotNetType, int fieldIndex)
    {
        base.AddElementFieldMapping(elementName, elementFieldName, sourceColumnName, isSourceColumnNullable, sourceColumnDbType, sourceColumnMaxLength, sourceColumnScale, sourceColumnPrecision, isIdentity, identityValueSequenceName, typeConverterToUse, actualDotNetType, fieldIndex);
    }
}

It is now possible to run the following code which adds the dynamic fields:

public static void MyCustomFieldsInit()
{
    FieldInfoProviderCore fieldProvider = (FieldInfoProviderCore)FieldInfoProviderSingleton.GetInstance();
    fieldProvider.AddElementFieldInfo("CustomBaseEntity", "VfxName", typeof(System.String), false, false, false, true, (int)CustomBaseFieldIndex.Name + 1, 50, 0, 0);
    fieldProvider.ConstructElementFieldStructures();

    PersistenceInfoProviderCore provider = (PersistenceInfoProviderCore)PersistenceInfoProviderSingleton.GetInstance();
    provider.AddElementFieldMapping("CustomBaseEntity", "VfxName", "VfxName", true, (int)SqlDbType.VarChar, 50, 0, 0, false, "", null, typeof(System.String), 2);
}

With this in place we can run the following code which works very well:

using (DataAccessAdapter da = new DataAccessAdapter())
{
    EntityCollection<CustomBaseEntity> col = new EntityCollection<CustomBaseEntity>();
    EntityField2 field = new EntityField2(FieldInfoProviderSingleton.GetInstance().GetFieldInfo("CustomBaseEntity","VfxName"));
    RelationPredicateBucket bucket = new RelationPredicateBucket(field % "Me%");
    da.FetchEntityCollection(col, bucket);
    
    CustomBaseEntity ent = new CustomBaseEntity();
    ent.Name = "Test2";
    ent.SetNewFieldValue("VfxName", "Test2VfxName");
    col.Add(ent);
    da.SaveEntityCollection(col);
}

The only problem is the call to ConstructElementFieldStructures(). ConstructElementFieldStructures calls PostProcessInfo() on the private class ElementFields in FieldInfoProviderBase. PostProcessInfo goes through all fieldInfos and ends up adding all pk values a 2nd time as the collection doesn't get cleared beforehand.

internal void PostProcessInfo()
{
    ...
    for(int i = 0; i < _fieldInfos.Length; i++)
    {
        IFieldInfo fieldInfo = _fieldInfos[i];
        if(fieldInfo.IsPrimaryKey)
        {
            _pkFieldIndices.Add(i);
        }
    ...
}

The question is now: could the _pkFieldIndices list be cleared before adding the primary keys again? If yes could this be become part of LLBLGen as I am hesitant to change the ORMSupportClasses since it makes upgrading later on more difficult.

Thanks a lot, Patrick

There's a big problem: concurrency.

the structures build by FieldInfoProvider and friends are used inside entities in a shared way: i.e. every entity instance of the same type has fields which reference the same objects. This both saves memory and also a lot of performance.

The essential thing is though that these objects have to be left alone once the provider has build its internal stores: for example the cache with the name-index information to quickly find a field index based on the name (so creating an entity doesn't have to build this index over and over again). If you're going to change that object at runtime, when there are already entity objects created, you're in for a lot of problems, because all the entities of the same type refer to the same object.

So it needs a lock, but locks are slow when you're creating a lot of entities.

So i.o.w.: adding fields at runtime to the entities is a pain. It can only be done BEFORE the app actually starts, as a kind of add-on code to the calls in the FieldInfoProvider generated code.

Otis wrote:

It can only be done BEFORE the app actually starts, as a kind of add-on code to the calls in the FieldInfoProvider generated code.

Thanks Otis. That's a real pity as the test worked so well! Would be very helpful if LLBLGen could support flushing the internal data store cache one day as everything else works very well with fields added at runtime. Like this code here:

MyCustomFieldsInit();
using (DataAccessAdapter da = new DataAccessAdapter())
{
    EntityCollection<CustomBaseEntity> col = new EntityCollection<CustomBaseEntity>();
    EntityField2 field = new EntityField2(FieldInfoProviderSingleton.GetInstance().GetFieldInfo("CustomBaseEntity","VfxName"));
    RelationPredicateBucket bucket = new RelationPredicateBucket(field % "Me%");
    da.FetchEntityCollection(col, bucket);
    
    CustomBaseEntity ent = new CustomBaseEntity();
    ent.Name = "Test2";
    ent.SetNewFieldValue("VfxName", "Test2VfxName");
    col.Add(ent);
    da.SaveEntityCollection(col);
}

Otis wrote:

If you're going to change that object at runtime, when there are already entity objects created, you're in for a lot of problems, because all the entities of the same type refer to the same object.

If the programmer had to ensure that after adding fields to a certain entity x that all entities of that type would have to be disposed and then refetched / recreated. Would this get around the problem? If yes that would be quite an acceptable solution.

Otis wrote:

So it needs a lock, but locks are slow when you're creating a lot of entities.

Or is the problem more related to multi threaded applications?

Thanks a lot, Patrick

pat wrote:

If the programmer had to ensure that after adding fields to a certain entity x that all entities of that type would have to be disposed and then refetched / recreated. Would this get around the problem? If yes that would be quite an acceptable solution.

Hi Frans,

I would appreciate to hear your thoughts on this.

Thanks a lot, Patrick

I have read http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8232&StartAtMessage=0&#59404 as well as this thread. The solutions offered therein are most extraordinary even though Frans clearly outlined why they would not work...concurrency being the most recent issue. My solution is not nearly as complex and DDL-oriented, yet nonetheless it seems to work. I would at least like to hear the master's opinion on the solution that I have used in the past and believe I am about to implement again, but this time as part of an LLBLGen Pro solution.

Please remember too before you tear my proposed solution to dynamically adding fields to a database at runtime to pieces that you should try to offer another more viable solution as others have done and not just criticism. The mere fact that you do not agree with the need for such functionality is avoiding the issue that the customer who pays me is demanding the functionality so I must deliver or go hungry... :-) So please offer a solution not just more criticism as I know already that the solution is not the perfect solution and has many issues. The last time I implemented this dynamic field functionality Environmental Engineers were wandering into caves here in Kentucky and discovering new species of animals, etc. and they needed a way to quickly alter the database (without a lot of red tape and time wasted on getting approvals to modify database schemas from DBAs who are already overworked). This time the manufacturing engineers need a way to alter the database as new clients come onboard and new information about electronic devices that was not being collected can be collected quickly without having to wait for a team of programmers to take the task on which as we all know can take a long time often for reasons beyond the programmers' control such as other programming tasks/development/maintenance taking priority. :-)

The idea here is that three tables can be used to allow the new database fields to be added. One defines new schema definitions, one associates the new rows in the schema definitions table to specific GUI User Controls (i.e. user controls can be taken to represent screens, meaning windows or web pages, or parts therein) and Panels within the User Controls as well positions the field's GUI counterpart appropriately, and the last of the three tables contains the actual value to be inserted which I proposed could have one of four datatypes that I referred to by their .NET type names

1. ArrayList (a string of comma-delimited values to be used for GUI Selection Lists such as DropDownLists, etc.)

2. DateTime

3. Decimal

4. String

Anyhow, here are the tables look like this:

Table 1 Table name: DynamicFieldSchema DynamicFieldName,PK,UnicodeString, Not Null DynamicFieldGUILabel,UnicodeString, Not Null DynamicFieldType,UnicodeString, Not Null DynamicFieldAllowableValues, UnicodeString DynamicFieldStringSizeOrDecimalPrecision, Decimal, Not Null DynamicFieldMinimumDecimalValue, Decimal DynamicFieldMaximumDecimalValue, Decimal DynamicFieldIsActive, Boolean, Not Null DynamicFieldCreatedBy, UnicodeString, Not Null DynamicFieldCreationDateTime, DateTime, Not Null DynamicFieldModifiedBy, UnicodeString, Not Null DynamicFieldModifiedDateTime, DateTime, Not Null

Table 2 Table name: DynamicFieldGUIMapping DynamicFieldGUIMappingID, PK, AutoNumber, Not Null DynamicFieldName, FK, UnicodeString, Not Null DynamicFieldGUIUserControlName, UnicodeString, Not Null DynamicFieldGUIUserControlPanelName, UnicodeString, Not Null DynamicFieldGUIUserControlPanelHorizontalXPosition, Decimal, Not Null DynamicFieldGUIUserControlPanelVerticalYPosition, Decimal, Not Null DynamicFieldIsActive, Boolean, Not Null DynamicFieldCreatedBy, UnicodeString, Not Null DynamicFieldCreationDateTime, DateTime, Not Null DynamicFieldModifiedBy, UnicodeString, Not Null DynamicFieldModifiedDateTime, DateTime, Not Null

Table 3 Table name: DynamicFieldValues DynamicFieldValueID, PK, AutoNumber, Not Null DynamicFieldGUIMappingID, FK, Integer, Not Null DynamicFieldDateTimeValue, UnicodeString DynamicFieldDecimalValue, UnicodeString DynamicFieldStringValue, UnicodeString DynamicFieldIsActive, Boolean, Not Null DynamicFieldCreatedBy, UnicodeString, Not Null DynamicFieldCreationDateTime, DateTime, Not Null DynamicFieldModifiedBy, UnicodeString, Not Null DynamicFieldModifiedDateTime, DateTime, Not Null

The reason I used three different values was to ensure that the data was typed correctly in this dynamic field values table. Of course, if DynamicFieldStringValue is populated with data, then DynamicFieldDateTimeValue and DynamicFieldDecimalValue will be necessarily null as a value can be of only one type at a time.

Look forward to your responses.

Respectfully,

John Flaherty smartwebagent@hotmail.com http://smartwebagent.com

pat wrote:

Otis wrote:

It can only be done BEFORE the app actually starts, as a kind of add-on code to the calls in the FieldInfoProvider generated code.

Thanks Otis. That's a real pity as the test worked so well! Would be very helpful if LLBLGen could support flushing the internal data store cache one day as everything else works very well with fields added at runtime. Like this code here:

MyCustomFieldsInit();
using (DataAccessAdapter da = new DataAccessAdapter())
{
    EntityCollection<CustomBaseEntity> col = new EntityCollection<CustomBaseEntity>();
    EntityField2 field = new EntityField2(FieldInfoProviderSingleton.GetInstance().GetFieldInfo("CustomBaseEntity","VfxName"));
    RelationPredicateBucket bucket = new RelationPredicateBucket(field % "Me%");
    da.FetchEntityCollection(col, bucket);
    
    CustomBaseEntity ent = new CustomBaseEntity();
    ent.Name = "Test2";
    ent.SetNewFieldValue("VfxName", "Test2VfxName");
    col.Add(ent);
    da.SaveEntityCollection(col);
}

The thing is that the whole app has to stop, then everything has to flush and then everything has to start over.

Otis wrote:

If you're going to change that object at runtime, when there are already entity objects created, you're in for a lot of problems, because all the entities of the same type refer to the same object.

If the programmer had to ensure that after adding fields to a certain entity x that all entities of that type would have to be disposed and then refetched / recreated. Would this get around the problem? If yes that would be quite an acceptable solution.

That's indeed what needs to be done, but also: you first have to make sure NO CODE will read or write to entities of that type, THEN you have to make the change and THEN you can proceed. So in multi-threaded apps, like websites, this is undoable.

smartwebagent wrote:

I have read http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8232&StartAtMessage=0&#59404 as well as this thread. The solutions offered therein are most extraordinary even though Frans clearly outlined why they would not work...concurrency being the most recent issue. My solution is not nearly as complex and DDL-oriented, yet nonetheless it seems to work. I would at least like to hear the master's opinion on the solution that I have used in the past and believe I am about to implement again, but this time as part of an LLBLGen Pro solution.

(snip)

Your solution of a related set of dyn. properties is one which works well, and is easy to implement. The only drawback is the set of columns to show in a grid for example.

(edit) btw, what's in the xls attached to your post? Virusscanner didn't reveal any virusses, but in general people are reluctant to open .xsl downloaded from the web, so if it just contains text, please attach a .txt file instead (I haven't opened the file yet)

Otis wrote

Your solution of a related set of dyn. properties is one which works well, and is easy to implement. The only drawback is the set of columns to show in a grid for example.

I believe that you meant that the drawback of using three tables to dynamically add fields to the system is that binding a set of dynamic fields to a grid would not be possible. If I am incorrect in my understanding, please clarify. Thank you.

Otis wrote

(edit) btw, what's in the xls attached to your post? Virusscanner didn't reveal any virusses, but in general people are reluctant to open .xsl downloaded from the web, so if it just contains text, please attach a .txt file instead (I haven't opened the file yet)

The .xls file I attached contained text-versions of the three tables specified in the text of my message. Sorry for the confusion. I have attached a .txt version of the same document for clarity purposes.

-------------------------------------break---------------------------------------

Back on the subject of the pros and cons of the technique I was discussing in my initial message, it appears to me that this methodology requires that a systematic approach of removing "dynamic fields" then inserting those fields into the database schema upon version, minor or major, upgrades is in order as any system which uses this kind of dynamic three-table database extensibility functionality may be burdened by performance issues as the number of "dynamic fields" inserted into the DynamicFieldSchema table and corresponding values inserted into the DynamicFieldValue table became larger. This would be a function of the maintenance developer(s) responsible for maintaining the system. Comments?

With regard to Pat's eloquent dynamic-DDL approach above, even if a programmatic LLBLGen Pro regeneration took place at runtime as Frans suggested (by stopping the application, regenerating, then restarting the application) using code something like

System.Diagnostics.Process(LLBLGen Pro Auto-Regeneration Command Line Goes Here);

the newly created entity/entities would still have to be programmed to in code. In other words, just because the system can allow new database tables to be created and new LLBLGen data access layer entities to be automatically regenerated, there still appears to be no way for the system as a whole to "dynamically grow" as input into new tables/entities/fields/properties will still need to be tied to new programmatically-creatable GUI components (i.e. textboxes, radiobuttonlists, dropdownlists, etc.) and input will further require input validation. As you can tell, complex validation is not possible with the technique I proposed either. Of course, I could have added a field to allow complex regular expressions to be entered and therefore associated with new GUI input controls/dynamic fields, but truth be told most programmers cannot construct complex regular expressions and no non-programmer is likely to be capable of constructing such complex validation mechanisms.

In summary, how are these new GUI elements to be created, mapped, positioned, validated, and have their events hooked up dynamically? Comments?

Respectfully,

John Flaherty smartwebagent@hotmail.com http://smartwebagent.com/

In summary, how are these new GUI elements to be created, mapped, positioned, validated, and have their events hooked up dynamically? Comments?

Here are my 2 cents: I worked in a system built 8 years ago, using V C++ 6. Where it enabled the user or admin to extend some forms by defining new fields. These fields could have been text fields, numeric fields dateTime fields, multi values fields....etc. The metadata of those fields would be stored in some database tables, also values stored for each form/object instance was stored in another database tables.

At runtime the form used the metadata stored in the database to dynamically build the appropriate GUI controls to display values stored in the database. (Controls like text boxes, check boxes comboboxes...etc.). The metadata included (x, y) position values for the placing the controls on the Form. If these values weren't set, then some default mechanism was used to place the controls

This was something like having a FormBuilder module in the application.

The admin had the ability to state some default values. Also the admin had the ability to assign some simple validation rules to different fields. And also these validation rules were stored in the database. And they can be loaded at FormLoad to let the GUI validate input values at runtime without hitting the database. (btw, it was a Windows application).

For complex cross fields validation rules, the admin had the ability to define some Scripts (VB scripts as far as I remember) to be used in the validation of the form. The system used to run these stored scripts when the user hit the submit button, to validate the form.

Here is an updated version of the same concept:

Table 1

Table name: DynamicFieldDefinitionSchema

DynamicFieldName,_PK,_UnicodeString, Not Null

DynamicFieldGUILabel,_UnicodeString_, Not Null // GUI Label's Text or Content property // associated with an input control

DynamicFieldType,_UnicodeString_, Not Null // Four (4) data types utilized: // ArrayList (DropDownList), // DateTime (Textbox) // Decimal (Textbox) // String (Textbox)

DynamicFieldDefaultValues, UnicodeString

DynamicFieldAllowableValues, UnicodeString // Allowable values is only applicable // if data type is defined in Table 1 as // an ArrayList such that a single // string can be comma-delimited // and split into an array which // can be bound to a dropdownlist // or listbox at runtime

DynamicFieldAllowableValuesSource, UnicodeString // Allowable values in a list that is // too big to store in a field so it // must be retrieved from a file // then split into an array and // bound to a dropdownlist // or listbox at runtime

DynamicFieldStringSizeOrDecimalPrecision, Decimal, Not Null // 6.3 means six places to left of the // decimal point, 3 places to the // right if type is decimal whereas // 6 means six total characters allowed // if type is string. NULL if DateTime // or ArrayList type.

DynamicFieldMinimumDecimalValue, Decimal

DynamicFieldMaximumDecimalValue, Decimal

DynamicFieldIsRequired, Boolean, Not Null

DynamicFieldValidationCodeSource, UnicodeString // Source of the validation method // either a stored procedure or .dll // name with one public function named // "Validate" calling private functions

DynamicFieldValidationFailureMessages, UnicodeString // Instructional validation failure // messages to be displayed to the user

DynamicFieldSiteID, Decimal, Not Null

DynamicFieldIsActive, Boolean, Not Null

DynamicFieldCreatedBy, UnicodeString, Not Null

DynamicFieldCreationDateTime, DateTime, Not Null

DynamicFieldModifiedBy, UnicodeString, Not Null

DynamicFieldModifiedDateTime, DateTime, Not Null

Table 2

Table name: DynamicFieldGUIMapping

DynamicFieldGUIMappingID, PK, UnicodeString, Not Null

DynamicFieldName, FK, UnicodeString, Not Null

DynamicFieldGUIScreenName, UnicodeString, Not Null

DynamicFieldGUIPanelName, UnicodeString, Not Null // One screen has many panels or one // webpage has many DIV tags

DynamicFieldGUIPanelTop, UnicodeString, Not Null

DynamicFieldGUIPanelLeft, UnicodeString, Not Null

DynamicFieldGUIControlDisplayYVerticalRowPosition, Decimal, Not Null

DynamicFieldGUIControlDisplayXHorizontalColumnPosition, Decimal, Not Null

DynamicFieldGUIControlType, UnicodeString, Not Null // Distinguishes between DropDownLists // and ListBoxes. Null for Textboxes

DynamicFieldSiteID, Decimal, Not Null

DynamicFieldIsActive, Boolean, Not Null

DynamicFieldCreatedBy, UnicodeString, Not Null

DynamicFieldCreationDateTime, DateTime, Not Null

DynamicFieldModifiedBy, UnicodeString, Not Null

DynamicFieldModifiedDateTime, DateTime, Not Null

Table 3

Table name: DynamicFieldValues

DynamicFieldValueID, PK, AutoNumber, Not Null

DynamicFieldGUIMappingID, FK, Integer, Not Null

DynamicFieldDateTimeValue, UnicodeString // Only one of these types will // actually have a value in it per // row. This is necessary in order // to use SQL's date-range querying // and number-range querying // capabilities. Otherwise, every // field's value would be a String // or Varchar2 meaning that you could // not query against values in the // database to retrieve dates less // than a given date, etc.

DynamicFieldDecimalValue, UnicodeString

DynamicFieldStringValue, UnicodeString

DynamicFieldSiteID, Decimal, Not Null

DynamicFieldIsActive, Boolean, Not Null

DynamicFieldCreatedBy, UnicodeString, Not Null

DynamicFieldCreationDateTime, DateTime, Not Null

DynamicFieldModifiedBy, UnicodeString, Not Null

DynamicFieldModifiedDateTime, DateTime, Not Null

Respectfully,

John Flaherty smartwebagent@hotmail.com http://smartwebagent.com/