Insert NVARCHAR string using LINQ to SQL

Question

Here is the problem. I have a table in a SQL Server database

CREATE TABLE Users 
(
    [userID]        INT             PRIMARY KEY IDENTITY,
    [userName]      NVARCHAR(255)   NOT NULL,
    [userEmail]     VARCHAR(127)    NOT NULL,
    [userComment]   NVARCHAR(2083)  NOT NULL,
    [userLanguage]  VARCHAR(63)     NOT NULL,
    [regTime]       DATE            NOT NULL,
    [hash]          VARCHAR(255)    NOT NULL
);

The specific part I have problems with is NVARCHAR.

I have the following C# code:

UsersDataContext context = new UsersDataContext();
User u = new User() {
    userName = name,
    userEmail = email,
    userComment = story,
    userLanguage = lang,
    hash = hash,
    regTime = System.DateTime.Now
};
context.Users.InsertOnSubmit(u);
context.SubmitChanges();

The problem is that the row inserted into DB looks like this:

????    dbogatov@wpi.edu    ???

Russian (cyrillic) symbols are displaying as ???.

Any ideas how to make DataContext insert proper NVARCHAR?

Any help is appreciated.

Can you make sure that `story` variable has the correct encoded values? — Kundan Singh Chouhan, Apr 30 '15 at 04:04
When you say " the row inserted into DB looks like this", what are you using to examine the DB? — AakashM, Apr 30 '15 at 08:17

score 1 · Answer 1 · edited May 16 '15 at 13:29

1

I solved it! Shame on me for such a stupid mistake.

I turns out that I did not update my .DBML file when I changed my db SCHEME to support NVARCHAR. So Entity Framework still treated my text as VARCHAR.

When I updated it, everything worked.

edited May 16 '15 at 13:29

jessehouwing

106,458
22
256
341

answered May 16 '15 at 13:23

Dmytro Bogatov

776
1
10
23

I changed all VARCHAR to NVARCHAR in stored procedures, tables and views, and (here is the trick!!!) updated my .DBML file. – Dmytro Bogatov May 19 '15 at 18:31

score 0 · Answer 2 · answered Apr 30 '15 at 04:17

0

Try adding character encoding "Charset=utf8" in your database connection string. Should look like below:

<add name="MyEntities" connectionString="metadata=res://*/Metadata;provider=Your Provide;provider connection string=&quot;server=ServerIP;User Id=UID;password=PASS;Persist Security Info=True;database=DB Name; Charset=utf8&quot;" providerName="Provider Name" /></connectionStrings>

Should work in your case.

answered Apr 30 '15 at 04:17

Priyank Sheth

2,352
19
32

Thank you! I did what you said but server gives runtime error. My new connection string looks like this Data Source=SQL5007.Smarterasp.net;Initial Catalog=***;User Id=***;Password=****;Charset=utf-8; With different variations of the words "CharSet" and "UTF-8" What I see is: Description: An exception occurred while processing your request. Additionally, another exception occurred while executing the custom error page for the first exception. The request has been terminated. Are you sure you give me write parameter for MSSQL (not MySQL) string? Thank you! – Dmytro Bogatov May 02 '15 at 02:48
1

Are you using SQL Server? Which version? – Priyank Sheth May 04 '15 at 04:00

score 0 · Answer 3 · answered Apr 30 '15 at 07:02

0

check the Collation of the Database by setting it to the proper value
take a look at this
i expect that this collation is what you need Latin1_General_CI_AI

answered Apr 30 '15 at 07:02

Yaser Jaradeh

322
1
6
27

I tried changing collation for some columns: ALTER TABLE Users ALTER COLUMN userName nvarchar(128) COLLATE Latin1_General_CI_AI NOT NULL; It does not work. Everything is still ??? even if I add new rows. Thank you! – Dmytro Bogatov May 02 '15 at 03:05
@Dima4kaBogatov try changing it for the entire database this happened to me once with Arabic Language – Yaser Jaradeh May 05 '15 at 12:44

Insert NVARCHAR string using LINQ to SQL

3 Answers3