SQL Server nvarchar and unicode

6 posts, 1 answers
  1. Dario
    Dario avatar
    61 posts
    Member since:
    Apr 2011

    Posted 14 Dec 2015 Link to this post

    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?

     

     

  2. Answer
    Andrea
    Andrea avatar
    73 posts
    Member since:
    Oct 2012

    Posted 15 Dec 2015 in reply to Dario Link to this post

    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

     

  3. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3816 posts

    Posted 15 Dec 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  4. Dario
    Dario avatar
    61 posts
    Member since:
    Apr 2011

    Posted 15 Dec 2015 in reply to Dess | Tech Support Engineer, Sr. Link to this post

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

    What do you think about this one?

  5. Dario
    Dario avatar
    61 posts
    Member since:
    Apr 2011

    Posted 15 Dec 2015 Link to this post

    Thank you Andrea, your suggest is the solution. I modified my code and now it's all correct!
  6. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3816 posts

    Posted 15 Dec 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top