unicode vs non-unicode column types (SQL Server)

Discussion of open issues, suggestions and bugs regarding Entity Developer - ORM modeling and code generation tool
Post Reply
mindplay
Posts: 148
Joined: Tue 13 Dec 2011 22:58
Location: Ithaca, NY

unicode vs non-unicode column types (SQL Server)

Post by mindplay » Wed 04 Jan 2012 21:55

Is there any way to configure column-type conventions?

For example, what causes a String property to be output as a VARCHAR when nothing is entered into the Column/SQLType setting?

In my schema, I need consistently NCHAR/NVARCHAR/NTEXT columns with Unicode support - as opposed to what I currently get, which is CHAR/VARCHAR/TEXT columns.

I would be happy simply having a configurable setting globally for the entire Model, e.g. a boolean setting like "Use Unicode SQL Types". (I believe all modern RDBMS at this point have support for unicode string-types in some form or another?)

I would be less than happy if I have to manually go through 1000+ properties and set the SQL column-type on every single property. I could hack in a convention, by updating the SQL-type from the code-generation template; I've used this approach for certain things, but it feels hacky and kills my ability to override the SQL-type in edge-cases, so it's a bit scary...

(alternatively, perhaps the Model ought to have the reverse version of Tools -> Options -> Type Mapping Rules... here I can customize how server-specific DBMS types are mapped to .NET types, but it appears the reverse mapping from .NET types to server-types is hard-coded?)

mindplay
Posts: 148
Joined: Tue 13 Dec 2011 22:58
Location: Ithaca, NY

Post by mindplay » Wed 04 Jan 2012 22:35

For that matter, I just discovered that DateTime is being output as the SQL-type DATETIME - but I need DATETIME2 in the schema for my application.

Again, I'd rather not have to hack the template or repeatedly type in DATETIME2 throughout the Model to achieve this - so this may be an indication that a simple Unicode option is not what's needed here, but more likely a configurable facility for SQL-type conventions.

(the DATETIME type is actually not generally recommended for use at all - according to SQL Server 2008 documentation, it was superseded by the DATETIME2 type.)

Helen
Devart Team
Posts: 127
Joined: Wed 07 Sep 2011 11:54

Post by Helen » Thu 05 Jan 2012 12:42

mindplay wrote:In my schema, I need consistently NCHAR/NVARCHAR/NTEXT columns with Unicode support - as opposed to what I currently get, which is CHAR/VARCHAR/TEXT columns.

You must set the Unicode property of a column to true to use Unicode SQL types.
mindplay wrote:alternatively, perhaps the Model ought to have the reverse version of Tools -> Options -> Type Mapping Rules... here I can customize how server-specific DBMS types are mapped to .NET types, but it appears the reverse mapping from .NET types to server-types is hard-coded?

We allow custom mapping of SQL types to .NET types, but reverse mapping is hard-coded for now.
mindplay wrote:For that matter, I just discovered that DateTime is being output as the SQL-type DATETIME - but I need DATETIME2 in the schema for my application.
We will change the behaviour so that our hard-coded mapping will generate DATETIME2 instead of DATETIME for DateTime fields.
This changed behaviour will be available in the next build. We will inform you when the corresponding build is available.

mindplay
Posts: 148
Joined: Tue 13 Dec 2011 22:58
Location: Ithaca, NY

Post by mindplay » Thu 05 Jan 2012 16:39

Is there a projected release-date?

(I can work around the DATETIME issue for now, by doing a search/replace prior to running a database update, but it's not very practical...)

mindplay
Posts: 148
Joined: Tue 13 Dec 2011 22:58
Location: Ithaca, NY

Post by mindplay » Thu 05 Jan 2012 19:08

Something occurs to me with regards to unicode.

You have the Unicode property on the Column object.

Presumably, this setting only has any effect when SQL-type is left empty.

And presumably, the only property-type for which this has any effect, is System.String.

If so, that means that the scope of the Unicode property is identical to that of SQL-type. Or in other words, if you're going to set Unicode to true, you might as well set SQL-type to NVARCHAR instead - it doesn't give you anymore or any less control over the generated schema, so it really is rather redundant.

The only real convenience of the Unicode property, is in terms of being able to set Unicode to true, rather than typing in "NVARCHAR".

It makes it seem rather pointless. Especially when taking into account the fact that System.String does implement a Unicode string - while the Unicode property is False by default.

I don't have a real good suggestion on how to improve or fix this at the moment, but it's something to think about...

Helen
Devart Team
Posts: 127
Joined: Wed 07 Sep 2011 11:54

Post by Helen » Tue 10 Jan 2012 11:20

Sorry for the delay.
mindplay wrote: Is there a projected release-date?
We plan to release the nearest build in a week.
mindplay wrote: I would be happy simply having a configurable setting globally for the entire Model, e.g. a boolean setting like "Use Unicode SQL Types". (I believe all modern RDBMS at this point have support for unicode string-types in some form or another?)
Thank you for your suggestions.
We will consider the possibility of implementing such functionality. But we cannot provide any timeframe at the moment.

Helen
Devart Team
Posts: 127
Joined: Wed 07 Sep 2011 11:54

Post by Helen » Thu 12 Jan 2012 15:48

The DATETIME2 data type is now used for columns, corresponding to System.DateTime properties, for Microsoft SQL Server 2008 and later.

This fix is available in the latest 4.2.110 build of Entity Developer.
The new build of Entity Developer can be downloaded from http://www.devart.com/entitydeveloper/download.html (the trial and free versions) or from Registered Users' Area (provided that you have an active subscription).

For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23135 .

mindplay
Posts: 148
Joined: Tue 13 Dec 2011 22:58
Location: Ithaca, NY

Post by mindplay » Mon 16 Jan 2012 20:36

The DATETIME2 type fix is working nicely, thank you.

Please disregard my previous comments on the whole Unicode vs non-Unicode issue - I have since realized that there are in fact practical reasons to consider the character-format on a by-column basis.

In case anyone else reads this and wonders, here's how I make the choice myself: I distinguish between "user content" and "implementation detail" string columns. If the column holds content populated by the user, such as names and addresses, I consider that "user content", and in that case I use Unicode, so that accents etc. can be properly encoded in international person and company names. As opposed to columns that holds application-generated values, such as discriminators, I consider those an "implementation detail", for which there is no expectation to user international characters (at least not in my application...)

Helen
Devart Team
Posts: 127
Joined: Wed 07 Sep 2011 11:54

Re: unicode vs non-unicode column types (SQL Server)

Post by Helen » Tue 08 May 2012 09:51

The possibility to specify default facets for the columns is implemented. We will notify you when the corresponding build of Entity Developer is available for download.

Helen
Devart Team
Posts: 127
Joined: Wed 07 Sep 2011 11:54

Re: unicode vs non-unicode column types (SQL Server)

Post by Helen » Wed 23 May 2012 12:53

This feature is available in the latest 4.4.186 build of Entity Developer.
The new build can be downloaded from http://www.devart.com/entitydeveloper/download.html (the trial and free versions) or from Registered Users' Area (provided that you have an active subscription).

For the detailed information about the improvements and fixes available in Entity Developer 4.4.186, please refer to http://forums.devart.com/viewtopic.php?t=24187

Post Reply