LLBLGen Pro 2.5 SelServicing

2 Classes

Is there any way to get LLBLGen to automatically trim char fields both when storing and retrieving? It's a big hassle having to do this manually with everything. If I don't, then when I populate a text field with a value from the db, it has a lot of trailing spaces and if the user puts the curser after the last non-space character and types, then the string is too big for the field in the table and when I save I get an error.

TIA

This seems to work. It gets applied to all string fields of all my entities in one place. Does anyone see any unexpected problems here? I'm not sure that doing a string comparison in the "if" statement is the most efficient though.

public partial class CommonEntityBase
{
    protected override void OnFetchComplete()
    {
        for(int fieldIndex = 0; fieldIndex < this.Fields.Count; fieldIndex++)
            if (this.Fields[fieldIndex].DataType.Name == "String")
            {
                bool OldDirty = this.IsDirty;
                this.SetValue(fieldIndex, ((string)this.Fields[fieldIndex].DbValue).Trim(), false);
                this.IsDirty = OldDirty;
            }
        base.OnFetchComplete();
    }
}

Your solution looks good if ALL string fields should be trimmed. You also can opt for use VARCHAR instead of CHAR types (I guess you're using CHAR data types).

You could also do a custom TypeConverter class and then assign this type converter to your fields in the designer. This would be the best solution I think.

I'd recommend the following solutions in order:

1- Handle this in the database, as it sounds like a database design issue, if you are using a dataType that you don't want or need, so better to use VARCHAR instead of CHAR.

2- Use a TypeConverter.

3- Use your solution, but I think you should be updating the CurrentValue, that's what the user deal with in the UI (read & modify).

Ofcourse I had to encounter this issue also

If I would go for changing the char in varchar in the database, do I have to regenerate the LLBLGen code? Or does it work on the fly?

Jan

Anytime you change your database model, you must refresh and rebuild LLBLGen to have those changes in effect in your code.

CSharpner - what did you end up doing?

OK, tried the change.

When I change the CHAR(5) into VARCHAR(5) the data itself keeps the trailing blanks. Apparently trailing blanks are significant in VARCHAR. I was expecting that this would not be te case.

I would opt for a feature in designer which allows the developper the possibility to specify that the data should be trimmed on retrieval. For the update this is irrelevant since the database automatically pads the data with spaces. (According to me the trimming could also be automatic in case of CHAR fields.)

In another thread there was a discussion going on when to use VARCHAR or when to use CHAR. I am not an expert in SQL Server, but in other DBMS systems a VARCHAR of e.g 5 bytes obtains more space than a CHAR of 5 bytes because the VARCHAR field itself has information about the length of the data. For the CHAR field this information is retrieved from the scheme definition and not part of the data. Therefore I think that CHAR should be prefered for short fields.

Jan

I use varchar everywhere, I do not have trailing blanks.

Perhaps when you converted the column to varchar from char - the blanks are still in it?

you could try this and see if anything changes... update mytable set myfield=trim(myfield)

For the time being I decided to add trim() in the situations where I compare a value in the database with a string-constant.

I ended up changing my chars to VarChars. Not entirely comfortable with that though as theoretically, indexing is slower and it of course, length bytes. If I've got fields where I know most rows will have say 6 characters, but a few may be smaller, it's more efficient to use char than varchar. Char would end up using less space (no leng bytes), and indexing would be more efficient too. But of course, char pads with spaces, causing problem of this topic.

I don't think switching types is the right answer in every case for a total and final solution. Both types exist for specific reasons, each with their own advantages and disadvantages. I would prefer that SQL Server not pad chars with spaces, and short of that, I'd like to see LLBLGen support automatic trimming of chars.

Oh! I forgot to mention: Changing the types in a database is RARELY an option. I did that for my personal stuff, but at the office, I'll never be able to justify making a huge database change just to support one of our development tools. For existing databases, there are many times MANY applications that use the database. Making a structural change is a SIGNIFICANT undertaking.

For those databases where making a structural change is not an option (most of the time), then I have to resort to either manually trimming everywhere (a very error prone and cumbersom process), or I'll have to get back to making my own solution by extending the generated classes.

Sure would be nice is this were an LLBLGen feature!

CSharpner, It does directly support this. You make a type converter that trims the fields, which would take all of 15 minutes and then you can specify in the designer which fields to trim. There is also the option to apply your type converter to ALL string fields...

Perhaps Otis could simply consider shipping that as a built in type converter like bit to boolean that users can choose - if it is that common of a problem.

Perhaps Otis could simply consider shipping that as a built in type converter like bit to boolean that users can choose - if it is that common of a problem.

I vote for that! Otis, make it so! :-o

You got my support also!

Actually, trimming char fields is bad practise. The spaces belong to the value, or 'could' belong to the value. If you want autotrimming of fields, use varchar, this is the type to avoid space padding by the DB. Converting char to varchar leaves the padded spaces, so that's not going to help much.

Some databases use auto-trimming when converting the db value to a .net type, e.g. oracle (and I think sqlserver) do that if I'm not mistaken. (so string s = myEntity.Name;, -> s isn't padded with spaces)

So if you want to autotrim and you can't change the db type, write a simple type converter, it's very simple. Then, create a Type Conversion definition for char typed fields and use llblgen to auto-assign the type converter to every field of that type, or use the plugin to auto-assign the typeconverter to fields.

The type converter should have as base type 'string', and simply only convert strings. In the convert from method you trim, in the convert to method you simply return the string value as passed in. Shouldn't be more than a couple of lines of code.

The thing with shipping this is that we do encourage trimming string values, and I don't think that's a good idea, plus there are a lot of other ideas for type converters possible (string to xmldoc, byte[] to bmp etc. )which we also don't ship. The boolean converter is in the installer because it makes it possible to use a single application on multiple databases, because the boolean type is one of the few (of not the only one) types which isn't supported by all databases.