Orchard CMS: SqlCeException: The data was truncated while converting from one data type to another.

While working on an Orchard CMS module, I got the following exception:

SqlCeException: The data was truncated while converting from one data type to another.

This happened in my Migrations class:

public class Migrations : DataMigrationImpl {
	public int Create() {
		// Creating table MyPartRecord
		SchemaBuilder.CreateTable("MyPartRecord",
			table =>
				table.ContentPartRecord()
					.Column("MyText", DbType.String));
		return 1;
	}
}

And the definition of my part was:

public class MyPart : ContentPart<MyPartRecord>
{
	[Required]
	public string MyText
	{
		get { return Record.MyText; }
		set { Record.MyText = value; }
	}
}

The problem is that when you use DbType.String without specifying a length, it creates a column of type NVARCHAR(255). And the string I was inserting was longer.

So an obvious solution is to set the length to something larger than 255 e.g.:

public class MyPart : ContentPart<MyPartRecord>
{
	[Required]
	[StringLength(2000)]
	public string MyText
	{
		get { return Record.MyText; }
		set { Record.MyText = value; }
	}
}

But since in my case the string the user enters can be very long and I do not really wanted to set any arbitrary limit, the best solution was to use the unlimited method on the column:

public class Migrations : DataMigrationImpl {
	public int Create() {
		// Creating table MyPartRecord
		SchemaBuilder.CreateTable("MyPartRecord",
			table =>
				table.ContentPartRecord()
					.Column<string>("MyText", x => x.Unlimited()));
		return 1;
	}
}

This way MyText is mapped to a column of type NTEXT and I can write as much text as I want into it (well not exactly but enough so that nobody will ever see the difference).

If you’ve already deployed your Orchard CMS module, you can just add an update method and change the column type:

public int UpdateFrom1()
{
	SchemaBuilder.AlterTable("MyPartRecord",
		table =>
		{
			table.AlterColumn("MyText", x => x.WithType(DbType.String).Unlimited());
		}
	);

	return 2;
}

For your reference, this is how DbType.String is registered in the MsSqlCeDialect in NHibernate:

RegisterColumnType(DbType.String, "NVARCHAR(255)");
RegisterColumnType(DbType.String, 4000, "NVARCHAR($l)");
RegisterColumnType(DbType.String, 1073741823, "NTEXT");

So if you do not specify a length, it will map it to an NVARCHAR(255). If you specify a length up to 4000 characters, it will map it to an NVARCHAR(X) where X is the defined length. If the length is over 4000, it will map it to an NTEXT.

Note that it is the same for the AnsiString:

RegisterColumnType(DbType.AnsiString, "NVARCHAR(255)");
RegisterColumnType(DbType.AnsiString, 4000, "NVARCHAR($l)");
RegisterColumnType(DbType.AnsiString, 1073741823, "NTEXT");

And fixed length strings only support more than 4000 characters:

RegisterColumnType(DbType.AnsiStringFixedLength, "NCHAR(255)");
RegisterColumnType(DbType.AnsiStringFixedLength, 4000, "NCHAR($l)");
...
RegisterColumnType(DbType.StringFixedLength, "NCHAR(255)");
RegisterColumnType(DbType.StringFixedLength, 4000, "NCHAR($l)");

Update: David Hayden has also written an interesting post regarding this topic.

Leave a Reply

Your email address will not be published. Required fields are marked *