What is the best way to handle null strings?

I have nullable varchars in the database. When I load them into an entity, and use myEntity.SomeStringField, they have the value of String.Empty.

I then set them to null.
Now, when I do an myEntity.Save(), they are classified as 'changed' and resaved back to the database, when they were null to start with and did not change.

In my code, where I normally use:

someEntity.StringField = null;  

now do I have to do this everywhere I assign it?:

if (!someEntity.TestOriginalFieldValueForNull(someEntityFieldIndex.StringField))
      someEntity.StringField = null;  

Using LLBLGen2.0, which has nullable types, I would hope/think I could use a natively nullable type like string, much like the other nullable types??

Either do the following: someEntity.StringField = null; someEntity.StringField.IsChanged = false;

Or better set the Project Property **ConvertNulledReferenceTypesToDefaultValue **to false. When set to false (default is true), an entity field which has a reference type (e.g. string) will return null / Nothing if the value for the field is null / Nothing. When set to true (default), the default value belonging to that reference type is returned. The default value for a type is produced by the generated class TypeDefaultValue."

Either do the following: someEntity.StringField = null; someEntity.StringField.IsChanged = false;

Well, I cannot set IsChanged=false, unless I do a compare to the original field everywhere I assign it.

Or better set the Project Property ConvertNulledReferenceTypesToDefaultValue to false. When set to false (default is true), an entity field which has a reference type (e.g. string) will return null / Nothing if the value for the field is null / Nothing. When set to true (default), the default value belonging to that reference type is returned. The default value for a type is produced by the generated class TypeDefaultValue."

This would be good, except I guess that would break every piece of code where it is assigned to some texbox - as in myTextbox.Text= someEntity.StringField. So, that would not be good.

Is this marking a field as changed even though it has not a bug? It would seem that FieldUtilities.DetermineIfFieldShouldBeSet should return false in this scenario?
(Scenario is the field has not changed, the dbvalue is null and the new value is null - that should result in false.)

(Second scenario, could potentially mark a field as unchanged, even if it had been changed before, but now the field is set to the original dbvalue?)

It's not a bug. May I ask why do you need to set the field to Null in the first place?

for more info about Null values, please read the Entities, NULL values and defaults section in the LLBLGen Pro Manual, under "Using the generated code -> Adapter/SelfServicing -> Using the entity classes"

It's not a bug.

Maybe....Can you tell me why in my scenario above, the code returns that it SHOULD update the field - when clearly it should not?

May I ask why do you need to set the field to Null in the first place?

Sure I have 5 fields that might be filled out. If they fill out the first field, then the rest are required. So, when the do NOT fill out the first field, I null out the first through the fith field in the database. In this case they are ALREADY null and that generates an update statement, when it is not necessary.

Sure I have 5 fields that might be filled out. If they fill out the first field, then the rest are required. So, when the do NOT fill out the first field, I null out the first through the fith field in the database. In this case they are ALREADY null and that generates an update statement, when it is not necessary.

IMHO, the best practice here is to have an emty string '' default value in the database for nullable string fields, this would save a lot of things.

But anyway for the above mentioned senario, you can check for the original value (DBValue) before setting the field to Null. And you can check for the CurrentValue (EmptyString in this case) too.

But anyway for the above mentioned senario, you can check for the original value (DBValue) before setting the field to Null. And you can check for the CurrentValue (EmptyString in this case) too.

Again, it seems like the fields class would do this? I still cannot see why the method I mentioned above would return that the field should be updated. That way I would not have to do this everywhere I used a string to avoid pointless updates and such?

This is a similar thread. http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7696&HighLight=1 The idea is that if you are setting a value then IsDirty is set. If you don't want to update the field then it's property shouldn't be set.

I read that.... However, the difference is I have an entity which is NOT new. And entities that are not new (loaded with data), do NOT set the field to a value if that value matches the entities current value.

So, if you have an SomeEntity.SomeProperty, where SomeProperty has a value of "Test", doing this:

SomeEntity.SomeProperty="Test";

Does not result in the isDirty flag being set or the field being changed.

What does not seem right is if you have SomeEntity.SomeString, where the value is dbnull, doing this:

SomeEntity.SomeString=null;

DOES set the isDirty flag.

If you don't want to update the field then it's property shouldn't be set.

Of course - however, I do not know if I need to update it unless I do something like this:

if (newValueFromUser==string.Empty && !someEntity.TestOriginalFieldValueForNull(someEntityFieldIndex.StringField))
     someEntity.StringField = null; 

WayneBrantley wrote:

I read that.... However, the difference is I have an entity which is NOT new. And entities that are not new (loaded with data), do NOT set the field to a value if that value matches the entities current value.

So, if you have an SomeEntity.SomeProperty, where SomeProperty has a value of "Test", doing this:

SomeEntity.SomeProperty="Test";

Does not result in the isDirty flag being set or the field being changed.

What does not seem right is if you have SomeEntity.SomeString, where the value is dbnull, doing this:

SomeEntity.SomeString=null;

DOES set the isDirty flag.

If you don't want to update the field then it's property shouldn't be set.

Of course - however, I do not know if I need to update it unless I do something like this:

if (newValueFromUser==string.Empty && !someEntity.TestOriginalFieldValueForNull(someEntityFieldIndex.StringField))
     someEntity.StringField = null; 

I agree with Wayne here, the isDirty flags should only be set if a field actually changes, not just if an attempt was made to set a value.

Cheers Simon

(Scenario is the field has not changed, the dbvalue is null and the new value is null - that should result in false.)

(Second scenario, could potentially mark a field as unchanged, even if it had been changed before, but now the field is set to the original dbvalue?)

Can you tell me why in my scenario above, the code returns that it SHOULD update the field - when clearly it should not?

I agree with Wayne here, the isDirty flags should only be set if a field actually changes, not just if an attempt was made to set a value.

I disagree with you Well it's the same dilemma about the null values.

What if I want to make sure the field is set to NULL in the database, in case someone else has updated its value, since the last time I fetched it. (even if its value was NULL when I fetched it).

Using your analogy, I would never be able to set the field to NULL. in this scenario.

That's why the choice was left to the developer, according to his business deal, and it was not decided by the field class.

The scenario again: 1- I fetched an entity with field A = Null in the database (DBValue = NULL) 2- Some other user has updated the field to have a value 3- I want to re-set the field to be NULL, or I want to make sure the field is set to NULL regardless of it's current database value. Do I have to re-fetech the entity to set this field to NULL.?

A field is changed when:

entityIsNew &&
(
    !fieldToSet.IsChanged
    ||
    (
        fieldToSet.IsChanged
        &&
        (
            (
                (fieldToSet.CurrentValue != null)
                &&
                !ValuesAreEqual( fieldToSet.CurrentValue, value )
            )
            ||
            (fieldToSet.CurrentValue == null)
        )
    )
)

OR

!entityIsNew
&&
(
    fieldToSet.IsChanged
    ||
    (
        (fieldToSet.DbValue == null)
        &&
        (value != null)
    )
    ||
    (fieldToSet.CurrentValue == null)
    ||
    (
        (fieldToSet.DbValue != null)
        &&
        (fieldToSet.CurrentValue != null)
        &&
        !ValuesAreEqual( fieldToSet.CurrentValue, value )
    )
)

Looking at this, the field which is null in a non-new entity and which is again set to null, will indeed be marked as changed (middle clause in the non-new entity branch). It forgets to check for null with that clause.

This is important to you because of timestamp increases etc. ?

Keep in mind that a lot of people use "" as NULL values for strings (Oracle makes NULLs in varchar fields become "") and working with a string typed variable in .NET which is null is really awkward and makes your code complicated due to the null checks on something which acts like a valuetype. (so only use string variables which are null if you need to define them as undefined and "" is a defined value)

Walaa wrote:

(Scenario is the field has not changed, the dbvalue is null and the new value is null - that should result in false.)

(Second scenario, could potentially mark a field as unchanged, even if it had been changed before, but now the field is set to the original dbvalue?)

Can you tell me why in my scenario above, the code returns that it SHOULD update the field - when clearly it should not?

I agree with Wayne here, the isDirty flags should only be set if a field actually changes, not just if an attempt was made to set a value.

I disagree with you Well it's the same dilemma about the null values.

What if I want to make sure the field is set to NULL in the database, in case someone else has updated its value, since the last time I fetched it. (even if its value was NULL when I fetched it).

Using your analogy, I would never be able to set the field to NULL. in this scenario.

That's why the choice was left to the developer, according to his business deal, and it was not decided by the field class.

The scenario again: 1- I fetched an entity with field A = Null in the database (DBValue = NULL) 2- Some other user has updated the field to have a value 3- I want to re-set the field to be NULL, or I want to make sure the field is set to NULL regardless of it's current database value. Do I have to re-fetech the entity to set this field to NULL.?

I disagree with your disagreement

As Wayne pointed out, setting a non-null string value does nothing if the entity field already holds the same non-null string value. I don't think that Null should be treated any differently.

The IsDirty flag should only be set when there is a material change.

In your scenario of forcing a null just in case some other user has changed it, well you'll have the opposite problem if you were trying to force a non-null value since the setter will effectively become a NOP.

I think also that forcing an update 'just in case' the db value has changed in the meantime would be the exception rather than the rule.

If you really did need to force a value to be updated regardless of the db value then the ForcedCurrentValueWrite method can be used. That is much more intuitive and would prevent uncessary update for the majority of cases.

Cheers Simon

Makes sense

Looking at this, the field which is null in a non-new entity and which is again set to null, will indeed be marked as changed (middle clause in the non-new entity branch). It forgets to check for null with that clause.

This is important to you because of timestamp increases etc. ?

That's what I thought.

It is important for several reasons. For example, if leaving the screen, the entity would show dirty and I would ask to save changs, etc. Also, I was planning on auditing in triggers on this table too, but I think this will mess that up too...

Finally, just from a pure performance issue - if the fields are not changed, lets not update them (which you already do in 99% of the cases)! If you call Save() on an entity and there are no changes, the framework does not do an update - but I was getting one in this case....

WayneBrantley wrote:

Looking at this, the field which is null in a non-new entity and which is again set to null, will indeed be marked as changed (middle clause in the non-new entity branch). It forgets to check for null with that clause.

This is important to you because of timestamp increases etc. ?

That's what I thought.

It is important for several reasons. For example, if leaving the screen, the entity would show dirty and I would ask to save changs, etc. Also, I was planning on auditing in triggers on this table too, but I think this will mess that up too...

Yes, it shouldn't issue an update if the actual value (null) isn't changed.

This is a leftover from the 1.0.2005.1 code, where CurrentValue couldn't be 'null' so if it was, it was already changed. This was changed in v2.0, where CurrentValue was always null. I'll add the check. It will be in build v2.0.07.0317 of the runtime lib.

[Test]
public void NullFieldIsChangedTest()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        CustomerEntity c = new CustomerEntity("ALFKI");
        adapter.FetchEntity(c);
        Assert.IsNull(c.Fields["Region"].CurrentValue);
        Assert.AreEqual(string.Empty, c.Region);
        Assert.IsNull(c.Fields["Region"].DbValue);
        Assert.IsFalse(c.SetNewFieldValue((int)CustomerFieldIndex.Region, null)); // A
        Assert.IsNull(c.Fields["Region"].CurrentValue);
        Assert.AreEqual(string.Empty, c.Region);
        Assert.IsFalse(c.Fields["Region"].IsChanged);
        Assert.IsFalse(c.IsDirty);
    }
}

First failed at line A, after the fix the test passes.

Yes, it shouldn't issue an update if the actual value (null) isn't changed.

This is a leftover from the 1.0.2005.1 code, where CurrentValue couldn't be 'null' so if it was, it was already changed. This was changed in v2.0, where CurrentValue was always null. I'll add the check. It will be in build v2.0.07.0317 of the runtime lib.

First failed at line A, after the fix the test passes.

Excellent - I think everyone will benefit from this. Thanks!

EDIT: Sorry about hijacking this thread, but ... I can't delete my post now and I can't close the thread, so ... I leave this here. Maybe an admin can move this to a new one if necessary.

EDIT2: Can an admin please close this thread again? Since I had to extend my post, I made a new thread now (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14878).

This thread is fairly old, but I seem to have a similar problem as WayneBrantley described. I am working on version 2.6 ...

I have a NULL value in the db for a field. When I read it, it is set to "" in the LGP entity/object. Now I can turn off the setting as Walaa suggested, but I have question about this just to be sure:

This flag will change the behavior of all new entities, or only of entities created from an existing row in the database?

I would like to have the functionality, that if I create a new entity and do not set a value of the a given column AND the column IS NOT allowed to be null, LGP will set the standard value for me. I assume it would do it immediately, cause the db would do it anyway upon inserting the entity into the db, right?

But if I create a new LGP entity by reading it from the db, a value that is NULL should not be changed to an empty string (or any other standard value). Because then obviously the field/column is a NULL column and I want to keep the NULL value.

Continued here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14878