Can not correctly insert cyrillic symbols into NTEXT field in SQL Server database

1.5k views Asked by At

How can I insert cyrillic symbols into my database?

I have table Articles and Web API controller with CRUD operations.

Articles { id: int, summary: nvarchar, text: ntext }

If I insert data using t-sql like

insert into Articles values (1, N'текст', N'еще много текста')

all works fine - data in database is ok - all russian symbols looks good.

But if I insert data using ajax field 'text' filled by question marks: '??? ????? ??????'

$.post("http://localhost:1000/api/articles", 
  {
    id: 1,
    summary: "текст",
    text: "еще много текста"
  }
).always(function(res) {console.log(res)} );

Where is my mistake?

PS: connectionString if it need:

<add name="DefaultConnection" connectionString="Data Source=xxx;Initial Catalog=yyy;User ID=zzz;Password=***;" providerName="System.Data.SqlClient"/>

PPS: I'm using deafult autogenerated POST action from WebAPI:

// POST: api/Articles
[ResponseType(typeof(Article))]
public IHttpActionResult PostArticle(Article article)
{
  if (!ModelState.IsValid)
  {
    return BadRequest(ModelState);
  }

  db.Articles.Add(article);
  db.SaveChanges();

  return CreatedAtRoute("DefaultApi", new { id = article.ArticleId }, article);
}

UPD:

[DataContract]
public class Article
{
  [DataMember]
  public int Id { get; set; }

  [DataMember]
  public string Summary { get; set; }

  [DataMember]
  [Column(TypeName = "ntext")]
  public string Text { get; set; }
}
1

There are 1 answers

0
JotaBe On BEST ANSWER

The problem is that you must be very explicit when you want to map NTEXT columns with EF Code First: you must be very explicit, and configure the column to be of the desired type, but you must also configure it to use Unicode. To do so you can use the Fluent API configuration, for example overriding the OnModelCreating() with a code like this:

modelBuilder
  .Entity<Aticle>()         // Choose the entity to configure
  .Property(c=> c.Text)     // Choose the property to configure
  .HasColumnType("NTEXT")   // Specify the DB type
  .IsUnicode(true);         // Specify Unicode (for EF internally generated params)

However, no wonder there is a problem with NTEXT. From ntext, text, and image (Transact-SQL) docs:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.