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
    52 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. UI for WinForms is Visual Studio 2017 Ready
  4. Dess
    Admin
    Dess avatar
    1607 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
  5. Dario
    Dario avatar
    61 posts
    Member since:
    Apr 2011

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

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

    What do you think about this one?

  6. 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!
  7. Dess
    Admin
    Dess avatar
    1607 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
UI for WinForms is Visual Studio 2017 Ready