NTEXT with more than 4000 characters in SQL Server CE in Windows Phone

Question

NTEXT with more than 4000 characters in SQL Server CE in windows phone

I have a database in my windows phone app with a ntext field in one of the tables, I'm trying to write some content to this field, but I get an InvalidOperationException with the message:

String truncation: max=4000, len=4621

I am trying to use ntext because I know that nvarchar doesn't accept more than 4000 chars.

I've searched for a solution but I couldn't find any.

The only solution I found I cannot use on windows phone, because it uses the SqlConnection and SqlCommand with SqlDbType.

Here is how the columns is declared:

    private string _content;
    [Column(DbType="ntext")]
    public string Content
    {
        get
        {
            return _content;
        }
        set
        {
            if (value != _content)
            {
                _content = value;
                NotifyChange(o => o.Content);
            }
        }
    }

I'm inserting it with:

cn.Articles.InsertAllOnSubmit(articlesToSave); 
cn.SubmitChanges();

Does anyone know any workaround?

Thanks for the answers in advance!!

Does CE not support `nvarchar(max)`? If it does that is a far better alternative than `ntext`. — Aaron Bertrand, Jul 06 '12 at 15:29
No, it doesn't support it, at least not in windows phone, It only accepts nvarchar(4000) as the maximum length. — Eric.M, Jul 06 '12 at 15:32
Yes, that's the problem I'm having, it truncates the ntext to 4000 bytes. — Eric.M, Jul 06 '12 at 15:44
Then you might have to break the text up and use two variables. — Aaron Bertrand, Jul 06 '12 at 15:45
Yeah, I started to think about a solution, to have a related table that I could add one or many strings for one article, then, when I had to get the content, I would query all the strings for this article, I will be kind of ugly, but might work. Other thing I thought about is to use xml serialization to store the data instead of the sqlce, I don't know if it will work or if the performance will be fine, but it's a try. — Eric.M, Jul 06 '12 at 15:51
nText (on SQL CE) supports 500 million characters. The problem you are having isn't with CE. — NotMe, Jul 06 '12 at 16:16
@Eric.M hey can you tell me how did you handle this scenario on wp7 — rakesh, Dec 20 '12 at 12:45

score 3 · Accepted Answer · answered Jul 06 '12 at 18:25

I think your column in the actual database file is not ntext, for whatever reason.

This works fine for me:

    using (NorthwindContext ctx = new NorthwindContext(NorthwindContext.ConnectionString))
    {
        ctx.DeleteDatabase();
        ctx.CreateDatabase();
        var category = new Categories();
        category.CategoryName = "Test";
        category.Description = new string('x', 6666);
        ctx.Categories.InsertOnSubmit(category);
        ctx.SubmitChanges();

        var testCat = ctx.Categories.First();
        if (testCat.Description.Length == 6666)
        {
            MessageBox.Show("Works on my Windows Phone");                
        }
    }

Column declaration:

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Description", DbType="NText", UpdateCheck=UpdateCheck.Never)]
        public string Description
        {
            get
            {
                return this._Description;
            }
            set
            {
                if ((this._Description != value))
                {
                    this.OnDescriptionChanging(value);
                    this.SendPropertyChanging();
                    this._Description = value;
                    this.SendPropertyChanged("Description");
                    this.OnDescriptionChanged();
                }
            }
        }

score 2 · Answer 2 · answered Jul 06 '12 at 16:19

ntext supports over 500 million characters, so the problem you have has nothing to do with it. See http://msdn.microsoft.com/en-us/library/ms172424.aspx

You might want to look at the following: http://msdn.microsoft.com/en-us/library/hh202872(v=vs.92).aspx

The list doesn't mention ntext, but it does mention text. I'm going to guess that you might need to provide your own custom formatter.

update

Look at the following hotfix. It covers certain situations when using linq, ce and the ntext data type. Looks like without the fix, the formatter is forcing ntext to be a nvarchar(4000) under the hood. http://support.microsoft.com/kb/958478

When I get home at night, I'll take another look, to see if I did something wrong, that might be the case. — Eric.M, Jul 06 '12 at 16:23
Yeah, I don't think this hotfix will work with the sqlce embedded in the windows phone sdk. — Eric.M, Jul 06 '12 at 18:22

NTEXT with more than 4000 characters in SQL Server CE in Windows Phone

2 Answers2

Linked