NTEXT with more than 4000 characters in SQL Server

问题:

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!!

回答1:

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();
                }
            }
        }

回答2:

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