NHibernate - prepare_sql and some considerations on mapping very long string fields

Recently I’ve switched some of my applications from NHibernate 2.1.x to NHibernate 3, everything was working fine until I encountered a ‘strange behavior’ with the mapping of long string fields (those that should be mapped to nvarchar(max) and varchar(max)...yes I use Microsoft Sql Server as my database engine): using the standard mapping the field are correctly mapped to nvarchar(max), but during the saving operation the data gets truncated.

The trouble arise due to some small modifications to the SqlClinetDriver that were made to optimize the reuse of the query plan Sql Server will generate, you can enable the same feature in NHibernate 2.1.x setting the ‘prepare_sql’ configuration option to true.

Basically the problem is in the way the parameters of the SqlCommand are generated: without using prepare_sql=true the length of the string parameter is computed on the the data you pass in (ie: ‘aaa’ is represented by string(3)) and this is limiting the database engine capability of reusing query plans (more info in a link later on); but you can map an nvarchar(max) column like this:

<property name="StringHugeLength" column="StringHugeLength" type="string" length="10000" />
and everything works as expected.

The following commented piece of mapping sums up the behavior I’ve noticed when using prepare_sql=true:

Read more: PrimordialCode

Posted via email from .NET Info