SQL Server nvarchar and unicode

[Dario]

Hi guys,

I have this case: I import a fUTF-8 without BOM file that contains translations of several languages.

My application import using T-SQL command INSERT BULK with DATAFILETYPE = 'widechar' (It needs to import Unicode format)

sql = $"BULK INSERT CaptionFlat FROM '{OpenFileDialog.FileName}' WITH (DATAFILETYPE ='widechar', FIELDTERMINATOR =':',ROWTERMINATOR = '\n')";

 

Afterthat It keeps several operation and INSERT into other table that have a nvarchar field that will contain translation.

This table it will shows by RadGridView with this code

conn = new SqlConnection(Settings.Default.RTK2ConnectionString);

sql = "SELECT CBase.[Key] [Key],\n" +
    "CBase.[Value] Base,\n" +
    $"ISNULL((SELECT [Value] FROM [Captions] WHERE [NAVKey] = CBase.[NAVKey] AND [LanguageID] = {LanguageToTranslateComboBox.SelectedValue}),'[Blank]') ToTranslate\n" +
    "FROM [Captions] CBase\n" +
    "WHERE CBase.[IsCaption] = 1\n" +
    $"AND CBase.LanguageID = {LanguageBaseComboBox.SelectedValue}";

cmd = new SqlCommand(sql, conn);
da = new SqlDataAdapter(cmd);
dtCaptions = new DataTable("Captions");

da.Fill(dtCaptions);

CaptionsRadGridView.DataSource = dtCaptions;

var obj = new ConditionalFormattingObject("BlankTranslation", ConditionTypes.Equal, "[Blank]", "", true);
obj.CellForeColor = Color.Black;
obj.RowBackColor = Color.LightCoral;
CaptionsRadGridView.Columns["ToTranslateColumn"].ConditionalFormattingObjectList.Add(obj);

CaptionsRadGridView.BestFitColumns(Telerik.WinControls.UI.BestFitColumnMode.DisplayedDataCells);

conn.Close();

 

I attach the effects, where the last column shows an error.

The label in language in original file is (for example DE language) "Rack-Jobben (nur für Italien)" but in RadGrid it shows "Rack-Jobben (nur f├╝r Italien)".

 

Can you help me to understand waher I wrong?

 

 

[Andrea]

Hi Dario, I do not think this has to do with the grid, I think the problem is your sql table, cause your bulk insert use 'widechar' that is only good for utf-16.

According to stack overflow accepted answer ( http://stackoverflow.com/questions/5498033/how-to-write-utf-8-characters-using-bulk-insert-in-sql-server ) you can't directly import utf-8 into sql server, also MSDN states "SQL Server does not support code page 65001 (UTF-8 encoding)." Maybe you could translate file to UTF-16, in pseudo code:

rd=StreamReader(inputFilePath, System.Text.Encoding.UTF8); //read an utf8 file

wt=StreamWriter(outputFilePath, System.Text.Encoding.UTF16); //write to an utf16 file

copy characters from input to output, then bulk insert from the utf 16 file into the NVARCHAR column.

Regards

 

[Dess | Tech Support Engineer, Principal]

Hello Dario,

Thank you for writing.
 
I was unable to reproduce the issue you are facing with the latest version. The specific "ü" letter is displayed as expected on my end. I have attached my sample project. Could you please specify the exact steps how to reproduce the problem or what should I modify to reproduce the experienced issue so I can investigate the precise case? Thank you in advance. 

I am looking forward to your reply.

 Regards,
Dess
Telerik

[Dario]

Hello Dess, I think that Andrea says is the correct way.

What do you think about this one?

[Dario]

Thank you Andrea, your suggest is the solution. I modified my code and now it's all correct!

[Dess | Tech Support Engineer, Principal]

Hello guys,

Thank you for writing.
 
@Dario, I am glad that the problem you were facing is now resolved.

@Andrea, I have updated your Telerik points for the community effort.

I hope this information helps. Should you have further questions I would be glad to help.

 

Regards,
Dess
Telerik