SqlBulkCopy Unicode/UTF8 error

Question

I am using c# SqlBulkCopy functionality to save records with UTF8 encoded multilingual values. My database field is 'NVARCHAR(max)' and my to be saved value is 我多言語で我. But it is saving as '???????' . Is there any way to save this exact Japanese/Multilingual value via SqlBulkCopy.

row["ParameterValue"] = RegData.ParamValue;

I debugged & checked the above line also. But data is coming to above line also totally fine. After this line i am executing

bulkCopy.WriteToServer(dataTable);

(I have tried this with simple db insert & its working fine. but i have millions records to be saved at once. so simple db insert is not good for me)

Peter Wishart · Accepted Answer · 2014-05-26T13:47:40.073

'???????' is the result I'd expect from:

select cast(N'我多言語で我' as varchar(max))

Inserting to a NVARCHAR column via a newly created DataTable with a string column seems to work for me...

If you run a SQL profiler trace you should see an insert bulk ... statement with the datatype being used for the bulk dataset (n.b. you won't see the values).

If this and the target field are the correct NVARCHAR type, maybe there's some server side processing occuring?

Edit: the following works for me

    // insert code to create SqlConnection
    var dbName = string.Format("Test{0}", Environment.TickCount);
    var command = connection.CreateCommand();
    command.CommandText = string.Format("create database {0}", dbName);
    command.ExecuteNonQuery();
    command.CommandText = string.Format("use {0}", dbName);
    command.ExecuteNonQuery();
    try
    {
        command.CommandText = "create table Test (Value nvarchar(max))";
        command.ExecuteNonQuery();

        var dataTable = new DataTable();
        dataTable.Columns.Add("Value", typeof(string));

        var row = dataTable.NewRow();
        row["Value"] = "我多言語で我";
        dataTable.Rows.Add(row);

        var sqlBulkCopy = new SqlBulkCopy(connection);
        sqlBulkCopy.DestinationTableName = "Test";
        sqlBulkCopy.WriteToServer(dataTable);

        Console.WriteLine("Please check the following query:");
        Console.WriteLine(string.Format("select * from {0}..Test", dbName));
        Console.ReadKey();                
    }
    finally
    {
        command.CommandText = string.Format("drop database {0}", dbName);
        command.ExecuteNonQuery();
    }

Thankz for your comment Peter. But i have used nvarchar. it works fine for nvarchar. Also as i have see all the server side processing & all are fine — Gayan Dinuzhka, May 26 '14 at 13:27
I've added the example code I used. AFAIK if you are setting the correct unicode into the dataTable row and the target column is `NVARCHAR`, the trace looks correct and with no triggers/views involved, there isn't much that could go wrong... — Peter Wishart, May 26 '14 at 13:53
This is exactly what I just overlooked. I had neglected to use `NVARCHAR` types in the database, and was trying to figure out why `SqlBulkCopy` was not working with the unicode data, when it was actually my own error. — Siddhartha Gandhi, Jun 02 '21 at 06:12
Just use nvarchar as your destination column type. It should solve the problem — Atibur Rahman, Apr 21 '22 at 16:24

SqlBulkCopy Unicode/UTF8 error

1 Answers1