Home > SQL Server > CodeFluent Entities And SQL Server Collation

CodeFluent Entities And SQL Server Collation


Collations in Microsoft SQL Server control physical storage of character strings, and a point of interest is that using collations you control whether ‘m‘ is equal or not equal to ‘M‘.

For instance, say I have a table with a "Label" column containing the value "Car", if your current collation is case insensitive, running the following query will match our "Car" value:

SELECT *
  FROM [Test]
  where Label like ‘ca%’

Now if you modify your collation using this query:

ALTER TABLE [Test]
ALTER COLUMN [Label] nvarchar(256) COLLATE Latin1_General_CS_AI

Note: "Latin1_General" is the collation designator, "CS" activates CaseSensitivity, "AI" disables accent sensitivity. More information available on those pages: COLLATE, Windows Collation Name.

And run the same query again, our "Car" value won’t match anymore as it is case sensitive.

Now back to CodeFluent Entities 🙂
In CodeFluent Entities, the SQL Server Producer provides attributes at project, entity and property levels to set your desired collation name.
Example:

<Test>
  <Id />
  <Name />
  <Label cfps:collationName="Latin1_General_CS_AI" />
</Test>

Note: "cfps" corresponds to the SQL Server Producer namespace, declared on the Project element like ‘xmlns:cfps="http://www.softfluent.com/codefluent/producers.sqlserver/2005/1"’

And this will generate the following code:

[Test_Label] [nvarchar] (256) COLLATE Latin1_General_CS_AI NULL

As mentioned, you can also specify it at project or entity level so it’s automatically added for all appropriate properties:

<Test cfps:defaultCollationName="Latin1_General_CS_AI">
  <Id />
  <Label />
  <Name />
</Test>

This will generate the following in your table script:

[Test_Label] [nvarchar] (256) COLLATE Latin1_General_CS_AI NULL,
[Test_Name] [nvarchar] (256) COLLATE Latin1_General_CS_AI NULL,

Carl Anderson

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s