0

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; }
}
feeeper
  • 2,865
  • 4
  • 28
  • 42
  • If there are no legacy constraints use nvarchar(max) instead of ntext – Stilgar Nov 23 '14 at 19:52
  • which DB you are using? T-SQL is pointing to a MSSQL but you should give this information. Try to insert without the National prefix. N'текст'--> 'текст'. Also check which collation your database, table AND the columns are set – Joe Platano Nov 23 '14 at 19:54
  • `ntext`, `text`, and `image` data types will be removed in a future version of 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. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Nov 23 '14 at 20:07
  • привет. Have you added a breakpoint in the `PostArticle` method? Is the text correct when this method is called or not? What charset is your $.post() method using - this is probably where it has happened, you have posted in a charset that does not include cyrillic characters. удачи. – JK. Nov 23 '14 at 20:41
  • Database/field collation is, probably, a non-Cyrillic one. In this case you have to make ajax send data as Unicode (there should be a way to do it). – Roger Wolf Nov 23 '14 at 23:36
  • Have you set a breakpoint on the controller's action and check the content of article? Most probably the problem is in this step, and not in the database insertion itself. – JotaBe Nov 24 '14 at 16:36
  • @JotaBe nope, as I said later on server-side (on the controller's action) content of article is ok. – feeeper Nov 24 '14 at 18:34
  • @joemexiko MSSQL. Without N inserted data looks incorrect (question marks for all fields) – feeeper Nov 24 '14 at 18:36
  • @marc_s it's not valuable for now – feeeper Nov 24 '14 at 18:36
  • @JK. привет :) on server-side data is OK. I think that the problem in entity framework... – feeeper Nov 24 '14 at 18:36
  • @RogerWolf I send data as Unicode (Chrome dev tools said so) – feeeper Nov 24 '14 at 18:37
  • @feeeper, Chrome dev tools are good, but what SQL Profiler is saying about it? – Roger Wolf Nov 24 '14 at 23:08
  • @RogerWolf it's a problem. Becouse database located on hosting (not on my PC) and I don's have permissions for profiling queries. – feeeper Nov 25 '14 at 04:36
  • 1
    @feeeper Please, configure the column explicitly as Unicode using fluent API: `modelBuilder.Entity().Property(c=> c.Text).HasColumnType("NTEXT").IsUnicode(true)` on your bd context `OnModelCreating`. You should also install it locally, an try it locally, instead of only in the hosting, so that you can beug it. (BTW how do you know the controller is receiving the rigth text? If it's deployed in a hosting I doubt you can set a break point and examine it...) – JotaBe Nov 25 '14 at 09:10
  • @JotaBe Thanks a lot! It's work! About hosting: database on the hosting but web app on my own pc – feeeper Nov 25 '14 at 11:07
  • @feeeper Please, accept it as correct answer, so that it becomes helpful for other poeple facing the same problem – JotaBe Nov 25 '14 at 12:37

1 Answers1

2

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.

JotaBe
  • 38,030
  • 8
  • 98
  • 117