NHibernate nvarchar / ntext truncation problem

Question

I use nhibernate to store some user preferences for an application in a SQL Server Compact Edition table.

This is a fragment of a mapping file:

<property name="Name" type="string" /> <property name="Value" type="string" /> 

The name is the regular string / nvarchar (50), and the value is set as ntext to DB

I am trying to write a lot of xml for the Value property. I get an exception every time:

 @p1 : String truncation: max=4000, len=35287, value='<lots of xml..../>' 

I worked a bit with it and tried several different matching configurations:

 <property name="Name" type="string" /> <property name="Value" type="string" > <column name="Value" sql-type="StringClob" /> </property> 

This example. Other configurations include "ntext" instead of "StringClob". Those configurations that do not throw an exception to display still throw a line break exception.

Is this a problem ("function") with SQL CE? Is it possible to place more than 4000 characters in a SQL CE database using nhibernate? If so, can someone tell me how?

Thank you very much!

Answer 1

Well, with many thanks to Arthur in this thread , here is the solution: Inherit from SqlServerCeDriver with a new one and override the InitializeParamter method:

 using System.Data; using System.Data.SqlServerCe; using NHibernate.Driver; using NHibernate.SqlTypes; namespace MySqlServerCeDriverNamespace { /// <summary> /// Overridden Nhibernate SQL CE Driver, /// so that ntext fields are not truncated at 4000 characters /// </summary> public class MySqlServerCeDriver : SqlServerCeDriver { protected override void InitializeParameter( IDbDataParameter dbParam, string name, SqlType sqlType) { base.InitializeParameter(dbParam, name, sqlType); if (sqlType is StringClobSqlType) { var parameter = (SqlCeParameter)dbParam; parameter.SqlDbType = SqlDbType.NText; } } } } 

Then use this driver instead of NHibernate in your app.config

 <nhibernateDriver>MySqlServerCeDriverNamespace.MySqlServerCeDriver , MySqlServerCeDriverNamespace</nhibernateDriver> 

I saw many other posts in which people encountered this problem, and solved them simply by changing the sql type attribute to "StringClob" - as I tried in this thread.

I am not sure why this will not work for me, but I suspect that it is a fact that I am using SQL CE and not some other DB. But, you have it!

Answer 2

 <property name="Value" type="string" /> <column name="Value" sql-type="StringClob" /> </property> 

I assume this is a small typo since you double closed the property tag. Just pointing out this if it's not a typo.

Answer 3

Try <property name="Value" type="string" length="4001" />

Answer 4

I tried:

 <property name="Value" type="string" length="4001" /> 

and

 <property name="Value" type="string" > <column name="Value" sql-type="StringClob" length="5000"/> </property> 

Nothing works, I'm afraid ... The same exception - he still says that the maximum value is 4000.

Answer 5

Why are you using subelement syntax?

to try:

 <property name='Value' type='StringClob' /> 

Answer 6

In my current description of SQL CE and NHibernate, I use a length of 4001. NHibernate then generates the material as NTEXT instead of NVARCHAR.

Try it.

Another thing to use with NHibernate and SQL CE:

 <session-factory> ... <property name="connection.release_mode">on_close</property> </session-factory> 

This solves some other problems for me at least.

Answer 7

After reading your post, this modification made it work in my code

 protected override void InitializeParameter(IDbDataParameter dbParam,string name,SqlType sqlType) { base.InitializeParameter(dbParam, name, sqlType); var stringType = sqlType as StringSqlType; if (stringType != null && stringType.LengthDefined && stringType.Length > 4000) { var parameter = (SqlCeParameter)dbParam; parameter.SqlDbType = SqlDbType.NText; } }