Developer forum

Hi Peter,

Currently the definition of itemtypes on the database is very simple and not so optimized with the limitations of SQL Server in mind.

We have already recieved a feature-request to provide the ability to override and/or choose the datatype and size for fields on the database.
When it will be implemented is not planned at the moment I'm afraid.
I have attached your request to the task, so we know it is needed by you.

In the meantime to workaround the issue, I suggest you define the itemtype in Dynamicweb with the text-fields needed, and then directly on the table in the database, change the length of the nvarchar-columns to fit your needs.
It should not give any problems as long as you do not change the datatype itself.

Regarding the exceedance of the max row size for some of the tables in the database, this is something we are aware of.
Eventhough you recieve a warning from the SQL Server, as long as the actual row size do not exceed 8,060 bytes no errors will thrown and indexing and so on should function correctly.

We are constantly working on improving performance and the overall design of the database, and the variable-length column sizes is also something we are looking at, since it can create issues with providing optimal default indexing of the database.

Best regards,
Hans-Henrik

Thanks Hans-Henrik,

I already tried changing some nvarchar(255) fields to nvarchar(50) but that didn't work, they automaticly were changed back to 255.

Or could that be caused by the Itemtypes synchronizations settings under file manager which were set to "Files"? (I changed that to "Database" just a few minutes ago on recommendation by Nicolai).

Hi Peter,

I just tested the different scenarios you are able to set up synchronization settings for itemtypes.
I'm afraid that all of them will update the table schema on the database and overwrite any changes you've made on the database.

That's what I experienced too ...
So there is no real solution/workaround at the moment other than changing the textbox to long text or remove them all together ...
Long text creates an nvarcher(max) which includes a pointer in the row, but not the data. But this control renders a text area in stead of a text field, which is not desirable ...

We do not have a workaround at present other than, as you point out, using the Long Text field type instead.
But it will create a textarea when rendering in the frontend, but you could opt to control the look and feel by CSS and JS making it look an act as a textbox.

I just changed to look of a textarea to look a bit like a textbox with a few lines of CSS

textarea.item-field {
 overflow: hidden; //hides the scrollbars
 max-height: 15px; //restricts the height
}

Thanks Hans-Henrik, I'm hoping the existing feature request will get pickud up soon ;)
 

You're very welcome and hopefully it will - Items are focus area for us.

Hi Hans-Henrik (and Nicolai),

Great, thanks for shharing that! In case someone needs it, here's the code for the editor in C# (you need references to the Dynamicweb.dll and Dynamicweb.Extensibility.dll assemblies)

using Dynamicweb.Content.Items.Editors;
using Dynamicweb.Content.Items.Annotations;

namespace MyItems.ItemEditors
{
  [Editor("Text 100")]
  public class String100Editor : StringEditor
  {
    public String100Editor()
    {
      MaxLength = 100;
    }
  }
}

Imar

OK, I tested this and the add-in works, but this would mean I will get a bunch of nvarchar(max) datatype fields for short text values.
I tried to figure out if there is a way to create a nvarchar(20) or something like that using add-ins, but can't find a way.
Is there a way to achief that in your opinion ??

Hi Peter,

I have taken a look at how the datatype is determined, and item fields with string-based editors are always converted to NVARCHAR(max) column on the table in database.
Only the TextEditor is created as NVARCHAR(255).

Currently there is no way of controlling the length of the NVARCHAR-column, since we do not use the MaxLength property of the editor when synchronizing the table.
And you have no way of overriding the behavior, so you will not be able to workaround it.

But if you don't use columns as predicates in queries against the table on the database, it shouldn't become an issue for you that the datatype is NVARCHAR(max). You will still have the possibility of including the NVARCHAR(max) columns in sql-indexes and thereby improving query-times.